Phenomena in GROUP BY

helli
Contributor

We found an inconsistent behaviour in the group by clause. The essence of a huge SQL boils down to the following effect: In the SQL below the first GROUP BY gives the error message:

Wrong column number. Too large value 12345 as select list column reference in GROUP BY (largest possible value is 1)

The second GROUP BY gives the correct result. This is inconsistent to my mind. The second works. Only problem is, that SQL programmers using "GROUP BY 1,2,3,4, ..."  end in HELL. We like to avoid that 😉 

SELECT 12345 AS dudu
FROM dual
GROUP BY LOCAL.dudu -- does not work but should do the same as second
--GROUP BY 1 -- works but less elegant

1 ACCEPTED SOLUTION

mwellbro
Xpert

This is a fun one 🙂
I think it´s at least explainable if you think of it like this: "local" is a short-cut to allow you to reference expressions or "column definitions" that you use multiple times in the same query block - it pretty much just does "copy-and-paste" before the execution.

In your case a copy-and-paste clashes with Exasols ability to reference columns by pos-id in a group by - if you´d have a column_name instead of a literal this wouldn´t be an issue but as it stands you end up telling Exasol to use the 12345th column in
your select list 😂

Looking forward to an exa-* - take on this 🙂

Cheers,
Malte

View solution in original post

5 REPLIES 5

exa-Kristof
Team Exasol
Team Exasol

Thank you @mwellbro !

Malte's explanation hits the nail on the head. It is a known issue, but for now, it is not planned to change the current behaviour.

You already provided one workaround (maybe HELL is merciful in this case!? 😉). Another one would be to put a string into the constant column.

May I ask what the actual use case is, @helli ?

helli
Contributor

Thanks @mwellbro for the fast answer and your explanation. Have to admit that i do not understand it fully.

Thanks @exa-Kristof, the variant with string works. (And also supports my theory that we should use varchar for ALL columns 😉)

--works
SELECT '12345' AS dudu
FROM dual
GROUP BY LOCAL.dudu;

--works not
SELECT 12345 AS dudu
FROM dual
GROUP BY LOCAL.dudu;

 

Third Way:

-- works
SELECT 12345 AS 'dudu'
FROM dual
GROUP BY 'dudu';

Fourth way: 

Encapsulate the fixvalue in an aggregate function and remove it from the group by.

-- works
SELECT max(12345) AS dudu, 'anothervalue' AS aov
FROM dual
GROUP BY LOCAL.aov;

Fifth Way.

find the difference to the faulty version ...

--works
SELECT 12345 + 0 AS dudu
FROM dual
GROUP BY LOCAL.dudu;

 

The use case is some standard "insert into select from". There are some aggregates (max) und same fixvalues needed. Needed to add the fixvalues to the group by. 

mwellbro
Xpert

Hi helli,

the difference of your 5th example is that 12345+5 is an expression and no longer an integer literal ( the referencing
of columns in the group by doesn´t work with expressions, you can´t tell a group by "give me the column at the 6+1-th ( or 2*4-th or MOD(10,2)-th ) position" ).
Fun fact: using 12345.0 for "dudu" will yield an error ( since it´s transparently translated to 12345, in contrast to '12345' which is and will remain a string ) , while 12345.01 will work 😉

Cheers,
Malte

 

mwellbro
Xpert

This is a fun one 🙂
I think it´s at least explainable if you think of it like this: "local" is a short-cut to allow you to reference expressions or "column definitions" that you use multiple times in the same query block - it pretty much just does "copy-and-paste" before the execution.

In your case a copy-and-paste clashes with Exasols ability to reference columns by pos-id in a group by - if you´d have a column_name instead of a literal this wouldn´t be an issue but as it stands you end up telling Exasol to use the 12345th column in
your select list 😂

Looking forward to an exa-* - take on this 🙂

Cheers,
Malte

View solution in original post

helli
Contributor

🙂 after reading it 10 + x times I think I got it. Really strange. Some of the variants i gave support your explanation.