Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.2, 10.3
Description
Please check the following test case kindly supplied by a customer of ours.
The 2nd query produces wrong resultset (should list all rows , it only lists those
explicited by the second condition).
If optimizer switch semijoin is turned off, it will produce correct results.
This bug is not present in Oracle MySQL 5.6.
CREATE TABLE `store_loan` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`local_name` varchar(64) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
|
|
|
insert into `store_loan`(`id`,`local_name`) values
|
(1,'Cash Advance'),
|
(2,'Cash Advance'),
|
(3,'Rollover'),
|
(4,'AL Installment'),
|
(5,'AL Installment'),
|
(6,'AL Installment'),
|
(7,'AL Installment'),
|
(8,'AL Installment'),
|
(9,'AL Installment'),
|
(10,'Internet Payday'),
|
(11,'Rollover - Internet Payday'),
|
(12,'AL Monthly Installment'),
|
(13,'AL Semi-Monthly Installment');
|
|
|
## Make sure @@Optimizer_switch semijoin=on is set
|
## current settings in my env is: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
|
##
|
## I would expect both queries below to return the same result set since the sub-queries in query 1 returns the same data s the IN() clause in query 2
|
|
|
#Query 1
|
SELECT SQL_NO_CACHE sl.id
|
FROM store_loan sl
|
WHERE (
|
sl.id IN (SELECT A.id FROM store_loan AS A WHERE A.local_name IN (SELECT B.local_name FROM store_loan AS B WHERE B.id IN (0,4,12,13,1,10,3,11)))
|
OR
|
(sl.id IN (0,4,12,13,1,10,3,11))
|
);
|
+----+
|
| id |
|
+----+
|
| 1 |
|
| 3 |
|
| 4 |
|
| 10 |
|
| 11 |
|
| 12 |
|
| 13 |
|
+----+
|
7 rows in set (0.01 sec)
|
|
|
#Query 2
|
SELECT SQL_NO_CACHE sl.id
|
FROM store_loan sl
|
WHERE (
|
sl.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13)
|
OR
|
(sl.id IN (0,4,12,13,1,10,3,11))
|
);
|
+----+
|
| id |
|
+----+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
| 5 |
|
| 6 |
|
| 7 |
|
| 8 |
|
| 9 |
|
| 10 |
|
| 11 |
|
| 12 |
|
| 13 |
|
+----+
|
13 rows in set (0.00 sec)
|