diff options
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 97ba35ed9cd..0b392305bbf 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -950,3 +950,68 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 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 |