diff options
| -rw-r--r-- | mysql-test/r/derived_view.result | 56 | ||||
| -rw-r--r-- | mysql-test/t/derived_view.test | 42 | ||||
| -rw-r--r-- | sql/opt_sum.cc | 12 |
3 files changed, 104 insertions, 6 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5e3fc1e8cf1..8cc53d7932d 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -589,3 +589,59 @@ f1 f1 224 224 DROP VIEW v1; DROP TABLE t1,t2; +# +# LP bug #802023: MIN/MAX optimization +# for mergeable derived tables and views +# +CREATE TABLE t1 (a int, b int, c varchar(32), INDEX idx(a,b)); +INSERT INTO t1 VALUES +(7, 74, 'yyyyyyy'), (9, 97, 'aaaaaaaaa'), (2, 23, 'tt'), +(5, 55, 'ddddd'), (2, 27, 'ss'), (7, 76, 'xxxxxxx'), +(7, 79, 'zzzzzzz'), (9, 92, 'bbbbbbbbb'), (2, 25, 'pp'), +(5, 53, 'eeeee'), (2, 23, 'qq'), (7, 76,'wwwwwww'), +(7, 74, 'uuuuuuu'), (9, 92, 'ccccccccc'), (2, 25, 'oo'); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT MIN(a) FROM t1 WHERE a >= 5; +MIN(a) +5 +EXPLAIN +SELECT MIN(a) FROM t1 WHERE a >= 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(a) FROM (SELECT * FROM t1) t WHERE a >= 5; +MIN(a) +5 +EXPLAIN +SELECT MIN(a) FROM(SELECT * FROM t1) t WHERE a >= 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(a) FROM v1 WHERE a >= 5; +MIN(a) +5 +EXPLAIN +SELECT MIN(a) FROM v1 WHERE a >= 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MAX(b) FROM t1 WHERE a=7 AND b<75; +MAX(b) +74 +EXPLAIN +SELECT MAX(b) FROM t1 WHERE a=7 AND b<75; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75; +MAX(b) +74 +EXPLAIN +SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MAX(b) FROM v1 WHERE a=7 AND b<75; +MAX(b) +74 +EXPLAIN +SELECT MAX(b) FROM v1 WHERE a=7 AND b<75; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 93c2eb48fcd..7433b83b8a4 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -235,3 +235,45 @@ SELECT * FROM v1 JOIN t2 ON v1.f1 = t2.f1; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # LP bug #802023: MIN/MAX optimization +--echo # for mergeable derived tables and views +--echo # + +CREATE TABLE t1 (a int, b int, c varchar(32), INDEX idx(a,b)); +INSERT INTO t1 VALUES + (7, 74, 'yyyyyyy'), (9, 97, 'aaaaaaaaa'), (2, 23, 'tt'), + (5, 55, 'ddddd'), (2, 27, 'ss'), (7, 76, 'xxxxxxx'), + (7, 79, 'zzzzzzz'), (9, 92, 'bbbbbbbbb'), (2, 25, 'pp'), + (5, 53, 'eeeee'), (2, 23, 'qq'), (7, 76,'wwwwwww'), + (7, 74, 'uuuuuuu'), (9, 92, 'ccccccccc'), (2, 25, 'oo'); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT MIN(a) FROM t1 WHERE a >= 5; +EXPLAIN +SELECT MIN(a) FROM t1 WHERE a >= 5; + +SELECT MIN(a) FROM (SELECT * FROM t1) t WHERE a >= 5; +EXPLAIN +SELECT MIN(a) FROM(SELECT * FROM t1) t WHERE a >= 5; + +SELECT MIN(a) FROM v1 WHERE a >= 5; +EXPLAIN +SELECT MIN(a) FROM v1 WHERE a >= 5; + +SELECT MAX(b) FROM t1 WHERE a=7 AND b<75; +EXPLAIN +SELECT MAX(b) FROM t1 WHERE a=7 AND b<75; + +SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75; +EXPLAIN +SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75; + +SELECT MAX(b) FROM v1 WHERE a=7 AND b<75; +EXPLAIN +SELECT MAX(b) FROM v1 WHERE a=7 AND b<75; + +DROP VIEW v1; +DROP TABLE t1; diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 78cb2fa8210..b55f4b7d7fa 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -506,18 +506,18 @@ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order) break; case 1: /* field IS NULL */ - item= func_item->arguments()[0]; + item= func_item->arguments()[0]->real_item(); if (item->type() != Item::FIELD_ITEM) return 0; args[0]= item; break; case 2: /* 'field op const' or 'const op field' */ - item= func_item->arguments()[0]; + item= func_item->arguments()[0]->real_item(); if (item->type() == Item::FIELD_ITEM) { args[0]= item; - item= func_item->arguments()[1]; + item= func_item->arguments()[1]->real_item(); if (!item->const_item()) return 0; args[1]= item; @@ -525,7 +525,7 @@ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order) else if (item->const_item()) { args[1]= item; - item= func_item->arguments()[1]; + item= func_item->arguments()[1]->real_item(); if (item->type() != Item::FIELD_ITEM) return 0; args[0]= item; @@ -536,13 +536,13 @@ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order) break; case 3: /* field BETWEEN const AND const */ - item= func_item->arguments()[0]; + item= func_item->arguments()[0]->real_item(); if (item->type() == Item::FIELD_ITEM) { args[0]= item; for (int i= 1 ; i <= 2; i++) { - item= func_item->arguments()[i]; + item= func_item->arguments()[i]->real_item(); if (!item->const_item()) return 0; args[i]= item; |