bzr commit into mysql-5.1-bugteam branch (ramil:2839) Bug#42957

[Ramil Kalimullin]

#At file:///home/ram/mysql/b42957-5.1-bugteam/ based on revid:aelkin@stripped

 2839 Ramil Kalimullin	2009-03-16
      Fix for bug #42957: no results from 
      select where .. (col=col and col=col) or ... (false expression)
      
      Problem: optimizer didn't take into account a singular case 
      when we eliminated all the predicates at the AND level of WHERE.
      That may lead to wrong results.
      
      Fix: replace (a=a AND a=a...) with TRUE if we eliminated all the
      predicates.
     @ mysql-test/r/select.result
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - test result.
     @ mysql-test/t/select.test
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - test case.
     @ sql/sql_select.cc
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - replacing equality predicates by multiple equality items check
        if we eliminate all the predicates at the AND level and 
        replace them with TRUE if so.

    modified:
      mysql-test/r/select.result
      mysql-test/t/select.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2009-02-10 13:27:35 +0000
+++ b/mysql-test/r/select.result	2009-03-16 05:02:10 +0000
@@ -4416,4 +4416,32 @@ date_nokey
 Warnings:
 Warning	1292	Incorrect date value: '10:41:7' for column 'date_nokey' at row 1
 DROP TABLE A,C;
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+INSERT INTO t1 VALUES (1, 1);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1
+SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
+a	b
+1	1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2008-12-24 15:26:48 +0000
+++ b/mysql-test/t/select.test	2009-03-16 05:02:10 +0000
@@ -3769,4 +3769,20 @@ SELECT date_nokey FROM C 
 
 DROP TABLE A,C;
 
+#
+# Bug #42957: no results from 
+# select where .. (col=col and col=col) or ... (false expression)
+#
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+INSERT INTO t1 VALUES (1, 1);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
+SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20;
+DROP TABLE t1;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-02-19 18:30:05 +0000
+++ b/sql/sql_select.cc	2009-03-16 05:02:10 +0000
@@ -7625,7 +7625,7 @@ static COND *build_equal_items_for_cond(
     if (and_level)
     {
       /*
-         Retrieve all conjucts of this level detecting the equality
+         Retrieve all conjuncts of this level detecting the equality
          that are subject to substitution by multiple equality items and
          removing each such predicate from the conjunction after having 
          found/created a multiple equality whose inference the predicate is.
@@ -7641,6 +7641,13 @@ static COND *build_equal_items_for_cond(
           li.remove();
       }
 
+      /*
+        Check if we eliminated all the predicates of the level, e.g.
+        (a=a AND b=b AND a=a)
+      */
+      if (!(args->elements + cond_equal.current_level.elements + eq_list.elements))
+        return new Item_int((longlong) 1,1);
+
       List_iterator_fast<Item_equal> it(cond_equal.current_level);
       while ((item_equal= it++))
       {


Attachment: [text/bzr-bundle] bzr/[e-mail]