Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
I could only reproduce it on bb-11.0 so far.
--source include/have_sequence.inc
|
|
CREATE TABLE t (a INT PRIMARY KEY); |
INSERT INTO t SELECT seq FROM seq_1_to_30; |
|
ANALYZE TABLE t PERSISTENT FOR ALL; |
|
SET IN_PREDICATE_CONVERSION_THRESHOLD=4; |
SELECT a FROM t WHERE a IN ( 1, 1, 2, 194 ); |
SET IN_PREDICATE_CONVERSION_THRESHOLD=100; |
SELECT a FROM t WHERE a IN ( 1, 1, 2, 194 ); |
|
drop table t; |
bb-11.0 |
SET IN_PREDICATE_CONVERSION_THRESHOLD=4; |
SELECT a FROM t WHERE a IN ( 1, 1, 2, 194 ); |
a
|
1
|
1
|
2
|
SET IN_PREDICATE_CONVERSION_THRESHOLD=100; |
SELECT a FROM t WHERE a IN ( 1, 1, 2, 194 ); |
a
|
1
|
2
|
The second one is of course the correct result.
Plan with the wrong result:
explain extended SELECT a FROM t WHERE a IN ( 1, 1, 2, 194 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 4 100.00 |
1 PRIMARY t eq_ref PRIMARY PRIMARY 4 tvc_0._col_1 1 100.00 Using index |
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used |
Warnings:
|
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from (values (1),(1),(2),(194)) `tvc_0` join `test`.`t` where `test`.`t`.`a` = `tvc_0`.`_col_1` |
Reproducible with MyISAM, InnoDB, Aria.
Attachments
Issue Links
- causes
-
MDEV-31022 SIGSEGV in maria_create from create_internal_tmp_table
- Closed
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
- Closed