summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/ps.result104
-rw-r--r--mysql-test/r/sp.result39
-rw-r--r--mysql-test/t/ps.test92
-rw-r--r--mysql-test/t/sp.test36
4 files changed, 269 insertions, 2 deletions
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 09b3c1a3c67..4825b4db403 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3125,3 +3125,107 @@ Handler_read_rnd_deleted 0
Handler_read_rnd_next 0
deallocate prepare st;
drop table t1;
+#
+# LP bug#993459 Execution of PS for a query with GROUP BY
+# returns wrong result (see also mysql bug#13805127)
+#
+PREPARE s1 FROM
+"
+SELECT c1, t2.c2, count(c3)
+FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE @x = 1
+ UNION
+ SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2
+";
+
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+UNION
+SELECT '2012-03-01 01:99345900:00', 2, 1 FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1 c2 count(c3)
+2012-03-01 01:00:00 3 1
+2012-03-01 01:99345900:00 2 1
+2012-03-01 02:00:00 3 1
+
+EXECUTE s1;
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+2012-03-01 01:00:00 3 1
+2012-03-01 02:00:00 3 1
+
+SET @x = 2;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+UNION
+SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+
+EXECUTE s1;
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+UNION
+SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+2012-03-01 01:00:00 3 1
+2012-03-01 02:00:00 3 1
+
+EXECUTE s1;
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+2012-03-01 01:00:00 3 1
+2012-03-01 02:00:00 3 1
+DEALLOCATE PREPARE s1;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index d9c34701bd1..3e7e0b5cd0c 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -7108,3 +7108,42 @@ DROP FUNCTION f1;
# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
+#
+# LP bug#993459 Execution of PS for a query with GROUP BY
+# returns wrong result (see also mysql bug#13805127)
+#
+CREATE PROCEDURE p1(x INT UNSIGNED)
+BEGIN
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE x = 1
+UNION
+SELECT 2 FROM dual WHERE x = 1 OR x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+UNION
+SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2
+;
+END|
+
+CALL p1(1);
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+2012-03-01 01:00:00 3 1
+2012-03-01 02:00:00 3 1
+CALL p1(2);
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+CALL p1(1);
+c1 c2 count(c3)
+2012-03-01 01:00:00 2 1
+2012-03-01 01:00:00 3 1
+2012-03-01 02:00:00 3 1
+DROP PROCEDURE p1;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index a3b064acb04..2f101b4e42f 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3152,6 +3152,94 @@ execute st;
show status like '%Handler_read%';
deallocate prepare st;
-
drop table t1;
-
+
+--echo #
+--echo # LP bug#993459 Execution of PS for a query with GROUP BY
+--echo # returns wrong result (see also mysql bug#13805127)
+--echo #
+
+PREPARE s1 FROM
+"
+SELECT c1, t2.c2, count(c3)
+FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE @x = 1
+ UNION
+ SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2
+";
+
+--echo
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE @x = 1
+ UNION
+ SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:99345900:00', 2, 1 FROM dual
+ ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+--echo
+EXECUTE s1;
+
+--echo
+SET @x = 2;
+SELECT c1, t2.c2, count(c3)
+FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE @x = 1
+ UNION
+ SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+--echo
+EXECUTE s1;
+
+--echo
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE @x = 1
+ UNION
+ SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+--echo
+EXECUTE s1;
+
+DEALLOCATE PREPARE s1;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index bbbb9784d35..d403e94ebbb 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -8441,3 +8441,39 @@ DROP FUNCTION f1;
--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------
+
+--echo #
+--echo # LP bug#993459 Execution of PS for a query with GROUP BY
+--echo # returns wrong result (see also mysql bug#13805127)
+--echo #
+delimiter |;
+
+CREATE PROCEDURE p1(x INT UNSIGNED)
+BEGIN
+ SELECT c1, t2.c2, count(c3)
+ FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE x = 1
+ UNION
+ SELECT 2 FROM dual WHERE x = 1 OR x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+ WHERE t2.c2 = t1.c2
+ GROUP BY c1, c2
+ ;
+END|
+
+delimiter ;|
+
+--echo
+CALL p1(1);
+CALL p1(2);
+CALL p1(1);
+
+DROP PROCEDURE p1;
View Lorry Controller Status