Bug #36728 | (LEFT) JOIN not working correctly | ||
---|---|---|---|
Submitted: | 14 May 2008 23:52 | Modified: | 16 May 2008 13:40 |
Reporter: | Peter Romianowski | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Federated storage engine | Severity: | S1 (Critical) |
Version: | 5.0.51a-3ubuntu5-log | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[14 May 2008 23:52]
Peter Romianowski
[15 May 2008 4:51]
Valeriy Kravchuk
Thank you for a problem report. Please, send the wrong results you get with FEDERATED. For me the results are the same on 5.0.60, with both local InnoDB and FEDERATED table: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.0.60-enterprise-gpl-nt MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `eem_unsubscribe` ( -> `lid` int(10) unsigned NOT NULL, -> `lmailinggroup` int(10) unsigned NOT NULL, -> `lmailingtouser` int(10) unsigned default NULL, -> `lmailing` int(10) unsigned default NULL, -> `luserlist` int(10) unsigned default NULL, -> `szuserid` varchar(255) NOT NULL, -> `istatus` tinyint(4) default NULL, -> `szstatushistory` varchar(255) default NULL, -> `dcreated` datetime NOT NULL, -> PRIMARY KEY (`lid`), -> KEY `mailinggroup_status_userid_userlist` -> (`lmailinggroup`,`istatus`,`szuserid`,`luserlist`), -> KEY `mailing_status_userid` (`lmailing`,`istatus`,`szuserid`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.14 sec) mysql> CREATE TABLE `eem_unsubscribe_fed` ( -> `lid` int(10) unsigned NOT NULL, -> `lmailinggroup` int(10) unsigned NOT NULL, -> `lmailingtouser` int(10) unsigned default NULL, -> `lmailing` int(10) unsigned default NULL, -> `luserlist` int(10) unsigned default NULL, -> `szuserid` varchar(255) NOT NULL, -> `istatus` tinyint(4) default NULL, -> `szstatushistory` varchar(255) default NULL, -> `dcreated` datetime NOT NULL, -> PRIMARY KEY (`lid`), -> KEY `mailinggroup_status_userid_userlist` -> (`lmailinggroup`,`istatus`,`szuserid`,`luserlist`), -> KEY `mailing_status_userid` (`lmailing`,`istatus`,`szuserid`) -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 -> CONNECTION='mysql://root:root@localhost:3308/test/eem_unsubscribe'; Query OK, 0 rows affected (0.47 sec) mysql> CREATE TABLE `eem_recipients_test` ( -> `luserlist` int(10) unsigned NOT NULL, -> `szemail` varchar(255) NOT NULL default '', -> `szfirstname` varchar(255) default '', -> `szlastname` varchar(255) default '', -> `szstreet` varchar(255) default '', -> `szzipcode` varchar(255) default '', -> `szcity` varchar(255) default '', -> `dbirthday` datetime default NULL, -> `dcreated` datetime default NULL, -> `dmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURR ENT_TIMESTAMP, -> PRIMARY KEY (`luserlist`,`szemail`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO `eem_unsubscribe` (`lid`, `lmailinggroup`, `lmailingtouser`, `lmailing`, -> `luserlist`, `szuserid`, `istatus`, `szstatushistory`, `dcreated`) VALUES -> (12011, 12000, 0, 0, 12008, '1@optivo.de', 0, NULL, '2008-05-15 01:38:4 1'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO `eem_recipients_test` (`luserlist`, `szemail`, `szfirstname`, `szlastname`, -> `szstreet`, `szzipcode`, `szcity`, `dbirthday`, `dcreated`, `dmodified`) VALUES -> (12008, '1@optivo.de', NULL, NULL, NULL, NULL, NULL, NULL, '2008-05-15 01:38:41', -> '2008-05-15 01:38:41'), -> (12008, '2@optivo.de', NULL, NULL, NULL, NULL, NULL, NULL, '2008-05-15 01:38:41', -> '2008-05-15 01:38:41'), -> (12008, '3@broadmail.de', NULL, NULL, NULL, NULL, NULL, NULL, '2008-05- 15 01:38:41', -> '2008-05-15 01:38:41'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT -> usr.szemail -> FROM -> eem_recipients_test usr -> LEFT JOIN eem_unsubscribe u ON -> u.lmailinggroup=12000 -> AND u.istatus=0 -> AND u.szuserid=usr.szemail -> WHERE -> usr.luserlist=12008 -> AND u.lid IS NULL; +----------------+ | szemail | +----------------+ | 2@optivo.de | | 3@broadmail.de | +----------------+ 2 rows in set (0.03 sec) Now with FEDERATED: mysql> SELECT -> usr.szemail -> FROM -> eem_recipients_test usr -> LEFT JOIN eem_unsubscribe_fed u ON -> u.lmailinggroup=12000 -> AND u.istatus=0 -> AND u.szuserid=usr.szemail -> WHERE -> usr.luserlist=12008 -> AND u.lid IS NULL; +----------------+ | szemail | +----------------+ | 2@optivo.de | | 3@broadmail.de | +----------------+ 2 rows in set (0.61 sec)
[15 May 2008 12:25]
Peter Romianowski
Hi Valeriy, I get: szemail 1@optivo.de 2@optivo.de 3@broadmail.de I have tried to put the source table for the federated into the same database to see if something changes. But it didn't. Currently I am not able to try this on a 5.0.60 but will later on.
[15 May 2008 15:08]
Valeriy Kravchuk
Please, inform about your experience with versions newer than 5.0.51, when you'll get it.
[15 May 2008 16:19]
Peter Romianowski
Same on 5.0.50sp1a-enterprise-gpl. On 5.0.54-enterprise-gpl-log (Linux bm-64-09 2.6.18.2-34-default #1 SMP Mon Nov 27 11:46:27 UTC 2006 x86_64 x86_64 x86_64 GNU/Linux) I get *NO* result until I change eem_unsubscribe.istatus to TINYINT NOT NULL). 5.0.60 to follow...
[15 May 2008 17:23]
Sveta Smirnova
Thank you for the report. Bug is not repeatable since version 5.0.60, although it is repeatable with earlier versions. So I close the report as "Can't repeat"
[16 May 2008 9:27]
Peter Romianowski
Closing it as "Can't repeat" is ok from your POV. But since the latest community edition is 5.0.51 community users will suffer from this bug and might not see that it is there since the bug is closed. Anyway, I will grab the 5.0.60 enterprise version and give it a try.
[16 May 2008 13:40]
Peter Romianowski
I just tested with 5.0.60 and yes - it works.