Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

[Dominique Devienne]

On Thu, Jan 9, 2014 at 10:18 AM, Dominique Devienne wrote:

> On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy wrote:
>
>> On 01/09/2014 03:38 PM, Dominique Devienne wrote:
>>
>>> Someone will soon point out exactly what commit/bug was fixed in SQLite
>>> I'm sure.
>>>
>>
>> Probably this:
>>   http://www.sqlite.org/src/info/f2d175f975
>>
>> Fix first appeared in 3.8.0.
>>
>
> Thanks Dan. FWIW, the test cases added look more complex than the one from
> this report. And the comments I read do seem to indicate that the ability
> to use output column names ("as" aliases) in later clauses like group-by is
> an SQLite extension over SQL indeed. --DD
>

Why made me think. There's a page in the doc listing what SQL SQLite does
not support, but is there one about what "extensions" SQLite does support
in its SQL "dialect" which are not standard SQL? This is obviously one such
extension, and such a page (if it does not exist) would help write more
portable SQL in SQLite, if users could be made aware of these subtle
differences. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

[Dominique Devienne]

On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy  wrote:

> On 01/09/2014 03:38 PM, Dominique Devienne wrote:
>
>> Someone will soon point out exactly what commit/bug was fixed in SQLite
>> I'm sure.
>>
>
> Probably this:
>   http://www.sqlite.org/src/info/f2d175f975
>
> Fix first appeared in 3.8.0.


Thanks Dan. FWIW, the test cases added look more complex than the one from
this report. And the comments I read do seem to indicate that the ability
to use output column names ("as" aliases) in later clauses like group-by is
an SQLite extension over SQL indeed. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

[Dan Kennedy]

On 01/09/2014 03:38 PM, Dominique Devienne wrote:

On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N  wrote:


I am seeing a change in the results returned for a query in SQLIte 3.8.2
version. The query used to return expected results with 3.7.7 and when I
updated to 3.8.2, it changed the results.

Here is a test case:

 CREATE TABLE test(name text, value text);
 insert into test values ('first', 't');
 insert into test values ('second', 'T');

Now execute the following query in 3.7.7 version:
 sqlite> select lower(value) as value from test group by value;
 t


[DD] that's not what I'd expect. This looks like 'select lower(value) as
value from test group by lower(value)' to me. i.e. group first, then lower
the grouped values.



Now execute the same query in 3.8.2 which returns
 t
 t


[DD] This is what I expect from your query, so it's more a bug fix IMHO.
Someone will soon point out exactly what commit/bug was fixed in SQLite I'm
sure.


Probably this:

  http://www.sqlite.org/src/info/f2d175f975

Fix first appeared in 3.8.0.

Dan.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

[Dominique Devienne]

On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N  wrote:

> I am seeing a change in the results returned for a query in SQLIte 3.8.2
> version. The query used to return expected results with 3.7.7 and when I
> updated to 3.8.2, it changed the results.
>
> Here is a test case:
>
> CREATE TABLE test(name text, value text);
> insert into test values ('first', 't');
> insert into test values ('second', 'T');
>
> Now execute the following query in 3.7.7 version:
> sqlite> select lower(value) as value from test group by value;
> t
>

[DD] that's not what I'd expect. This looks like 'select lower(value) as
value from test group by lower(value)' to me. i.e. group first, then lower
the grouped values.


> Now execute the same query in 3.8.2 which returns
> t
> t
>

[DD] This is what I expect from your query, so it's more a bug fix IMHO.
Someone will soon point out exactly what commit/bug was fixed in SQLite I'm
sure.


> I can fix it by changing the query like to one the following:
> select lower(value) as value1 from test group by value1;
> select lower(value) as value from test group by lower(value);
>
> But I am not sure if this is expected. Is this a bug or should I change
> the query like the above?
>

[DD] Fix the query IMHO, but using the second one, as Oracle doesn't allow
using the select clause alias in the group by clause (see below), so this
might be a SQLite thing (or an Oracle thing, not sure. In any case, better
to pick the more portable alternative).


C:\Users\DDevienne>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 9 09:32:10 2014
... Connected to: ... Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - 64bit Production
SQL> create table test(name varchar2(64), value varchar2(64));
Table created.

SQL> insert into test values ('first', 't');
1 row created.

SQL> insert into test values ('second', 'T');
1 row created.

SQL> select lower(value) from test group by value;
LOWER(VALUE)

t
t

SQL> select lower(value) from test group by lower(value);
LOWER(VALUE)

t

SQL> select lower(value) as lvalue from test group by lvalue;
select lower(value) as lvalue from test group by lvalue
 *
ERROR at line 1:
ORA-00904: "LVALUE": invalid identifier
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users