maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

Kristian,

Below you'll find a patch for bug #53334 (against mysql-5.1.46).

I investigated other latest fixes pushed by Sergey Glukhov.

The fix for bugs ##51242/52336 looked suspicious for me.
And it actually provides a work-around rather than fixes the following
bug that causes both failures:

In the function JOIN::exec
there should be
      Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
						 used_tables,
                                                 (table_map) 0);
instead of
       Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
						 used_tables,
						 used_tables);

Regards,
Igor.

3447 Igor Babaev	2010-05-02
      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-03-17 14:18:46 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-05-03 04:22:37 +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-03-17 14:18:46 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-05-03 04:22:37 +0000
@@ -589,4 +589,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-12 10:12:20 +0000
+++ b/sql/sql_select.cc	2010-05-03 04:22:37 +0000
@@ -2968,8 +2968,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



Kristian Nielsen wrote:
> 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@xxxxxxx-20100319060102-57ykzjf4pc93avy1
> parent: omer@xxxxxxxxx-20100318064207-l3ap0mpxt510b4n3
> committer: Sergey Glukhov <Sergey.Glukhov@xxxxxxx>
> 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
>