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.gluk...@sun.com-20100319060102-57ykzjf4pc93avy1
> parent: o...@mysql.com-20100318064207-l3ap0mpxt510b4n3
> committer: Sergey Glukhov <sergey.gluk...@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 `t...@1`.`t@1`;
> DROP DATABASE `t...@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 `t...@1`.`t@1`;
> DROP DATABASE `t...@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
>
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
