[MDEV-3733] LP:960587 - Bit Field Causing Issues with Inner Join Created: 2012-03-20  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: DaveFM (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug960587.xml    

 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

 Comments   
Comment by DaveFM (Inactive) [ 2012-03-20 ]

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 );
------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------+

1 SIMPLE category const PRIMARY PRIMARY 4 const 1  
1 SIMPLE string ref category category 4 const 391 Using where

------------------------------------------------------------------------------+

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 );
----------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

----------------------------------------------------------------------+

1 SIMPLE category const PRIMARY PRIMARY 4 const 1  
1 SIMPLE string ref category category 4 const 391  

----------------------------------------------------------------------+
2 rows in set (0.00 sec)

Comment by Elena Stepanova [ 2012-03-20 ]

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.

Comment by Elena Stepanova [ 2012-03-21 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2012-03-21 ]

Launchpad bug id: 960587

Generated at Thu Feb 08 06:50:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.