bk commit into 5.0 tree (igor:1.1826) BUG#6106

[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
  1.1826 05/04/13 23:06:37 igor@stripped +6 -0
  information_schema.result, information_schema.test:
    Added a test in connection with the fix for
    bug #6106.
  view.result, view.test:
    Added test cases for bugs #6106/6107.
  sql_show.cc:
    The addition of the case for items of the type REF_ITEM in the
    function uses_only_table_name_fields became necessary after
    the fix for bug #6106.
  sql_base.cc:
    The problem was due to the fact that two different column
    references were glued together though one of them belonged to
    a subquery while another to an outer query. This caused
    eventually a wrong calculation of values for the used_tables
    attribute.

  mysql-test/r/information_schema.result
    1.46 05/04/13 23:04:11 igor@stripped +17 -0
    Added a test in connection with the fix for
    bug #6106.

  mysql-test/t/information_schema.test
    1.33 05/04/13 23:03:09 igor@stripped +5 -0
    Added a test in connection with the fix for
    bug #6106.

  mysql-test/r/view.result
    1.76 05/04/13 23:02:10 igor@stripped +82 -0
    Added test cases for bugs #6106/6107.

  mysql-test/t/view.test
    1.66 05/04/13 23:01:24 igor@stripped +62 -0
    Added test cases for bugs #6106/6107.

  sql/sql_show.cc
    1.236 05/04/13 23:00:14 igor@stripped +2 -0
    The addition of the case for items of the type REF_ITEM in the
    function uses_only_table_name_fields became necessary after
    the fix for bug #6106.

  sql/sql_base.cc
    1.235 05/04/13 22:54:50 igor@stripped +1 -1
    The problem was due to the fact that two different column
    references were glued together though one of them belonged to
    a subquery while another to an outer query. This caused
    eventually a wrong calculation of values for the used_tables
    attribute.

# 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/mysql-5.0-0

--- 1.234/sql/sql_base.cc	Tue Apr  5 04:22:04 2005
+++ 1.235/sql/sql_base.cc	Wed Apr 13 22:54:50 2005
@@ -3588,7 +3588,7 @@
 
   thd->set_query_id=1;
 
-  thd->lex->current_select->no_wrap_view_item= 1;
+  thd->lex->current_select->no_wrap_view_item= 0;
   select_lex->cond_count= 0;
   if (*conds)
   {

--- 1.235/sql/sql_show.cc	Wed Apr  6 09:43:28 2005
+++ 1.236/sql/sql_show.cc	Wed Apr 13 23:00:14 2005
@@ -1704,6 +1704,8 @@
                                strlen(item_field->field_name), 0)))
       return 0;
   }
+  else if (item->type() == Item::REF_ITEM)
+    return uses_only_table_name_fields(item->real_item(), table);
   if (item->type() == Item::SUBSELECT_ITEM &&
       !item->const_item())
     return 0;

--- 1.75/mysql-test/r/view.result	Mon Apr  4 12:43:54 2005
+++ 1.76/mysql-test/r/view.result	Wed Apr 13 23:02:10 2005
@@ -1612,3 +1612,85 @@
 insert into t2 select x from v1;
 drop view v1;
 drop table t1,t2,t3;
+CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10));
+INSERT INTO t1 VALUES(1,'trudy');
+INSERT INTO t1 VALUES(2,'peter');
+INSERT INTO t1 VALUES(3,'sanja');
+INSERT INTO t1 VALUES(4,'monty');
+INSERT INTO t1 VALUES(5,'david');
+INSERT INTO t1 VALUES(6,'kent');
+INSERT INTO t1 VALUES(7,'carsten');
+INSERT INTO t1 VALUES(8,'ranger');
+INSERT INTO t1 VALUES(10,'matt');
+CREATE TABLE t2 (col1 int, col2 int, col3 char(1));
+INSERT INTO t2 VALUES (1,1,'y');
+INSERT INTO t2 VALUES (1,2,'y');
+INSERT INTO t2 VALUES (2,1,'n');
+INSERT INTO t2 VALUES (3,1,'n');
+INSERT INTO t2 VALUES (4,1,'y');
+INSERT INTO t2 VALUES (4,2,'n');
+INSERT INTO t2 VALUES (4,3,'n');
+INSERT INTO t2 VALUES (6,1,'n');
+INSERT INTO t2 VALUES (8,1,'y');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT a.col1,a.col2,b.col2,b.col3 
+FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
+WHERE b.col2 IS NULL OR 
+b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+col1	col2	col2	col3
+1	trudy	2	y
+2	peter	1	n
+3	sanja	1	n
+4	monty	3	n
+5	david	NULL	NULL
+6	kent	1	n
+7	carsten	NULL	NULL
+8	ranger	1	y
+10	matt	NULL	NULL
+SELECT a.col1,a.col2,b.col2,b.col3 
+FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
+WHERE b.col2 IS NULL OR 
+b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+col1	col2	col2	col3
+1	trudy	2	y
+2	peter	1	n
+3	sanja	1	n
+4	monty	3	n
+5	david	NULL	NULL
+6	kent	1	n
+7	carsten	NULL	NULL
+8	ranger	1	y
+10	matt	NULL	NULL
+CREATE VIEW v2 AS SELECT * FROM t2;
+SELECT a.col1,a.col2,b.col2,b.col3
+FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
+WHERE b.col2 IS NULL OR
+b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
+col1	col2	col2	col3
+1	trudy	2	y
+2	peter	1	n
+3	sanja	1	n
+4	monty	3	n
+5	david	NULL	NULL
+6	kent	1	n
+7	carsten	NULL	NULL
+8	ranger	1	y
+10	matt	NULL	NULL
+SELECT a.col1,a.col2,b.col2,b.col3
+FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
+WHERE a.col1 IN (1,5,9) AND
+(b.col2 IS NULL OR
+b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1));
+col1	col2	col2	col3
+1	trudy	2	y
+5	david	NULL	NULL
+CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9);
+SELECT a.col1,a.col2,b.col2,b.col3
+FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1
+WHERE b.col2 IS NULL OR
+b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
+col1	col2	col2	col3
+1	trudy	2	y
+5	david	NULL	NULL
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;

--- 1.65/mysql-test/t/view.test	Mon Apr  4 12:43:55 2005
+++ 1.66/mysql-test/t/view.test	Wed Apr 13 23:01:24 2005
@@ -1457,3 +1457,65 @@
 insert into t2 select x from v1;
 drop view v1;
 drop table t1,t2,t3;
+
+#
+# Test for BUG #6106: query over a view using subquery for the underlying table
+#
+
+CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); 
+INSERT INTO t1 VALUES(1,'trudy'); 
+INSERT INTO t1 VALUES(2,'peter'); 
+INSERT INTO t1 VALUES(3,'sanja'); 
+INSERT INTO t1 VALUES(4,'monty'); 
+INSERT INTO t1 VALUES(5,'david'); 
+INSERT INTO t1 VALUES(6,'kent'); 
+INSERT INTO t1 VALUES(7,'carsten'); 
+INSERT INTO t1 VALUES(8,'ranger'); 
+INSERT INTO t1 VALUES(10,'matt'); 
+CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); 
+INSERT INTO t2 VALUES (1,1,'y'); 
+INSERT INTO t2 VALUES (1,2,'y'); 
+INSERT INTO t2 VALUES (2,1,'n'); 
+INSERT INTO t2 VALUES (3,1,'n'); 
+INSERT INTO t2 VALUES (4,1,'y'); 
+INSERT INTO t2 VALUES (4,2,'n'); 
+INSERT INTO t2 VALUES (4,3,'n'); 
+INSERT INTO t2 VALUES (6,1,'n'); 
+INSERT INTO t2 VALUES (8,1,'y');
+ 
+CREATE VIEW v1 AS SELECT * FROM t1; 
+
+SELECT a.col1,a.col2,b.col2,b.col3 
+  FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
+    WHERE b.col2 IS NULL OR 
+          b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+
+SELECT a.col1,a.col2,b.col2,b.col3 
+  FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
+    WHERE b.col2 IS NULL OR 
+          b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+
+CREATE VIEW v2 AS SELECT * FROM t2; 
+
+SELECT a.col1,a.col2,b.col2,b.col3
+  FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
+    WHERE b.col2 IS NULL OR
+          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 
+
+# Tests from the report for bug #6107
+
+SELECT a.col1,a.col2,b.col2,b.col3
+  FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
+    WHERE a.col1 IN (1,5,9) AND
+         (b.col2 IS NULL OR
+          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); 
+
+CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9);
+
+SELECT a.col1,a.col2,b.col2,b.col3
+  FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1
+    WHERE b.col2 IS NULL OR
+          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 
+ 
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;

--- 1.45/mysql-test/r/information_schema.result	Tue Apr  5 03:12:10 2005
+++ 1.46/mysql-test/r/information_schema.result	Wed Apr 13 23:04:11 2005
@@ -68,6 +68,23 @@
 t3
 v1
 select c,table_name from v1 
+inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+c	table_name
+TABLES	TABLES
+TABLE_PRIVILEGES	TABLE_PRIVILEGES
+TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
+tables_priv	tables_priv
+time_zone	time_zone
+time_zone_leap_second	time_zone_leap_second
+time_zone_name	time_zone_name
+time_zone_transition	time_zone_transition
+time_zone_transition_type	time_zone_transition_type
+t1	t1
+t4	t4
+t2	t2
+t3	t3
+select c,table_name from v1 
 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
 c	table_name

--- 1.32/mysql-test/t/information_schema.test	Tue Apr  5 03:12:10 2005
+++ 1.33/mysql-test/t/information_schema.test	Wed Apr 13 23:03:09 2005
@@ -24,6 +24,11 @@
 create table mysqltest.t4(a int);
 create view v1 (c) as select table_name from information_schema.TABLES;
 select * from v1;
+
+select c,table_name from v1 
+inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
 select c,table_name from v1 
 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";