unexpected error with "GROUP BY 0"

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

unexpected error with "GROUP BY 0"

Mark Brand
Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Hick Gunter
Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.

There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Mark Brand
Thanks. I had forgotten that GROUP BY considers a literal integer in
this context to be a column number, a feature I don't use.

These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:

> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>
> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 11:32
> An: SQLite mailing list <[hidden email]>
> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>
> Hi,
>
> Is there a good reason for this error:
>
> sqlite> SELECT  0 GROUP BY 0;
> Error: 1st GROUP BY term out of range - should be between 1 and 1
> sqlite> SELECT 0 GROUP BY 1;
> 0
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Hick Gunter
You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use.

These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:

> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>
> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]] Im Auftrag von
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 11:32
> An: SQLite mailing list <[hidden email]>
> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>
> Hi,
>
> Is there a good reason for this error:
>
> sqlite> SELECT  0 GROUP BY 0;
> Error: 1st GROUP BY term out of range - should be between 1 and 1
> sqlite> SELECT 0 GROUP BY 1;
> 0
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Mark Brand
Thanks for the clarification.


> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the
"constant integer" is what gets interpreted as a result column number. 
What is an "output column identifier" then? Isn't it already covered by
the broader category "any other expression"?

It's still a pretty astonishing language feature(!?) that an integer
numeric-literal, which in every other column-like context represents its
integer value, gets interpreted after GROUP BY or ORDER BY as a result
column number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it
for GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:

> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: [hidden email]
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>>
>> -----Ursprüngliche Nachricht-----
>> Von: sqlite-users
>> [mailto:[hidden email]] Im Auftrag von
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list <[hidden email]>
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>    Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Vladimir Vissoultchev
> By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY.

It's not standard for GROUP BY e.g. SQL Server does not support it (ORDER BY col indexes are fine there too)

At least sqlite does not support the abomination GROUP BY 1 DESC the way MySQL does.

cheers,
</wqw>

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Mark Brand
Sent: Wednesday, May 30, 2018 5:22 PM
To: [hidden email]
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant integer" is what gets interpreted as a result column number. What is an "output column identifier" then? Isn't it already covered by the broader category "any other expression"?

It's still a pretty astonishing language feature(!?) that an integer numeric-literal, which in every other column-like context represents its integer value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:

> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]] Im Auftrag von
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: [hidden email]
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>>
>> -----Ursprüngliche Nachricht-----
>> Von: sqlite-users
>> [mailto:[hidden email]] Im Auftrag von
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list <[hidden email]>
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>    Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Hick Gunter
In reply to this post by Mark Brand
Ponder the following select fragment

SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table

Then you can have

ORDER BY 1,2,3 -- integer output column numbers

which is equivalent to

ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other expression"

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Mark Brand
Sent: Mittwoch, 30. Mai 2018 16:22
To: [hidden email]
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant integer" is what gets interpreted as a result column number. What is an "output column identifier" then? Isn't it already covered by the broader category "any other expression"?

It's still a pretty astonishing language feature(!?) that an integer numeric-literal, which in every other column-like context represents its integer value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:

> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]] Im Auftrag von
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: [hidden email]
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>>
>> -----Ursprüngliche Nachricht-----
>> Von: sqlite-users
>> [mailto:[hidden email]] Im Auftrag von
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list <[hidden email]>
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>    Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Mark Brand
At first I  wasn't sure what you meant by "column identifier". It's what
the documentation calls "column-alias". But this also qualifies as an
"other expression" doesn't it? A column alias can appear pretty much
anywhere any expression can, except in the same column list where it is
defined.

Mark


On 31/05/18 18:27, Hick Gunter wrote:

> Ponder the following select fragment
>
> SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table
>
> Then you can have
>
> ORDER BY 1,2,3 -- integer output column numbers
>
> which is equivalent to
>
> ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other expression"
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Mark Brand
> Sent: Mittwoch, 30. Mai 2018 16:22
> To: [hidden email]
> Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks for the clarification.
>
>
>> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY.
> Just to make sure I'm not missing something subtle: I understand the "constant integer" is what gets interpreted as a result column number. What is an "output column identifier" then? Isn't it already covered by the broader category "any other expression"?
>
> It's still a pretty astonishing language feature(!?) that an integer numeric-literal, which in every other column-like context represents its integer value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this.
>
> By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY.
>
> Mark
>
> On 30/05/18 13:28, Hick Gunter wrote:
>> You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row.
>>
>> -----Ursprüngliche Nachricht-----
>> Von: sqlite-users
>> [mailto:[hidden email]] Im Auftrag von
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 12:11
>> An: [hidden email]
>> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>>
>> Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use.
>>
>> These, on the other hand, work as I would have expected:
>>
>> sqlite> select 0 group by cast (0 as int);
>> 0
>> sqlite> select 0 group by (select 0);
>> 0
>>
>> Mark
>>
>>
>> On 30/05/18 12:00, Hick Gunter wrote:
>>> Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1.
>>>
>>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: sqlite-users
>>> [mailto:[hidden email]] Im Auftrag von
>>> Mark Brand
>>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>>> An: SQLite mailing list <[hidden email]>
>>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>>
>>> Hi,
>>>
>>> Is there a good reason for this error:
>>>
>>> sqlite> SELECT  0 GROUP BY 0;
>>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>>> sqlite> SELECT 0 GROUP BY 1;
>>> 0
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> ___________________________________________
>>>     Gunter Hick | Software Engineer | Scientific Games International
>>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>>> 0430013 | (O) +43 1 80100 - 0
>>>
>>> May be privileged. May be confidential. Please delete if not the addressee.
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>    Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] unexpected error with "GROUP BY 0"

Shevek
SQL overall is phenomenally ambiguous about group-by:

1) select a0 + 1 as a0 from a group by a0

has a different behaviour in different SQL implementations, depending on
whether the group-by prefers the underlying column or the projection alias.

2) As you rightly point out, group by integer has a different behaviour
in different implementations, depending on how they range-check for "is
a projection reference" - is a valid integer, is out of range, ...

3) group by expression has several of the above ambiguities, when it's
legal.

4) Everything else is AFU'd too.

S.

On 05/31/2018 09:49 AM, Mark Brand wrote:

> At first I  wasn't sure what you meant by "column identifier". It's what
> the documentation calls "column-alias". But this also qualifies as an
> "other expression" doesn't it? A column alias can appear pretty much
> anywhere any expression can, except in the same column list where it is
> defined.
>
> Mark
>
>
> On 31/05/18 18:27, Hick Gunter wrote:
>> Ponder the following select fragment
>>
>> SELECT name, year * 100 + month as period, day + hour / 24.0 from
>> some_table
>>
>> Then you can have
>>
>> ORDER BY 1,2,3 -- integer output column numbers
>>
>> which is equivalent to
>>
>> ORDER BY name, period, day + hour / 24.0 -- output column identifier
>> x2 + "any other expression"
>>
>> -----Original Message-----
>> From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of
>> Mark Brand
>> Sent: Mittwoch, 30. Mai 2018 16:22
>> To: [hidden email]
>> Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>>
>> Thanks for the clarification.
>>
>>
>>> You have constant integers, output column identifiers and "any other
>>> expression" as terms for GROUP BY.
>> Just to make sure I'm not missing something subtle: I understand the
>> "constant integer" is what gets interpreted as a result column number.
>> What is an "output column identifier" then? Isn't it already covered
>> by the broader category "any other expression"?
>>
>> It's still a pretty astonishing language feature(!?) that an integer
>> numeric-literal, which in every other column-like context represents
>> its integer value, gets interpreted after GROUP BY or ORDER BY as a
>> result column number. Fortunately, SQLite isn't to blame for designing
>> this.
>>
>> By the way, this feature is documented for ORDER BY, but I don't see
>> it for GROUP BY.
>>
>> Mark
>>
>> On 30/05/18 13:28, Hick Gunter wrote:
>>> You have constant integers, output column identifiers and "any other
>>> expression" as terms for GROUP BY. If the expression evalutes to a
>>> constant value, you will have only one output row.
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: sqlite-users
>>> [mailto:[hidden email]] Im Auftrag von
>>> Mark Brand
>>> Gesendet: Mittwoch, 30. Mai 2018 12:11
>>> An: [hidden email]
>>> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>>>
>>> Thanks. I had forgotten that GROUP BY considers a literal integer in
>>> this context to be a column number, a feature I don't use.
>>>
>>> These, on the other hand, work as I would have expected:
>>>
>>> sqlite> select 0 group by cast (0 as int);
>>> 0
>>> sqlite> select 0 group by (select 0);
>>> 0
>>>
>>> Mark
>>>
>>>
>>> On 30/05/18 12:00, Hick Gunter wrote:
>>>> Yes. If the expression is a constant integer K, then it is
>>>> considered an alias for the K-th column of the result set. Columns
>>>> are ordered from left to right starting with 1.
>>>>
>>>> There is no 0-th column, so GROUP BY 0 is "out of range", just the
>>>> same as "SELECT 0 GROUP BY 31" would be.
>>>>
>>>> -----Ursprüngliche Nachricht-----
>>>> Von: sqlite-users
>>>> [mailto:[hidden email]] Im Auftrag von
>>>> Mark Brand
>>>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>>>> An: SQLite mailing list <[hidden email]>
>>>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>>>
>>>> Hi,
>>>>
>>>> Is there a good reason for this error:
>>>>
>>>> sqlite> SELECT  0 GROUP BY 0;
>>>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>>>> sqlite> SELECT 0 GROUP BY 1;
>>>> 0
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>>> ___________________________________________
>>>>     Gunter Hick | Software Engineer | Scientific Games International
>>>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>>>> 0430013 | (O) +43 1 80100 - 0
>>>>
>>>> May be privileged. May be confidential. Please delete if not the
>>>> addressee.
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> ___________________________________________
>>>    Gunter Hick | Software Engineer | Scientific Games International
>>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>>> 0430013 | (O) +43 1 80100 - 0
>>>
>>> May be privileged. May be confidential. Please delete if not the
>>> addressee.
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>   Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the
>> addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users