bk commit into 5.0 tree (igor:1.2278) BUG#21646

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-09-25 06:15:14-07:00, igor@stripped +5 -0
  Fixed bug #21646.
  Presence of a subquery in the ON expression of a join 
  should not block merging the view that contains this join.
  Before this patch the such views were converted into 
  into temporary table views.

  mysql-test/r/view.result@stripped, 2006-09-25 06:15:04-07:00, igor@stripped +14 -0
    Added a test case for bug #21646.

  mysql-test/t/view.test@stripped, 2006-09-25 06:15:04-07:00, igor@stripped +18 -0
    Added a test case for bug #21646.

  sql/mysql_priv.h@stripped, 2006-09-25 06:15:04-07:00, igor@stripped +2 -1
    Fixed bug #21646.
    Added a new parsing state 'IN_ON', true when
    the parser is in an ON expression of a join.

  sql/sql_lex.cc@stripped, 2006-09-25 06:15:04-07:00, igor@stripped +2 -1
    Fixed bug #21646.
    Presence of a subquery in the ON expression of a join 
    should not block merging the view that contains this join.

  sql/sql_yacc.yy@stripped, 2006-09-25 06:15:04-07:00, igor@stripped +8 -0
    Fixed bug #21646.
    Added a new parsing state 'IN_ON', true when
    the parser is in an ON expression of a join.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug21646

--- 1.410/sql/mysql_priv.h	2006-09-25 06:15:23 -07:00
+++ 1.411/sql/mysql_priv.h	2006-09-25 06:15:23 -07:00
@@ -446,7 +446,8 @@
   NO_MATTER,
   IN_HAVING,
   SELECT_LIST,
-  IN_WHERE
+  IN_WHERE,
+  IN_ON
 };
 
 struct st_table;

--- 1.200/sql/sql_lex.cc	2006-09-25 06:15:23 -07:00
+++ 1.201/sql/sql_lex.cc	2006-09-25 06:15:23 -07:00
@@ -1710,7 +1710,8 @@
          unit= unit->next_unit())
     {
       if (unit->first_select()->parent_lex == this &&
-          (unit->item == 0 || unit->item->place() != IN_WHERE))
+          (unit->item == 0 ||
+           (unit->item->place() != IN_WHERE && unit->item->place() !=
IN_ON)))
       {
         selects_allow_merge= 0;
         break;

--- 1.487/sql/sql_yacc.yy	2006-09-25 06:15:23 -07:00
+++ 1.488/sql/sql_yacc.yy	2006-09-25 06:15:23 -07:00
@@ -5212,11 +5212,13 @@
             /* Change the current name resolution context to a local context. */
             if (push_new_name_resolution_context(YYTHD, $1, $3))
               YYABORT;
+            Select->parsing_place= IN_ON;
           }
           expr
 	  {
             add_join_on($3,$6);
             Lex->pop_context();
+            Select->parsing_place= NO_MATTER;
           }
         | table_ref STRAIGHT_JOIN table_factor
           ON
@@ -5225,12 +5227,14 @@
             /* Change the current name resolution context to a local context. */
             if (push_new_name_resolution_context(YYTHD, $1, $3))
               YYABORT;
+            Select->parsing_place= IN_ON;
           }
           expr
           {
             $3->straight=1;
             add_join_on($3,$6);
             Lex->pop_context();
+            Select->parsing_place= NO_MATTER;
           }
 	| table_ref normal_join table_ref
 	  USING
@@ -5254,6 +5258,7 @@
             /* Change the current name resolution context to a local context. */
             if (push_new_name_resolution_context(YYTHD, $1, $5))
               YYABORT;
+            Select->parsing_place= IN_ON;
           }
           expr
 	  {
@@ -5261,6 +5266,7 @@
             Lex->pop_context();
             $5->outer_join|=JOIN_TYPE_LEFT;
             $$=$5;
+            Select->parsing_place= NO_MATTER;
           }
 	| table_ref LEFT opt_outer JOIN_SYM table_factor
 	  {
@@ -5285,6 +5291,7 @@
             /* Change the current name resolution context to a local context. */
             if (push_new_name_resolution_context(YYTHD, $1, $5))
               YYABORT;
+            Select->parsing_place= IN_ON;
           }
           expr
           {
@@ -5293,6 +5300,7 @@
               YYABORT;
             add_join_on($$, $8);
             Lex->pop_context();
+            Select->parsing_place= NO_MATTER;
           }
 	| table_ref RIGHT opt_outer JOIN_SYM table_factor
 	  {

--- 1.176/mysql-test/r/view.result	2006-09-25 06:15:23 -07:00
+++ 1.177/mysql-test/r/view.result	2006-09-25 06:15:23 -07:00
@@ -2935,4 +2935,18 @@
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1(pk int PRIMARY KEY);
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+CREATE ALGORITHM=MERGE VIEW v1 AS 
+SELECT t1.*
+FROM t1 JOIN t2 
+ON t2.fk = t1.pk AND 
+t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+Level	Code	Message
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS
select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver`
= (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`))))))
+DROP VIEW v1;
+DROP TABLE t1, t2;
 End of 5.0 tests.

--- 1.163/mysql-test/t/view.test	2006-09-25 06:15:23 -07:00
+++ 1.164/mysql-test/t/view.test	2006-09-25 06:15:23 -07:00
@@ -2850,4 +2850,22 @@
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+#
+# Bug #21646: view qith a subquery in ON expression 
+#
+
+CREATE TABLE t1(pk int PRIMARY KEY); 
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+
+CREATE ALGORITHM=MERGE VIEW v1 AS 
+SELECT t1.*
+  FROM t1 JOIN t2 
+       ON t2.fk = t1.pk AND 
+          t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+SHOW CREATE VIEW v1;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
 --echo End of 5.0 tests.