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:
None 
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
Description:
The following query returns an empty result set (not as expected):

SELECT artemis_services.id
FROM artemis_services WHERE artemis_services.id IN ('1') AND artemis_services.service_aktiv=1 AND 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)   
ORDER BY artemis_services.service_name_de

The result set contains one row (as expected) when "artemis_services.id IN ('1')" is removed from the where clause, thus reading:

SELECT artemis_services.id
FROM artemis_services WHERE artemis_services.service_aktiv=1 AND 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)   
ORDER BY artemis_services.service_name_de

Surprisingly, the row's value in the column that we removed from the where clause has exactly the value we tried to match ('1'):

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Another weird thing is that the row is also found when IN is extended with random data, for example:

SELECT artemis_services.id
FROM artemis_services WHERE artemis_services.id IN ('1','foo','bar') AND artemis_services.service_aktiv=1 AND 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)   
ORDER BY artemis_services.service_name_de

Results again in:

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

It must have something to do with the subquery - the IN thing works as expected without the subquery, or when the result of the subquery is added to the query statically, such as:

SELECT artemis_services.id
FROM artemis_services WHERE artemis_services.id IN ('1') AND artemis_services.service_aktiv=1 AND id NOT IN (2)   
ORDER BY artemis_services.service_name_de

How to repeat:
Try a similiar construct, using a subquery together with an WHERE .. IN().
[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>