[Commits] Rev 3480: MDEV-3862 Lift limitation for merging VIEWS with Subqueries in SELECT list. in file:///home/bell/maria/bzr/work-maria-10.0-MDEV-3862/

sanja at montyprogram.com sanja at montyprogram.com
Tue Nov 20 15:11:25 EET 2012


At file:///home/bell/maria/bzr/work-maria-10.0-MDEV-3862/

------------------------------------------------------------
revno: 3480
revision-id: sanja at montyprogram.com-20121120131122-0a8a0xbjd715ykha
parent: timour at askmonty.org-20121115105450-zkiva73utg8pg49j
committer: sanja at montyprogram.com
branch nick: work-maria-10.0-MDEV-3862
timestamp: Tue 2012-11-20 15:11:22 +0200
message:
  MDEV-3862 Lift limitation for merging VIEWS with Subqueries in SELECT list.
-------------- next part --------------
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/view.result	2012-11-20 13:11:22 +0000
@@ -4825,4 +4825,110 @@ drop tables t1,t2;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------
+# some subqueries in SELECT list test
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10);
+insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10);
+create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1;
+explain extended
+select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1`
+select * from v1;
+a	c
+1	2
+3	4
+3	4
+5	6
+7	8
+9	10
+explain extended
+select * from t2, v1 where t2.a=v1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
+select * from t2, v1 where t2.a=v1.a;
+a	b	a	c
+1	2	1	2
+3	4	3	4
+3	3	3	4
+3	4	3	4
+3	3	3	4
+5	6	5	6
+7	8	7	8
+9	10	9	10
+explain extended
+select * from t1, v1 where t1.a=v1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t1`.`a`)
+select * from t1, v1 where t1.a=v1.a;
+a	b	a	c
+1	2	1	2
+3	4	3	4
+3	3	3	4
+3	4	3	4
+3	3	3	4
+5	6	5	6
+7	8	7	8
+9	10	9	10
+explain extended
+select * from t1, v1 where t1.b=v1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`b` = (select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+select * from t1, v1 where t1.b=v1.c;
+a	b	a	c
+1	2	1	2
+3	4	3	4
+3	4	3	4
+5	6	5	6
+7	8	7	8
+9	10	9	10
+explain extended
+select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (incremental, BNL join)
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`a`))
+select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
+a	b	a	b	a	c
+1	2	1	2	1	2
+3	4	3	4	3	4
+3	3	3	4	3	4
+3	4	3	3	3	4
+3	3	3	3	3	4
+3	4	3	4	3	4
+3	3	3	4	3	4
+3	4	3	3	3	4
+3	3	3	3	3	4
+5	6	5	6	5	6
+7	8	7	8	7	8
+9	10	9	10	9	10
+drop view v1;
+drop table t1,t2;
+# -----------------------------------------------------------------
+# -- End of 10.0 tests.
+# -----------------------------------------------------------------
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/suite/funcs_1/r/innodb_views.result'
--- a/mysql-test/suite/funcs_1/r/innodb_views.result	2012-08-27 16:13:17 +0000
+++ b/mysql-test/suite/funcs_1/r/innodb_views.result	2012-11-20 13:11:22 +0000
@@ -22340,10 +22340,9 @@ SET @variant2= 'CREATE VIEW v1 AS SELECT
 SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
-SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
-SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
-SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
-SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
+SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
+SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
+SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
 CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -22368,14 +22367,6 @@ ERROR HY000: The target table v1 of the
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 DROP VIEW v1;
-CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
-INSERT INTO v1 VALUES (1002);
-ERROR HY000: The target table v1 of the INSERT is not insertable-into
-UPDATE v1 SET f61=1007;
-ERROR HY000: The target table v1 of the UPDATE is not updatable
-DELETE FROM v1;
-ERROR HY000: The target table v1 of the DELETE is not updatable
-DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -22416,6 +22407,13 @@ ERROR HY000: The target table v1 of the
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 DROP VIEW v1;
+CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
+INSERT INTO v1 VALUES (1002);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+UPDATE v1 SET f61=1007;
+ERROR HY000: Column 'f61' is not updatable
+DELETE FROM v1;
+DROP VIEW v1;
 Drop TABLE t1, t2 ;
 Drop VIEW  v2 ;
 

=== modified file 'mysql-test/suite/funcs_1/r/memory_views.result'
--- a/mysql-test/suite/funcs_1/r/memory_views.result	2012-08-27 16:13:17 +0000
+++ b/mysql-test/suite/funcs_1/r/memory_views.result	2012-11-20 13:11:22 +0000
@@ -22342,10 +22342,9 @@ SET @variant2= 'CREATE VIEW v1 AS SELECT
 SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
-SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
-SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
-SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
-SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
+SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
+SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
+SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
 CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -22370,14 +22369,6 @@ ERROR HY000: The target table v1 of the
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 DROP VIEW v1;
-CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
-INSERT INTO v1 VALUES (1002);
-ERROR HY000: The target table v1 of the INSERT is not insertable-into
-UPDATE v1 SET f61=1007;
-ERROR HY000: The target table v1 of the UPDATE is not updatable
-DELETE FROM v1;
-ERROR HY000: The target table v1 of the DELETE is not updatable
-DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -22418,6 +22409,13 @@ ERROR HY000: The target table v1 of the
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 DROP VIEW v1;
+CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
+INSERT INTO v1 VALUES (1002);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+UPDATE v1 SET f61=1007;
+ERROR HY000: Column 'f61' is not updatable
+DELETE FROM v1;
+DROP VIEW v1;
 Drop TABLE t1, t2 ;
 Drop VIEW  v2 ;
 

=== modified file 'mysql-test/suite/funcs_1/views/views_master.inc'
--- a/mysql-test/suite/funcs_1/views/views_master.inc	2012-01-13 14:50:02 +0000
+++ b/mysql-test/suite/funcs_1/views/views_master.inc	2012-11-20 13:11:22 +0000
@@ -3812,16 +3812,14 @@ SET @variant3= 'CREATE VIEW v1 AS SELECT
 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
 # For HAVING                         3.3.2.16
 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
-# For a sub query in the select list 3.3.2.17
-SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
 # For a WHERE clause sub query that refers to a table also referenced in a
 # FROM clause 3.3.2.18
-SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
-SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
+SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
+SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
 # For ALGORITHM = TEMPTABLE          3.3.2.20
-SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
+SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
 
-let $num= 9;
+let $num= 8;
 while ($num)
 {
    --disable_abort_on_error
@@ -3844,6 +3842,15 @@ while ($num)
    DROP VIEW v1;
    dec $num;
 }
+# For a sub query in the select list 3.3.2.17
+CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
+--error ER_NON_INSERTABLE_TABLE
+INSERT INTO v1 VALUES (1002);
+--error ER_NONUPDATEABLE_COLUMN
+UPDATE v1 SET f61=1007;
+# no error ER_NON_UPDATABLE_TABLE, because we can find columns for deleting
+DELETE FROM v1;
+DROP VIEW v1;
 
 Drop TABLE t1, t2 ;
 Drop VIEW  v2 ;

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2012-10-18 21:33:06 +0000
+++ b/mysql-test/t/view.test	2012-11-20 13:11:22 +0000
@@ -4751,4 +4751,33 @@ drop tables t1,t2;
 --echo # -- End of 5.3 tests.
 --echo # -----------------------------------------------------------------
 
+
+--echo # some subqueries in SELECT list test
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10);
+insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10);
+create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1;
+explain extended
+select * from v1;
+select * from v1;
+explain extended
+select * from t2, v1 where t2.a=v1.a;
+select * from t2, v1 where t2.a=v1.a;
+explain extended
+select * from t1, v1 where t1.a=v1.a;
+select * from t1, v1 where t1.a=v1.a;
+explain extended
+select * from t1, v1 where t1.b=v1.c;
+select * from t1, v1 where t1.b=v1.c;
+explain extended
+select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
+select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
+
+drop view v1;
+drop table t1,t2;
+
+--echo # -----------------------------------------------------------------
+--echo # -- End of 10.0 tests.
+--echo # -----------------------------------------------------------------
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-10-19 18:38:59 +0000
+++ b/sql/sql_lex.cc	2012-11-20 13:11:22 +0000
@@ -2595,7 +2595,8 @@ bool LEX::can_be_merged()
       if (tmp_unit->first_select()->parent_lex == this &&
           (tmp_unit->item == 0 ||
            (tmp_unit->item->place() != IN_WHERE &&
-            tmp_unit->item->place() != IN_ON)))
+            tmp_unit->item->place() != IN_ON &&
+            tmp_unit->item->place() != SELECT_LIST)))
       {
         selects_allow_merge= 0;
         break;



More information about the commits mailing list