MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error

check for VIEW/DERIVED fields

5 files changed, 181 insertions, 29 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 2c316a829a7..a4d474c9cdf 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -924,3 +924,68 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 DROP TABLES t1,t2;
+#
+# MDEV-10663: Use of Inline table columns in HAVING clause
+# throws 1463 Error
+#
+set @save_sql_mode = @@sql_mode;
+set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+CREATE TABLE `example1463` (
+`Customer` varchar(255) NOT NULL,
+`DeliveryStatus` varchar(255) NOT NULL,
+`OrderSize` int(11) NOT NULL
+);
+INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
+INSERT INTO example1463 VALUES ('David', 'Success', 110);
+INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
+INSERT INTO example1463 VALUES ('David', 'Success', 100);
+INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
+INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
+INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
+SELECT Customer, Success, SUM(OrderSize)
+FROM (SELECT Customer,
+CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+OrderSize
+FROM example1463) as subQ
+GROUP BY Success, Customer
+WITH ROLLUP;
+Customer	Success	SUM(OrderSize)
+Charlie	No	200
+David	No	100
+Edward	No	150
+NULL	No	450
+Charlie	Yes	100
+David	Yes	210
+Edward	Yes	150
+NULL	Yes	460
+NULL	NULL	910
+SELECT Customer, Success, SUM(OrderSize)
+FROM (SELECT Customer,
+CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+OrderSize
+FROM example1463) as subQ
+GROUP BY Success, Customer;
+Customer	Success	SUM(OrderSize)
+Charlie	No	200
+David	No	100
+Edward	No	150
+Charlie	Yes	100
+David	Yes	210
+Edward	Yes	150
+SELECT Customer, Success, SUM(OrderSize)
+FROM (SELECT Customer,
+CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+OrderSize
+FROM example1463) as subQ
+GROUP BY Success, Customer
+HAVING Success IS NOT NULL;
+Customer	Success	SUM(OrderSize)
+Charlie	No	200
+David	No	100
+Edward	No	150
+Charlie	Yes	100
+David	Yes	210
+Edward	Yes	150
+DROP TABLE example1463;
+set sql_mode= @save_sql_mode;
+# end of 5.5
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 0b4973cc35b..262bb2ebd84 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2517,3 +2517,17 @@ MAX(i)	c
 0	bar
 7	foo
 drop table t1,t2;
+#
+# ONLY_FULL_GROUP_BY references
+#
+set @save_sql_mode = @@sql_mode;
+set sql_mode='ONLY_FULL_GROUP_BY';
+create table t1 (a int, b int);
+select a+b as x from t1 group by x having x > 1;
+x
+select a as x from t1 group by x having x > 1;
+x
+select a from t1 group by a having a > 1;
+a
+drop table t1;
+set sql_mode= @save_sql_mode;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index e10349b451c..e8a6ac34392 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -796,3 +796,50 @@ A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_L
 ORDER BY TOTAL DESC;
 
 DROP TABLES t1,t2;
+
+--echo #
+--echo # MDEV-10663: Use of Inline table columns in HAVING clause
+--echo # throws 1463 Error
+--echo #
+
+set @save_sql_mode = @@sql_mode;
+set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+
+CREATE TABLE `example1463` (
+  `Customer` varchar(255) NOT NULL,
+  `DeliveryStatus` varchar(255) NOT NULL,
+  `OrderSize` int(11) NOT NULL
+);
+INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
+INSERT INTO example1463 VALUES ('David', 'Success', 110);
+INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
+INSERT INTO example1463 VALUES ('David', 'Success', 100);
+INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
+INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
+INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
+
+SELECT Customer, Success, SUM(OrderSize)
+ FROM (SELECT Customer,
+ CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+ OrderSize
+ FROM example1463) as subQ
+ GROUP BY Success, Customer
+ WITH ROLLUP;
+SELECT Customer, Success, SUM(OrderSize)
+ FROM (SELECT Customer,
+ CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+ OrderSize
+ FROM example1463) as subQ
+ GROUP BY Success, Customer;
+SELECT Customer, Success, SUM(OrderSize)
+ FROM (SELECT Customer,
+ CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
+ OrderSize
+ FROM example1463) as subQ
+ GROUP BY Success, Customer
+ HAVING Success IS NOT NULL;
+
+DROP TABLE example1463;
+set sql_mode= @save_sql_mode;
+
+--echo # end of 5.5
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index c94d27b1d16..4162e9c67a1 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1691,6 +1691,18 @@ SELECT MAX(i), c FROM t1
 WHERE c != 'qux' AND ( SELECT SUM(j) FROM t1, t2 ) IS NOT NULL GROUP BY c;
 drop table t1,t2;
 
+--echo #
+--echo # ONLY_FULL_GROUP_BY references
+--echo #
+
+set @save_sql_mode = @@sql_mode;
+set sql_mode='ONLY_FULL_GROUP_BY';
+create table t1 (a int, b int);
+select a+b as x from t1 group by x having x > 1;
+select a as x from t1 group by x having x > 1;
+select a from t1 group by a having a > 1;
+drop table t1;
+set sql_mode= @save_sql_mode;
 #
 # End of MariaDB 5.5 tests
 #
diff --git a/sql/item.cc b/sql/item.cc
index ede1df6aa9b..53666aaf83d 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4556,8 +4556,6 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list)
   const char *field_name;
   ORDER      *found_group= NULL;
   int         found_match_degree= 0;
-  Item_ident *cur_field;
-  int         cur_match_degree= 0;
   char        name_buff[SAFE_NAME_LEN+1];
 
   if (find_item->type() == Item::FIELD_ITEM ||
@@ -4582,54 +4580,70 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list)
 
   for (ORDER *cur_group= group_list ; cur_group ; cur_group= cur_group->next)
   {
-    if ((*(cur_group->item))->real_item()->type() == Item::FIELD_ITEM)
+    int cur_match_degree= 0;
+
+    /* SELECT list element with explicit alias */
+    if ((*(cur_group->item))->name &&
+        !(*(cur_group->item))->is_autogenerated_name &&
+        !my_strcasecmp(system_charset_info,
+                       (*(cur_group->item))->name, field_name))
     {
-      cur_field= (Item_ident*) *cur_group->item;
-      cur_match_degree= 0;
-      
-      DBUG_ASSERT(cur_field->field_name != 0);
+      ++cur_match_degree;
+    }
+    /* Reference on the field or view/derived field. */
+    else if ((*(cur_group->item))->type() == Item::FIELD_ITEM ||
+             (*(cur_group->item))->type() == Item::REF_ITEM )
+    {
+      Item_ident *cur_field= (Item_ident*) *cur_group->item;
+      const char *l_db_name= cur_field->db_name;
+      const char *l_table_name= cur_field->table_name;
+      const char *l_field_name= cur_field->field_name;
+
+      DBUG_ASSERT(l_field_name != 0);
 
       if (!my_strcasecmp(system_charset_info,
-                         cur_field->field_name, field_name))
+                         l_field_name, field_name))
         ++cur_match_degree;
       else
         continue;
 
-      if (cur_field->table_name && table_name)
+      if (l_table_name && table_name)
       {
         /* If field_name is qualified by a table name. */
-        if (my_strcasecmp(table_alias_charset, cur_field->table_name, table_name))
+        if (my_strcasecmp(table_alias_charset, l_table_name, table_name))
           /* Same field names, different tables. */
           return NULL;
 
         ++cur_match_degree;
-        if (cur_field->db_name && db_name)
+        if (l_db_name && db_name)
         {
           /* If field_name is also qualified by a database name. */
-          if (strcmp(cur_field->db_name, db_name))
+          if (strcmp(l_db_name, db_name))
             /* Same field names, different databases. */
             return NULL;
           ++cur_match_degree;
         }
       }
+    }
+    else
+      continue;
 
-      if (cur_match_degree > found_match_degree)
-      {
-        found_match_degree= cur_match_degree;
-        found_group= cur_group;
-      }
-      else if (found_group && (cur_match_degree == found_match_degree) &&
-               ! (*(found_group->item))->eq(cur_field, 0))
-      {
-        /*
-          If the current resolve candidate matches equally well as the current
-          best match, they must reference the same column, otherwise the field
-          is ambiguous.
-        */
-        my_error(ER_NON_UNIQ_ERROR, MYF(0),
-                 find_item->full_name(), current_thd->where);
-        return NULL;
-      }
+    if (cur_match_degree > found_match_degree)
+    {
+      found_match_degree= cur_match_degree;
+      found_group= cur_group;
+    }
+    else if (found_group && (cur_match_degree == found_match_degree) &&
+             !(*(found_group->item))->eq((*(cur_group->item)), 0))
+    {
+      /*
+        If the current resolve candidate matches equally well as the current
+        best match, they must reference the same column, otherwise the field
+        is ambiguous.
+      */
+      my_error(ER_NON_UNIQ_ERROR, MYF(0),
+               find_item->full_name(), current_thd->where);
+      return NULL;
     }
   }