Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
returns no rows when executed with partial_match_rowid_merge even though the WHERE clause is TRUE always. Even though this particular example has 1 row in t2, the issue was also observed with t2 having more than 1 row, in which case only some of the rows were returned.
Explain:
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 3 |
test case:
CREATE TABLE t1 ( f1 integer NOT NULL , f2 integer) ;
INSERT INTO t1 VALUES (3,3),(48,NULL),(49,1);
CREATE TABLE t2 ( f3 int) ;
INSERT INTO t2 VALUES (5);
SET SESSION optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
revision-id: <email address hidden>
date: 2011-09-22 01:55:17 +0400
build-date: 2011-09-22 09:45:27 +0300
revno: 3190
branch-nick: maria-5.3
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 856152