order by a field, which is not in select distinct list -- let Firebird raise an error at prepare time [CORE2997]

[firebird-automations]

Submitted by: Volker Rehn (vr2_s18)

CREATE TABLE TEST (
TYP INTEGER,
ATEXT VARCHAR(20),
ORD SMALLINT
);
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(3, 1, 'I63.9');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(7, 1, 'I48.10');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(7, 1, 'I63.9');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'E78.0');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'I10.90');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'I48.10');

select
--distinct
typ, atext
from test
order by ord, atext

*Without* distinct all is as expected, the result is ordered by "ord".
*With* distinct the field "ord" isn't and cannot be used anymore.

Firebird should raise an error *at prepare time*, since it cannot solve this task without ignoring part of the given select statement.

regards, Volker

[update vr] replaced sort by ord, removed comma - thanks Sean

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

1 - You realize that 'sort' is a reserved word and can't be used without treating the field as a delimited name (i.e. "SORT").

2 - Your CREATE TABLE statement has an extra comma, which causes the statement to fail.

3 - Firebird v2.1 executes the SELECT (both variants) without error.

4 - Although the reference to the "sort" column is extraneous in the order by clause, why do you think it is invalid?

[firebird-automations]

Modified by: Volker Rehn (vr2_s18)

description: CREATE TABLE TEST (
TYP INTEGER,
ATEXT VARCHAR(20),
SORT SMALLINT,
);
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(3, 1, 'I63.9');
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(7, 1, 'I48.10');
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(7, 1, 'I63.9');
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(8, 0, 'E78.0');
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(8, 0, 'I10.90');
INSERT INTO TEST (SORT, TYP, ATEXT) VALUES(8, 0, 'I48.10');

select
--distinct
typ, atext
from test
order by sort, atext

*Without* distinct all is as expected, the result is ordered by sort.
*With* distinct the field "sort" isn't and cannot be used anymore.

Firebird should raise an error *at prepare time*, since it cannot solve this task without ignoring part of the given select statement.

regards, Volker

=>

CREATE TABLE TEST (
TYP INTEGER,
ATEXT VARCHAR(20),
ORD SMALLINT
);
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(3, 1, 'I63.9');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(7, 1, 'I48.10');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(7, 1, 'I63.9');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'E78.0');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'I10.90');
INSERT INTO TEST (ORD, TYP, ATEXT) VALUES(8, 0, 'I48.10');

select
--distinct
typ, atext
from test
order by ord, atext

*Without* distinct all is as expected, the result is ordered by "ord".
*With* distinct the field "ord" isn't and cannot be used anymore.

Firebird should raise an error *at prepare time*, since it cannot solve this task without ignoring part of the given select statement.

regards, Volker

[update vr] replaced sort by ord, removed comma - thanks Sean

[firebird-automations]

Commented by: Volker Rehn (vr2_s18)

1 + 2: updated the original posting

3 + 4: If I give the server a specific task which it simply can't solve and silently ignores, I would like to know about it. What I ask for is order by ord - what I get is *not* ordered by ord

- If I goup by a column which isn't groupable, I get an error.
- If I write sql which returns multiple rows where only one row is permitted, I get an error.

[firebird-automations]

Commented by: @hvlad

Looks like we have a bug

select distinct typ, atext
from test
order by sort, atext

"works", while equivalent query

select typ, atext
from test
group by typ, atext
order by sort, atext

raised at prepare (!) time

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).