MonetDB: default - no more GROUP BY constant (part 1)

Niels Nes via checkin-list Thu, 02 May 2024 06:14:55 -0700

Changeset: 8bac8f7f028c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/8bac8f7f028c
Modified Files:
        sql/server/rel_select.c
        sql/test/BugTracker-2023/Tests/misc-crashes-7390.test
        sql/test/BugTracker-2024/Tests/exp_equal-Bug-7478.test
        sql/test/BugTracker-2024/Tests/where_null-7450.test
        sql/test/SQLancer/Tests/sqlancer01.test
        sql/test/SQLancer/Tests/sqlancer02.test
        sql/test/SQLancer/Tests/sqlancer03.test
        sql/test/SQLancer/Tests/sqlancer04.test
        sql/test/SQLancer/Tests/sqlancer09.test
        sql/test/SQLancer/Tests/sqlancer11.test
        sql/test/SQLancer/Tests/sqlancer18.test
        sql/test/SQLancer/Tests/sqlancer23.test
        sql/test/miscellaneous/Tests/groupby_expressions.test
        sql/test/miscellaneous/Tests/simple_selects.test
Branch: default
Log Message:


no more GROUP BY constant (part 1)
We dis-allow grouping by constants.
Todo handle the group by numeric (again), ie lookup the numbered column in the
resulting selection


diffs (truncated from 511 to 300 lines):

diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4068,6 +4068,15 @@ rel_group_column(sql_query *query, sql_r
        exp_kind ek = {type_value, card_value, TRUE};
        sql_exp *e = rel_value_exp2(lquery, rel, grp, f, ek);
 
+       if (e && exp_is_atom(e)) {
+               sql_subtype *tpe = exp_subtype(e);
+               if (!tpe || tpe->type->eclass != EC_NUM) {
+                       if (!tpe)
+                               return sql_error(sql, 02, SQLSTATE(42000) 
"Cannot have a parameter (?) for group by column");
+                       return sql_error(sql, 02, SQLSTATE(42000) "SELECT: 
non-integer constant in GROUP BY");
+               }
+       }
+
        if (!e) {
                char buf[ERRSIZE], *name;
                int status = sql->session->status;
diff --git a/sql/test/BugTracker-2023/Tests/misc-crashes-7390.test 
b/sql/test/BugTracker-2023/Tests/misc-crashes-7390.test
--- a/sql/test/BugTracker-2023/Tests/misc-crashes-7390.test
+++ b/sql/test/BugTracker-2023/Tests/misc-crashes-7390.test
@@ -111,7 +111,7 @@ CREATE TEMP TABLE Table0 (Col0 INT, PRIM
 
 -- 07.sql
 statement ok
-CREATE VIEW v0 AS SELECT CAST (NULL AS INT) EXCEPT SELECT CAST (NULL AS INT) 
GROUP BY NULL
+CREATE VIEW v0 AS SELECT CAST (NULL AS INT) EXCEPT SELECT CAST (NULL AS INT)
 
 statement ok
 DROP VIEW v0
@@ -452,7 +452,7 @@ statement ok
 CREATE TABLE v0(v2 DOUBLE PRIMARY KEY, v1 VARCHAR(1))
 
 statement ok
-DELETE FROM v0 WHERE (SELECT v2 FROM v0 WHERE v0.v2 = v0.v1 GROUP BY 2.100000, 
v1) IN (10.100000, 10, 10 )
+DELETE FROM v0 WHERE (SELECT v2 FROM v0 WHERE v0.v2 = v0.v1 GROUP BY v1) IN 
(10.100000, 10, 10 )
 
 statement ok
 DROP TABLE v0
@@ -533,7 +533,7 @@ statement ok
 CREATE TABLE v0(v1 VARCHAR(30), v2 CHAR(20))
 
 statement ok
-DELETE FROM v0 WHERE (SELECT v1 WHERE (SELECT COUNT (*) OVER (ORDER BY v1 DESC 
NULLS LAST)) = v2 GROUP BY 2.100000 HAVING 0 NOT LIKE v2)
+DELETE FROM v0 WHERE (SELECT v1 WHERE (SELECT COUNT (*) OVER (ORDER BY v1 DESC 
NULLS LAST)) = v2)
 
 statement ok
 DROP TABLE v0
diff --git a/sql/test/BugTracker-2024/Tests/exp_equal-Bug-7478.test 
b/sql/test/BugTracker-2024/Tests/exp_equal-Bug-7478.test
--- a/sql/test/BugTracker-2024/Tests/exp_equal-Bug-7478.test
+++ b/sql/test/BugTracker-2024/Tests/exp_equal-Bug-7478.test
@@ -1,10 +1,10 @@
 query T nosort
-WITH x AS ( SELECT NULL ) SELECT 'x' IN ( SELECT * FROM x GROUP BY 'x' )
+WITH x AS ( SELECT NULL ) SELECT 'x' IN ( SELECT * FROM x)
 ----
 NULL
 
 query T nosort
-SELECT ( WITH x AS ( SELECT NULL ) SELECT 'x' IN ( SELECT * FROM x GROUP BY 
'x' ) )
+SELECT ( WITH x AS ( SELECT NULL ) SELECT 'x' IN ( SELECT * FROM x) )
 ----
 NULL
 
diff --git a/sql/test/BugTracker-2024/Tests/where_null-7450.test 
b/sql/test/BugTracker-2024/Tests/where_null-7450.test
--- a/sql/test/BugTracker-2024/Tests/where_null-7450.test
+++ b/sql/test/BugTracker-2024/Tests/where_null-7450.test
@@ -5,7 +5,7 @@ statement ok
 INSERT INTO t0 ( c0) VALUES (false)
 
 statement ok
-CREATE VIEW v0(c0) AS SELECT true FROM t0 WHERE NULL GROUP BY t0.c0, true
+CREATE VIEW v0(c0) AS SELECT true FROM t0 WHERE NULL GROUP BY t0.c0
 
 query I nosort
 SELECT * FROM v0
diff --git a/sql/test/SQLancer/Tests/sqlancer01.test 
b/sql/test/SQLancer/Tests/sqlancer01.test
--- a/sql/test/SQLancer/Tests/sqlancer01.test
+++ b/sql/test/SQLancer/Tests/sqlancer01.test
@@ -279,7 +279,7 @@ 0.835       0
 0.455  -1239303309
 
 query I rowsort
-SELECT MIN(DISTINCT t0.c1) FROM t0 WHERE 1 >= t0.c0 GROUP BY true
+SELECT MIN(DISTINCT t0.c1) FROM t0 WHERE 1 >= t0.c0
 ----
 -1239303309
 
@@ -547,7 +547,7 @@ 1511423462.000
 NULL
 
 query RR rowsort
-SELECT ALL 0.1002352, AVG(ALL t0.c0) FROM t0 GROUP BY CAST(t0.c0 AS 
STRING(799)), 0.4665444117594173, ((sql_min(+ ("locate"('', 'F', 150648381)), 
(((-1039870396))*(length('u')))))>>(CAST(0.588018201374832 AS INT)))
+SELECT ALL 0.1002352, AVG(ALL t0.c0) FROM t0 GROUP BY CAST(t0.c0 AS 
STRING(799)), ((sql_min(+ ("locate"('', 'F', 150648381)), 
(((-1039870396))*(length('u')))))>>(CAST(0.588018201374832 AS INT)))
 ----
 0.100
 -939120682.000
@@ -691,7 +691,7 @@ between symmetric (cast(0.5 as string)) 
 
 statement ok
 create view v1(c0, c1) as (select all 0.1, ((((cast(0.2 as int))/(+ 
(((((-212060493)*(816153822)))|(((2000022046)+(1143103273))))))))%(cast(cast(0.76693934
 as int) as int))) from v0
-group by 0.3, cast(- (cast(2.000022046E9 as int)) as boolean), 
sql_max(cast(cast(v0.c0 as string(336)) as int), length('1')), 
((cast(0.19825737 as int))%(-1509376269)), v0.c0
+group by sql_max(cast(cast(v0.c0 as string(336)) as int), length('1')), v0.c0
 having count(all (0.7) is not null) order by v0.c0 asc nulls first) with check 
option
 
 query I rowsort
@@ -738,18 +738,20 @@ NULL      02:45:58
 NULL   05:45:05
 
 query I rowsort
-SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY abs(0.8)
+SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1
 ----
 NULL
 
 query I rowsort
-SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY abs(0.8) HAVING (NOT 
(MIN(ALL NOT (NOT ((0.7) IS NULL))))) = TRUE
+SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY c1 HAVING (NOT (MIN(ALL NOT 
(NOT ((0.7) IS NULL))))) = TRUE
+UNION ALL
+SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY c1 HAVING NOT ((NOT 
(MIN(ALL NOT (NOT ((0.7) IS NULL))))) = TRUE)
 UNION ALL
-SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY abs(0.8) HAVING NOT ((NOT 
(MIN(ALL NOT (NOT ((0.7) IS NULL))))) = TRUE)
-UNION ALL
-SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY abs(0.8) HAVING ((NOT 
(MIN(ALL NOT (NOT ((0.7) IS NULL))))) = TRUE) IS NULL
+SELECT MEDIAN(ALL abs(CASE TIMESTAMP '1970-01-24 09:25:06' WHEN TIMESTAMP 
'1970-01-19 10:35:50' THEN 2 END)) FROM t1 GROUP BY c1 HAVING ((NOT (MIN(ALL 
NOT (NOT ((0.7) IS NULL))))) = TRUE) IS NULL
 ----
 NULL
+NULL
+NULL
 
 statement ok
 ROLLBACK
diff --git a/sql/test/SQLancer/Tests/sqlancer02.test 
b/sql/test/SQLancer/Tests/sqlancer02.test
--- a/sql/test/SQLancer/Tests/sqlancer02.test
+++ b/sql/test/SQLancer/Tests/sqlancer02.test
@@ -93,7 +93,6 @@ 0.1228907839970418
 
 statement error 22012!division by zero.
 SELECT AVG(ALL ((((CAST(((+ (-12))/(sql_min(-12, -67))) AS 
INT))+(abs(((13)*(10))))))*(((((greatest(12, -11))/(((((34-15)))/(CAST(t0.c0 AS 
INT))))))*(- (+ (((-29)*(6))))))))), 0.777 FROM t0 WHERE (0.06) NOT  BETWEEN 
ASYMMETRIC (t0.c0) AND (t0.c0)
-GROUP BY 0.1, 0.7, 0.2, + (+ (- (length('q')))), 
greatest(upper(upper('623865997')), trim(replace('vAd(Lup}Z', '-1774953973', 
'0.4235712099498189'), substr('', -19)))
 
 statement ok
 ROLLBACK
@@ -200,9 +199,11 @@ 0.920
 0.939
 
 query RRI rowsort
-SELECT ALL CAST((- (- (VAR_POP(ALL ((CAST(CAST(abs(709845242) AS INT) AS 
INT))%(abs(CAST((931144491) NOT IN (-587881807) AS INT))))))))^(- (+ (- 
(char_length('>])'))))) as decimal(10,3)), CAST(0.5186927954967 as 
decimal(14,13)), CAST("locate"(lower('[]'), rtrim('..ۄUH*6鉡q'), 
((((-233289450)&(-476676450)))-(- (236876413)))) AS INT)
-FROM t0 WHERE FALSE GROUP BY 1.2807753E7 HAVING MAX(ALL ((t0.c0)>=(NULL)))
+SELECT ALL CAST((- (- (VAR_POP(ALL ((CAST(CAST(abs(709845242) AS INT) AS 
INT))%(abs(CAST((931144491) NOT IN (-587881807) AS INT))))))))^(- (+ (- 
(char_length('>])'))))) as decimal(10,3)), CAST(0.5186927954967 as 
decimal(14,13)), CAST("locate"(lower('[]'), rtrim('..ۄUH*6鉡q'), 
((((-233289450)&(-476676450)))-(- (236876413)))) AS INT) FROM t0 WHERE FALSE
 ----
+NULL
+0.519
+0
 
 statement ok
 ROLLBACK
diff --git a/sql/test/SQLancer/Tests/sqlancer03.test 
b/sql/test/SQLancer/Tests/sqlancer03.test
--- a/sql/test/SQLancer/Tests/sqlancer03.test
+++ b/sql/test/SQLancer/Tests/sqlancer03.test
@@ -58,7 +58,7 @@ NULL
 
 query IR rowsort
 SELECT ALL length(upper(MIN(ALL CAST(((trim(CAST('' AS STRING(659)), 
CAST('o3%+i]抔DCöf▟nßOpNbybಜ7' AS STRING)))||(sql_min(sql_max(NULL, ''), 
splitpart('x', ',7+.', t0.c1)))) AS STRING(151))))), 0.4179268710155164
-FROM v0 LEFT OUTER JOIN t0 ON NOT (t0.c0) WHERE t0.c0 GROUP BY 0.3584962, 
CAST(t0.c1 AS STRING(601)), t0.c1
+FROM v0 LEFT OUTER JOIN t0 ON NOT (t0.c0) WHERE t0.c0 GROUP BY CAST(t0.c1 AS 
STRING(601)), t0.c1
 ----
 
 statement ok
@@ -371,8 +371,10 @@ SELECT 1 FROM t0 WHERE NULL IN (DATE '19
 ----
 
 query TI rowsort
-SELECT ALL DATE '1970-01-01', CAST(COUNT(ALL TIMESTAMP '1970-01-01 01:06:52') 
AS INT) FROM t0 WHERE (NULL) NOT IN (DATE '1970-01-01') GROUP BY ((CAST(0.6 AS 
STRING))||(0.75)), INTERVAL '-13' SECOND
+SELECT ALL DATE '1970-01-01', CAST(COUNT(ALL TIMESTAMP '1970-01-01 01:06:52') 
AS INT) FROM t0 WHERE (NULL) NOT IN (DATE '1970-01-01')
 ----
+1970-01-01
+0
 
 statement ok
 ROLLBACK
@@ -984,14 +986,18 @@ SELECT 1 FROM t0 GROUP BY 1, 2 HAVING MA
 1
 
 query IT rowsort
-SELECT ALL COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), 
trim('W', '1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM 
t0 WHERE ((t0.b)<=(t0.a)) GROUP BY 0.1, TRUE HAVING MAX(ALL TRUE)
+SELECT ALL COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), 
trim('W', '1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM 
t0 WHERE ((t0.b)<=(t0.a)) 
+UNION ALL
+SELECT COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), trim('W', 
'1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM t0 WHERE 
((t0.b)<=(t0.a)) 
 UNION ALL
-SELECT COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), trim('W', 
'1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM t0 WHERE 
((t0.b)<=(t0.a)) GROUP BY 0.1, TRUE HAVING NOT (MAX(ALL TRUE))
-UNION ALL
-SELECT ALL COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), 
trim('W', '1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM 
t0 WHERE ((t0.b)<=(t0.a)) GROUP BY 0.1, TRUE HAVING (MAX(ALL TRUE)) IS NULL
+SELECT ALL COUNT(ALL DATE '1970-01-02'), ((rtrim(CAST(0.4 AS STRING), 
trim('W', '1')))||(lower(CAST((('3')||(INTERVAL '4' MONTH)) AS STRING)))) FROM 
t0 WHERE ((t0.b)<=(t0.a)) 
 ----
 6
 0.434
+6
+0.434
+6
+0.434
 
 statement ok
 ROLLBACK
@@ -1045,7 +1051,7 @@ SELECT COUNT(t0.c0 = t0.c0 OR 1 BETWEEN 
 4
 
 query RIR rowsort
-SELECT 0.9, COUNT(ALL (((((((CAST(TRUE AS INT))-(t0.c0)))=(t0.c0))) = 
TRUE)OR((CAST(char_length('H') AS INT)) BETWEEN ASYMMETRIC (CAST(-1.3 AS INT)) 
AND (+ (length('0')))))), 0.3 FROM t0 GROUP BY 0.5, 0.4
+SELECT 0.9, COUNT(ALL (((((((CAST(TRUE AS INT))-(t0.c0)))=(t0.c0))) = 
TRUE)OR((CAST(char_length('H') AS INT)) BETWEEN ASYMMETRIC (CAST(-1.3 AS INT)) 
AND (+ (length('0')))))), 0.3 FROM t0
 ----
 0.900
 4
diff --git a/sql/test/SQLancer/Tests/sqlancer04.test 
b/sql/test/SQLancer/Tests/sqlancer04.test
--- a/sql/test/SQLancer/Tests/sqlancer04.test
+++ b/sql/test/SQLancer/Tests/sqlancer04.test
@@ -12,22 +12,22 @@ 332127116
 2136089006
 
 query R rowsort
-SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 WHERE 
((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), nullif(t0.c0, 
t0.c0))))) GROUP BY 0.1
-HAVING MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS STRING(538)))))
-UNION ALL SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0))))) GROUP BY 0.1
-HAVING NOT (MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS 
STRING(538))))))
-UNION ALL SELECT ALL SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0))))) GROUP BY 0.1
-HAVING (MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS STRING(538)))))) 
IS NULL
+SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 WHERE 
((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), nullif(t0.c0, 
t0.c0)))))
+UNION ALL SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0)))))
+UNION ALL SELECT ALL SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0)))))
 ----
+NULL
+NULL
+NULL
 
 query R rowsort
-SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 WHERE 
((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), nullif(t0.c0, 
t0.c0))))) GROUP BY 0.1
-HAVING MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS STRING(538)))))
-UNION ALL SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0))))) GROUP BY 0.1
-HAVING NOT (MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS 
STRING(538))))))
-UNION ALL SELECT ALL SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0))))) GROUP BY 0.1
-HAVING (MIN(ALL (('946496923')LIKE(CAST(nullif(0.5, 0.03) AS STRING(538)))))) 
IS NULL
+SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 WHERE 
((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), nullif(t0.c0, 
t0.c0)))))
+UNION ALL SELECT SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0)))))
+UNION ALL SELECT ALL SUM(ALL 0.12830007105673624234754015560611151158) FROM t0 
WHERE ((sql_min(t0.c0, t0.c0))<>(COALESCE (t0.c0, sql_min(abs(t0.c0), 
nullif(t0.c0, t0.c0)))))
 ----
+NULL
+NULL
+NULL
 
 statement ok
 ROLLBACK
@@ -57,7 +57,7 @@ else coalesce (case coalesce (dayofmonth
 cast(t0.c3 as double)) then case least(t0.c1, t0.c1) when case t0.c2 when 
interval '5' month then '*pf6/+}öq壚,j2\302\205K]sNKk,_%Tu' when 1016331084 then 
'0.4'
 else '*' end then 0.8 end when sql_min(t0.c3, t0.c3) then coalesce (cast(t0.c2 
as decimal), "second"(t0.c3), cast(t0.c2 as decimal),
 0.9) when coalesce (cast(t0.c1 as double), 0.2) then 0.0 else 0.0 end, 0.2) 
end from t0 where (interval '6' month)
-is not null group by cast(dayofmonth(t0.c0) as string(679)), 0.2
+is not null
 
 statement ok
 ROLLBACK
@@ -68,8 +68,10 @@ START TRANSACTION
 statement ok
 CREATE TABLE "sys"."t0" ("c0" INTERVAL SECOND NOT NULL)
 
-statement error 42000!types sec_interval(13,0) and int(31,0) are not equal
-SELECT CASE '5'^3 WHEN COUNT(TRUE) THEN 1 END FROM t0 GROUP BY 2 IN 
((CAST(INTERVAL '-2' SECOND AS INT))%2)
+query I
+SELECT CASE '5'^3 WHEN COUNT(TRUE) THEN 1 END FROM t0
+----
+NULL
 
 statement ok
 ROLLBACK
@@ -106,7 +108,6 @@ query I rowsort
 SELECT ALL COUNT(TIME '06:32:50') FROM t1 LEFT OUTER JOIN t0 ON TRUE
 LEFT OUTER JOIN (SELECT t1.c1, t0.c1, 0.43 FROM t0, t1) AS sub0 ON TRUE
 WHERE ((NOT ((TIME '00:25:07') IN (TIME '07:29:34', TIME '05:21:58', CASE 0.54 
WHEN 0.65 THEN TIME '17:23:46' ELSE TIME '05:14:30' END))))
-GROUP BY TIMESTAMP '1969-12-08 01:47:58'
 ----
 3
 
@@ -156,9 +157,10 @@ create view v1(c0, c1) as (select distin
 statement ok
 create view v2(c0) as (select distinct coalesce(abs(interval '-1976292283' 
month), cast(greatest('Dnwxjm4btQ9cp&\c''a_', '(y7,{q?][NHU,') as interval 
month)) from t1, t0 where ((upper(''))not ilike(lower('''wAg_ z 
''PPxXßgrd⍮G'))))
 
-statement error 42000!SELECT: cannot use non GROUP BY column 'v2.c0' in query 
results without an aggregate function
+query I
 select all v2.c0 from v2 right outer join v1 on 
(('8*By1q)*Oc<n')like(substr('', 1151312829, 406714197)))
-where (case v1.c0 when v1.c0 then interval '104279220' second end) in 
(interval '2129103763' second) group by timestamp '1970-01-21 14:05:46'
+where (case v1.c0 when v1.c0 then interval '104279220' second end) in 
(interval '2129103763' second)
+----
 
 statement ok
 ROLLBACK
@@ -200,13 +202,13 @@ NULL      "0.48396026646393187"
 
 query R rowsort
 SELECT CAST(SUM(ALL 0.14) AS DECIMAL(10,2)) FROM sys.t0 JOIN sys.t1 ON 
CAST(CASE t0.c1 WHEN t1.c1 THEN NOT (FALSE) WHEN t1.c1 THEN (t0.c0) NOT  
BETWEEN ASYMMETRIC (t1.c0) AND (t0.c0) WHEN t0.c1
-THEN (0.2) IN (0.3) END AS BOOLEAN) GROUP BY NOT (FALSE), 
COALESCE(((COALESCE(2, 4, 1))<<(COALESCE(5, -6))), ((COALESCE('-8', -4, 
6))|(COALESCE(-2, 3, 4, 5, -6))))
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]

MonetDB: default - no more GROUP BY constant (part 1)

Reply via email to