SQL Query throw Error 4818:(Subqueries in the SELECT or ORDER BY are not supported if the subquery i
Hi;
I got this error 4818 for some SQL generated by my application. And I think it's a bug.
I use HP Vertica 6.1.x. Here are steps to reproduce:
1. Create a simple table ttest (id int, name varchar(50), age int);
2. Insert some records.
insert into ttest(id,name,age)values(1,'ken',32);
insert into ttest(id,name,age)values(2,'susan',27);
insert into ttest(id,name,age)values(3,'better',28);
insert into ttest(id,name,age)values(4,'blue',23);
insert into ttest(id,name,age)values(5,'sam',40);
Query1 and Query2 listed below can execute successfully and get result. Query3 will always fail. I just don't see how. Could anyone please help??
Query1:
SELECT
"ttest"."Column_2_3" AS "Column_1_1",
CAST(MAX(CAST(CASE WHEN "ttest"."Column_2_2" > 0 THEN 1 WHEN NOT "ttest"."Column_2_2" > 0 THEN 0 END AS INT)) AS BOOLEAN) AS "Column_1_2"
FROM
(SELECT
"ttest"."Column_3_1" AS "Column_2_1",
CAST(CASE WHEN "ttest"."Column_3_2" > 0 THEN 1 ELSE 0 END AS BOOLEAN) AS "Column_2_2",
(Width_Bucket (
CAST("ttest"."Column_3_1" AS DOUBLE PRECISION),
CAST((SELECT MIN("ttest"."Column_4_1")
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("ttest"."Column_4_1") - ((MAX("ttest"."Column_4_1") - MIN("ttest"."Column_4_1")) / 5))
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
4) - 1) AS "Column_2_3",
1 AS "Column_2_4"
FROM
(SELECT
"public#ttest"."id" AS "Column_3_1",
CAST(CASE WHEN 0 > 0 THEN 1 WHEN 0 = 0 THEN 0 ELSE 0 END AS BOOLEAN) AS "Column_3_2"
FROM "public"."ttest" AS "public#ttest")
AS "ttest")
AS "ttest"
GROUP BY "ttest"."Column_2_3" ;
Query2:
SELECT
CAST(CASE WHEN "ttest"."Column_3_2" > 0 THEN 1 ELSE 0 END AS BOOLEAN) AS "Column_2_2",
(Width_Bucket (
CAST("ttest"."Column_3_1" AS DOUBLE PRECISION),
CAST((SELECT MIN("ttest"."Column_4_1")
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("ttest"."Column_4_1") - ((MAX("ttest"."Column_4_1") - MIN("ttest"."Column_4_1")) / 15))
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
14) - 1
) AS "Column_2_3"
FROM
(SELECT
"public#ttest"."id" AS "Column_3_1",
CAST(CASE WHEN (
Width_Bucket (
CAST("public#ttest"."id" AS DOUBLE PRECISION),
CAST((SELECT MIN("public#ttest"."id") FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("public#ttest"."id") - ((MAX("public#ttest"."id") - MIN("public#ttest"."id")) / 15))
FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
14) - 1
) IN (7)
THEN 1
WHEN NOT (
Width_Bucket (
CAST("public#ttest"."id" AS DOUBLE PRECISION),
CAST((SELECT MIN("public#ttest"."id") FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("public#ttest"."id") - ((MAX("public#ttest"."id") - MIN("public#ttest"."id")) / 15))
FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
14) - 1
) IN (7)
THEN 0
ELSE 0
END AS BOOLEAN
) AS "Column_3_2"
FROM "public"."ttest" AS "public#ttest")
AS "ttest";
Query3:
SELECT
"ttest"."Column_2_3" AS "Column_1_1",
CAST(MAX(CAST(CASE WHEN "ttest"."Column_2_2" > 0 THEN 1 WHEN NOT "ttest"."Column_2_2" > 0 THEN 0 END AS INT)) AS BOOLEAN) AS "Column_1_2"
FROM
(SELECT
CAST(CASE WHEN "ttest"."Column_3_2" > 0 THEN 1 ELSE 0 END AS BOOLEAN) AS "Column_2_2",
(Width_Bucket (
CAST("ttest"."Column_3_1" AS DOUBLE PRECISION),
CAST((SELECT MIN("ttest"."Column_4_1")
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("ttest"."Column_4_1") - ((MAX("ttest"."Column_4_1") - MIN("ttest"."Column_4_1")) / 15))
FROM (SELECT "public#ttest"."id" AS "Column_4_1" FROM "public"."ttest" AS "public#ttest") AS "ttest") AS DOUBLE PRECISION),
14) - 1
) AS "Column_2_3"
FROM
(SELECT
"public#ttest"."id" AS "Column_3_1",
CAST(CASE WHEN (
Width_Bucket (
CAST("public#ttest"."id" AS DOUBLE PRECISION),
CAST((SELECT MIN("public#ttest"."id") FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("public#ttest"."id") - ((MAX("public#ttest"."id") - MIN("public#ttest"."id")) / 15))
FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
14) - 1
) IN (7)
THEN 1
WHEN NOT (
Width_Bucket (
CAST("public#ttest"."id" AS DOUBLE PRECISION),
CAST((SELECT MIN("public#ttest"."id") FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
CAST((SELECT (MAX("public#ttest"."id") - ((MAX("public#ttest"."id") - MIN("public#ttest"."id")) / 15))
FROM "public"."ttest" AS "public#ttest") AS DOUBLE PRECISION),
14) - 1
) IN (7)
THEN 0
ELSE 0
END AS BOOLEAN
) AS "Column_3_2"
FROM "public"."ttest" AS "public#ttest")
AS "ttest")
AS "ttest"
GROUP BY "ttest"."Column_2_3" ;
Comments
I have the same question. What do have workaround for this bug? Thanks.
SELECT 'abc' AS ctx,
SUM(a.TID) AS TID,
SUM(a.AMT) AS AMT,
SUM(a.BMT) AS BMT,
(SELECT SUM(rzrm) FROM abcd o) AS rzrm
FROM xyz a
GROUP BY 1
but i get the error as