When users combine integer constants as expressions with DISTINCT, the engine pushes down GROUP BY <integer-constant>. Exasol interprets this as "group by column number", which yields wrong results. What should happen here is that you get a result with a single group. GROUP BY TRUE, GROUP BY '0' or any other non-integer constant will work as expected.
Note that some databases interpret this as "group by integer constant". So the behavior is database specific. The reason is that this case is not covered by the SQL standard.
Workaround:
The adapter should work around this by turning GROUP BY <integer-constant> into GROUP BY <other-constant>.
Theoretically that should be done on a db-by-db basis, but that is too much effort, so a general workaround in VSCL and VSCJ should be implemented. This can be overridden in the dialect if there is ever the need.
Features
Turns GROUP BY <integer-constant> into GROUP BY <other-constant>
Summary
When users combine integer constants as expressions with
DISTINCT
, the engine pushes downGROUP BY <integer-constant>
. Exasol interprets this as "group by column number", which yields wrong results. What should happen here is that you get a result with a single group.GROUP BY TRUE
,GROUP BY '0'
or any other non-integer constant will work as expected.Note that some databases interpret this as "group by integer constant". So the behavior is database specific. The reason is that this case is not covered by the SQL standard.
Workaround:
The adapter should work around this by turning
GROUP BY <integer-constant>
intoGROUP BY <other-constant>
.Theoretically that should be done on a db-by-db basis, but that is too much effort, so a general workaround in VSCL and VSCJ should be implemented. This can be overridden in the dialect if there is ever the need.
Features
GROUP BY <integer-constant>
intoGROUP BY <other-constant>