Query returns rows where no result is expected (impossible WHERE) in maria-5.3-mwl89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
The following query:
SELECT table1 .`col_varchar_
FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
WHERE ( 's' , 'm' ) IN (
SELECT `col_varchar_nokey` , `col_varchar_key`
FROM C ) ;
returns rows even though there is no value 's' in table C
Test case:
SET LOCAL optimizer_
--disable_warnings
DROP TABLE /*! IF EXISTS */ A;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings
CREATE TABLE `A` (
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (NULL,2,'w','w');
INSERT INTO `C` VALUES (7,9,'m','m');
INSERT INTO `C` VALUES (9,3,'m','m');
INSERT INTO `C` VALUES (7,9,'k','k');
INSERT INTO `C` VALUES (4,NULL,'r','r');
INSERT INTO `C` VALUES (2,9,'t','t');
INSERT INTO `C` VALUES (6,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (NULL,8,'h','h');
INSERT INTO `C` VALUES (5,53,'o','o');
INSERT INTO `C` VALUES (NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (6,5,'k','k');
INSERT INTO `C` VALUES (188,166,'e','e');
INSERT INTO `C` VALUES (2,3,'n','n');
INSERT INTO `C` VALUES (1,0,'t','t');
INSERT INTO `C` VALUES (1,1,'c','c');
INSERT INTO `C` VALUES (0,9,'m','m');
INSERT INTO `C` VALUES (9,5,'y','y');
INSERT INTO `C` VALUES (NULL,6,'f','f');
INSERT INTO `C` VALUES (4,2,'d','d');
SELECT table1 .`col_varchar_
FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
WHERE ( 's' , 'm' ) IN (
SELECT `col_varchar_nokey` , `col_varchar_key`
FROM C ) ;
Related branches
Changed in maria: | |
status: | New → In Progress |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
maria-5.3, as well as running the same query within a view produce the correct result - empty result set.