Bug#11756928 48916: SERVER INCORRECTLY PROCESSING HAVING CLAUSES WITH AN ORDER BY CLAUSE

Before sorting HAVING condition is split into two parts,
first part is a table related condition and the rest of is
HAVING part. Extraction of HAVING part does not take into account
the fact that some of conditions might be non-const but
have 'used_tables' == 0 (independent subqueries)
and because of that these conditions are cut off by
make_cond_for_table() function.
The fix is to use (table_map) 0 instead of used_tables in
third argument for make_cond_for_table() function.
It allows to extract elements which belong to sorted
table and in addition elements which are independend
subqueries.


mysql-test/r/having.result:
  test case
mysql-test/t/having.test:
  test case
sql/sql_select.cc:
  The fix is to use (table_map) 0 instead of used_tables in
  third argument for make_cond_for_table() function.
  It allows to extract elements which belong to sorted
  table and in addition elements which are independend
  subqueries.

3 files changed, 85 insertions, 1 deletions

diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index cd1b4ae0218..4253ac7e5c3 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -545,4 +545,26 @@ FROM t1 JOIN t2 ON t2.f2 LIKE 'x'
 HAVING field1 < 7;
 field1
 DROP TABLE t1,t2;
+#
+# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+#
+CREATE TABLE t1 (f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
+CREATE TABLE t2 (f1 INT, f2 INT);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+SELECT t1.f1
+FROM t1
+HAVING 2 IN (SELECT f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+DROP TABLE t1,t2;
 End of 5.1 tests
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index c808e747523..2ed8b40b858 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -564,4 +564,30 @@ HAVING field1 < 7;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+--echo #
+
+CREATE TABLE t1 (f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
+CREATE TABLE t2 (f1 INT, f2 INT);
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+SELECT t1.f1
+FROM t1
+HAVING 2 IN (SELECT f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+DROP TABLE t1,t2;
+
+
 --echo End of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ab287e57aa1..46e9ad242b3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2215,7 +2215,7 @@ JOIN::exec()
 
       Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
 						 used_tables,
-						 used_tables);
+						 (table_map) 0);
       if (sort_table_cond)
       {
 	if (!curr_table->select)
@@ -12852,6 +12852,42 @@ static bool test_if_ref(Item_field *left_item,Item *right_item)
   return 0;					// keep test
 }
 
+/**
+   Extract a condition that can be checked after reading given table
+
+   @param cond       Condition to analyze
+   @param tables     Tables for which "current field values" are available
+   @param used_table Table that we're extracting the condition for (may
+                     also include PSEUDO_TABLE_BITS, and may be zero)
+   @param exclude_expensive_cond  Do not push expensive conditions
+
+   @retval <>NULL Generated condition
+   @retval =NULL  Already checked, OR error
+
+   @details
+     Extract the condition that can be checked after reading the table
+     specified in 'used_table', given that current-field values for tables
+     specified in 'tables' bitmap are available.
+     If 'used_table' is 0
+     - extract conditions for all tables in 'tables'.
+     - extract conditions are unrelated to any tables
+       in the same query block/level(i.e. conditions
+       which have used_tables == 0).
+
+     The function assumes that
+     - Constant parts of the condition has already been checked.
+     - Condition that could be checked for tables in 'tables' has already
+     been checked.
+
+     The function takes into account that some parts of the condition are
+     guaranteed to be true by employed 'ref' access methods (the code that
+     does this is located at the end, search down for "EQ_FUNC").
+
+   @note
+     Make sure to keep the implementations of make_cond_for_table() and
+     make_cond_after_sjm() synchronized.
+     make_cond_for_info_schema() uses similar algorithm as well.
+*/ 
 
 static COND *
 make_cond_for_table(COND *cond, table_map tables, table_map used_table)