On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
