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" ;
