SQL Query throw Error 4818:(Subqueries in the SELECT or ORDER BY are not supported if the subquery i

Ying_Lu

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

freeman

hi amelia,
I have the same question. What do have workaround for this bug?  Thanks.

sameer_tuladhar

How to aggregate values in a subquery. I have tried following query

 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  

Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY