| 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 = MyISAM PARTITION BY HASH (c) PARTITIONS 3; Warnings: Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. CREATE TABLE t2 (a tinyint) ENGINE = MyISAM; INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13), (14),(15),(16); SET SESSION optimizer_switch="index_merge=on"; SET SESSION optimizer_switch="index_merge_intersection=on"; SET SESSION optimizer_switch="index_merge_union=off"; SET SESSION optimizer_switch="index_merge_sort_union=off"; INSERT INTO t1 VALUES (1,1,0), (1,1,0), (2,1,0), (2,2,1), (1,1,1), (2,2,4); Warnings: Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. # Add some rows to make the index_merge_intersect possible INSERT INTO t1 SELECT 1,1,0 FROM t2 A, t2 B; INSERT INTO t1 SELECT 1,1,1 FROM t2 A, t2 B; INSERT INTO t1 SELECT 1,1,2 FROM t2 A, t2 B LIMIT 68; SELECT COUNT(*) FROM t1; COUNT(*) 586 ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK test.t1 analyze warning The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2; 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 1 100.00 Using intersect(b,a); Using where Warnings: Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 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)) # Before fix: # (p0 - partition 0, R3 - 3rd record in that partition = offset) # Make 'a' read p0-R3, p1-R1, p1-R3 # Make 'b' read p1-R1, p1-R3 # 'b' will skip p1-R1 since R3 is bigger than R1. SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2; a b c 2 2 1 2 2 4 SET @old_opt_switch = @@session.optimizer_switch; SET SESSION optimizer_switch="index_merge_intersection=off"; # Without index_merge_intersection SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2; a b c 2 2 1 2 2 4 EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p0,p1,p2 ref a,b b 3 const 2 2.50 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)) 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 5 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 2 2 1 2 2 4 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 2 2 1 2 2 4 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 586 6.29 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="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 10 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 2 2 1 2 2 4 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 2 2 1 2 2 4 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 586 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;