From d67ef7a2fb3b52b3f61ce71dfe23cf4d610afc3c Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 5 Dec 2016 17:37:54 +0100 Subject: MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error check for VIEW/DERIVED fields --- mysql-test/r/derived.result | 65 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 65 insertions(+) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 2c316a829a7..a4d474c9cdf 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -924,3 +924,68 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table DROP TABLES t1,t2; +# +# MDEV-10663: Use of Inline table columns in HAVING clause +# throws 1463 Error +# +set @save_sql_mode = @@sql_mode; +set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +CREATE TABLE `example1463` ( +`Customer` varchar(255) NOT NULL, +`DeliveryStatus` varchar(255) NOT NULL, +`OrderSize` int(11) NOT NULL +); +INSERT INTO example1463 VALUES ('Charlie', 'Success', 100); +INSERT INTO example1463 VALUES ('David', 'Success', 110); +INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200); +INSERT INTO example1463 VALUES ('David', 'Success', 100); +INSERT INTO example1463 VALUES ('David', 'Unknown', 100); +INSERT INTO example1463 VALUES ('Edward', 'Success', 150); +INSERT INTO example1463 VALUES ('Edward', 'Pending', 150); +SELECT Customer, Success, SUM(OrderSize) +FROM (SELECT Customer, +CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, +OrderSize +FROM example1463) as subQ +GROUP BY Success, Customer +WITH ROLLUP; +Customer Success SUM(OrderSize) +Charlie No 200 +David No 100 +Edward No 150 +NULL No 450 +Charlie Yes 100 +David Yes 210 +Edward Yes 150 +NULL Yes 460 +NULL NULL 910 +SELECT Customer, Success, SUM(OrderSize) +FROM (SELECT Customer, +CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, +OrderSize +FROM example1463) as subQ +GROUP BY Success, Customer; +Customer Success SUM(OrderSize) +Charlie No 200 +David No 100 +Edward No 150 +Charlie Yes 100 +David Yes 210 +Edward Yes 150 +SELECT Customer, Success, SUM(OrderSize) +FROM (SELECT Customer, +CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, +OrderSize +FROM example1463) as subQ +GROUP BY Success, Customer +HAVING Success IS NOT NULL; +Customer Success SUM(OrderSize) +Charlie No 200 +David No 100 +Edward No 150 +Charlie Yes 100 +David Yes 210 +Edward Yes 150 +DROP TABLE example1463; +set sql_mode= @save_sql_mode; +# end of 5.5 -- cgit v1.2.1