On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N <n...@riseup.net> 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