Bug #11479 | Subquery and WHERE IN returns wrong results | ||
---|---|---|---|
Submitted: | 21 Jun 2005 12:08 | Modified: | 24 Aug 2005 17:13 |
Reporter: | Martin Bachmann | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 4.1.11 | OS: | Linux (Linux) |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
[21 Jun 2005 12:08]
Martin Bachmann
[21 Jun 2005 12:20]
MySQL Verification Team
Hello Martin, Thanl you for the report. First of all: please, specify MySQL server version number and OS you are using. You query is a bit complex and I can't reproduce using my simple test. Please create repeatable a test case. This means we need table structure (output of SHOW CREATE TABLE) and test data.
[21 Jun 2005 12:53]
Martin Bachmann
MySQL version: 4.1.11 DB Dump: -- -- Table structure for table `artemis_object_relations` -- CREATE TABLE `artemis_object_relations` ( `id` int(10) unsigned NOT NULL auto_increment, `one_id` int(10) unsigned NOT NULL default '0', `two_id` int(10) unsigned NOT NULL default '0', `one_type` varchar(255) NOT NULL default '', `two_type` varchar(255) NOT NULL default '', `relation` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=130 ; -- -- Dumping data for table `artemis_object_relations` -- INSERT INTO `artemis_object_relations` VALUES (20, 4512, 1, 'user_artemis', 'geschaeftsstelle_artemis', 'geschaeftsstelle_user'); INSERT INTO `artemis_object_relations` VALUES (21, 4513, 1, 'user_artemis', 'geschaeftsstelle_artemis', 'geschaeftsstelle_user'); INSERT INTO `artemis_object_relations` VALUES (128, 4, 6, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (24, 4513, 2, 'user_artemis', 'geschaeftsstelle_artemis', 'geschaeftsstelle_user'); INSERT INTO `artemis_object_relations` VALUES (122, 4, 2, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (121, 1, 2, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (115, 4, 1, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (114, 2, 1, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (129, 8, 6, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (127, 1, 6, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (123, 1, 7, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (124, 4, 7, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (125, 8, 7, 'sub_service', 'sub_package', 'package_service'); INSERT INTO `artemis_object_relations` VALUES (126, 9, 7, 'sub_service', 'sub_package', 'package_service'); -- -------------------------------------------------------- -- -- Table structure for table `artemis_packagelengths` -- CREATE TABLE `artemis_packagelengths` ( `id` int(10) unsigned NOT NULL auto_increment, `id_package` int(10) unsigned NOT NULL default '0', `packagelength_name_de` varchar(255) NOT NULL default '', `packagelength_name_fr` varchar(255) NOT NULL default '', `packagelength_length_value` int(10) unsigned NOT NULL default '0', `packagelength_length_type` varchar(255) NOT NULL default '', `packagelength_preis` decimal(10,2) NOT NULL default '0.00', `packagelength_preistext_de` varchar(255) NOT NULL default '', `packagelength_preistext_fr` varchar(255) NOT NULL default '', `packagelength_insDate` varchar(14) NOT NULL default '', `packagelength_insPrsName` varchar(255) NOT NULL default '', `packagelength_insPrsUid` varchar(11) NOT NULL default '', `packagelength_mutDate` varchar(14) NOT NULL default '', `packagelength_mutPrsName` varchar(255) NOT NULL default '', `packagelength_mutPrsUid` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `artemis_packagelengths` -- INSERT INTO `artemis_packagelengths` VALUES (1, 1, 'Ein Jahr', 'Ein Jahr', 12, 'M', 65.00, '60.-', '60.-', '', '', '', '20050621102128', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (2, 1, 'Zwei Jahre', 'Zwei Jahre', 24, 'M', 99.95, 'unter 100.-', 'unter 100.-', '', '', '', '20050621102212', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (3, 1, 'Drei Jahre', 'Drei Jahre', 36, 'M', 148.00, 'knappe schlappe 150.-', 'knappe schlappe 150.-', '', '', '', '20050621102259', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (4, 2, 'Ein Jahr', '', 12, 'D', 123.00, '', '', '', '', '', '', '', ''); INSERT INTO `artemis_packagelengths` VALUES (6, 6, 'Ein Jahr', 'Ein Jahr', 12, 'M', 185.00, 'bereits für 185.-', 'bereits für 185.-', '20050621112605', 'Flavio Sadeghi [su]', 's4513', '20050621112605', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (7, 6, 'Zwei Jahre', 'Zwei Jahre', 24, 'M', 250.00, 'breits für 250.-', 'breits für 250.-', '20050621112639', 'Flavio Sadeghi [su]', 's4513', '20050621112639', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (8, 6, 'Drei Jahre', 'Drei Jahre', 36, 'M', 350.00, 'nur noch 350.-', 'nur noch 350.-', '20050621112715', 'Flavio Sadeghi [su]', 's4513', '20050621112715', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (9, 7, 'Ein Jahr', 'Ein Jahr', 12, 'M', 195.00, '', '', '20050621112933', 'Flavio Sadeghi [su]', 's4513', '20050621112933', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_packagelengths` VALUES (10, 7, 'Zwei Jahre', 'Zwei Jahre', 24, 'M', 350.00, 'ab sagenhaften 350.-', 'ab sagenhaften 350.-', '20050621112957', 'Flavio Sadeghi [su]', 's4513', '20050621112957', 'Flavio Sadeghi [su]', 's4513'); -- -------------------------------------------------------- -- -- Table structure for table `artemis_packagesubscriptions` -- CREATE TABLE `artemis_packagesubscriptions` ( `id` int(11) unsigned NOT NULL auto_increment, `id_account` int(11) NOT NULL default '0', `id_packagelength` int(11) unsigned NOT NULL default '0', `subpack_aktiv` tinyint(1) NOT NULL default '0', `subpack_manual` tinyint(1) NOT NULL default '0', `subpack_description` text NOT NULL, `subpack_jahre` varchar(255) NOT NULL default '', `subpack_expirationdate` varchar(14) NOT NULL default '', `subpack_renewalcount` tinyint(3) NOT NULL default '0', `subpack_package_name` varchar(255) NOT NULL default '', `subpack_package_description` text NOT NULL, `subpack_packagelength_name` varchar(255) NOT NULL default '', `subpack_packagelength_lengthtype` enum('D','M') NOT NULL default 'D', `subpack_packagelength_value` tinyint(5) NOT NULL default '0', `subpack_packagelength_price` float(8,2) NOT NULL default '0.00', `subpack_letzteZahlung` varchar(14) NOT NULL default '', `subpack_abacus_id` varchar(255) NOT NULL default '', `subpack_abacus_datum` varchar(14) NOT NULL default '', `subpack_abacus_status` varchar(255) NOT NULL default '', `subpack_created` varchar(14) NOT NULL default '', `subpack_insDate` varchar(14) NOT NULL default '', `subpack_insPrsName` varchar(255) NOT NULL default '', `subpack_insPrsUid` varchar(11) NOT NULL default '', `subpack_mutDate` varchar(14) NOT NULL default '', `subpack_mutPrsName` varchar(255) NOT NULL default '', `subpack_mutPrsUid` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; -- -- Dumping data for table `artemis_packagesubscriptions` -- -- -------------------------------------------------------- -- -- Table structure for table `artemis_servicelengths` -- CREATE TABLE `artemis_servicelengths` ( `id` int(10) unsigned NOT NULL auto_increment, `id_service` int(10) unsigned NOT NULL default '0', `servicelength_name_de` varchar(255) NOT NULL default '', `servicelength_name_fr` varchar(255) NOT NULL default '', `servicelength_length_value` int(10) unsigned NOT NULL default '0', `servicelength_length_type` varchar(255) NOT NULL default '', `servicelength_preis` decimal(10,2) NOT NULL default '0.00', `servicelength_preistext_de` varchar(255) NOT NULL default '', `servicelength_preistext_fr` varchar(255) NOT NULL default '', `servicelength_insDate` varchar(14) NOT NULL default '', `servicelength_insPrsName` varchar(255) NOT NULL default '', `servicelength_insPrsUid` varchar(11) NOT NULL default '', `servicelength_mutDate` varchar(14) NOT NULL default '', `servicelength_mutPrsName` varchar(255) NOT NULL default '', `servicelength_mutPrsUid` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ; -- -- Dumping data for table `artemis_servicelengths` -- INSERT INTO `artemis_servicelengths` VALUES (1, 2, 'Neue Laufzeit 1', 'Neue Laufzeit 1 fr', 123456, 'D', 123.00, 'nur 123', 'nur 123 fr', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (2, 2, 'Neue Laufzeit 2', '', 24, 'M', 200.00, 'Ab 200.- Fränkli für 2 volle Jahre', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (6, 1, 'eine woche', '', 7, 'D', 0.00, 'fasdfasdf', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (5, 1, 'ein jahr', '', 12, 'M', 0.00, 'dfasdfasf', 'asdfas', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (7, 4, '3 Jahre', '', 36, 'M', 333.00, '333', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (8, 4, '2 Jahre', '', 24, 'M', 222.30, '222', '', '', '', '', '20050614152615', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (9, 4, '1 Jöhrli', '', 12, 'M', 111.00, '111', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicelengths` VALUES (12, 8, 'Ein Jahr', 'Ein Jahr', 12, 'M', 148.00, '148.-', '148.-', '20050621104134', 'Flavio Sadeghi [su]', 's4513', '20050621104134', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (13, 8, 'Zwei Jahre', 'Zwei Jahre', 24, 'M', 198.00, 'unter 200.-', 'unter 200.-', '20050621110219', 'Flavio Sadeghi [su]', 's4513', '20050621110302', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (14, 8, 'Drei Jahre', 'Drei Jahre', 36, 'M', 278.00, 'knappe 280.-', 'knappe 280.-', '20050621110337', 'Flavio Sadeghi [su]', 's4513', '20050621110337', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (15, 9, 'Ein Jahr', 'Ein Jahr', 12, 'M', 25.00, 'für einmalige 25.-', 'für einmalige 25.-', '20050621110455', 'Flavio Sadeghi [su]', 's4513', '20050621110455', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (16, 9, 'Zwei Jahre', 'Zwei Jahre', 24, 'M', 40.00, 'für einmalige 40.-', 'für einmalige 40.-', '20050621110531', 'Flavio Sadeghi [su]', 's4513', '20050621110531', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicelengths` VALUES (17, 9, 'Drei Jahre', 'Drei Jahre', 36, 'M', 62.50, 'jetzt nur für 62.50', 'jetzt nur für 62.50', '20050621112418', 'Flavio Sadeghi [su]', 's4513', '20050621112449', 'Flavio Sadeghi [su]', 's4513'); -- -------------------------------------------------------- -- -- Table structure for table `artemis_services` -- CREATE TABLE `artemis_services` ( `id` int(10) unsigned NOT NULL auto_increment, `service_order` int(10) unsigned NOT NULL default '0', `service_type` varchar(255) NOT NULL default '', `service_aktiv` tinyint(1) NOT NULL default '0', `service_abacus_produkt` varchar(255) NOT NULL default '', `service_abacus_debitor` varchar(255) NOT NULL default '', `service_name_de` varchar(255) NOT NULL default '', `service_name_fr` varchar(255) NOT NULL default '', `service_beschreibung_de` text NOT NULL, `service_beschreibung_fr` text NOT NULL, `service_beispiel_de` text NOT NULL, `service_beispiel_fr` text NOT NULL, `service_bild_de` varchar(255) NOT NULL default '', `service_bild_fr` varchar(255) NOT NULL default '', `service_access_text_de` varchar(255) NOT NULL default '', `service_access_text_fr` varchar(255) NOT NULL default '', `service_access_link` varchar(255) NOT NULL default '', `service_preisname_de` varchar(255) NOT NULL default '', `service_preisname_fr` varchar(255) NOT NULL default '', `service_preisbeschr_de` varchar(255) NOT NULL default '', `service_preisbeschr_fr` varchar(255) NOT NULL default '', `service_zsrrequired` tinyint(4) NOT NULL default '0', `service_autorenew` tinyint(4) NOT NULL default '0', `service_renewyearly` tinyint(4) NOT NULL default '0', `service_notifygsuser` tinyint(4) NOT NULL default '0', `service_availabilitycode` text NOT NULL, `service_insDate` varchar(14) NOT NULL default '', `service_insPrsName` varchar(255) NOT NULL default '', `service_insPrsUid` varchar(11) NOT NULL default '', `service_mutDate` varchar(14) NOT NULL default '', `service_mutPrsName` varchar(255) NOT NULL default '', `service_mutPrsUid` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ; -- -- Dumping data for table `artemis_services` -- INSERT INTO `artemis_services` VALUES (1, 3, 'DOWNLOAD', 1, '', '321', 'statsstandard', 'statsstandard', 'Unlimitierter Zugriff auf Ihre persönliche Rechnungssteller-Statistik mit Gruppenvergleich als PDF-Datei.', 'Unlimitierter Zugriff auf Ihre persönliche Rechnungssteller-Statistik mit Gruppenvergleich als PDF-Datei.', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, '', '', '', '', '20050615160146', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_services` VALUES (2, 1, 'DOWNLOAD', 1, '8888', '', 'RSS-Statistik (Dummy-Eintrag)', 'RSS-Statistik FR', 'besch de 1111', 'besch fr 2222', 'beispiel_de33', 'beispiel_fr44', '', '', 'access_text_de555', 'access_text_fr11 66666', 'access_link', '3333', '444', '5555', '66666', 1, 1, 0, 1, 'return CallInstance(''subscription'', '''', ''evalcode_checkAvailabilityRSS'', $service, $account);', '', '', '', '20050620103456', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_services` VALUES (4, 2, 'ZUGRIFF', 1, '', '', 'contractspersonal', 'contractspersonal', ' Zugriff auf die OKP-Verträge des registrierten Leistungserbringers.', ' Zugriff auf die OKP-Verträge des registrierten Leistungserbringers.', '', '', '', '', '', '', '', 'ab unglaublichen CHF 123.- pro Jahr', '', '', '', 0, 0, 0, 0, '', '', '', '', '20050615160146', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_services` VALUES (8, 4, 'DOWNLOAD', 1, '', '', 'contractsplus asd sad', 'contractsplus', '', '', '', '', '', '', '', '', '', 'ab jährlichen 150.-', 'ab jährlichen 150.-', '', '', 1, 1, 0, 1, '', '20050621103854', 'Flavio Sadeghi [su]', 's4513', '20050621121459', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_services` VALUES (9, 5, 'DOWNLOAD', 1, '', '', 'contractstariffs', 'contractstariffs', '', '', '', '', '', '', '', '', '', 'bereits ab 25.-', 'bereits ab 25.-', '', '', 1, 1, 0, 1, '', '20050621104046', 'Flavio Sadeghi [su]', 's4513', '20050621112511', 'Flavio Sadeghi [su]', 's4513'); -- -------------------------------------------------------- -- -- Table structure for table `artemis_servicesubscriptions` -- CREATE TABLE `artemis_servicesubscriptions` ( `id` int(11) unsigned NOT NULL auto_increment, `id_account` int(11) NOT NULL default '0', `id_servicelength` int(11) unsigned NOT NULL default '0', `subserv_aktiv` tinyint(1) NOT NULL default '0', `subserv_manual` tinyint(1) NOT NULL default '0', `subserv_description` text NOT NULL, `subserv_expirationdate` varchar(14) NOT NULL default '', `subserv_renewalcount` tinyint(3) NOT NULL default '0', `subserv_service_name` varchar(255) NOT NULL default '', `subserv_service_description` text NOT NULL, `subserv_servicelength_name` varchar(255) NOT NULL default '', `subserv_servicelength_lengthtype` enum('D','M') NOT NULL default 'D', `subserv_servicelength_value` tinyint(5) NOT NULL default '0', `subserv_servicelength_price` float(8,2) NOT NULL default '0.00', `subserv_letzteZahlung` varchar(14) NOT NULL default '', `subserv_jahre` varchar(255) NOT NULL default '', `subserv_abacus_id` varchar(255) NOT NULL default '', `subserv_abacus_datum` varchar(14) NOT NULL default '', `subserv_abacus_status` varchar(255) NOT NULL default '', `subserv_created` varchar(14) NOT NULL default '', `subserv_insDate` varchar(14) NOT NULL default '', `subserv_insPrsName` varchar(255) NOT NULL default '', `subserv_insPrsUid` varchar(11) NOT NULL default '', `subserv_mutDate` varchar(14) NOT NULL default '', `subserv_mutPrsName` varchar(255) NOT NULL default '', `subserv_mutPrsUid` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; -- -- Dumping data for table `artemis_servicesubscriptions` -- INSERT INTO `artemis_servicesubscriptions` VALUES (29, 0, 14, 1, 0, 'Ordered on 21.06.2005 from [192.168.1.222]', 'TODO', 0, 'contractsplus', '', 'Drei Jahre', 'M', 36, 278.00, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicesubscriptions` VALUES (30, 0, 17, 1, 0, 'Ordered on 21.06.2005 from [192.168.1.222]', 'TODO', 0, 'contractstariffs', '', 'Drei Jahre', 'M', 36, 62.50, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicesubscriptions` VALUES (31, 0, 14, 1, 0, 'Ordered on 21.06.2005 from [192.168.1.222]', 'TODO', 0, 'contractsplus asd sad', '', 'Drei Jahre', 'M', 36, 278.00, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicesubscriptions` VALUES (32, 0, 17, 1, 0, 'Ordered on 21.06.2005 from [192.168.1.222]', 'TODO', 0, 'contractstariffs', '', 'Drei Jahre', 'M', 36, 62.50, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO `artemis_servicesubscriptions` VALUES (27, 14, 2, 1, 1, '', '20070620000000', 0, 'RSS-Statistik (Dummy-Eintrag)', 'besch de 1111', 'Neue Laufzeit 2', 'D', 0, 200.00, '', '2002,2003,2005', '', '', '', '20050620103728', '20050620103728', 'Flavio Sadeghi [su]', 's4513', '20050620103728', 'Flavio Sadeghi [su]', 's4513'); INSERT INTO `artemis_servicesubscriptions` VALUES (28, 0, 7, 1, 0, 'Ordered on 21.06.2005 from [192.168.1.222]', 'TODO', 0, 'contractspersonal', ' Zugriff auf die OKP-Verträge des registrierten Leistungserbringers.', '3 Jahre', 'M', 36, 333.00, '', '', '', '', '', '', '', '', '', '', '', '');
[22 Jun 2005 11:13]
Vasily Kishkin
Thank for table structure and test data. Tested on Win 2000 Sp4, MySQL server 4.1.13
[14 Aug 2005 3:43]
Igor Babaev
There is a more striking demonstration of existing problems: mysql> SELECT artemis_services.id FROM artemis_services; +----+ | id | +----+ | 1 | | 2 | | 4 | | 8 | | 9 | +----+ 5 rows in set (5.31 sec) mysql> SELECT artemis_services.id, artemis_servicesubscriptions.id, artemis_packagesubscriptions.id -> FROM artemis_services -> LEFT JOIN artemis_servicelengths ON -> artemis_services.id=artemis_servicelengths.id_service -> LEFT JOIN artemis_servicesubscriptions ON -> artemis_servicelengths.id=artemis_servicesubscriptions.id_servicelength -> AND artemis_servicesubscriptions.id_account='14' -> LEFT JOIN artemis_object_relations AS package_service ON -> package_service.one_id=artemis_services.id -> AND package_service.relation='package_service' -> LEFT JOIN artemis_packagelengths ON -> artemis_packagelengths.id_package=package_service.two_id -> LEFT JOIN artemis_packagesubscriptions ON -> artemis_packagelengths.id=artemis_packagesubscriptions.id_packagelength -> AND artemis_packagesubscriptions.id_account='14' -> WHERE artemis_servicesubscriptions.id IS NOT NULL OR -> artemis_packagesubscriptions.id IS NOT NULL; +----+------+------+ | id | id | id | +----+------+------+ | 2 | 27 | NULL | | 2 | 27 | NULL | | 2 | 27 | NULL | +----+------+------+ 3 rows in set (2.49 sec) mysql> SELECT artemis_services.id -> FROM artemis_services WHERE id NOT IN -> (SELECT artemis_services.id FROM artemis_services -> LEFT JOIN artemis_servicelengths ON -> artemis_services.id=artemis_servicelengths.id_service -> LEFT JOIN artemis_servicesubscriptions ON -> artemis_servicelengths.id=artemis_servicesubscriptions.id_servicelength -> AND artemis_servicesubscriptions.id_account='14' -> LEFT JOIN artemis_object_relations AS package_service ON -> package_service.one_id=artemis_services.id -> AND package_service.relation='package_service' -> LEFT JOIN artemis_packagelengths ON -> artemis_packagelengths.id_package=package_service.two_id -> LEFT JOIN artemis_packagesubscriptions ON -> artemis_packagelengths.id=artemis_packagesubscriptions.id_packagelength -> AND artemis_packagesubscriptions.id_account='14' -> WHERE artemis_servicesubscriptions.id IS NOT NULL OR -> artemis_packagesubscriptions.id IS NOT NULL); +----+ | id | +----+ | 1 | +----+ 1 row in set (7.30 sec) As we can see the result set for the last query lacks values 4, 8, 9. An incorrect result set for one of the reported query and for the last query are due to the same bug in the function subselect_single_select_engine::exec(). For a non first execution of a subquery the function calls JOIN::reinit that invokes setup_tables. The last function, in particular, reset the null_row flag to 0 for each involved table. Since a non first execution of a subquery skips the optimization phase the null complements for empty tables lose their null_row mark and their fields declared as NOT NULL are evaluated as not null values. The above problem can be displayed with much simpler example: mysql> CREATE TABLE t1 (a int); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE t2 (a int, b int); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE t3 (b int NOT NULL); Query OK, 0 rows affected (0.08 sec) mysql> mysql> INSERT INTO t1 VALUES (1), (2), (3), (4); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 VALUES (1,10), (3,30); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b -> WHERE t3.b IS NOT NULL OR t2.a > 10; Empty set (3.09 sec) mysql> mysql> SELECT * FROM t1 -> WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b -> WHERE t3.b IS NOT NULL OR t2.a > 10); +------+ | a | +------+ | 1 | | 2 | | 4 | +------+ 3 rows in set (5.11 sec) Here the result set lacks value 3.
[16 Aug 2005 4:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/28324
[18 Aug 2005 6:12]
Igor Babaev
This bug existsted only in 4.1 code. ChangeSet 1.2376 05/08/15 21:33:58 igor@rurik.mysql.com +3 -0 sql_select.cc: Fixed bug #11479. The JOIN::reinit method cannot call setup_tables after the optimization phase since this function removes some optimization settings for joined tables. E.g. it resets values of the null_row flag to 0. subselect.result, subselect.test: Added a test case for bug #11479. The fix will appear in 4.1.14
[18 Aug 2005 22:07]
Igor Babaev
Queries with subqueries that contain outer joins may return wrong results.
[24 Aug 2005 17:13]
Mike Hillyer
Documented in 4.1.14 changelog: <listitem><para>Queries with subqueries that contain outer joins could return wrong results. (Bug #11479)</para></listitem>