From 8b41f491c82063382bc76d8a8c266b83b0ab8fe0 Mon Sep 17 00:00:00 2001
From: Chaithra Gopalareddy <chaithra.gopalareddy@oracle.com>
Date: Thu, 10 Jan 2013 16:17:13 +0530
Subject: Bug#11760726: LEFT JOIN OPTIMIZED INTO JOIN LEADS TO              
 INCORRECT RESULTS

This is a backport of fix for Bug#13068506.

mysql-test/r/join_outer.result:
  Added test result for Bug#13068506
mysql-test/t/join_outer.test:
  Added test case for Bug#13068506
sql/item.h:
  Implement Item_outer_ref::not_null_tables()
---
 mysql-test/r/join_outer.result | 32 ++++++++++++++++++++++++++++++++
 mysql-test/t/join_outer.test   | 30 ++++++++++++++++++++++++++++++
 2 files changed, 62 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index d8a734a7158..f745e953331 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1485,4 +1485,36 @@ EXECUTE prep_stmt;
 f
 1
 DROP TABLE t1;
+#
+# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS 
+#                WRONG RESULT
+#
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;;
+field1
+20
+20
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1 GROUP BY field1;;
+field1
+20
+
+drop table t1,t2,t3;
+# End of test for Bug#13068506
 End of 5.1 tests
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 1ba29fdd4cf..13811731dad 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1067,5 +1067,35 @@ EXECUTE prep_stmt;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS 
+--echo #                WRONG RESULT
+--echo #
+
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+let $query= SELECT (
+  SELECT MAX( t2.i2 )
+  FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+  WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;
+
+--echo
+--eval $query;
+--echo
+--eval $query GROUP BY field1;
+
+--echo
+drop table t1,t2,t3;
+
+--echo # End of test for Bug#13068506
 
 --echo End of 5.1 tests
-- 
cgit v1.2.1