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
