| Bug #5716 | Subquery returns value but should return NULL | ||
|---|---|---|---|
| Submitted: | 23 Sep 2004 12:18 | Modified: | 15 Oct 2004 12:53 |
| Reporter: | Dmitry L | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.3 | OS: | Windows (Win2k) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[24 Sep 2004 11:10]
MySQL Verification Team
Hi Dmitry, Thank you for the report. Please, provide tables structure and data (utility) for testing.
[24 Sep 2004 11:40]
Dmitry L
/*----------------------------*/
SET @MAP = 1001;
/*----------- Here is the select (uncomment to get incorrect result) -----------------*/
SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s,
dd.value_c) value, (SELECT flags_0 FROM objects_hier oh WHERE oh.id =
dd.id AND oh.map= @MAP
/*
AND flags_0 & 1 != 0
*/
) flags
FROM objects_path op,
discounts_di dd
WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map =@MAP)
AND op.map = @MAP
AND dd.map = @MAP
AND dd.slave = op.pid
ORDER BY op.id, dd.master, op.level
/*------------------ And tables with data; -------------------------------*/
/**
may be it'll be useful not to enable foreign key checks ))))
**/
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 24, 2004 at 03:36 PM
-- Server version: 4.1.3
-- PHP Version: 5.0.2RC1
SET FOREIGN_KEY_CHECKS=0;
--
-- Database: `parallax_kernel`
--
-- --------------------------------------------------------
--
-- Table structure for table `discounts_di`
--
DROP TABLE IF EXISTS `discounts_di`;
CREATE TABLE `discounts_di` (
`id` int(10) unsigned NOT NULL default '0',
`master` int(10) unsigned NOT NULL default '0',
`slave` int(10) unsigned NOT NULL default '0',
`map` smallint(6) unsigned NOT NULL default '0',
`value_s` float(7,4) NOT NULL default '0.0000',
`value_c` float(7,4) NOT NULL default '0.0000',
PRIMARY KEY (`id`,`map`),
KEY `discounts_di_cache_ibfk_2` (`master`,`map`),
KEY `discounts_di_cache_ibfk_3` (`slave`,`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `discounts_di`
--
INSERT INTO `discounts_di` (`id`, `master`, `slave`, `map`, `value_s`, `value_c`) VALUES (10017, 10001, 6, 1001, 10.0000, 20.0000),
(10019, 10006, 10011, 1001, 30.0000, 40.0000),
(10020, 10001, 10011, 1001, 50.0000, 60.0000);
-- --------------------------------------------------------
--
-- Table structure for table `objects_hier`
--
DROP TABLE IF EXISTS `objects_hier`;
CREATE TABLE `objects_hier` (
`id` int(10) unsigned NOT NULL auto_increment,
`pid` int(10) unsigned NOT NULL default '0',
`flags_0` int(10) unsigned NOT NULL default '0',
`flags_1` int(10) unsigned NOT NULL default '0',
`flags_2` int(10) unsigned NOT NULL default '0',
`proto` smallint(6) unsigned NOT NULL default '0',
`sort` smallint(6) NOT NULL default '0',
`map` smallint(6) unsigned NOT NULL default '0',
`mapped_from` smallint(6) unsigned default NULL,
`lnk4` int(10) unsigned default NULL,
`crt` double(15,4) unsigned NOT NULL default '0.0000',
`mdf` double(15,4) unsigned NOT NULL default '0.0000',
`title` varchar(255) default NULL,
`remark` varchar(255) default NULL,
`xml` mediumtext,
PRIMARY KEY (`id`,`map`),
KEY `proto` (`proto`),
KEY `title` (`title`),
KEY `pid` (`pid`),
KEY `id` (`id`,`mapped_from`),
KEY `lnk4` (`lnk4`,`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10021 ;
--
-- Dumping data for table `objects_hier`
--
INSERT INTO `objects_hier` (`id`, `pid`, `flags_0`, `flags_1`, `flags_2`, `proto`, `sort`, `map`, `mapped_from`, `lnk4`, `crt`, `mdf`, `title`, `remark`, `xml`) VALUES (1, 0, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Root', NULL, NULL),
(1, 0, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Root', NULL, NULL),
(2, 1, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Reference Objects', NULL, NULL),
(2, 1, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Reference Objects', NULL, NULL),
(3, 1, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Maps', NULL, NULL),
(3, 1, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Maps', NULL, NULL),
(4, 1, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'User Groups', NULL, NULL),
(4, 1, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'User Groups', NULL, NULL),
(5, 1, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Users', NULL, NULL),
(5, 1, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Users', NULL, NULL),
(6, 1, 1, 0, 0, 0, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Templates', NULL, NULL),
(6, 1, 1, 0, 0, 0, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Templates', NULL, NULL),
(7, 1, 1, 0, 0, 7, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Collectors', NULL, NULL),
(7, 1, 1, 0, 0, 7, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Collectors', NULL, NULL),
(8, 1, 1, 0, 0, 8, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Discounts Dealer-Item', NULL, NULL),
(8, 1, 1, 0, 0, 8, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Discounts Dealer-Item', NULL, NULL),
(9, 1, 1, 0, 0, 9, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Ranged Discounts', NULL, NULL),
(9, 1, 1, 0, 0, 9, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Ranged Discounts', NULL, NULL),
(10, 1, 1, 0, 0, 10, 0, 0, NULL, NULL, 0.0000, 0.0000, 'Sellable Items', NULL, NULL),
(10, 1, 1, 0, 0, 10, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Sellable Items', NULL, NULL),
(1001, 3, 1, 0, 0, 3, 0, 1001, NULL, NULL, 0.0000, 0.0000, 'Map #1', NULL, NULL),
(10001, 4, 1, 1, 0, 4, 0, 1001, NULL, NULL, 1095678333.0300, 1095678333.0300, 'Administrators', NULL, '<?xml version="1.0" encoding="utf-8"?><xusrgroup/>'),
(10002, 6, 1, 0, 0, 6, 0, 1001, NULL, NULL, 1095679689.8600, 1095681351.3300, 'Main test xtemplate object', NULL, '<?xml version="1.0" encoding="utf-8"?>\n<xtemplate>\n <box title="First box">\n <f id="0" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="1" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="2" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="3" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n </box>\n <box title="Second test box">\n <f id="4" type="set" min="0" max="0" default="1" basic="1" required="0">\n <o v="0">a</o>\n <o v="1">b</o>\n <o v="2">c</o>\n <o v="3">d</o>\n <o v="4">e</o>\n </f>\n <f id="5" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="6" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="7" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n <f id="8" type="num" min="1" max="100" default="50" basic="1" required="0"/>\n </box>\n <box title="Second test box">\n <f id="9" type="set" min="0" max="0" default="1" basic="1" required="0">\n <o v="0">a</o>\n <o v="1">b</o>\n <o v="2">c</o>\n <o v="3">d</o>\n <o v="4">e</o>\n </f>\n </box>\n</xtemplate>\n'),
(10005, 5, 1, 0, 0, 5, 0, 1001, NULL, NULL, 1095756139.1000, 1095756139.1000, 'test_user', NULL, '<?xml version="1.0" encoding="utf-8"?><xuser/>'),
(10006, 5, 1, 0, 0, 5, 0, 1001, NULL, NULL, 1095758613.0900, 1095758613.0900, 'test_user2', NULL, '<?xml version="1.0" encoding="utf-8"?><xuser/>'),
(10011, 6, 1, 0, 0, 6, 0, 1001, NULL, NULL, 1095763781.9800, 1095854542.1632, 'Hier template 1', NULL, '<?xml version="1.0" encoding="utf-8"?>\n<xtemplate>\n <box title="Simple std name" id="0"/>\n</xtemplate>\n'),
(10012, 10011, 1, 0, 0, 6, 0, 1001, NULL, NULL, 1095763781.9800, 1095854542.1632, 'Hier template 2', NULL, '<?xml version="1.0" encoding="utf-8"?>\n<xtemplate>\n <box title="Simple std name" id="0"/>\n</xtemplate>\n'),
(10013, 10012, 1, 0, 0, 6, 0, 1001, NULL, NULL, 1095763781.9800, 1095854542.1632, 'Hier template 3', NULL, '<?xml version="1.0" encoding="utf-8"?>\n<xtemplate>\n <box title="Simple std name" id="0"/>\n</xtemplate>\n'),
(10014, 10013, 1, 0, 0, 6, 0, 1001, NULL, NULL, 1095763781.9800, 1095854542.1632, 'Hier template 4', NULL, '<?xml version="1.0" encoding="utf-8"?>\n<xtemplate>\n <box title="Simple std name" id="0"/>\n</xtemplate>\n'),
(10017, 8, 1, 9, 0, 8, 0, 1001, NULL, NULL, 1095931862.3410, 1095938221.0731, 'Test Discount 1', NULL, '<?xml version="1.0" encoding="utf-8"?><xdiscountdi/>'),
(10019, 8, 0, 9, 0, 8, 0, 1001, NULL, NULL, 1095938239.5899, 1095938239.5899, 'Test Discount 2', NULL, '<?xml version="1.0" encoding="utf-8"?><xdiscountdi/>'),
(10020, 8, 1, 43, 0, 8, 0, 1001, NULL, NULL, 1095938410.7728, 1095938410.7728, 'Test Discount 3', NULL, '<?xml version="1.0" encoding="utf-8"?><xdiscountdi/>');
-- --------------------------------------------------------
--
-- Table structure for table `objects_path`
--
DROP TABLE IF EXISTS `objects_path`;
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `objects_path`
--
INSERT INTO `objects_path` (`id`, `pid`, `map`, `level`, `title`) VALUES (1, 1, 0, 0, 'Root'),
(1, 1, 1001, 0, 'Root'),
(2, 1, 0, 0, 'Root'),
(2, 2, 0, 1, 'Reference Objects'),
(2, 1, 1001, 0, 'Root'),
(2, 2, 1001, 1, 'Reference Objects'),
(3, 1, 0, 0, 'Root'),
(3, 3, 0, 1, 'Maps'),
(3, 1, 1001, 0, 'Root'),
(3, 3, 1001, 1, 'Maps'),
(4, 1, 0, 0, 'Root'),
(4, 4, 0, 1, 'User Groups'),
(4, 1, 1001, 0, 'Root'),
(4, 4, 1001, 1, 'User Groups'),
(5, 1, 0, 0, 'Root'),
(5, 5, 0, 1, 'Users'),
(5, 1, 1001, 0, 'Root'),
(5, 5, 1001, 1, 'Users'),
(6, 1, 0, 0, 'Root'),
(6, 6, 0, 1, 'Templates'),
(6, 1, 1001, 0, 'Root'),
(6, 6, 1001, 1, 'Templates'),
(7, 1, 0, 0, 'Root'),
(7, 7, 0, 1, 'Collectors'),
(7, 1, 1001, 0, 'Root'),
(7, 7, 1001, 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'),
(9, 1, 0, 0, 'Root'),
(9, 9, 0, 1, 'Ranged Discounts'),
(9, 1, 1001, 0, 'Root'),
(9, 9, 1001, 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'),
(1001, 1, 1001, 0, 'Root'),
(1001, 3, 1001, 1, 'Maps'),
(1001, 1001, 1001, 2, 'Map #1'),
(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'),
(10014, 10014, 1001, 5, 'Hier template 4'),
(10017, 1, 1001, 0, 'Root'),
(10017, 8, 1001, 1, 'Discounts Dealer-Item'),
(10017, 10017, 1001, 2, ''),
(10019, 1, 1001, 0, 'Root'),
(10019, 8, 1001, 1, 'Discounts Dealer-Item'),
(10019, 10019, 1001, 2, ''),
(10020, 1, 1001, 0, 'Root'),
(10020, 8, 1001, 1, 'Discounts Dealer-Item'),
(10020, 10020, 1001, 2, '');
--
-- Constraints for dumped tables
--
--
-- Constraints for table `discounts_di`
--
ALTER TABLE `discounts_di`
ADD CONSTRAINT `discounts_di_ibfk_2` FOREIGN KEY (`master`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE,
ADD CONSTRAINT `discounts_di_ibfk_3` FOREIGN KEY (`slave`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE,
ADD CONSTRAINT `discounts_di_ibfk_4` FOREIGN KEY (`id`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE;
--
-- Constraints for table `objects_hier`
--
ALTER TABLE `objects_hier`
ADD CONSTRAINT `objects_hier_ibfk_4` FOREIGN KEY (`id`, `mapped_from`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE,
ADD CONSTRAINT `objects_hier_ibfk_5` FOREIGN KEY (`lnk4`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE;
--
-- Constraints for table `objects_path`
--
ALTER TABLE `objects_path`
ADD CONSTRAINT `objects_path_map` FOREIGN KEY (`id`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
[8 Oct 2004 0:18]
Matthew Lord
Hi Dimitry, Thank you for your bug report! Using the dump you provided, when I execute the querys I get an empty result set. Am I missing anything? I'm trying to repeat the problem using 4.1.5-gamma on windows 2000. Best Regards
[11 Oct 2004 8:48]
Dmitry L
Sorry, i fogot to set the @MAP var; Exec this BEFORE all other queries: SET @MAP = 1001;
[12 Oct 2004 0:16]
Matthew Lord
Hi Dimitry, I'm having trouble seeing the bug here. The subquery: (SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP AND flags_0 & 1 != 1) should not effect the number of rows returned as you've suggested because it is merely a column value in the resultset. The query should always return 1, which it does, so each row has a value of 1 for the column. Could you help me out? What am I missing or not understanding? Best Regards
[12 Oct 2004 0:39]
Matthew Lord
I got the same results using 4.1.5 on windows 2000.
[12 Oct 2004 6:15]
Dmitry L
Ok, i'll try to show the bug another way (i'll use only the row with invalid result):
First set the var:
SET @MAP = 1001;
Exec the standalone subquery
Query:
SELECT flags_0 FROM objects_hier oh WHERE oh.id = 10019 AND oh.map= @MAP
Record set (correct):
+----------------+
| flags_0 |
+----------------+
| 0 |
+----------------+
Query: SELECT flags_0 FROM objects_hier oh WHERE oh.id = 10019 AND oh.map= @MAP
AND flags_0 & 1 != 0;
Record set (correct): Empty set.
Query: SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, dd.id,
(SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP
/*
AND flags_0 & 1 != 0
*/
) flags
FROM objects_path op,
discounts_di dd
WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP)
AND op.map = @MAP
AND dd.map = @MAP
AND dd.slave = op.pid
ORDER BY op.id, dd.master, op.level;
Recordset (correct, take a look at ID and FLAGS columns):
+------------+--------------+----------------+------------+------------+
| slave | master | value | id | flags |
+------------+--------------+----------------+------------+------------+
| 10011 | 10006 | 30.0000 | 10019 | 0 |
| 10012 | 10006 | 40.0000 | 10019 | 0 |
| 10013 | 10006 | 40.0000 | 10019 | 0 |
| 10014 | 10006 | 40.0000 | 10019 | 0 |
+------------+--------------+----------------+------------+------------+
Flag column is "0", but if i incomment the condition & use query:
SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, dd.id,
(SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP
AND flags_0 & 1 != 0
) flags
FROM objects_path op,
discounts_di dd
WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP)
AND op.map = @MAP
AND dd.map = @MAP
AND dd.slave = op.pid
ORDER BY op.id, dd.master, op.level;
Result set is incorrect:
+------------+--------------+----------------+------------+------------+
| slave | master | value | id | flags |
+------------+--------------+----------------+------------+------------+
| 10011 | 10006 | 30.0000 | 10019 | 1 |
| 10012 | 10006 | 40.0000 | 10019 | 1 |
| 10013 | 10006 | 40.0000 | 10019 | 1 |
| 10014 | 10006 | 40.0000 | 10019 | 1 |
+------------+--------------+----------------+------------+------------+
Eg. i got "1"-s for flags where i should have NULLS (cause 1&0 is 0 and subquery MUST return 0 rows as it was shown upper);
And another thins, that, imho, could make smthng cleaner:
When i add a condition to select ONLY records that were invalid in prev. sets (i marked it in query) the recordset is CORRECT(with nulls):
SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, dd.id,
(SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP
AND flags_0 & 1 != 0
) flags
FROM objects_path op,
discounts_di dd
WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP)
AND op.map = @MAP
AND dd.map = @MAP
AND dd.slave = op.pid
-- COND
AND dd.id = 10019
--
ORDER BY op.id, dd.master, op.level;
The set:
+------------+--------------+----------------+------------+------------+
| slave | master | value | id | flags |
+------------+--------------+----------------+------------+------------+
| 10011 | 10006 | 30.0000 | 10019 | NULL |
| 10012 | 10006 | 40.0000 | 10019 | NULL |
| 10013 | 10006 | 40.0000 | 10019 | NULL |
| 10014 | 10006 | 40.0000 | 10019 | NULL |
+------------+--------------+----------------+------------+------------+
[14 Oct 2004 10:34]
Oleksandr Byelkin
I can't repeat this bug on last bk sources and linux: + SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, + (SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP AND + flags_0 & 1 != 0) flags + FROM objects_path op, + discounts_di dd + WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP) + AND op.map = @MAP + AND dd.map = @MAP + AND dd.slave = op.pid + ORDER BY op.id, dd.master, op.level; + slave master value flags + 6 10001 10.0000 1 + 10002 10001 20.0000 1 + 10011 10001 20.0000 1 + 10011 10001 50.0000 1 + 10011 10006 30.0000 NULL + 10012 10001 20.0000 1 + 10012 10001 60.0000 1 + 10012 10006 40.0000 NULL + 10013 10001 20.0000 1 + 10013 10001 60.0000 1 + 10013 10006 40.0000 NULL + 10014 10001 20.0000 1 + 10014 10001 60.0000 1 + 10014 10006 40.0000 NULL But I am not sure that it is something windows-specific, so we will need time to check it on windows.
[15 Oct 2004 12:53]
MySQL Verification Team
Can't repeat it with 4.1.6-gamma-nt-max on Windows 2000.
Description: Subquery returns value but should return NULL How to repeat: SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, (SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP /* AND flags_0 & 1 != 0 */ ) flags FROM objects_path op, discounts_di dd WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP) AND op.map = @MAP AND dd.map = @MAP AND dd.slave = op.pid ORDER BY op.id, dd.master, op.level Recordset (correct) +------------+--------------+----------------+------------+ | slave | master | value | flags | +------------+--------------+----------------+------------+ | 6 | 10001 | 10.0000 | 1 | | 10002 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 50.0000 | 1 | | 10011 | 10006 | 30.0000 | 0 | | 10012 | 10001 | 20.0000 | 1 | | 10012 | 10001 | 60.0000 | 1 | | 10012 | 10006 | 40.0000 | 0 | | 10013 | 10001 | 20.0000 | 1 | | 10013 | 10001 | 60.0000 | 1 | | 10013 | 10006 | 40.0000 | 0 | | 10014 | 10001 | 20.0000 | 1 | | 10014 | 10001 | 60.0000 | 1 | | 10014 | 10006 | 40.0000 | 0 | +------------+--------------+----------------+------------+ SELECT op.id slave, dd.master, if(op.id = op.pid, dd.value_s, dd.value_c) value, (SELECT flags_0 FROM objects_hier oh WHERE oh.id = dd.id AND oh.map= @MAP AND flags_0 & 1 != 0) flags FROM objects_path op, discounts_di dd WHERE op.id IN (SELECT id FROM objects_path WHERE pid = 1 AND map = @MAP) AND op.map = @MAP AND dd.map = @MAP AND dd.slave = op.pid ORDER BY op.id, dd.master, op.level Result recordset +------------+--------------+----------------+------------+ | slave | master | value | flags | +------------+--------------+----------------+------------+ | 6 | 10001 | 10.0000 | 1 | | 10002 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 50.0000 | 1 | | 10011 | 10006 | 30.0000 | 1 | | 10012 | 10001 | 20.0000 | 1 | | 10012 | 10001 | 60.0000 | 1 | | 10012 | 10006 | 40.0000 | 1 | | 10013 | 10001 | 20.0000 | 1 | | 10013 | 10001 | 60.0000 | 1 | | 10013 | 10006 | 40.0000 | 1 | | 10014 | 10001 | 20.0000 | 1 | | 10014 | 10001 | 60.0000 | 1 | | 10014 | 10006 | 40.0000 | 1 | +------------+--------------+----------------+------------+ Instead of: +------------+--------------+----------------+------------+ | slave | master | value | flags | +------------+--------------+----------------+------------+ | 6 | 10001 | 10.0000 | 1 | | 10002 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 20.0000 | 1 | | 10011 | 10001 | 50.0000 | 1 | | 10012 | 10001 | 20.0000 | 1 | | 10012 | 10001 | 60.0000 | 1 | | 10013 | 10001 | 20.0000 | 1 | | 10013 | 10001 | 60.0000 | 1 | | 10014 | 10001 | 20.0000 | 1 | | 10014 | 10001 | 60.0000 | 1 | +------------+--------------+----------------+------------+