Re: [sqlite] SELECT ... GROUP BY: Bug or misunderstanding?

[Martin Engelschalk]

Hi Christoph,

the id column does not appear in an aggregate function and also not in 
group by.


Your statement uses 'GROUP BY Name' and so returns exactly one row per 
name. If there are several rows with the same name, the ID of your 
result is from one of these rows. The appropriate documentation is


"Each expression in the result-set is then evaluated once for each group 
of rows. If the expression is an aggregate expression, it is evaluated 
across all rows in the group. Otherwise, it is evaluated against a 
single arbitrarily chosen row from within the group. If there is more 
than one non-aggregate expression in the result-set, then all such 
expressions are evaluated for the same row. " from here 
http://www.sqlite.org/lang_select.html#resultset


The keyword here is "arbitrarily chosen".

So, you can not expect to get the same id every time even if you do not 
change the sqlite version.


Hope this helps
Martin

Am 22.08.2014 15:19, schrieb Christoph Wiedemann:

SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name ORDER BY Score


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[sqlite] SELECT ... GROUP BY: Bug or misunderstanding?

[Christoph Wiedemann]

Hello,
 
I'm using sqlite over the python sqlite3 module shipped with the standard 
distribution. Being not an SQL expert at all, I stumbled over an inconsistency 
with a SELECT  GROUP BY statement. The following python script might be 
used to reproduce:
 
<<<
import sqlite3
print("sqlite_version=",sqlite3.sqlite_version)
db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute("CREATE TABLE Test(Id INTEGER PRIMARY KEY, Name TEXT, Score INTEGER)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',100)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',99)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d1',98)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d2',101)")
c.execute("INSERT INTO Test(Name,Score) VALUES('d2',102)")
a = c.execute("SELECT * FROM Test").fetchall()
print(a)
a = c.execute("SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name 
ORDER BY Score").fetchall()
print(a)
>>>

Using the default sqlite.dll of the python 3.2.x distribution, the output is as 
follows:
 
sqlite_version= 3.7.4
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (5, 'd2', 101)]
^^^
Please note the '5' in the output of the select ... group by statement. 
Exchanging the sqlite.dll with a newer version, I get the following output:
 
sqlite_version= 3.8.6
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (4, 'd2', 101)]
    ^^^
Question: Did I encounter a bug in the sqlite version 3.7.4, or are both 
outputs correct and my understanding of the GROUP BY semantic is wrong?
 
Thanks in advance!
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users