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:
None 
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
Description:
Having two tables, one local and one federated, a LEFT JOIN between these two returns wrong results.

How to repeat:
Two tables:

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=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://dev@localhost:3306/another_db/eem_unsubscribe';

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 CURRENT_TIMESTAMP,
  PRIMARY KEY  (`luserlist`,`szemail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Using this data:

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:41');

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

This does not work:

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

Using a derived table does not work either:

SELECT 
    usr.szemail 
FROM 
    eem_recipients_test usr 
WHERE 
    usr.luserlist=12008 
    AND szemail NOT IN (
        SELECT 
            szuserid 
        FROM 
            eem_unsubscribe u
        WHERE
            u.lmailinggroup=12000
            AND u.istatus=0
    )

(Removing the "u.istatus=0" condition makes the query work, btw. But obviously this is not intended.)

Each of the following makes the query work:

* drop eem_unsubscribe and recreate it as InnoDb table
* drop the index "mailinggroup_status_userid_userlist" on the *federated* table
* make column istatus NOT NULL (on federated table is enough)
[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.