From: Sergey Glukhov
Date: March 19 2010 9:09am
Subject: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3411)
 Bug#51242
List-Archive: http://lists.mysql.com/commits/103783
X-Bug: 51242
Message-Id: <0KZI0055NUPQ1H50@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Boundary_(ID_3RFYxVMsd3W4AcHf0ps6tA)"

--Boundary_(ID_3RFYxVMsd3W4AcHf0ps6tA)
MIME-version: 1.0
Content-type: text/plain; CHARSET=US-ASCII
Content-transfer-encoding: 7BIT
Content-disposition: inline

#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:martin.hansson@stripped

 3411 Sergey Glukhov	2010-03-19
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. These parts may be lost
      during further having condition transformation
      in JOIN::exec. The fix is adding 'having'
      condition check for const tables after
      make_join_statistics is performed.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test result
     @ sql/sql_select.cc
        added 'having' condition check for const tables
        after make_join_statistics is performed.

    modified:
      mysql-test/r/having.result
      mysql-test/t/having.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2010-02-26 13:40:01 +0000
+++ b/mysql-test/r/having.result	2010-03-19 09:09:22 +0000
@@ -450,4 +450,39 @@ HAVING amount > 0
 ORDER BY t1.id1;
 id1	amount
 DROP TABLE t1;
+#
+# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+#
+CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
+INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9);
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+f1	f2
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having (('7' = 8) and (`test`.`table1`.`f1` >= 6))
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having ('7' = 8)
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2010-02-26 11:39:25 +0000
+++ b/mysql-test/t/having.test	2010-03-19 09:09:22 +0000
@@ -467,5 +467,35 @@ ORDER BY t1.id1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+--echo #
+CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
+INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9);
+
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8);
+
+DROP TABLE t1;
 
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-03-19 06:01:02 +0000
+++ b/sql/sql_select.cc	2010-03-19 09:09:22 +0000
@@ -1112,6 +1112,31 @@ JOIN::optimize()
   {
     conds=new Item_int((longlong) 0,1);	// Always false
   }
+
+  /*
+    It's necessary to check const part of HAVING cond as
+    there is a chance that some cond parts may become
+    const items after make_join_statisctics(for example
+    when Item is a reference to cost table field from
+    outer join).
+    This check is performed only for those conditions
+    which do not use aggregate functions. In such case
+    temporary table may not be used and const condition
+    elements may be lost during further having
+    condition transformation in JOIN::exec.
+  */
+  if (having && !having->with_sum_func)
+  {
+    COND *const_cond= make_cond_for_table(having, const_table_map, 0);
+    DBUG_EXECUTE("where", print_where(const_cond, "const_having_cond",
+                                      QT_ORDINARY););
+    if (const_cond && !const_cond->val_int())
+    {
+      zero_result_cause= "Impossible HAVING noticed after reading const tables";
+      DBUG_RETURN(0);
+    }
+  }
+
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=


--Boundary_(ID_3RFYxVMsd3W4AcHf0ps6tA)
MIME-version: 1.0
Content-type: text/bzr-bundle; CHARSET=US-ASCII;
 name="bzr/sergey.glukhov@stripped"
Content-transfer-encoding: 7BIT
Content-disposition: inline;
 filename="bzr/sergey.glukhov@stripped"

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: sergey.glukhov@stripped
# target_branch: file:///home/gluh/MySQL/mysql-5.1-bugteam/
# testament_sha1: a23484f53a0ea518ff27cdc292e89efeeb0cd991
# timestamp: 2010-03-19 13:09:32 +0400
# source_branch: bzr+ssh://sgluhov@stripped/bzrroot\
#   /server/mysql-5.1-bugteam/
# base_revision_id: martin.hansson@stripped\
#   v16i3urto17owty3
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcq7l1YABXTfgFAweff//3//
/+D////6YAvfVex0d1jR2wyDCGZYZGQ+1uzGR21So73oZKaZU8KejQmaGpp6nqbJNDE9CAaaABoA
ACSSBMBT2mlTbFE9IaY1A0NAGT1AAA0Gg4yZNGgNGmIyNDEMCaNMQYjQYQAGCREmlT2FT0aU9kam
NTKfpR+pP1E8p5HqEANqAaNDIwbUkamhHqNpPSHqNMmhptIZDRoBoAAAACSQBAIAjCaAJPIKep5E
eoaZMEzUyNN6mozRQH1H1HqAPA1r9A8yR8T7TeVIB/7o6u0MXXeGlohB6QB/77pv5yWbRVeX7KiR
Jmw7O7U2WRRPXbLTWgOiI2QEBa1NMLWoOlJos9wgxKTF6SXuiyz7xMoCPBhfsx4/UjQXhoUwR1lO
BpV4kAuAgIEIwDIrVmM0v4wNXqQcxadwwpZpG/pFfpfWH8r402xttZM3sDdyVv1ON73G80ip87EE
b40fJEnOA/QoKYchiYoQDkDiI8F+B94vA+4PbZxN3FVLyJ/k9G1fmQE4gaT3mJMqdQaA9pU3GY8Q
c3Ys3+z1PJEpOn+xOP8PmiTwNaiaqbdMco1Z+pYzjG4a67ZZZAnuYLKCJtZVgS1kC23Ib5qXUgWO
1/KN+YhbgVozLtcST8i1Pdo1J9VLCNKKJGLSWh1TCi07KVLFrYdWnA+IusX+h5KN/AXqDrFxFUUB
UFnBaxWIwFhp0vL4PwJPc7YTNwXZ0TLhy5reZ8ZMaO7rlMwySKbfXYIFVdGxPXDCiAgdPAoMe1bi
DxOfMDGeYtOcLEcXRFGp1wgkeZmZwhOK3aDSIGa+IQAxMbCopUvhTa0v8kAv3KQNFnSD4ZjAvAOc
uPcegYeGY6FsW5LiAecU07c+Tu2S+xbVhbEXDusPbcIdFXlF6ebNIl0vazGz1bBBijkgIC0ADAJh
RQnrSI+nijKn2oZpT1Jy0xvw9NghhhS+nYbwppocxyqBZkgpAA0HNKoJgSdQw5MkrT1ETgwySHF6
YsCbjMevpN88Ud8haB6iIcCYYYFh9ERIcfYMJYMgmwcnFRtGJEX4MK+yA+S0dzVlnxdbUIBaFBni
dqLMCMbypzLEjF1+RVw3pxKXhUTtsuB6cFY4GQRGZIQINqggqc9BHynRlz1SYmLUtN7hyKGYi4Tn
OTpEWRElQWlJG4RIt4jGxaU7nrlS4jGo+OciLIoVIHoUzzvMShzKq25LguxZExYDAS5+WfEmpyTh
L5KC0MpSLeBluJlhebIrDBNEjARm2+DKPcuSqvnVxu6DXkG2IGuA9P15ihN5AYkXklEh9Izyjizh
GmDe0G6YlJJa9biq6CV19VWfEOkyjgQ9T1ti2xrWNgjqInlrcYW4lw5WzYEyppvbjU2iUlHOoRuM
tJI083i6Y83MWE8jaTiiDyo9RJHMs9BtW2hXncuGyi6/eKJ4BrThRNxMoXXeUPvY4Gk4sYl/p1QP
FbDWPDHU5Gi4oWG00G8zqauVlqqQpuCr3BlQhzEy1YDzftJ9Zc4ylCd7iRE1S1vduNwGCqJYCGyH
CKa8iUEVU1OY0DF2Gs6ekpM+gbLgO1KzHAlQiIQxUYS3PxCstydoMa1MXO38i3/JAHqanlAlDA5G
LrorR3HzOG1XrTQRh5w6uKaRLq70UsNp1hVMRuD8w9p3g/E5oAPrP6QsM7Yxtv1h6ioFWcXZaUSM
iJ/eA/VWHfMowkTmHvF+9ofeKQOMRPPMp8qhiHmfWPFehMKZH4PE5L5CqWGkPiBhtQqFALje5+pz
hMGkA/MXuDaLMAXl4vMSYWTk54RB6SLwC02H7gUFeLzcONrhXCuOcOJI0YEUwqlcxX9xaznFMXph
gC4WZeV/e0eSFomCINBW8IynolqbHExJgDAWmuYrQpwKUFhOAzh+hu0fNlv9nqmVsUKA+9gvGOad
3k/K1FhawqFI+hh9p2JecYxpEpHjWgYxiY0mL9GDLSD8Mp3Gv7vsLLAJDmOYhESGw2FwekR/py9J
CNk4iiy64MBy62UUaRO3i/t8abkX5TNdXDnDKrYYiPgMQTKN89ZGgx0zTi0Pt5KYxNCOj8qCKB4S
MTWfkyuVdxLBbzIUCpsTJJAw3YyMOnpX+KHARq2bnLeZhjoIdxz2mY/ExmULxvh3o7TjeWGtPypr
NxCJNrdTBaLQ6ShyIwQFCmLGhrOzsYNpbD//Z4KYp4BtXrWAjlrBltgtOCyWwZ4YIcws6k3vASaC
i5iaIryORMra1tqPxKlohomBybiG9PenqKDh+S0XDXcV2FAxO/Y5j0Dk57ebnh2z55w7Te6pSjpk
t1CaHDAl3u79xBBvGBAzD15WR9MJU2Dbxmmx41ciQylg5YWpMDEgmvIXqZ5eZmFulaAiKCdkmDcp
T2uxYsNA8k7FpQnsrFFqqQcVNowxtFMWXOsnJSIkByWvPsfFDEKLl9xeJTgJ5cEBsCvNgjSrkIc2
KCjCOXsIJaiiSYJ7DquGHcOQjT1eQjxGBR/VLwP7wEGYgaR4J48ttO1dx/zp8tniYk1nCw0aQ44/
qrdauUA0jWnLwHra3sZdB0JxrBTfAWp6Qi2MBaaslSpshQOAdYEXM2Yp6cawoP3ZqzWYio8HAb4M
oJztIDh6sjGxZc6dyaoL8OcOr4/CrEaWBRpkzKGdydCQ0AISrLMD1cLNYUBaH75wCZihdDz17fWl
TswUwCZIr4IemB6zChstLmbw4WUgAwO/OnZkvxRcwaBlylZmibxJh0o8SvPIiRi/vS0hVErsyLqM
bXSeorZsTgQonQp8mybuGQW2PFl0ajxSChKvthUjJRjDJMCAZJlBAzYjkVy1HUIcL1MULza/4HsQ
uObf2PIdyzDIDOsrMg6jR6fI70RCoUQ9WRXp79R2Z677KBpEOs+KudLd83Co4ibi1YPYtGTxNa5Q
iiGpANUlbISJDgRZ25O87FSstsJEwVYSSll3FvFaozcdKXPIdKagAuom02B0KF6F0XbVqNblrd7l
gHemMwe9A5cRBgzNgKrEmSgmCCA8YPHgEkrRTFqcBBOesHI7yHcfWanLhYrvZYknniuBz50LXQO7
u60PQy5CMOmYdK3BdrqtAtExD03Auq6Zl3C+iKhD3piPaswWVmALIRfbuw6wyYDfah+LLBqTpgMW
yCJRwLKoOEyUMaXiJ3A7Idq8XLBk+YIlrM14Gu07JxDLrVXixWRFWKpaVIUJqZE4GbDjJxtquFPT
STWWLc5CqpYwxHxGafdvFlW3FYtVnsZGZDHWth5IZOiTzDwmGCV4jOpXh30WrjLVgGDuiIiI16FE
yqUMRb6GZFDhw1EQiBNDGwWAZ1GVXC6VjqViC33oCWOqwdcx1N5N1GxylNwpum+eVz1BXURngYiD
0B2q180YDqi9P5xMuJhI5C5YMEr0DAXlEwnD3IiBkW2XXRy8tvYaRkcJmw7w4DzMkPyE+pimecxc
735wyNjSIjKdGTMEXBJxFRUZC2KOkkPgrZvYYZkTcdyY2mw6lFa8fWWh5f1XTggznghllDpaKhVE
gVVhmUsSrylAgmJC9AgwX4iIQidsCAgiYEWDSORtQpl44FUWzqSIdu4qiErFaWuSe4RDS9Ihkxmm
S9BBy6ZKsp+AicREgk2uREQqM1oysCmwPkNMehA98K1wOVF3NYzG1Gif4u5IpwoSGVdy6sA=


--Boundary_(ID_3RFYxVMsd3W4AcHf0ps6tA)--