CODEWITHSUNDEEP
mysqlinnodbmyisam
Bentaye
Bentaye

Reputation: 9766

SQL query evaluates COUNT(*) differently if tables are defined as MyISAM or InnoDB

I am running a MySQL database.

I have the following script:

DROP TABLE IF EXISTS `org_apiinteg_assets`;
DROP TABLE IF EXISTS `assessmentinstances`;

CREATE TABLE `org_apiinteg_assets` (
  `id` varchar(20) NOT NULL default '0',
  `instance_id` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE= MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

CREATE TABLE `assessmentinstances` (
  `id` varchar(20) NOT NULL default '0',
  `title` varchar(180) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE= MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;


INSERT INTO assessmentinstances(id, title) VALUES ('14026lvplotw6','One radio question survey');
INSERT INTO org_apiinteg_assets(id, instance_id) VALUES ('8kp9wgx43jflrgjfe','14026lvplotw6');

Looks like this

assessmentinstances
+---------------+---------------------------+
|        id     |          title            |
+---------------+---------------------------+
| 14026lvplotw6 | One radio question survey |
+---------------+---------------------------+

org_apiinteg_assets
+-------------------+---------------+
|        id         |  instance_id  |
+-------------------+---------------+
| 8kp9wgx43jflrgjfe | 14026lvplotw6 |
+-------------------+---------------+

And I then have the following query (I reduced it to the simplest failing query)

SELECT ai.id, COUNT(*) AS `count` 
FROM assessmentinstances ai, org_apiinteg_assets a 
WHERE a.instance_id = ai.id 
AND ai.id = '14026lvplotw6'
AND a.id != '8kp9wgx43jflrgjfe';

When I run the query I get this 

null, 0

Until now, all is good. Now, here is my issue, when I recreate both tables with ENGINE=InnoDB instead of ENGINE=MyISAM and run the same query again, I get this:

'14026lvplotw6','0'

So 2 things are confusing me:

I am lost, I'd appreciate if anybody could explain this behaviour to me.

EDIT: Interestingly, if I add GROUP BY ai.id at the end of the query, it works fine in both cases and return no rows.

Upvotes: 3

Views: 143

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

This happen because you are using aggregation function without GROUP BY .. in this case the result for non aggregated column is unpredictable .. (typically is show the first value encountered during the query)

Try adding a GROUP BY 

SELECT ai.id, COUNT(*) AS `count` 
FROM assessmentinstances ai, org_apiinteg_assets a 
WHERE a.instance_id = ai.id 
AND a.id != '8kp9wgx43jflrgjfe'
AND ai.id = '14026lvplotw6' 
GROUP BY  ai.id;

Remember that the use of aggregation in presence of column not mentioned in group by is deprecated in SQL and is not allowed in most of the db and in the more recent version of mysql (starting from 5.7)

Upvotes: 3

Grzegorz Adam Kowalski
Grzegorz Adam Kowalski

Reputation: 5575

EXPLAIN SELECT for MyISAM returns: Impossible WHERE noticed after reading const tables. So MyISAM isn't processing any data at all.

For the InnoDB there are two rows of EXPLAIN results: one Using Index and one Using where. So InnoDB data is being scanned and bits of it slip into the output as there is no aggregate function specified for the first column and AFAIK its not specified what should happen in such situation. If you directly specify some aggregate function, then if there are no matching rows, it will return NULL. So, for example, SELECT min(ai.id), COUNT(*) ... would return NULL, 0.

Upvotes: 1

Related Questions