Description:
2 equivalent queries return 2 different result sets:
1 query uses subselect
another uses join with derived table;
# Query:
# SELECT op1.id, op1.pid, op1.map
# FROM objects_path op1,
# (SELECT id, map
# FROM objects_path
# WHERE map IN (0, 1001, 1002)
# AND pid = 10030) op2
# WHERE (op1.id, op1.map) = (op2.id, op2.map)
#
Result:
'id','pid','map'
____________________
'10030','1','1001'
'10030','10','1001'
'10030','10030','1001'
'10030','1','1002'
'10030','10','1002'
'10030','10030','1002'
'10053','1','1001'
'10053','10','1001'
'10053','10030','1001'
'10053','10053','1001'
'10053','1','1002'
'10053','10','1002'
'10053','10030','1002'
'10053','10053','1002'
# Query:
# SELECT op1.id, op1.pid, op1.map
# FROM objects_path op1
# WHERE (op1.id, op1.map) IN (SELECT id, map
# FROM objects_path op
# WHERE map IN (0, 1001, 1002)
# AND pid = 10030)
#
Result:
____________________
'id','pid','map'
Empty Set
Sorry if i'm wrong in SQL, but it's seem that everything is correct
How to repeat:
Here is dump;
CREATE TABLE `objects_path` (
`id` int(10) unsigned NOT NULL default '0',
`pid` int(10) unsigned NOT NULL default '0',
`map` smallint(6) unsigned NOT NULL default '0',
`level` tinyint(4) unsigned NOT NULL default '0',
`title` varchar(255) default NULL,
PRIMARY KEY (`id`,`map`,`level`),
KEY `pid` (`pid`),
KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (1,1,0,0,'Root'),(1,1,1001,0,'Root'),(1,1,1002,0,'Root'),(1,1,1003,0,'Root'),(1,1,1004,0,'Root'),(2,1,0,0,'Root'),(2,2,0,1,'Reference Objects'),(2,1,1001,0,'Root'),(2,2,1001,1,'Reference Objects'),(2,1,1002,0,'Root'),(2,2,1002,1,'Reference Objects'),(2,1,1003,0,'Root'),(2,2,1003,1,'Reference Objects'),(2,1,1004,0,'Root'),(2,2,1004,1,'Reference Objects'),(3,1,0,0,'Root'),(3,3,0,1,'Maps'),(3,1,1001,0,'Root'),(3,3,1001,1,'Maps'),(3,1,1002,0,'Root'),(3,3,1002,1,'Maps'),(3,1,1003,0,'Root'),(3,3,1003,1,'Maps'),(3,1,1004,0,'Root'),(3,3,1004,1,'Maps'),(4,1,0,0,'Root'),(4,4,0,1,'User Groups'),(4,1,1001,0,'Root'),(4,4,1001,1,'User Groups'),(4,1,1002,0,'Root'),(4,4,1002,1,'User Groups'),(4,1,1003,0,'Root'),(4,4,1003,1,'User Groups'),(4,1,1004,0,'Root'),(4,4,1004,1,'User Groups'),(5,1,0,0,'Root'),(5,5,0,1,'Users'),(5,1,1001,0,'Root'),(5,5,1001,1,'Users'),(5,1,1002,0,'Root'),(5,5,1002,1,'Users'),(5,1,1003,0,'Root'),(5,5,1003,1,'Users'),(5,1,1004,0,'Root');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (5,5,1004,1,'Users'),(6,1,0,0,'Root'),(6,6,0,1,'Templates'),(6,1,1001,0,'Root'),(6,6,1001,1,'Templates'),(6,1,1002,0,'Root'),(6,6,1002,1,'Templates'),(6,1,1003,0,'Root'),(6,6,1003,1,'Templates'),(6,1,1004,0,'Root'),(6,6,1004,1,'Templates'),(7,1,0,0,'Root'),(7,7,0,1,'Collectors'),(7,1,1001,0,'Root'),(7,7,1001,1,'Collectors'),(7,1,1002,0,'Root'),(7,7,1002,1,'Collectors'),(7,1,1003,0,'Root'),(7,7,1003,1,'Collectors'),(7,1,1004,0,'Root'),(7,7,1004,1,'Collectors'),(8,1,0,0,'Root'),(8,8,0,1,'Discounts Dealer-Item'),(8,1,1001,0,'Root'),(8,8,1001,1,'Discounts Dealer-Item'),(8,1,1002,0,'Root'),(8,8,1002,1,'Discounts Dealer-Item'),(8,1,1003,0,'Root'),(8,8,1003,1,'Discounts Dealer-Item'),(8,1,1004,0,'Root'),(8,8,1004,1,'Discounts Dealer-Item'),(9,1,0,0,'Root'),(9,9,0,1,'Ranged Discounts'),(9,1,1001,0,'Root'),(9,9,1001,1,'Ranged Discounts'),(9,1,1002,0,'Root'),(9,9,1002,1,'Ranged Discounts'),(9,1,1003,0,'Root'),(9,9,1003,1,'Ranged Discounts'),(9,1,1004,0,'Root');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (9,9,1004,1,'Ranged Discounts'),(10,1,0,0,'Root'),(10,10,0,1,'Sellable Items'),(10,1,1001,0,'Root'),(10,10,1001,1,'Sellable Items'),(10,1,1002,0,'Root'),(10,10,1002,1,'Sellable Items'),(10,1,1003,0,'Root'),(10,10,1003,1,'Sellable Items'),(10,1,1004,0,'Root'),(10,10,1004,1,'Sellable Items'),(11,1,0,0,'Root'),(11,11,0,1,'Currencies'),(11,1,1001,0,'Root'),(11,11,1001,1,'Currencies'),(11,1,1002,0,'Root'),(11,11,1002,1,'Currencies'),(11,1,1003,0,'Root'),(11,11,1003,1,'Currencies'),(11,1,1004,0,'Root'),(11,11,1004,1,'Currencies'),(12,1,0,0,'Root'),(12,12,0,1,'Carts'),(12,1,1001,0,'Root'),(12,12,1001,1,'Carts'),(12,1,1002,0,'Root'),(12,12,1002,1,'Carts'),(12,1,1003,0,'Root'),(12,12,1003,1,'Carts'),(12,1,1004,0,'Root'),(12,12,1004,1,'Carts'),(13,1,0,0,'Root'),(13,13,0,1,'Orders'),(13,1,1001,0,'Root'),(13,13,1001,1,'Orders'),(13,1,1002,0,'Root'),(13,13,1002,1,'Orders'),(13,1,1003,0,'Root'),(13,13,1003,1,'Orders'),(13,1,1004,0,'Root'),(13,13,1004,1,'Orders');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (14,1,0,0,'Root'),(14,14,0,1,'Order States'),(14,1,1001,0,'Root'),(14,14,1001,1,'Order States'),(14,1,1002,0,'Root'),(14,14,1002,1,'Order States'),(14,1,1003,0,'Root'),(14,14,1003,1,'Order States'),(14,1,1004,0,'Root'),(14,14,1004,1,'Order States'),(15,1,0,0,'Root'),(15,15,0,1,'Mime Types'),(15,1,1001,0,'Root'),(15,15,1001,1,'Mime Types'),(15,1,1002,0,'Root'),(15,15,1002,1,'Mime Types'),(15,1,1003,0,'Root'),(15,15,1003,1,'Mime Types'),(15,1,1004,0,'Root'),(15,15,1004,1,'Mime Types'),(16,1,0,0,'Root'),(16,16,0,1,'File System'),(16,1,1001,0,'Root'),(16,16,1001,1,'File System'),(16,1,1002,0,'Root'),(16,16,1002,1,'File System'),(16,1,1003,0,'Root'),(16,16,1003,1,'File System'),(16,1,1004,0,'Root'),(16,16,1004,1,'File System'),(1001,1,1001,0,'Root'),(1001,3,1001,1,'Maps'),(1001,1001,1001,2,'Map #1'),(1002,1,1002,0,'Root'),(1002,3,1002,1,'Maps'),(1002,1002,1002,2,'Map #2'),(1003,1,1003,0,'Root'),(1003,3,1003,1,'Maps'),(1003,1003,1003,2,'Map #3'),(1004,1,1004,0,'Root');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (1004,3,1004,1,'Maps'),(1004,1004,1004,2,'Map #4'),(10001,1,1001,0,'Root'),(10001,4,1001,1,'User Groups'),(10001,10001,1001,2,'Administrators'),(10002,1,1001,0,'Root'),(10002,6,1001,1,'Templates'),(10002,10002,1001,2,'Main test xtemplate object'),(10005,1,1001,0,'Root'),(10005,5,1001,1,'Users'),(10005,10005,1001,2,'test_user'),(10006,1,1001,0,'Root'),(10006,5,1001,1,'Users'),(10006,10006,1001,2,'test_user2'),(10011,1,1001,0,'Root'),(10011,6,1001,1,'Templates'),(10011,10011,1001,2,'Hier template 1'),(10012,1,1001,0,'Root'),(10012,6,1001,1,'Templates'),(10012,10011,1001,2,'Hier template 1'),(10012,10012,1001,3,'Hier template 2'),(10013,1,1001,0,'Root'),(10013,6,1001,1,'Templates'),(10013,10011,1001,2,'Hier template 1'),(10013,10012,1001,3,'Hier template 2'),(10013,10013,1001,4,'Hier template 3'),(10014,1,1001,0,'Root'),(10014,6,1001,1,'Templates'),(10014,10011,1001,2,'Hier template 1'),(10014,10012,1001,3,'Hier template 2'),(10014,10013,1001,4,'Hier template 3');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (10014,10014,1001,5,'Hier template 4'),(10017,1,1001,0,'Root'),(10017,8,1001,1,'Discounts Dealer-Item'),(10017,10017,1001,2,'Test Discount 1'),(10019,1,1001,0,'Root'),(10019,8,1001,1,'Discounts Dealer-Item'),(10019,10019,1001,2,'Test Discount 2'),(10020,1,1001,0,'Root'),(10020,8,1001,1,'Discounts Dealer-Item'),(10020,10020,1001,2,'Test Discount 3'),(10021,1,1001,0,'Root'),(10021,11,1001,1,'Currencies'),(10021,10021,1001,2,'RU'),(10025,1,1001,0,'Root'),(10025,10,1001,1,'Sellable Items'),(10025,10025,1001,2,'EeeeeEEeeeeeEe'),(10030,1,1001,0,'Root'),(10030,10,1001,1,'Sellable Items'),(10030,10030,1001,2,'EeeeeEEeeeee'),(10030,1,1002,0,'Root'),(10030,10,1002,1,'Sellable Items'),(10030,10030,1002,2,NULL),(10030,1,1003,0,'Root'),(10030,10,1003,1,'Sellable Items'),(10030,10030,1003,2,'title'),(10031,1,1001,0,'Root'),(10031,16,1001,1,'File System'),(10031,10034,1001,2,'Test folder'),(10031,10031,1001,3,'extended.txt'),(10032,1,1001,0,'Root'),(10032,16,1001,1,'File System');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (10032,10034,1001,2,'Test folder'),(10032,10032,1001,3,'flevSwapTrans102.mxp'),(10033,1,1001,0,'Root'),(10033,16,1001,1,'File System'),(10033,10033,1001,2,'Untitled-2.gif'),(10034,1,1001,0,'Root'),(10034,16,1001,1,'File System'),(10034,10034,1001,2,'Test folder'),(10035,1,1001,0,'Root'),(10035,15,1001,1,'Mime Types'),(10035,10035,1001,2,'image/jpeg'),(10036,1,1001,0,'Root'),(10036,15,1001,1,'Mime Types'),(10036,10036,1001,2,'image/x-png'),(10037,1,1001,0,'Root'),(10037,16,1001,1,'File System'),(10037,10037,1001,2,'compile.png'),(10048,1,1001,0,'Root'),(10048,16,1001,1,'File System'),(10048,10048,1001,2,'del.png'),(10049,1,1001,0,'Root'),(10049,16,1001,1,'File System'),(10049,10049,1001,2,'minus.gif'),(10050,1,1001,0,'Root'),(10050,16,1001,1,'File System'),(10050,10050,1001,2,'ren_sm.png'),(10051,1,1001,0,'Root'),(10051,16,1001,1,'File System'),(10051,10051,1001,2,'menu__.png'),(10052,1,1001,0,'Root'),(10052,16,1001,1,'File System'),(10052,10052,1001,2,'nodes.js');
INSERT INTO `objects_path` (`id`,`pid`,`map`,`level`,`title`) VALUES (10053,1,1001,0,'Root'),(10053,10,1001,1,'Sellable Items'),(10053,10030,1001,2,'EeeeeEEeeeee'),(10053,10053,1001,3,'TESTING CRASSMAPPING'),(10053,1,1002,0,'Root'),(10053,10,1002,1,'Sellable Items'),(10053,10030,1002,2,NULL),(10053,10053,1002,3,'TESTING CRASSMAPPING'),(10053,1,1003,0,'Root'),(10053,10,1003,1,'Sellable Items'),(10053,10030,1003,2,'title'),(10053,10053,1003,3,'TESTING CRASSMAPPING');
