MDEV-16386: Wrong result when pushdown into the HAVING clause of the

            materialized derived table/view that uses aliases is done

The problem appears when a column alias inside the materialized derived
table/view t1 definition coincides with the column name used in the
GROUP BY clause of t1. If the condition that can be pushed into t1
uses that ambiguous column name this column is determined as a column that
is used in the GROUP BY clause instead of the alias used in the projection
list of t1. That causes wrong result.
To prevent it resolve_ref_in_select_and_group() was changed.

6 files changed, 267 insertions, 4 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 3fbc81019cc..83e70dd634c 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -9473,3 +9473,199 @@ WHERE (a>0 AND a<2 OR a IN (2,3)) AND
 a
 2
 DROP TABLE t1;
+#
+# MDEV-16386: pushing condition into the HAVING clause when ambiguous
+#             fields warning appears
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(2,3),(3,4);
+SELECT * FROM
+(
+SELECT t1.b AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a=2);
+a
+2
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT t1.b AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a=2);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "dt.a = 2",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "a = 2",
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM
+(
+SELECT t1.b AS a
+FROM t1
+GROUP BY t1.a
+HAVING (t1.a<3)
+) dt
+WHERE (dt.a>1);
+a
+2
+3
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT t1.b AS a
+FROM t1
+GROUP BY t1.a
+HAVING (t1.a<3)
+) dt
+WHERE (dt.a>1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "dt.a > 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "t1.a < 3 and a > 1",
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM
+(
+SELECT 'ab' AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a='ab');
+a
+ab
+ab
+ab
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT 'ab' AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a='ab');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "dt.a = 'ab'",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM
+(
+SELECT 1 AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a=1);
+a
+1
+1
+1
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT 1 AS a
+FROM t1
+GROUP BY t1.a
+) dt
+WHERE (dt.a=1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "dt.a = 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+DROP TABLE t1;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index d3832ce1ec3..718140d3a77 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1745,3 +1745,59 @@ WHERE (a>0 AND a<2 OR a IN (2,3)) AND
       (a=2 OR 0);
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16386: pushing condition into the HAVING clause when ambiguous
+--echo #             fields warning appears
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+
+INSERT INTO t1 VALUES (1,2),(2,3),(3,4);
+
+LET $query=
+SELECT * FROM
+(
+  SELECT t1.b AS a
+  FROM t1
+  GROUP BY t1.a
+) dt
+WHERE (dt.a=2);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM
+(
+  SELECT t1.b AS a
+  FROM t1
+  GROUP BY t1.a
+  HAVING (t1.a<3)
+) dt
+WHERE (dt.a>1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM
+(
+  SELECT 'ab' AS a
+  FROM t1
+  GROUP BY t1.a
+) dt
+WHERE (dt.a='ab');
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM
+(
+  SELECT 1 AS a
+  FROM t1
+  GROUP BY t1.a
+) dt
+WHERE (dt.a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1;
diff --git a/sql/item.cc b/sql/item.cc
index f9200ccf56d..4bc6fe7e5bc 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4984,9 +4984,11 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list)
     in the SELECT clause of Q.
     - Search for a column named col_ref_i [in table T_j]
     in the GROUP BY clause of Q.
-    - If found different columns with the same name in GROUP BY and SELECT
-    - issue a warning and return the GROUP BY column,
-    - otherwise
+    - If found different columns with the same name in GROUP BY and SELECT:
+    - if the condition that uses this column name is pushed down into
+    the HAVING clause return the SELECT column
+    - else issue a warning and return the GROUP BY column.
+    - Otherwise
     - if the MODE_ONLY_FULL_GROUP_BY mode is enabled return error
     - else return the found SELECT column.
 
@@ -5025,7 +5027,8 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select)
     
     /* Check if the fields found in SELECT and GROUP BY are the same field. */
     if (group_by_ref && (select_ref != not_found_item) &&
-        !((*group_by_ref)->eq(*select_ref, 0)))
+        !((*group_by_ref)->eq(*select_ref, 0)) &&
+        (!select->having_fix_field_for_pushed_cond))
     {
       ambiguous_fields= TRUE;
       push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 750fb75bfb9..d3ddd9e208c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2107,6 +2107,7 @@ void st_select_lex::init_query()
   cond_pushed_into_where= cond_pushed_into_having= 0;
   olap= UNSPECIFIED_OLAP_TYPE;
   having_fix_field= 0;
+  having_fix_field_for_pushed_cond= 0;
   context.select_lex= this;
   context.init();
   /*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ed5e423fd5a..a1f6b202ae6 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -887,6 +887,11 @@ public:
   bool  braces;   	/* SELECT ... UNION (SELECT ... ) <- this braces */
   /* TRUE when having fix field called in processing of this SELECT */
   bool having_fix_field;
+  /*
+    TRUE when fix field is called for a new condition pushed into the
+    HAVING clause of this SELECT
+  */
+  bool having_fix_field_for_pushed_cond;
   /* List of references to fields referenced from inner selects */
   List<Item_outer_ref> inner_refs_list;
   /* Number of Item_sum-derived objects in this SELECT */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ee5ba4ade54..4ebd5ef4954 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1350,9 +1350,11 @@ JOIN::optimize_inner()
     if (having)
     {
       select_lex->having_fix_field= 1;
+      select_lex->having_fix_field_for_pushed_cond= 1;
       if (having->fix_fields(thd, &having))
         DBUG_RETURN(1);
       select_lex->having_fix_field= 0;
+      select_lex->having_fix_field_for_pushed_cond= 0;
     }
   }