MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:April 15 2010 7:38am
Subject:bzr commit into mysql-next-mr-bugfixing branch (tor.didriksen:3075) Bug#49097
View as plain text  
#At file:///export/home/didrik/mysqldev-next-mr/next-mr-opt-backporting-br2/ based on revid:tor.didriksen@stripped

 3075 Tor Didriksen	2010-04-15
      Bug#49097 subquery with view generates wrong result with non-prepared statement.
      
      backport of tor.didriksen@stripped
      
      The problem was that flatten_subqueries() would only flatten the
      original where clause of the outer query, thus losing the
      where clause of the view.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        In flatten_subqueries():
        Use the augmented 'conds' which is produced by setup_conds() when preparing views.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2010-04-15 07:27:26 +0000
+++ b/mysql-test/r/subselect_sj.result	2010-04-15 07:38:15 +0000
@@ -987,3 +987,73 @@ Algeria	2
 drop table t1, t2;
 drop view v1;
 # End of bug#48073
+
+Bug#49097 subquery with view generates wrong result with
+non-prepared statement
+
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'XAmerican Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+;
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM v1
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+PREPARE stmt FROM
+"
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+";
+execute stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+deallocate prepare stmt;
+drop table t1, t2;
+drop view v1;
+# End of Bug#49097

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2010-04-15 07:27:26 +0000
+++ b/mysql-test/t/subselect_sj.test	2010-04-15 07:38:15 +0000
@@ -682,3 +682,73 @@ drop table t1, t2;
 drop view v1;
 
 --echo # End of bug#48073
+
+--echo
+--echo Bug#49097 subquery with view generates wrong result with
+--echo           non-prepared statement
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  city VARCHAR(50) NOT NULL,
+  country_id SMALLINT UNSIGNED NOT NULL
+);
+
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+
+CREATE TABLE t2 (
+  country_id SMALLINT UNSIGNED NOT NULL,
+  country VARCHAR(50) NOT NULL
+);
+
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'XAmerican Samoa') ;
+
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+; 
+
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM t2
+  WHERE LEFT(country,1) = "A" 
+);
+
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+
+PREPARE stmt FROM
+"
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+";
+
+execute stmt;
+
+deallocate prepare stmt;
+drop table t1, t2;
+drop view v1;
+
+--echo # End of Bug#49097

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-04-15 07:27:26 +0000
+++ b/sql/sql_select.cc	2010-04-15 07:38:15 +0000
@@ -665,8 +665,9 @@ JOIN::prepare(Item ***rref_pointer_array
         }
       }
 
-      /* Register the subquery for further processing */
-      select_lex->outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
+      /* Register the subquery for further processing in flatten_subqueries() */
+      select_lex->
+        outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
       in_subs->expr_join_nest= thd->thd_marker.emb_on_expr_nest;
     }
     else
@@ -3609,8 +3610,18 @@ bool JOIN::flatten_subqueries()
   for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back(); 
        in_subq != in_subq_end; in_subq++)
   {
-    JOIN *child_join= (*in_subq)->unit->first_select()->join;
+    st_select_lex *child_select= (*in_subq)->get_select_lex();
+    JOIN *child_join= child_select->join;
     child_join->outer_tables = child_join->tables;
+
+    /*
+      child_select->where contains only the WHERE predicate of the
+      subquery itself here. We may be selecting from a VIEW, which has its
+      own predicate. The combined predicates are available in child_join->conds,
+      which was built by setup_conds() doing prepare_where() for all views.
+    */
+    child_select->where= child_join->conds;
+
     if (child_join->flatten_subqueries())
       DBUG_RETURN(TRUE);
     (*in_subq)->sj_convert_priority= 


Attachment: [text/bzr-bundle] bzr/tor.didriksen@sun.com-20100415073815-2e4hm4xanna2a1q6.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (tor.didriksen:3075) Bug#49097Tor Didriksen15 Apr