Re: [sqlite] Bug report: ORDER BY ignored in presence of GROUP BY and index

[Richard Hipp]

On Sat, Apr 19, 2014 at 11:14 PM, foxlit  wrote:

> Hi,
>
> I recently noticed something similar to the following behaviour:
>
> sqlite> .version
> SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
> sqlite> CREATE TABLE t1 (x, y);
> sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0);
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y;
> 1|1
> 2|0
> sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic)
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y;
> 2|0
> 1|1
>
> The second result appears to be ignoring the ORDER BY clause. Is this a
> bug, or am I missing something obvious?
>

Bug.  I created a ticket here
http://www.sqlite.org/src/tktview/b75a9ca6b0499

The work-around is to add a "+" before one of the terms on the ORDER BY
clause.  Ex:

 SELECT x,y FROM t1 GROUP BY x,y ORDER BY x,+y;

The problem is caused by an optimization (
http://www.sqlite.org/src/artifact/269c3e31a4?ln=4722-4732) that has been
in the code since 2010-04-26 that omits the ORDER BY clause if there is an
identical GROUP BY clause, since GROUP BY is (or at least was) implemented
by sorting as if it were an ORDER BY.  This optimization worked fine until
the next generation query planner (
http://www.sqlite.org/queryplanner-ng.html) was cut over in 2013-06-26.
The NGQP introduced some new ways to handle GROUP BY which made that
optimization no longer valid in some circumstances - one of which you have
just found.  So, this is a case of two separate optimizations interfering
with one another.

Thanks for the bug report.

Oops.  Look like Dan and I entered duplicate tickets.  I'll cancel one of
them



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

[sqlite] Bug report: ORDER BY ignored in presence of GROUP BY and index

[foxlit]

Hi,

I recently noticed something similar to the following behaviour:

sqlite> .version
SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
sqlite> CREATE TABLE t1 (x, y);
sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0);
sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y;
1|1
2|0
sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic)
sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y;
2|0
1|1

The second result appears to be ignoring the ORDER BY clause. Is this a bug, or 
am I missing something obvious?


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

AW: [sqlite] bug in ORDER BY ?

[Christian Schwarz]

Does "select * from mactor order by id desc limit 1" and
"select * from mactor order by id limit 1" not work?

Greetings, Christian

Re: [sqlite] bug in ORDER BY ?

[Thomas Steffen]

On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

> EXPLAIN is your friend.  As can be seen by EXPLAINing each query (see below),
> there are fewer instructions involved in the one with the subquery, and no
> sorts or loops as are done in the initial method.

Well, not everybody is a specialist on SQLite bytecode, I guess. It
looks a lot like RISC code to me, which means that it is mostly
illegible :-)

Anyway, I have compared statements with identical functions, and my
result is rather different. I don't know about loops, and I can't do a
real performance test at the moment, but at least the bytecode is a
lot longer for the subquery (as I would expect). I did:

sqlite> CREATE TABLE test (id INTEGER, addr CHAR(10), rest CHAR(20));
sqlite> CREATE INDEX testindex ON test (addr,id);
sqlite> EXPLAIN SELECT rest FROM test WHERE id>100 AND addr='1234'
ORDER BY id LIMIT 1;
...
39
sqlite> EXPLAIN SELECT rest FROM test WHERE id=(SELECT min(id) FROM
test WHERE id>100 AND addr='1234') AND addr='1234';
...
72

So which one is more efficient? Adding LIMIT 1 to the second one adds
5 more bytecodes (weird?).

That having said SQLite seems to match the performance of MySQL pretty
well in this task. I am positively surprised.

Thomas

 (Note that I've changed the
> maximum value to what fits in a signed 32-bit field since I'm doing this with
> 2.8.16.  You could try a similar experiment with 3.0.x.  Actually, it looks
> like you don't even need the WHERE clause in the original query, and I've
> tested that modification at the end of the EXPLAINation below, as well.
>
> > What if id is not unique, and I may have rows with identical id?
>
> The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be
> possible.  If it is possible, then you'd have to decide what you wanted to do
> with multiple results.
>
> % sqlite :memory:
> SQLite version 2.8.16
> Enter ".help" for instructions
> sqlite> CREATE TABLE Mactor
>...> (
>...>   id INTEGER PRIMARY KEY,
>...>   name TEXT,
>...>   -- any other fields
>...>   comment TEXT
>...> );
> sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id 
> DESC LIMIT 1;
> addr|opcode|p1|p2|p3
> 0|ColumnName|0|0|id
> 1|ColumnName|1|0|name
> 2|ColumnName|2|1|comment
> 3|Integer|-1|0|
> 4|MemStore|0|1|
> 5|ColumnName|3|0|INTEGER
> 6|ColumnName|4|0|TEXT
> 7|ColumnName|5|0|TEXT
> 8|Integer|0|0|
> 9|OpenRead|0|3|Mactor
> 10|VerifyCookie|0|31|
> 11|Rewind|0|25|
> 12|Integer|2147483647|0|2147483647
> 13|MemStore|1|1|
> 14|Recno|0|0|
> 15|MemLoad|1|0|
> 16|Ge|0|25|
> 17|Recno|0|0|
> 18|Column|0|1|
> 19|Column|0|2|
> 20|SortMakeRec|3|0|
> 21|Recno|0|0|
> 22|SortMakeKey|1|0|-
> 23|SortPut|0|0|
> 24|Next|0|14|
> 25|Close|0|0|
> 26|Sort|0|0|
> 27|SortNext|0|32|
> 28|MemIncr|0|31|
> 29|SortCallback|3|0|
> 30|Goto|0|27|
> 31|Pop|1|0|
> 32|SortReset|0|0|
> 33|Halt|0|0|
> sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM 
> Mactor);
> addr|opcode|p1|p2|p3
> 0|VerifyCookie|0|31|
> 1|Integer|0|0|
> 2|OpenRead|1|3|Mactor
> 3|Last|1|0|
> 4|Recno|1|0|
> 5|MemStore|0|1|
> 6|Goto|0|7|
> 7|Close|1|0|
> 8|ColumnName|0|0|id
> 9|ColumnName|1|0|name
> 10|ColumnName|2|1|comment
> 11|ColumnName|3|0|INTEGER
> 12|ColumnName|4|0|TEXT
> 13|ColumnName|5|0|TEXT
> 14|Integer|0|0|
> 15|OpenRead|0|3|Mactor
> 16|MemLoad|0|0|
> 17|MustBeInt|1|23|
> 18|NotExists|0|23|
> 19|Recno|0|0|
> 20|Column|0|1|
> 21|Column|0|2|
> 22|Callback|3|0|
> 23|Close|0|0|
> 24|Halt|0|0|
> sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1;
> addr|opcode|p1|p2|p3
> 0|ColumnName|0|0|id
> 1|ColumnName|1|0|name
> 2|ColumnName|2|1|comment
> 3|Integer|-1|0|
> 4|MemStore|0|1|
> 5|ColumnName|3|0|INTEGER
> 6|ColumnName|4|0|TEXT
> 7|ColumnName|5|0|TEXT
> 8|Integer|0|0|
> 9|OpenRead|0|3|Mactor
> 10|VerifyCookie|0|31|
> 11|Rewind|0|20|
> 12|Recno|0|0|
> 13|Column|0|1|
> 14|Column|0|2|
> 15|SortMakeRec|3|0|
> 16|Recno|0|0|
> 17|SortMakeKey|1|0|-
> 18|SortPut|0|0|
> 19|Next|0|12|
> 20|Close|0|0|
> 21|Sort|0|0|
> 22|SortNext|0|27|
> 23|MemIncr|0|26|
> 24|SortCallback|3|0|
> 25|Goto|0|22|
> 26|Pop|1|0|
> 27|SortReset|0|0|
> 28|Halt|0|0|
> sqlite>
>

Re: [sqlite] bug in ORDER BY ?

[Derrell . Lipman]

Thomas Steffen <[EMAIL PROTECTED]> writes:

> On 4/14/05, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
>> How about these:
>> 
>>   SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
>>   SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);
>
> I am working on a similar problem at the moment, but unless I missed
> something, ORDER BY  id LIMIT 1 works fine for me. Is there any
> benefit of one formulation against the other? Is the nested SELECT
> less efficient?  Or are they identical in bytecode? 

EXPLAIN is your friend.  As can be seen by EXPLAINing each query (see below),
there are fewer instructions involved in the one with the subquery, and no
sorts or loops as are done in the initial method.  (Note that I've changed the
maximum value to what fits in a signed 32-bit field since I'm doing this with
2.8.16.  You could try a similar experiment with 3.0.x.  Actually, it looks
like you don't even need the WHERE clause in the original query, and I've
tested that modification at the end of the EXPLAINation below, as well.

> What if id is not unique, and I may have rows with identical id?

The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be
possible.  If it is possible, then you'd have to decide what you wanted to do
with multiple results.

% sqlite :memory:
SQLite version 2.8.16
Enter ".help" for instructions
sqlite> CREATE TABLE Mactor
   ...> (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT,
   ...>   -- any other fields
   ...>   comment TEXT
   ...> );
sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id DESC 
LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|25|
12|Integer|2147483647|0|2147483647
13|MemStore|1|1|
14|Recno|0|0|
15|MemLoad|1|0|
16|Ge|0|25|
17|Recno|0|0|
18|Column|0|1|
19|Column|0|2|
20|SortMakeRec|3|0|
21|Recno|0|0|
22|SortMakeKey|1|0|-
23|SortPut|0|0|
24|Next|0|14|
25|Close|0|0|
26|Sort|0|0|
27|SortNext|0|32|
28|MemIncr|0|31|
29|SortCallback|3|0|
30|Goto|0|27|
31|Pop|1|0|
32|SortReset|0|0|
33|Halt|0|0|
sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM 
Mactor);
addr|opcode|p1|p2|p3
0|VerifyCookie|0|31|
1|Integer|0|0|
2|OpenRead|1|3|Mactor
3|Last|1|0|
4|Recno|1|0|
5|MemStore|0|1|
6|Goto|0|7|
7|Close|1|0|
8|ColumnName|0|0|id
9|ColumnName|1|0|name
10|ColumnName|2|1|comment
11|ColumnName|3|0|INTEGER
12|ColumnName|4|0|TEXT
13|ColumnName|5|0|TEXT
14|Integer|0|0|
15|OpenRead|0|3|Mactor
16|MemLoad|0|0|
17|MustBeInt|1|23|
18|NotExists|0|23|
19|Recno|0|0|
20|Column|0|1|
21|Column|0|2|
22|Callback|3|0|
23|Close|0|0|
24|Halt|0|0|
sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|20|
12|Recno|0|0|
13|Column|0|1|
14|Column|0|2|
15|SortMakeRec|3|0|
16|Recno|0|0|
17|SortMakeKey|1|0|-
18|SortPut|0|0|
19|Next|0|12|
20|Close|0|0|
21|Sort|0|0|
22|SortNext|0|27|
23|MemIncr|0|26|
24|SortCallback|3|0|
25|Goto|0|22|
26|Pop|1|0|
27|SortReset|0|0|
28|Halt|0|0|
sqlite> 

Re: [sqlite] bug in ORDER BY ?

[Thomas Steffen]

On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> How about these:
> 
>   SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
>   SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);

I am working on a similar problem at the moment, but unless I missed
something, ORDER BY  id LIMIT 1 works fine for me. Is there any
benefit of one formulation against the other? Is the nested SELECT
less efficient? Or are they identical in bytecode? What if id is not
unique, and I may have rows with identical id?

And can this be generalised for other databases?

Yours,
Thomas

Re: [sqlite] bug in ORDER BY ?

[Derrell . Lipman]

"Miha Vrhovnik"<[EMAIL PROTECTED]> writes:

> SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;
>
> where 9223372036854775807 is Maximum value of signed Int64.
>
> P.S. If anybody has better Idea of how to get the last/first row (the one
> with highest/lowest ID) then comments are welcome.
>

How about these:

  SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
  SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);

Derrell

Re: [sqlite] bug in ORDER BY ?

[Xavier Aguila]

try
SELECT * FROM Mactor WHERE id=(Select max(id) from Mactor); //with this you get 
last id.
SELECT * FROM Mactor WHERE id=(Select min(id) from Mactor); //with this you get 
first id.
Xavier
Miha Vrhovnik wrote:
Hi,
sqlite dll is 3.2.1
I have the folowing query:
SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;
where 9223372036854775807 is Maximum value of signed Int64.
Table is defined as:
CREATE TABLE Mactor (
id INTEGER PRIMARY KEY, 
name TEXT, 
birthName TEXT, 
birthday DATE, 
gender INTEGER, 
idCountry INTEGER, 
idProvince INTEGER, 
imdb TEXT, 
url TEXT, 
otherWork TEXT, 
biographiy TEXT, 
comment TEXT, 
pictures TEXT, 
custom TEXT);

There are two records in table one with id = 1 and other with id = 2
The query retuns an empty "dataset" BUT it works as soon as I remove ORDER BY 
clause.
P.S. If anybody has better Idea of how to get the last/first row (the one with 
highest/lowest ID) then comments are welcome.
Regards,
Miha 

+*+
It's time to get rid of your current e-mail client ...
... and start using si.Mail.
It's small & free. ( http://simail.sourceforge.net/ )
+*+
 

[sqlite] bug in ORDER BY ?

[Miha Vrhovnik]

Hi,

sqlite dll is 3.2.1

I have the folowing query:

SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;

where 9223372036854775807 is Maximum value of signed Int64.

Table is defined as:

CREATE TABLE Mactor (
id INTEGER PRIMARY KEY,
name TEXT,
birthName TEXT,
birthday DATE,
gender INTEGER,
idCountry INTEGER,
idProvince INTEGER,
imdb TEXT,
url TEXT,
otherWork TEXT,
biographiy TEXT,
comment TEXT,
pictures TEXT,
custom TEXT);

There are two records in table one with id = 1 and other with id = 2

The query retuns an empty "dataset" BUT it works as soon as I remove ORDER BY 
clause.

P.S. If anybody has better Idea of how to get the last/first row (the one with 
highest/lowest ID) then comments are welcome.

Regards,
Miha

+*+
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )
+*+