summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_cond_pushdown.result203
-rw-r--r--mysql-test/t/derived_cond_pushdown.test58
-rw-r--r--sql/item_cmpfunc.cc13
-rw-r--r--sql/item_cmpfunc.h1
4 files changed, 275 insertions, 0 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 839ae587839..fd930d13547 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10017,3 +10017,206 @@ a
7
4
DROP TABLE t1,t2,t3;
+#
+# MDEV-16765: pushdown condition with the CASE structure
+# defined with Item_cond item
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,2), (3,4), (2,3);
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a b
+1 2
+1 3
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1",
+ "materialized": {
+ "query_block": {
+ "select_id": 3,
+ "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1",
+ "filesort": {
+ "sort_key": "t1.b",
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a b
+1 2
+1 4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1",
+ "materialized": {
+ "query_block": {
+ "select_id": 3,
+ "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1",
+ "filesort": {
+ "sort_key": "t1.b",
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a b
+1 3
+1 4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1",
+ "materialized": {
+ "query_block": {
+ "select_id": 3,
+ "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1",
+ "filesort": {
+ "sort_key": "t1.b",
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a b
+1 2
+1 4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1",
+ "materialized": {
+ "query_block": {
+ "select_id": 3,
+ "filesort": {
+ "sort_key": "t1.b",
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 3,
+ "filtered": 100,
+ "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1"
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+}
+DROP TABLE t1;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index f85b301de35..b8e74a2d5aa 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1964,3 +1964,61 @@ eval $q3;
SELECT * FROM t2;
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-16765: pushdown condition with the CASE structure
+--echo # defined with Item_cond item
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,2), (3,4), (2,3);
+
+LET $query=
+SELECT *
+FROM
+(
+ SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+ THEN 1 ELSE 0 END AS max_a,b
+ FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+ SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+ THEN 1 ELSE 0 END AS max_a,b
+ FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+ SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+ THEN 1 ELSE 0 END AS max_a,b
+ FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+ SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+ THEN 1 ELSE 0 END AS max_a,b
+ FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f176a0a8193..6834820c0b5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4970,6 +4970,19 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root)
}
+bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel)
+{
+ List_iterator_fast<Item> li(list);
+ Item *item;
+ while ((item= li++))
+ {
+ if (!item->excl_dep_on_grouping_fields(sel))
+ return false;
+ }
+ return true;
+}
+
+
void Item_cond_and::mark_as_condition_AND_part(TABLE_LIST *embedding)
{
List_iterator<Item> li(list);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 41a51ee8d12..59ac5f56fe1 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2224,6 +2224,7 @@ public:
Item_transformer transformer, uchar *arg_t);
bool eval_not_null_tables(void *opt_arg);
Item *build_clone(THD *thd, MEM_ROOT *mem_root);
+ bool excl_dep_on_grouping_fields(st_select_lex *sel);
};
template <template<class> class LI, class T> class Item_equal_iterator;
View Lorry Controller Status