Wrong result with subquery in the select list in maria-5.3-mwl89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
The following query:
SELECT (
SELECT 1 ) , MIN( table1 .`col_int_nokey` )
FROM C table1 JOIN BB table2 ON table2 .`col_int_nokey` = table1 .`pk`
WHERE ( table1 .`col_varchar_key` , table1 .`col_varchar_key` ) IN (
SELECT SUBQUERY2_t2 .`col_varchar_
FROM CC SUBQUERY2_t1 STRAIGHT_JOIN BB SUBQUERY2_t2 ON SUBQUERY2_t1 .`col_varchar_
returns 0 in maria-5.3-mwl89, even though the SELECT 1 should have returned 1 . When the query is executed in maria-5.3 or in a prepared statement, 1 is returned.
Test case:
SET SESSION optimizer_switch = 'materializatio
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'r','r');
INSERT INTO `CC` VALUES (12,5,9,'a','a');
INSERT INTO `CC` VALUES (13,3,186,'m','m');
INSERT INTO `CC` VALUES (14,6,NULL,
INSERT INTO `CC` VALUES (15,92,2,'j','j');
INSERT INTO `CC` VALUES (16,7,3,'d','d');
INSERT INTO `CC` VALUES (17,NULL,
INSERT INTO `CC` VALUES (18,3,133,'e','e');
INSERT INTO `CC` VALUES (19,5,1,'h','h');
INSERT INTO `CC` VALUES (20,1,8,'b','b');
INSERT INTO `CC` VALUES (21,2,5,'s','s');
INSERT INTO `CC` VALUES (22,NULL,
INSERT INTO `CC` VALUES (23,1,8,'j','j');
INSERT INTO `CC` VALUES (24,0,6,'e','e');
INSERT INTO `CC` VALUES (25,210,
INSERT INTO `CC` VALUES (26,8,4,'v','v');
INSERT INTO `CC` VALUES (27,7,7,'x','x');
INSERT INTO `CC` VALUES (28,5,6,'m','m');
INSERT INTO `CC` VALUES (29,NULL,
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,'w','w');
INSERT INTO `C` VALUES (2,7,9,'m','m');
INSERT INTO `C` VALUES (3,9,3,'m','m');
INSERT INTO `C` VALUES (4,7,9,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'t','t');
INSERT INTO `C` VALUES (7,6,3,'j','j');
INSERT INTO `C` VALUES (8,8,8,'u','u');
INSERT INTO `C` VALUES (9,NULL,8,'h','h');
INSERT INTO `C` VALUES (10,5,53,'o','o');
INSERT INTO `C` VALUES (11,NULL,
INSERT INTO `C` VALUES (12,6,5,'k','k');
INSERT INTO `C` VALUES (13,188,
INSERT INTO `C` VALUES (14,2,3,'n','n');
INSERT INTO `C` VALUES (15,1,0,'t','t');
INSERT INTO `C` VALUES (16,1,1,'c','c');
INSERT INTO `C` VALUES (17,0,9,'m','m');
INSERT INTO `C` VALUES (18,9,5,'y','y');
INSERT INTO `C` VALUES (19,NULL,
INSERT INTO `C` VALUES (20,4,2,'d','d');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,NULL,NULL);
SELECT (
SELECT 1 ) , MIN( table1 .`col_int_nokey` )
FROM C table1 JOIN BB table2 ON table2 .`col_int_nokey` = table1 .`pk`
WHERE ( table1 .`col_varchar_key` , table1 .`col_varchar_key` ) IN (
SELECT SUBQUERY2_t2 .`col_varchar_
FROM CC SUBQUERY2_t1 STRAIGHT_JOIN BB SUBQUERY2_t2 ON SUBQUERY2_t1 .`col_varchar_
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Appears to be fixed in the latest 5.3-mwl89 tree.