diff options
| -rw-r--r-- | mysql-test/r/derived_view.result | 54 | ||||
| -rw-r--r-- | mysql-test/t/derived_view.test | 36 | ||||
| -rw-r--r-- | sql/item_row.cc | 16 | ||||
| -rw-r--r-- | sql/item_row.h | 1 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 16 |
5 files changed, 118 insertions, 5 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5e9eeb99732..b50858f4a7c 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1024,3 +1024,57 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 ref a a 35 test.t3.a 2 DROP VIEW v1; DROP TABLE t1,t2,t3; +# +# LP bug #809179 +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (6,5); +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (6,5); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +a b +6 5 +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` `t` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><5,6>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +a b +6 5 +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><5,6>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +a b +6 5 +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 +WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><5,6>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +DROP VIEW v1; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 240eaf480a6..70bd10aef51 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -590,3 +590,39 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #809179 +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (6,5); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,0); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (6,5); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); + +SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); + +SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); +EXPLAIN EXTENDED +SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 + WHERE (t.a,t.b) NOT IN (SELECT 7, 5); + +DROP VIEW v1; +DROP TABLE t1,t2,t3; diff --git a/sql/item_row.cc b/sql/item_row.cc index 99a1644cc48..09977d71bb7 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -93,6 +93,22 @@ bool Item_row::fix_fields(THD *thd, Item **ref) } +bool +Item_row::eval_not_null_tables(uchar *opt_arg) +{ + Item **arg,**arg_end; + not_null_tables_cache= 0; + if (arg_count) + { + for (arg= items, arg_end= items+arg_count; arg != arg_end ; arg++) + { + not_null_tables_cache|= (*arg)->not_null_tables(); + } + } + return FALSE; +} + + void Item_row::cleanup() { DBUG_ENTER("Item_row::cleanup"); diff --git a/sql/item_row.h b/sql/item_row.h index 0b43309391d..1572ef77b79 100644 --- a/sql/item_row.h +++ b/sql/item_row.h @@ -73,6 +73,7 @@ public: bool walk(Item_processor processor, bool walk_subquery, uchar *arg); Item *transform(Item_transformer transformer, uchar *arg); + bool eval_not_null_tables(uchar *opt_arg); uint cols() { return arg_count; } Item* element_index(uint i) { return items[i]; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 80a80363cc0..c8b5a3439b0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3472,18 +3472,24 @@ void SELECT_LEX::update_used_tables() List_iterator<TABLE_LIST> ti(leaf_tables); while ((tl= ti++)) { - for (embedding= tl; - !tl->table->maybe_null && embedding; - embedding= embedding->embedding) + TABLE_LIST *embedding; + embedding= tl; + do { - tl->table->maybe_null= test(embedding->outer_join); + bool maybe_null; + if ((maybe_null= test(embedding->outer_join))) + { + tl->table->maybe_null= maybe_null; + break; + } } + while ((embedding= embedding->embedding)); if (tl->on_expr) { tl->on_expr->update_used_tables(); tl->on_expr->walk(&Item::eval_not_null_tables, 0, NULL); } - TABLE_LIST *embedding= tl->embedding; + embedding= tl->embedding; while (embedding) { if (embedding->on_expr && |