diff options
author | Sergei Golubchik <serg@mariadb.org> | 2016-12-11 09:53:42 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2016-12-11 09:53:42 +0100 |
commit | 2f20d297f8ea731d845bb220e680ad10c7a927bc (patch) | |
tree | 9bd18ef1ab766422ba4c51b4ab189e259955a2d0 /mysql-test/r/derived.result | |
parent | a629b5172e96c96c414fca70fffd64c80f2f7e8f (diff) | |
parent | eb4f2e063c341d9f3644339c68cb01679e782001 (diff) | |
download | mariadb-git-2f20d297f8ea731d845bb220e680ad10c7a927bc.tar.gz |
Merge branch '10.0' into 10.1
Diffstat (limited to 'mysql-test/r/derived.result')
-rw-r--r-- | mysql-test/r/derived.result | 65 |
1 files changed, 65 insertions, 0 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 08709c6def0..2f6662e0132 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -950,6 +950,71 @@ id select_type table type possible_keys key key_len ref rows Extra DROP TABLES t1,t2; set optimizer_switch=@save_derived_optimizer_switch; # +# 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 +# # Start of 10.1 tests # # |