WL#1972 "Evaluate HAVING before SELECT"

- more tests, post-review changes, bug-fixes, simplifications, and improved comments


mysql-test/r/having.result:
  - added tests for subqueries with HAVING
  - added tests for few other discovered bugs
  - renamed tables to the more standard t1,t2,..
mysql-test/t/having.test:
  - added tests for subqueries with HAVING
  - added tests for few other discovered bugs
  - renamed tables to the more standard t1,t2,..
sql/item.cc:
  - Extended the name resolution to support nested HAVING clauses in nested sub-queries
  - Factored out the code that resolves a column ref against a single query
  - Fixed several logical bugs
  - Removed unused variables
  - More/better comments
sql/sql_base.cc:
  Corrected function spec.

1 files changed, 134 insertions, 62 deletions

diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 8d21f070956..d2a5ca4361e 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -2,7 +2,7 @@
 #
 
 --disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
 --enable_warnings
 
 create table t1 (a int);
@@ -134,108 +134,180 @@ drop table t1,t2,t3;
 # set global sql_mode='ansi';
 # set session sql_mode='ansi';
 
---disable_warnings
-drop table if exists wl1972;
---enable_warnings
-
-create table wl1972 (
-   col1 int, 
-   col2 varchar(5) character set latin1 collate latin1_bin) 
-engine=innodb;
-
-insert into wl1972 values(10,'hello');
-insert into wl1972 values(20,'hello');
-insert into wl1972 values(30,'hello');
-insert into wl1972 values(10,'bye');
-insert into wl1972 values(10,'sam');
-insert into wl1972 values(10,'bob');
+create table t1 (col1 int, col2 varchar(5), col_t1 int);
+create table t2 (col1 int, col2 varchar(5), col_t2 int);
+create table t3 (col1 int, col2 varchar(5), col_t3 int);
 
-select count(*) from wl1972 group by col1 having col1 = 10;
+insert into t1 values(10,'hello',10);
+insert into t1 values(20,'hello',20);
+insert into t1 values(30,'hello',30);
+insert into t1 values(10,'bye',10);
+insert into t1 values(10,'sam',10);
+insert into t1 values(10,'bob',10);
 
-select count(*) as count_col1 from wl1972 group by col1 having col1 = 10;
-
-select count(*) as count_col1 from wl1972 as t1 group by col1 having col1 = 10;
-
-select count(*) from wl1972 group by col2 having col2 = 'hello';
+insert into t2 select * from t1;
+insert into t3 select * from t1;
 
+select count(*) from t1 group by col1 having col1 = 10;
+select count(*) as count_col1 from t1 group by col1 having col1 = 10;
+select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
+select count(*) from t1 group by col2 having col2 = 'hello';
 --error 1054
-select count(*) from wl1972 group by col2 having col1 = 10;
-
-select col1 as count_col1 from wl1972 as t1 group by col1 having col1 = 10;
-
-select col1 as count_col1 from wl1972 as t1 group by col1 having count_col1 = 10;
-
-select col1 as count_col1 from wl1972 as t1 group by count_col1 having col1 = 10;
+select count(*) from t1 group by col2 having col1 = 10;
+select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
+select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;
+select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10;
 # ANSI: should return SQLSTATE 42000 Syntax error or access violation 
 # MySQL: returns 10 - because of GROUP BY name resolution
-
-select col1 as count_col1 from wl1972 as t1 group by count_col1 having count_col1 = 10;
+select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10;
 # ANSI: should return SQLSTATE 42000 Syntax error or access violation 
 # MySQL: returns 10 - because of GROUP BY name resolution
+select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10;
+select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;
+select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';
+select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello';
+--error 1064
+select sum(col1) as co12 from t1 group by col2 having col2 10; 
+select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; 
+--error 1054
+select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10;
 
-select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col1 = 10;
 
-select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having count_col1 = 10;
+#
+# queries with nested sub-queries
+#
 
-select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col2 = 'hello';
+# the having column is resolved in the same query
+select t1.col1 from t1
+where t1.col2 in 
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having t2.col1 <= 10);
+
+select t1.col1 from t1
+where t1.col2 in 
+      (select t2.col2 from t2
+       group by t2.col1, t2.col2
+       having t2.col1 <=
+              (select min(t3.col1) from t3));
+
+# the having column is resolved in the SELECT clause of the outer query -
+# works in ANSI
+select t1.col1 from t1
+where t1.col2 in
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having t1.col1 <= 10);
+
+# the having column is resolved in the SELECT clause of the outer query -
+# error in ANSI, works with MySQL extension
+select t1.col1 as tmp_col from t1
+where t1.col2 in 
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having tmp_col <= 10);
+
+# the having column is resolved in the FROM clause of the outer query -
+# works in ANSI
+select t1.col1 from t1
+where t1.col2 in 
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having col_t1 <= 10);
+
+# nested queries with HAVING, inner having column resolved in outer FROM clause
+# the outer having column is not referenced in GROUP BY which results in an error
+--error 1054
+select t1.col1 from t1
+where t1.col2 in 
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having col_t1 <= 10)
+having col_t1 <= 20;
 
-select col1 as count_col1,col2 as group_col2 from wl1972 as t1 
-group by col1,col2 having group_col2 = 'hello';
+# both having columns are resolved in the GROUP clause of the outer query
+select t1.col1 from t1
+where t1.col2 in 
+      (select t2.col2 from t2 
+       group by t2.col1, t2.col2 having col_t1 <= 10)
+group by col_t1
+having col_t1 <= 20;
 
---error 1064
-select sum(col1) as co12 from wl1972 group by col2 having col2 10; 
 
-select sum(col1) as co2, count(col2) as cc from wl1972 group by col1 having col1 =10; 
+#
+# nested HAVING clauses
+#
 
-select t1.col1 from wl1972 as t1 where t1.col2 in 
-   (select t2.col2 from wl1972 as t2 
-     group by t2.col1,t2.col2 having t2.col1<=10);
+# non-correlated subqueries
+select col_t1, sum(col1) from t1
+group by col_t1
+having col_t1 > 10 and
+       exists (select sum(t2.col1) from t2
+               group by t2.col2 having t2.col2 > 'b');
 
-select t1.col1 from wl1972 as t1 where t1.col2 in 
-   (select t2.col2 from wl1972 as t2 
-     group by t2.col1,t2.col2 having t2.col1<=
-        (select min(t3.col1) from wl1972 as t3));
+# correlated subqueries - inner having column 't1.col2' resolves to
+# the outer FROM clause, which cannot be used because the outer query
+# is grouped
+--error 1054
+select sum(col1) from t1
+group by col_t1
+having col_t1 in (select sum(t2.col1) from t2
+                  group by t2.col2, t2.col1 having t2.col1 = t1.col1);
+
+# correlated subqueries - inner having column 'col_t1' resolves to
+# the outer GROUP clause
+select sum(col1) from t1
+group by col_t1
+having col_t1 in (select sum(t2.col1) from t2
+                  group by t2.col2, t2.col1 having t2.col1 = col_t1);
 
-drop table wl1972;
+
+#
+# queries with joins and ambiguous column names
+#
+--error 1052
+select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1
+group by t1.col1, t2.col1 having col1 = 2;
+
+--error 1052
+select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1
+group by t1.col1, t2.col1 having col1 = 2;
+
+drop table t1, t2, t3;
 
 # More queries to test ANSI compatibility
-create table wl1972 (s1 int);
-insert into wl1972 values (1),(2),(3);
+create table t1 (s1 int);
+insert into t1 values (1),(2),(3);
 
-select count(*) from wl1972 group by s1 having s1 is null;
+select count(*) from t1 group by s1 having s1 is null;
 
-select s1*0 as s1 from wl1972 group by s1 having s1 <> 0;
+select s1*0 as s1 from t1 group by s1 having s1 <> 0;
 # ANSI requires: 3 rows
 # MySQL returns: 0 rows - because of GROUP BY name resolution
 
-select s1*0 from wl1972 group by s1 having s1 = 0;
+select s1*0 from t1 group by s1 having s1 = 0;
 
-select s1 from wl1972 group by 1 having 1 = 0;
+select s1 from t1 group by 1 having 1 = 0;
 
-select count(s1) from wl1972 group by s1 having count(1+1)=2;
+select count(s1) from t1 group by s1 having count(1+1)=2;
 # ANSI requires: 3 rows
 # MySQL returns: 0 rows - because of GROUP BY name resolution
 
-select count(s1) from wl1972 group by s1 having s1*0=0;
+select count(s1) from t1 group by s1 having s1*0=0;
 
 -- error 1052
-select * from wl1972 a, wl1972 b group by a.s1 having s1 is null;
+select * from t1 a, t1 b group by a.s1 having s1 is null;
 # ANSI requires: 0 rows
 # MySQL returns:
 # "ERROR 1052 (23000): Column 's1' in having clause is ambiguous"
 # I think the column is ambiguous in ANSI too.
 # It is the same as:
-#   select a.s1, b.s1 from wl1972 a, wl1972 b group by a.s1 having s1 is null;
+#   select a.s1, b.s1 from t1 a, t1 b group by a.s1 having s1 is null;
 # currently we first check SELECT, thus s1 is ambiguous.
 
-drop table wl1972;
+drop table t1;
 
-create table wl1972 (s1 char character set latin1 collate latin1_german1_ci);
-insert into wl1972 values ('ü'),('y');
+create table t1 (s1 char character set latin1 collate latin1_german1_ci);
+insert into t1 values ('ü'),('y');
 
-select s1,count(s1) from wl1972
+select s1,count(s1) from t1
 group by s1 collate latin1_swedish_ci having s1 = 'y';
 # ANSI requires: 1 row, with count(s1) = 2
 # MySQL returns: 1 row, with count(s1) = 1
 
-drop table wl1972;
+drop table t1;