| Server IP : 185.86.78.101 / Your IP : 216.73.216.213 Web Server : Apache System : Linux 675867-vds-valikoshka1996.gmhost.pp.ua 5.4.0-150-generic #167-Ubuntu SMP Mon May 15 17:35:05 UTC 2023 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : OFF Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
# # Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK # CREATE TABLE t1 (a smallint, b smallint, c smallint, KEY a (a), KEY b (b) ) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 3; # c will simulate DB_ROW_ID + force to chosen partition. # c is unique so we can identify rows in the test. # Fillers to make the optimizer choose index_merge_intersect/union: INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9); # row N..N+3 INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21); # More index matching rows for index_merge_intersect: N+4, N+5 INSERT INTO t1 VALUES (2,2,1+24); INSERT INTO t1 VALUES (2,1,1+27); CREATE TABLE t2 (a int primary key) ENGINE = InnoDB; INSERT INTO t2 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); INSERT INTO t1 SELECT 1, 1, 97 FROM t2 LIMIT 10; INSERT INTO t1 SELECT 2, 1, 98 FROM t2 LIMIT 4; INSERT INTO t1 SELECT 1, 2, 99 FROM t2 LIMIT 4; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SET @old_opt_switch = @@session.optimizer_switch; SET SESSION optimizer_switch="index_merge=on"; SET SESSION optimizer_switch="index_merge_intersection=on"; SET SESSION optimizer_switch="index_merge_sort_union=off"; SET SESSION optimizer_switch="index_merge_union=off"; EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 index_merge a,b a,b 3,3 NULL 2 11.11 Using intersect(a,b); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` = 2) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; a b c 2 2 17 2 2 20 2 2 25 EXPLAIN SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p1 index_merge a,b a,b 3,3 NULL 1 30.00 Using intersect(a,b); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 2) and (`test`.`t1`.`a` = 2) and (`test`.`t1`.`c` in (13,25,28))) SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28); a b c 2 2 25 SET SESSION optimizer_switch="index_merge_intersection=off"; SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; a b c 2 2 25 2 2 17 2 2 20 EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 ref a,b a 3 const 8 3.57 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` = 2) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) # Adding more fillers to get index_merge_union INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 32; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SET SESSION optimizer_switch="index_merge_union=on"; EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 index_merge a,b b,a 3,3 NULL 17 11.11 Using union(b,a); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` = 2) or (`test`.`t1`.`a` = 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; a b c 1 2 13 2 2 17 2 2 20 1 2 24 2 2 25 2 1 28 2 1 98 2 1 98 2 1 98 2 1 98 1 2 99 1 2 99 1 2 99 1 2 99 SET SESSION optimizer_switch="index_merge_union=off"; SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; a b c 1 2 24 1 2 99 1 2 99 1 2 99 1 2 99 1 2 13 2 2 25 2 1 28 2 2 17 2 2 20 2 1 98 2 1 98 2 1 98 2 1 98 EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 ALL a,b NULL NULL NULL 60 7.22 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` = 2) or (`test`.`t1`.`a` = 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) Insert a few more rows to trigger sort_union INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 48; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SET SESSION optimizer_switch="index_merge_sort_union=on"; EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 index_merge a,b b,a 3,3 NULL 17 11.11 Using sort_union(b,a); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` >= 2) or (`test`.`t1`.`a` >= 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) # Not affected, added for completeness... SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; a b c 1 2 13 2 2 17 2 2 20 1 2 24 2 2 25 2 1 28 2 1 98 2 1 98 2 1 98 2 1 98 1 2 99 1 2 99 1 2 99 1 2 99 SET SESSION optimizer_switch="index_merge_sort_union=off"; SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; a b c 1 2 24 1 2 99 1 2 99 1 2 99 1 2 99 1 2 13 2 2 25 2 1 28 2 2 17 2 2 20 2 1 98 2 1 98 2 1 98 2 1 98 EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 ALL a,b NULL NULL NULL 108 6.17 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` >= 2) or (`test`.`t1`.`a` >= 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100)) SET @@session.optimizer_switch = @old_opt_switch; DROP TABLE t1, t2;