May 2010 - developers - lists.mariadb.org

[Maria-developers] Please check optimiser regression in MySQL 5.1.46
by Kristian Nielsen 03 May '10

03 May '10

Hi Igor, Timour, Sergey, Can one of you please check this patch from MySQL 5.1.46? It is a commit to fix http://bugs.mysql.com/bug.php?id=51494 This patch introduces a regression: http://bugs.mysql.com/bug.php?id=53334 If I revert the patch, the regression disappears. And interestingly, the included test case does not fail even when the fix is reverted (go figure...) So it would be good if one of you could check the patch and check what is wrong with it, and if a different fix for Bug#51494 is needed. (needed to complete merge of MySQL 5.1.46). Thanks, - Kristian. ------------------------------------------------------------ revno: 3407.1.1 revision-id: sergey.glukhov(a)sun.com-20100319060102-57ykzjf4pc93avy1 parent: omer(a)mysql.com-20100318064207-l3ap0mpxt510b4n3 committer: Sergey Glukhov <Sergey.Glukhov(a)sun.com> branch nick: mysql-5.1-bugteam timestamp: Fri 2010-03-19 10:01:02 +0400 message: Bug#51494 crash with join, explain and 'sounds like' operator The crash happens because of discrepancy between values of conts_tables and join->const_table_map(make_join_statisctics). Calculation of conts_tables used condition with HA_STATS_RECORDS_IS_EXACT flag check. Calculation of join->const_table_map does not use this flag check. In case of MERGE table without union with index the table does not become const table and thus join_read_const_table() is not called for the table. join->const_table_map supposes this table is const and later in make_join_select this table is used for making&calculation const condition. As table record buffer is not populated it leads to crash. The fix is adding a check if an engine supports HA_STATS_RECORDS_IS_EXACT flag before updating join->const_table_map. diff: === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2010-03-14 16:01:45 +0000 +++ sql/sql_select.cc 2010-03-19 06:01:02 +0000 @@ -2943,7 +2943,8 @@ s->quick=select->quick; s->needed_reg=select->needed_reg; select->quick=0; - if (records == 0 && s->table->reginfo.impossible_range) + if (records == 0 && s->table->reginfo.impossible_range && + (s->table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) { /* Impossible WHERE or ON expression === modified file 'mysql-test/r/merge.result' --- mysql-test/r/merge.result 2010-03-03 10:49:03 +0000 +++ mysql-test/r/merge.result 2010-03-19 06:01:02 +0000 @@ -2286,4 +2286,16 @@ DROP TABLE m1; DROP TABLE `test@1`.`t@1`; DROP DATABASE `test@1`; +# +# Bug#51494c rash with join, explain and 'sounds like' operator +# +CREATE TABLE t1 (a INT) ENGINE=MYISAM; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, +KEY idx0 (d, c)) ENGINE=MERGE; +EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN +t2 WHERE b SOUNDS LIKE e AND d = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t2, t1; End of 5.1 tests === modified file 'mysql-test/t/merge.test' --- mysql-test/t/merge.test 2010-03-03 10:49:03 +0000 +++ mysql-test/t/merge.test 2010-03-19 06:01:02 +0000 @@ -1690,4 +1690,19 @@ DROP TABLE `test@1`.`t@1`; DROP DATABASE `test@1`; +--echo # +--echo # Bug#51494c rash with join, explain and 'sounds like' operator +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MYISAM; +INSERT INTO t1 VALUES(1); + +CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, +KEY idx0 (d, c)) ENGINE=MERGE; + +EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN +t2 WHERE b SOUNDS LIKE e AND d = 1; + +DROP TABLE t2, t1; + --echo End of 5.1 tests

2 3

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2858) Bug#53334
by knielsen＠knielsen-hq.org 03 May '10

03 May '10

#At lp:maria 2858 knielsen(a)knielsen-hq.org 2010-05-03 Fixed bug #53334. The fix actually reverts the change introduced by the patch for bug 51494 The fact is that the patch for bug 52177 fixes bug 51194 as well. modified: mysql-test/r/innodb_mysql.result mysql-test/t/innodb_mysql.test sql/sql_select.cc === modified file 'mysql-test/r/innodb_mysql.result' --- a/mysql-test/r/innodb_mysql.result 2010-04-28 12:52:24 +0000 +++ b/mysql-test/r/innodb_mysql.result 2010-05-03 08:44:39 +0000 @@ -2350,4 +2350,34 @@ Null Index_type BTREE Comment DROP TABLE t1; +# +# Bug #53334: wrong result for outer join with impossible ON condition +# (see the same test case for MyISAM in join.test) +# +create table t1 (id int primary key); +create table t2 (id int); +insert into t1 values (75); +insert into t1 values (79); +insert into t1 values (78); +insert into t1 values (77); +replace into t1 values (76); +replace into t1 values (76); +insert into t1 values (104); +insert into t1 values (103); +insert into t1 values (102); +insert into t1 values (101); +insert into t1 values (105); +insert into t1 values (106); +insert into t1 values (107); +insert into t2 values (107),(75),(1000); +select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 +where t2.id=75 and t1.id is null; +id id +NULL 75 +explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 +where t2.id=75 and t1.id is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +drop table t1,t2; End of 5.1 tests === modified file 'mysql-test/t/innodb_mysql.test' --- a/mysql-test/t/innodb_mysql.test 2010-04-28 12:52:24 +0000 +++ b/mysql-test/t/innodb_mysql.test 2010-05-03 08:44:39 +0000 @@ -591,4 +591,35 @@ ALTER TABLE t1 DROP INDEX k, ADD UNIQUE DROP TABLE t1; +--echo # +--echo # Bug #53334: wrong result for outer join with impossible ON condition +--echo # (see the same test case for MyISAM in join.test) +--echo # + +create table t1 (id int primary key); +create table t2 (id int); + +insert into t1 values (75); +insert into t1 values (79); +insert into t1 values (78); +insert into t1 values (77); +replace into t1 values (76); +replace into t1 values (76); +insert into t1 values (104); +insert into t1 values (103); +insert into t1 values (102); +insert into t1 values (101); +insert into t1 values (105); +insert into t1 values (106); +insert into t1 values (107); + +insert into t2 values (107),(75),(1000); + +select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 + where t2.id=75 and t1.id is null; +explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 + where t2.id=75 and t1.id is null; + +drop table t1,t2; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-04-28 12:52:24 +0000 +++ b/sql/sql_select.cc 2010-05-03 08:44:39 +0000 @@ -3008,8 +3008,7 @@ make_join_statistics(JOIN *join, TABLE_L s->quick=select->quick; s->needed_reg=select->needed_reg; select->quick=0; - if (records == 0 && s->table->reginfo.impossible_range && - (s->table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) + if (records == 0 && s->table->reginfo.impossible_range) { /* Impossible WHERE or ON expression

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2857)
by knielsen＠knielsen-hq.org 02 May '10

02 May '10

#At lp:maria 2857 knielsen(a)knielsen-hq.org 2010-05-02 5.1.46 after-merge fixes: must FLUSH TABLES before copying .frm. modified: mysql-test/r/myisam.result mysql-test/t/myisam.test === modified file 'mysql-test/r/myisam.result' --- a/mysql-test/r/myisam.result 2010-04-28 12:52:24 +0000 +++ b/mysql-test/r/myisam.result 2010-05-02 06:47:28 +0000 @@ -2379,6 +2379,7 @@ DROP TABLE t1; CREATE TABLE t1(a INT, b BIT(1)); INSERT INTO t1 VALUES(1, 0), (2, 1); CREATE TABLE t2 SELECT * FROM t1; +FLUSH TABLES; CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 3775188275 === modified file 'mysql-test/t/myisam.test' --- a/mysql-test/t/myisam.test 2010-04-28 12:52:24 +0000 +++ b/mysql-test/t/myisam.test 2010-05-02 06:47:28 +0000 @@ -1641,6 +1641,7 @@ DROP TABLE t1; CREATE TABLE t1(a INT, b BIT(1)); INSERT INTO t1 VALUES(1, 0), (2, 1); CREATE TABLE t2 SELECT * FROM t1; +FLUSH TABLES; --copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t3.frm --copy_file $MYSQLD_DATADIR/test/t1.MYD $MYSQLD_DATADIR/test/t3.MYD --copy_file $MYSQLD_DATADIR/test/t1.MYI $MYSQLD_DATADIR/test/t3.MYI

1 0