Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Not a Bug
-
None
-
None
-
None
Description
SELECT `category`.`keyword` AS `category_keyword`, `string`.`keyword` AS `string_keyword`, `string`.`string` AS `string_string` FROM `application_i18n_category` AS `category` LEFT JOIN `application_i18n_string` AS `string` ON `category`.`id` = `string`.`category` AND `string`.`disabled` = 0 WHERE `category`.`id` IN ( 9757 );
Query returns 1 result on mariaDB, same query returns 28 results on mysql 5.5
Removing the AND `string`.`disabled` = 0 returns the same amount of results on both mysql and mariadb
a count on both returns identical results.
MySQL
mysql> select count
from application_i18n_string where disabled=0;
----------
| count |
----------
| 335895 |
----------
MariaDB [sabretooth]> select count from application_i18n_string where disabled=0;
----------
| count |
----------
| 335233 |
----------
show create table application_i18n_category;
CREATE TABLE `application_i18n_category` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`appid` int(11) unsigned NOT NULL,
`keyword` varchar(30) NOT NULL,
`disabled` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`id`),
KEY `appid` (`appid`,`disabled`,`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=12272 DEFAULT CHARSET=utf8
show create table application_i18n_string;
CREATE TABLE `application_i18n_string` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category` int(11) unsigned NOT NULL,
`keyword` varchar(30) NOT NULL,
`language` varchar(2) NOT NULL,
`string` text NOT NULL,
`disabled` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`id`),
KEY `category` (`category`,`keyword`,`language`,`disabled`)
) ENGINE=InnoDB AUTO_INCREMENT=934209 DEFAULT CHARSET=utf8
Attachments
Activity
Re: Bit Field Causing Issues with Inner Join
As discussed on IRC, the query on the uploaded data returns 1 row both on MariaDB 5.5.21 and MySQL 5.5.21, and it looks correct, considering the contents. David is going to provide another test case to reproduce the problem.
Re: Bit Field Causing Issues with Inner Join
As further discussed on IRC, the mismatch between MySQL (master) and MariaDB (slave) was caused by a problem in replication setup.
Re: Bit Field Causing Issues with Inner Join
MariaDB [sabretooth]> explain SELECT `category`.`keyword` AS `category_keyword`, `string`.`keyword` AS `string_keyword`, `string`.`string` AS `string_string` FROM `application_i18n_category` AS `category` LEFT JOIN `application_i18n_string` AS `string` ON `category`.`id` = `string`.`category` AND `string`.`disabled` = 0 WHERE `category`.`id` IN ( 9757 );
-----
-------------------------------------------------------------------------+-----
-------------------------------------------------------------------------+-----
-------------------------------------------------------------------------+mysql> explain SELECT `category`.`keyword` AS `category_keyword`, `string`.`keyword` AS `string_keyword`, `string`.`string` AS `string_string` FROM `application_i18n_category` AS `category` LEFT JOIN `application_i18n_string` AS `string` ON `category`.`id` = `string`.`category` AND `string`.`disabled` = 0 WHERE `category`.`id` IN ( 9757 );
---
-------------------------------------------------------------------+---
-------------------------------------------------------------------+---
-------------------------------------------------------------------+2 rows in set (0.00 sec)