Re: [sqlite] ORDER BY is ignored during INSERT INTO

[Simon Slavin]

On 25 Aug 2019, at 10:09pm, André Borchert <0xa...@gmail.com> wrote:

> I try to copy one table into a second identical one. Once the second table is 
> created I want to move the content over sorted by ASC.

It's worth noting here that the rows of a table do not have any order in SQL.  
A table is a collection of rows, like marbles in a bag.  It is quite correct 
for any SQL engine to do this:

SELECT name,phone FROM contacts; --> all rows appear
SELECT name,phone FROM contacts; --> same rows in different order

Unless you specify a sort order using ORDER BY you cannot complain about the 
order SQL returns your rows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY is ignored during INSERT INTO

[Keith Medcalf]

Which version of SQLite3 are you using?  Tip of trunk seems to work correctly 
...

SQLite version 3.30.0 2019-08-24 20:18:04
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> create table y(y);
sqlite> insert into x values (3),(2),(4),(1);
sqlite> insert into y select x from x order by x asc;
sqlite> select * from x;
3
2
4
1
sqlite> select * from y;
1
2
3
4

Can you post the output of EXPLAIN INSERT INTO CompanyDetails2 SELECT * FROM 
CompanyDetails WHERE CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of André Borchert
>Sent: Sunday, 25 August, 2019 15:10
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] ORDER BY is ignored during INSERT INTO
>
>Hello,
>
>I try to copy one table into a second identical one. Once the second
>table
>is created I want to move the content over sorted by ASC.
>
>The issue is that the ORDER BY statement gets ignored during a INSERT
>INTO:
>
>INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE
>CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC
>
>When the SELECT query is executed alone, the content is sorted fine
>as
>expected.
>
>Andre
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

[Dominique Devienne]

On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp  wrote:

> On 6/28/18, Dominique Devienne  wrote:
> > From reading this list, I've learned that for an index to have a change
> to
> > be used to consume an order by, the collation of the query and the index
> > must match.
> >
> > But in many instances, that index is one from a virtual table we
> implement.
> > So is there a way to tell SQLite that vindex is of a given custom
> collation,
> > to open the possibility of the index being used?
>
> The only way to avoid sorting the output of a virtual table is for the
> xBestIndex routine to set the sqlite3_index_info.orderByConsumed boolean.
>

My point is more than when doing so, i.e. setting  orderByConsumed to
1/true,
there's no way that I can see for the vtable to know the collation used by
the query,
to validate whether it matches the vindex's own ordering. Or am I missing
something?

sqlite3_vtab_collation that Gunther pointed me to, works for  aConstraint[]
only,
according to the doc, so there's no way to know the aOrderBy[]
collation(s). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

[Richard Hipp]

On 6/28/18, Dominique Devienne  wrote:
> From reading this list, I've learned that for an index to have a change to
> be used to consume an order by, the collation of the query and the index
> must match.
>
> But in many instances, that index is one from a virtual table we implement.
> So is there a way to tell SQLite that vindex is of a given custom collation,
> to open the possibility of the index being used?

The only way to avoid sorting the output of a virtual table is for the
xBestIndex routine to set the sqlite3_index_info.orderByConsumed
boolean.

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

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

[Dominique Devienne]

On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne 
wrote:

> So is there a way to tell SQLite that vindex is of a given custom
> collation,
> to open the possibility of the index being used?
>

Note that there's no mention at all of "collation" or "collate" in
https://www.sqlite.org/vtab.html
so this might once again be one of these corners of SQLite vtables where
functionality in not
on-par with "real" tables and indexes. I hope I'm wrong though, and hope to
hear about it here. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

[Keith Medcalf]

No.  Column name and table referents (identifiers) must be specified explicitly 
(as part of the command) and MUST NOT be bound parameters.  You are asking to 
sort by the value 1 for all rows, which means that the output is in "visitation 
order" since the ORDER BY value is the same for all rows ...

That is to say that you cannot use a bound parameter (? for example) it the 
locations that have a ? in the following query, and have that bound parameter 
refer to an index name, column name, or table name (or any defined identifier 
whatsoever) -- it will only refer to the "value" given the bound parameter or 
result in a syntax error if a "value" is not permitted at that part of the 
syntax:

SELECT ? from ? INDEXED BY ? WHERE ? = 17 GROUP BY ? ORDER BY ?;

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Danny Milosavljevic
>Sent: Monday, 29 January, 2018 12:14
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird
>things
>
>Hi,
>
>I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?"
>and
>then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3).
>
>Expected result: Orders result by column "a", in ascending order.
>Observed result: Orders in some strange order.
>
>I also tried sqlite3_bind_int64, didn't change the result.
>
>Should this use case work?
>
>To reproduce:
>
>OK case (prints 2 and then 5):
>
>#include 
>#include 
>#include 
>
>int main() {
>sqlite3* db;
>sqlite3_stmt* stmt;
>printf("%s\n", sqlite3_libversion());
>if (sqlite3_open(":memory:", ) != SQLITE_OK ||
>sqlite3_exec(db, "CREATE TABLE t(a int);"
> "INSERT INTO t(a) VALUES (5);"
> "INSERT INTO t(a) VALUES (2);", NULL, 0,
>NULL) != SQLITE_OK ||
>sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1", -1,
>, 0) != SQLITE_OK ||
>sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>if (sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>return 0;
>}
>
>Not OK case (prints 5 and then 2):
>
>#include 
>#include 
>#include 
>
>int main() {
>sqlite3* db;
>sqlite3_stmt* stmt;
>printf("%s\n", sqlite3_libversion());
>if (sqlite3_open(":memory:", ) != SQLITE_OK ||
>sqlite3_exec(db, "CREATE TABLE t(a int);"
> "INSERT INTO t(a) VALUES (5);"
> "INSERT INTO t(a) VALUES (2);", NULL, 0,
>NULL) != SQLITE_OK ||
>sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY ?", -1,
>, 0) != SQLITE_OK ||
>sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
>sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>if (sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>return 0;
>}
>
>Also OK but not that useful:
>
>#include 
>#include 
>#include 
>
>int main() {
>sqlite3* db;
>sqlite3_stmt* stmt;
>printf("%s\n", sqlite3_libversion());
>if (sqlite3_open(":memory:", ) != SQLITE_OK ||
>sqlite3_exec(db, "CREATE TABLE t(a int);"
> "INSERT INTO t(a) VALUES (5);"
> "INSERT INTO t(a) VALUES (2);", NULL, 0,
>NULL) != SQLITE_OK ||
>sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1, ?", -
>1, , 0) != SQLITE_OK ||
>sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
>sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>if (sqlite3_step(stmt) != SQLITE_ROW)
>abort();
>printf("%d\n", sqlite3_column_int(stmt, 0));
>return 0;
>}
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

[Jens Alfke]

> On Jan 29, 2018, at 11:13 AM, Danny Milosavljevic  
> wrote:
> 
> Should this use case work?

Nope. The ORDER BY clause can affect the query plan and the generated bytecode, 
so it's not something you can change in a binding. You have to compile a new 
statement with a different ORDER BY clause.

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

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

[Clemens Ladisch]

Danny Milosavljevic wrote:
> I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and
> then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3).
>
> Expected result: Orders result by column "a", in ascending order.
> Observed result: Orders in some strange order.

Ordering by column number works only for a plain integer.  A parameter
might not be an integer, so it is parsed differently, even if the
actual value later ends up being an integer.

So the effect is that you are sorting by a constant value: it's the
same for all rows, so the order of the output rows is undefined.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order By gives different result

[Dan Kennedy]

On 08/21/2017 11:51 PM, Stephen Chrzanowski wrote:

I think I may have found a query issue.  I haven't checked the SQLite docs
to see if this is something that is specified as an order of operations, or
if this is a check that has been overlooked.


I think using a column alias in a complex expression within an ORDER BY 
is non-standard SQL. An extension that was accidentally implemented and 
is now maintained for backward compatibility. In SQL Server you can use 
a column alias as a standalone ORDER BY term but not as part of a 
complex expression.


More here:

  http://sqlite.org/src/info/f617ea3125e9c

https://stackoverflow.com/questions/25763920/why-cant-i-refer-to-a-column-alias-in-the-order-by-using-case

Dan.








The database SQL code is here: https://pastebin.com/raw/FukX4qEB

select [main].[StateLabels].[LabelText] as
[FromState],[StateLabels1].[LabelText] as [ToState]
from [main].[StateLabels]
   inner join [main].[StateTables] on [main].[StateLabels].[LabelID] =
[main].[StateTables].[FromState]
   inner join [main].[StateLabels] [StateLabels1] on
[StateLabels1].[LabelID] = [main].[StateTables].[ToState]
order by [FromState],[ToState]

I get an ordered list that I'd expect.  However, if I change the order by
portion to

order by upper([FromState]), upper([ToState])

I get an undefined order (Actually, it looks like it is ordering based on
the StateLabels.LabelID -- And this is the clue)

But then, if I go and change the order by to look like:

order by upper([main].[StateLabels].[LabelText]),
upper([StateLabels1].[LabelText])

I get the original order mentioned.

It seems like the query analyzer is picking a real field name versus the
one I defined with "As".  My bad design, sure, I'll take credit for that,
but FWIW, I would have expected an error to be thrown because I've got two
types of fields named FromState and ToState.  One being I've defined by
using "As" and the other by an actual field name in StateTables.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

All I/O is done via page sized blocks.  So the minimum amount of data to be
fetched will always be a page.  The bigger issue is, as you said, when you
need to follow a chain of pages to get a small value at the end.

 -j

On Thu, Oct 6, 2016 at 9:53 AM, Paul Sanderson  wrote:

> > Long columns, especially TEXT or BLOBs which may have lots of data in,
> should go at the end.  Because you don't want SQLite to have to fetch all
> that data from storage just to get at the column after it.
>
> To be pedantic SQLite does not need to "fetch" all of the data from
> strorage before a needed column, it just needs to be able to skip it -
> unless the data oveflows in to one or more overflow pages then it will
> need to fetch each page until it reaches the one with the data in it.
> If the complete row is held in one page and your query just needs the
> last column - SQLite just needs to know the size of all of the data
> that preceedes the column you want. There is still the overhead of
> decoding every serial type before the column you require.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
>
Sorta, kinda, but not really.  SQLite does not use a traditional data
compression algorithm in storing row data, but it does "pack" rows into a
compact format (including variable size integers).  As such, a row's worth
of data, as stored in the raw database, acts very similar to a compressed
block of data... you have to read it from the start and can't directly jump
to a field in a middle of it.

This is the issue with column ordering; the data engine will only read and
unpack the columns it needs, but it has to read and unpack the columns in
the order they're defined until it gets all the columns it needs.  This
makes it generally better to put more frequently accessed and/or smaller
values at the start of a row.

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

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

> Long columns, especially TEXT or BLOBs which may have lots of data in, should 
> go at the end.  Because you don't want SQLite to have to fetch all that data 
> from storage just to get at the column after it.


To be pedantic SQLite does not need to "fetch" all of the data from
strorage before a needed column, it just needs to be able to skip it -
unless the data oveflows in to one or more overflow pages then it will
need to fetch each page until it reaches the one with the data in it.
If the complete row is held in one page and your query just needs the
last column - SQLite just needs to know the size of all of the data
that preceedes the column you want. There is still the overhead of
decoding every serial type before the column you require.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

ok

On 6 Oct 2016 8:08 p.m., "Simon Slavin"  wrote:

>
> On 6 Oct 2016, at 3:37pm, Krishna Shukla 
> wrote:
>
> > Help how can i import exel file in sqlite and get result in c# desktop
> > application ...?
>
> Please start a new thread for this question.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 2:46pm, Jeff Archer  wrote:

> Are there any performance or other considerations of the order of the
> fields for an insert?

No.  Order of columns in the CREATE TABLE command matters.  Order they're named 
in operations after that doesn't.

When SQLite needs to fetch values from a table row, it has to start reading the 
row at the first column mentioned in CREATE TABLE, then go through it reading 
data until it has reached the last column it needs.  Because of this it's best 
to put short, frequently-needed columns first in your CREATE TABLE command.

Long columns, especially TEXT or BLOBs which may have lots of data in, should 
go at the end.  Because you don't want SQLite to have to fetch all that data 
from storage just to get at the column after it.

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

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 3:37pm, Krishna Shukla  wrote:

> Help how can i import exel file in sqlite and get result in c# desktop
> application ...?

Please start a new thread for this question.

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

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

Help how can i import exel file in sqlite and get result in c# desktop
application ...?

On 6 Oct 2016 7:55 p.m., "Hick Gunter"  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a
> general answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the
> fields for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> ​mytable(
> wid1,cnt,
> ​dat,​
> wid3,wid2) VALUES (?,?,?,?)
> ​ - VS - ​
>
> INSERT INTO
> ​mytable(
> wid1,wid2,wid3,cnt
> ​,dat​
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> ​mytable
> (
> ​id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> ​wid1
>  INTEGER REFERENCES
> ​othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid2
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid3
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​cnt
>  INTEGER DEFAULT
> ​1
> ,dat TEXT
> )
>
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

SQLite does not use any compression when storing data.

Occasionally rows have so much data that they overflow to an additonal
page(s) so the advice about defining tables so that blobs are at the
end of the definition is good - also columns that store long strings
might be better at the end of a table definition to avoid the same
sort of overflow.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 6 October 2016 at 15:25, Hick Gunter  wrote:
> SQLite compresses rows before storing and decompresses rows before returning 
> fields. BLOB fields are the most time consuming to process and so should be 
> placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
> should be placed at the front of the row. This will help most if your select 
> field list is limited to the fields you actually need instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some 
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a general 
> answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the fields 
> for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> mytable(
> wid1,cnt,
> dat,
> wid3,wid2) VALUES (?,?,?,?)
> - VS -
>
> INSERT INTO
> mytable(
> wid1,wid2,wid3,cnt
> ,dat
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> mytable
> (
> id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> wid1
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid2
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid3
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> cnt
>  INTEGER DEFAULT
> 1
> ,dat TEXT
> )
>
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Hick Gunter]

SQLite compresses rows before storing and decompresses rows before returning 
fields. BLOB fields are the most time consuming to process and so should be 
placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
should be placed at the front of the row. This will help most if your select 
field list is limited to the fields you actually need instead of "*".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jeff Archer
Gesendet: Donnerstag, 06. Oktober 2016 15:46
An: SQLite mailing list 
Betreff: [sqlite] Order of fields for insert

Just a quick question.  I am actually deciding if I need to do some performance 
testing of this but thought I might gain some quick insight.
My specific insert and table are below but really I am looking for a general 
answer to the question not just this specific case.

Are there any performance or other considerations of the order of the fields 
for an insert?
Are the following equivalent?  regardless of number of values inserting?
 regardless of size of the data being inserted?

INSERT INTO
​mytable(
wid1,cnt,
​dat,​
wid3,wid2) VALUES (?,?,?,?)
​ - VS - ​

INSERT INTO
​mytable(
wid1,wid2,wid3,cnt
​,dat​
) VALUES (?,?,?,?)


CREATE TABLE
​mytable
(
​id
 INTEGER PRIMARY KEY AUTOINCREMENT"
,
​wid1
 INTEGER REFERENCES
​othertable
(
​id
) ON DELETE CASCADE
,
​wid2
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​wid3
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​cnt
 INTEGER DEFAULT
​1
,dat TEXT
)

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Order of fields for insert

[Jeff Archer]

Sorry, that was just mistake in reducing the code for the email, please
ignore.

What do you mean "what matters is order of columns in table"?  or was that
just referring to the typo?


Jeff Archer
jeffarch...@gmail.com 


On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch  wrote:

> Jeff Archer wrote:
> > Are there any performance or other considerations of the order of the
> > fields for an insert?
>
> No; what matters is the order of columns in the table.
>
> > INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
> ​> - VS - ​
> > INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)
>
> Both statements will result in exactly the same error.  ;-)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Clemens Ladisch]

Jeff Archer wrote:
> Are there any performance or other considerations of the order of the
> fields for an insert?

No; what matters is the order of columns in the table.

> INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
​> - VS - ​
> INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)

Both statements will result in exactly the same error.  ;-)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of applying subquery in SELECT statement

[Keith Medcalf]

> On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf 
> wrote:
> 
> >
> > You have all ill-phrased correlated subquery.  Only by putting c.id in
> > the select list and adding c.id to the group go do you have a properly
> > phrased correlated subquery.
> >
> > However,
> >
> > The projection (join) will be performed first using whatever order and
> > indexes the optimizer decides to use to do that.  Whenever it is going
> to
> > "pop out" a result the subquery will be executed to provide that data.
> > That means that if there are multiple values of c.id to go with a single
> > value of a.id, you will get a result for "one of" those c.id values.
> > Which one is an implementation detail but is entirely deterministic.
> >
> >
> saying [some] 'one of' mean it isn't deterministic .   As data changes,
> id's get added, analyzes and optimizations are performed... the results
> may
> change with time.  If you're collapsing a whole branch of a tree into a
> single line that has one of the values from somewhere on the branch, you
> might want to think again about what you're doing.  as long as the dataset
> is exactly the same or is modified in ways that don't break the index
> lookup ordering you'll be 'stable'
> 
> I dunno it's not deterministic for all time

Of course it is.  As long as the inputs are the same, the output will be the 
same.  There is no randomness involved.  If you change an input (data in the 
database, statistics, optimizer, indices, etc) then the output may be change -- 
but it is still determined by the inputs and only the inputs.  However, for any 
given state of the inputs it is possible to determine the output, and if the 
inputs are held constant, the output will remain constant.  Hence it is indeed 
deterministic.

However, like celestial mechanics, you may not know all the inputs and their 
interactions to know if one of them has changed or not.  That does not make the 
output non-deterministic.  It just makes it complicated.





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

Re: [sqlite] Order of applying subquery in SELECT statement

[Simon Slavin]

On 21 Jun 2016, at 1:31am, J Decker  wrote:

> as long as the dataset
> is exactly the same or is modified in ways that don't break the index
> lookup ordering you'll be 'stable'

Or you upgrade to a new version of SQLite which optimizes the query differently.

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

Re: [sqlite] Order of applying subquery in SELECT statement

[J Decker]

On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf  wrote:

>
> You have all ill-phrased correlated subquery.  Only by putting c.id in
> the select list and adding c.id to the group go do you have a properly
> phrased correlated subquery.
>
> However,
>
> The projection (join) will be performed first using whatever order and
> indexes the optimizer decides to use to do that.  Whenever it is going to
> "pop out" a result the subquery will be executed to provide that data.
> That means that if there are multiple values of c.id to go with a single
> value of a.id, you will get a result for "one of" those c.id values.
> Which one is an implementation detail but is entirely deterministic.
>
>
saying [some] 'one of' mean it isn't deterministic .   As data changes,
id's get added, analyzes and optimizations are performed... the results may
change with time.  If you're collapsing a whole branch of a tree into a
single line that has one of the values from somewhere on the branch, you
might want to think again about what you're doing.  as long as the dataset
is exactly the same or is modified in ways that don't break the index
lookup ordering you'll be 'stable'

I dunno it's not deterministic for all time





> > -Original Message-
> > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org] On Behalf Of Harmen de Jong - CoachR
> > Group B.V.
> > Sent: Monday, 20 June, 2016 09:37
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Order of applying subquery in SELECT statement
> >
> > I have a query with a subquery (using an aggregate function) in the
> SELECT
> > statement. Will this subquery be executed before or after the GROUP BY?
> > For example:
> > SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as
> maxorderno
> > FROM a INNER JOIN c on c.a_id=a.id
> > GROUP BY a.id
> >
> > Best regards,
> > Harmen
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of applying subquery in SELECT statement

[Keith Medcalf]

You have all ill-phrased correlated subquery.  Only by putting c.id in the 
select list and adding c.id to the group go do you have a properly phrased 
correlated subquery.

However,

The projection (join) will be performed first using whatever order and indexes 
the optimizer decides to use to do that.  Whenever it is going to "pop out" a 
result the subquery will be executed to provide that data.  That means that if 
there are multiple values of c.id to go with a single value of a.id, you will 
get a result for "one of" those c.id values.  Which one is an implementation 
detail but is entirely deterministic.

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Harmen de Jong - CoachR
> Group B.V.
> Sent: Monday, 20 June, 2016 09:37
> To: General Discussion of SQLite Database
> Subject: [sqlite] Order of applying subquery in SELECT statement
> 
> I have a query with a subquery (using an aggregate function) in the SELECT
> statement. Will this subquery be executed before or after the GROUP BY?
> For example:
> SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
> FROM a INNER JOIN c on c.a_id=a.id
> GROUP BY a.id
> 
> Best regards,
> Harmen
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Order of applying subquery in SELECT statement

[Igor Tandetnik]

On 6/20/2016 3:32 PM, R Smith wrote:

SELECT a.id, MAX(b.orderno) as maxorderno
  FROM a
  INNER JOIN c on c.a_id = a.id
  INNER JOIN b ON b.c_id = c.id
GROUP BY a.id;


  --  | maxorder-   --- This is what I think you might
actually want,
  --  id  | no  --- it precludes NULLs and is sure to
pick the
  --  | -   --- correct max when joined to b too.
  --   1  | ORD005
  --   2  | ORD009


And if you do need NULLs, then just change it to "LEFT JOIN b".
--
Igor Tandetnik

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

Re: [sqlite] Order of applying subquery in SELECT statement

[R Smith]

On 2016/06/20 5:37 PM, Harmen de Jong - CoachR Group B.V. wrote:

I have a query with a subquery (using an aggregate function) in the SELECT 
statement. Will this subquery be executed before or after the GROUP BY?
For example:
SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
FROM a INNER JOIN c on c.a_id=a.id
GROUP BY a.id


This is a tricky one to answer due to a peculiarity in SQLite in that it 
allows you to not put all aggregates in an aggregated query (GROUP BY) 
in aggregate functions. The SQL standard prescribes different and you 
will find some other DB engines adhere to the rule where every 
field/value NOT explicitly in the GROUP BY clause MUST be contained in 
its own aggregate function.


The short version is: For your query to be correct semantically and in 
result, it has to specify both MAX() for the inner query and the 
maxorderno resulting value, or join the b table too.


The long version with example data and queries:

In this next script, 3 tables are made with schemata that will resemble 
in principle what you might typically have in your tables (or at least 
what would make a query like that a sensible endeavour).
Note that only the very last 2 queries are fully correct versions of 
your query, even though some others yield the correct results, some of 
them have results that show why it is dangerous to not follow the 
every-value-in-its-own-aggregate rule. I hope it's self-explanatory, but 
please ask if anything is unclear or you have any other questions.



  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 10 Parameter Count: 0
  -- 2016-06-20 21:08:42.768  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE a(
  id INTEGER PRIMARY KEY,
  supplier TEXT
);

CREATE TABLE b(
  id INTEGER PRIMARY KEY,
  c_id INT,
  orderno TEXT
);

CREATE TABLE c(
  id INTEGER PRIMARY KEY,
  a_id INT,
  payref TEXT
);

INSERT INTO a VALUES
(1, 'Wallcart'),
(2, 'Pro-Shop'),
(3, 'Pizza-for-All');

INSERT INTO b VALUES
(1, 1, 'ORD001'), (2, 1, 'ORD002'), (3, 1, 'ORD003'),
(4, 2, 'ORD004'), (5, 2, 'ORD005'),
(6, 3, 'ORD006'), (7, 3, 'ORD007'), (8, 3, 'ORD008'),
(9, 4, 'ORD009');

INSERT INTO c VALUES
(1, 1, 'P001'), (2, 1, 'P002'),
(3, 2, 'P003'), (4, 2, 'P004'),
(5, 3, 'P005');

SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
  FROM a
  INNER JOIN c on c.a_id = a.id
 GROUP BY a.id;


  --  | maxorder-   --- Yields correct results, but unsafely.
  --  id  | no
  --  | -
  --   1  | ORD005
  --   2  | ORD009
  --   3  | NULL

SELECT a.id, MAX((SELECT orderno FROM b WHERE b.c_id=c.id)) as maxorderno
  FROM a
  INNER JOIN c on c.a_id = a.id
 GROUP BY a.id;


  --  | maxorder-  --- These results are wrong because MAX 
is operating
  --  id  | no  --- only on one inner set which 
happened to be picked

  --  | -   --- last - a Random function at best.
  --   1  | ORD004
  --   2  | ORD009
  --   3  | NULL

SELECT a.id, (SELECT MAX(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
  FROM a
  INNER JOIN c on c.a_id = a.id
 GROUP BY a.id, maxorderno;


  -- | maxorder-   --- By adding maxorderno tot he group, this shows
  --  id | no  --- the Query planner's thinking
  -- --- | -
  --  1  | ORD003
  --  1  | ORD005
  --  2  | ORD008
  --  2  | ORD009
  --  3  | NULL

SELECT a.id, MAX((SELECT MAX(orderno) FROM b WHERE b.c_id=c.id)) as 
maxorderno

  FROM a
  INNER JOIN c on c.a_id = a.id
 GROUP BY a.id;


  --  | maxorder-   --- This is the only fully correct 
version of your

  --  id  | no  --- original query
  --  | -
  --   1  | ORD005
  --   2  | ORD009
  --   3  | NULL


SELECT a.id, MAX(b.orderno) as maxorderno
  FROM a
  INNER JOIN c on c.a_id = a.id
  INNER JOIN b ON b.c_id = c.id
GROUP BY a.id;


  --  | maxorder-   --- This is what I think you might 
actually want,
  --  id  | no  --- it precludes NULLs and is sure to 
pick the

  --  | -   --- correct max when joined to b too.
  --   1  | ORD005
  --   2  | ORD009


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

Re: [sqlite] Order of applying subquery in SELECT statement

[Igor Tandetnik]

On 6/20/2016 11:37 AM, Harmen de Jong - CoachR Group B.V. wrote:

I have a query with a subquery (using an aggregate function) in the SELECT 
statement. Will this subquery be executed before or after the GROUP BY?
For example:
SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
FROM a INNER JOIN c on c.a_id=a.id
GROUP BY a.id


Effectively, after: the projection in SELECT clause applies to the whole 
group. If there are multiple rows in c with the same c.a_id, then one of 
them will be picked in an unpredictable fashion to run the subuqery. You 
won't, in general, get max(b.orderno) across the whole group, only 
across some random item in it.


If you want max(orderno) across the group, just join with b:

SELECT a.id, max(b.orderno) as maxorderno
FROM a INNER JOIN c on c.a_id=a.id JOIN b ON b.c_id=c.id
GROUP BY a.id

--
Igor Tandetnik

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

Re: [sqlite] Order of applying subquery in SELECT statement

[J Decker]

On Mon, Jun 20, 2016 at 8:37 AM, Harmen de Jong - CoachR Group B.V. <
har...@coachr.com> wrote:

> I have a query with a subquery (using an aggregate function) in the SELECT
> statement. Will this subquery be executed before or after the GROUP BY?
> For example:
> SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
> FROM a INNER JOIN c on c.a_id=a.id
> GROUP BY a.id
>
>
that parens would suggest that the subs-slect is executed first. before
anything else...
but the reamining select also happens before the group by; it would never
be AFTER the group by, unless the group by was on the subselect, then it
would run between some of the select.s


Best regards,
> Harmen
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY and LIMIT regression

[Angelo Mottola]

> On 1/19/15, Angelo Mottola  converge.it> 
> wrote:
>> Hello,
>>
>> I have a regression to report, that seems to have been introduced between
>> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
>> and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
>> 3.8.8 but eventually it wasn’t).
>>
>
> Thank you for reporting the problem.
>
> We always fix every problem that we are aware of in SQLite.  But this
> problem had not been previously reported to us, and did not occur in
> any of the 168 million test cases that we ran prior to releasing
> SQLite 3.8.8, so it didn't get fixed.
>
> We are running another release cycle now.  SQLite version 3.8.8.1
> should be out sometime tomorrow, assuming everything goes well.


Woah, no matter how many test cases you may have, bugs are always around the 
corner, aren’t them? :-)

Anyway, thanks a lot for your quick reply and fix, I really appreciate the 
amount of work and love you all put in SQLite.
Looking forward to 3.8.8.1...


--
Ing. Angelo Mottola
Software architect
Converge SpA
a.mott...@converge.it



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

Re: [sqlite] ORDER BY and LIMIT regression

[Rich Shepard]

On Mon, 19 Jan 2015, Richard Hipp wrote:


Thank you for reporting the problem.


  Certainly!


We always fix every problem that we are aware of in SQLite.  But this
problem had not been previously reported to us, and did not occur in any
of the 168 million test cases that we ran prior to releasing SQLite 3.8.8,
so it didn't get fixed.


  And while I've not upgraded that many times over the years, it's never
before been an issue. I've no idea how the timestamps could get so
different, but it's not likely a coding error.

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

Re: [sqlite] ORDER BY and LIMIT regression

[Richard Hipp]

On 1/19/15, Angelo Mottola  wrote:
> Hello,
>
> I have a regression to report, that seems to have been introduced between
> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
> and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
> 3.8.8 but eventually it wasn’t).
>

Thank you for reporting the problem.

We always fix every problem that we are aware of in SQLite.  But this
problem had not been previously reported to us, and did not occur in
any of the 168 million test cases that we ran prior to releasing
SQLite 3.8.8, so it didn't get fixed.

We are running another release cycle now.  SQLite version 3.8.8.1
should be out sometime tomorrow, assuming everything goes well.

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

Re: [sqlite] ORDER BY and LIMIT regression

[Richard Hipp]

On 1/19/15, Simon Slavin  wrote:
>
> On 19 Jan 2015, at 3:10pm, Richard Hipp  wrote:
>
>> It is a very
>> complex problem.  In particular, the sample query works fine as long
>> as the number of columns in the result set is not exactly 60.  Adding
>> or removing a single column of result gives the correct answer.
>
> I would love to know the cause of that.
>

So would we :-\   Will probably figure it out soon

The ticket is https://www.sqlite.org/src/tktview/f97c4637102a3


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

Re: [sqlite] ORDER BY and LIMIT regression

[Simon Slavin]

On 19 Jan 2015, at 3:10pm, Richard Hipp  wrote:

> It is a very
> complex problem.  In particular, the sample query works fine as long
> as the number of columns in the result set is not exactly 60.  Adding
> or removing a single column of result gives the correct answer.

I would love to know the cause of that.

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

Re: [sqlite] ORDER BY and LIMIT regression

[Richard Hipp]

On 1/19/15, RSmith  wrote:
> Might this not be a "reverse_unordered_selects" pragma or compile option
> going wrong, or at least the code making it work getting
> somehow hooked in the new versions for this query?
>

It looks like a bug.  If you update to the latest trunk check-in and set:

 .testctrl never_corrupt 1

prior to running the test query, you get an assertion fault.

That new dot-command invokes
sqlite3_test_control(SQLITE_TESTCTRL_NEVER_CORRUPT, 1) which tells
SQLite that no database it deals with will ever be corrupt.  And this,
in turn, enables some additional assert() statements which are
normally turned off.  One of those new asserts finds the incorrect
logic.

We are working on a simple test case and a fix now.  It is a very
complex problem.  In particular, the sample query works fine as long
as the number of columns in the result set is not exactly 60.  Adding
or removing a single column of result gives the correct answer.



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

Re: [sqlite] ORDER BY and LIMIT regression

[RSmith]

Might this not be a "reverse_unordered_selects" pragma or compile option  going wrong, or at least the code making it work getting 
somehow hooked in the new versions for this query?


I have seen similar things when using that pragma (but of course that was 
intended).
Just a thought...


On 2015/01/19 16:27, Richard Hipp wrote:

Ignore my previous email on this subject.  We are able to get
different results from 3.8.6 and 3.8.8.  Unclear yet if the one or the
other is incorrect.

On 1/19/15, Richard Hipp  wrote:

On 1/19/15, Angelo Mottola  wrote:

Hello,

I have a regression to report, that seems to have been introduced between
SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
3.8.8 but eventually it wasn’t).

...

The query worked correctly with SQLite 3.8.6, returning for our test-case
database 5 records with the same EB_DocumentiFiscali__00.NumeroInterno,
ordered by EB_RigheDocFiscali.NumeroRiga in ascending order.
With 3.8.7 and 3.8.8 however, the very same query returns the same 5
records
but in the wrong order, as if it was ordered by NumeroRiga DESC (instead
of
ASC). What’s even more strange is the fact that if you remove the LIMIT
clause, the records are returned in the correct order even with 3.8.7 and
3.8.8.


I downloaded your test database and ran your query on 3.8.6, 3.8.7.4,
and 3.8.8.  All three give the same answer for me.  Dan did likewise
with the same results, and in addition ran the test under valgrind
with no warnings issued.

Unable to recreate the problem.

--
D. Richard Hipp
d...@sqlite.org





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

Re: [sqlite] ORDER BY and LIMIT regression

[Richard Hipp]

Ignore my previous email on this subject.  We are able to get
different results from 3.8.6 and 3.8.8.  Unclear yet if the one or the
other is incorrect.

On 1/19/15, Richard Hipp  wrote:
> On 1/19/15, Angelo Mottola  wrote:
>> Hello,
>>
>> I have a regression to report, that seems to have been introduced between
>> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
>> and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
>> 3.8.8 but eventually it wasn’t).
> ...
>>
>> The query worked correctly with SQLite 3.8.6, returning for our test-case
>> database 5 records with the same EB_DocumentiFiscali__00.NumeroInterno,
>> ordered by EB_RigheDocFiscali.NumeroRiga in ascending order.
>> With 3.8.7 and 3.8.8 however, the very same query returns the same 5
>> records
>> but in the wrong order, as if it was ordered by NumeroRiga DESC (instead
>> of
>> ASC). What’s even more strange is the fact that if you remove the LIMIT
>> clause, the records are returned in the correct order even with 3.8.7 and
>> 3.8.8.
>>
>
> I downloaded your test database and ran your query on 3.8.6, 3.8.7.4,
> and 3.8.8.  All three give the same answer for me.  Dan did likewise
> with the same results, and in addition ran the test under valgrind
> with no warnings issued.
>
> Unable to recreate the problem.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


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

Re: [sqlite] ORDER BY and LIMIT regression

[Richard Hipp]

On 1/19/15, Angelo Mottola  wrote:
> Hello,
>
> I have a regression to report, that seems to have been introduced between
> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
> and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
> 3.8.8 but eventually it wasn’t).
...
>
> The query worked correctly with SQLite 3.8.6, returning for our test-case
> database 5 records with the same EB_DocumentiFiscali__00.NumeroInterno,
> ordered by EB_RigheDocFiscali.NumeroRiga in ascending order.
> With 3.8.7 and 3.8.8 however, the very same query returns the same 5 records
> but in the wrong order, as if it was ordered by NumeroRiga DESC (instead of
> ASC). What’s even more strange is the fact that if you remove the LIMIT
> clause, the records are returned in the correct order even with 3.8.7 and
> 3.8.8.
>

I downloaded your test database and ran your query on 3.8.6, 3.8.7.4,
and 3.8.8.  All three give the same answer for me.  Dan did likewise
with the same results, and in addition ran the test under valgrind
with no warnings issued.

Unable to recreate the problem.

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

Re: [sqlite] Order Statistic Trees

[Scott Robison]

On Sat, May 17, 2014 at 9:41 PM, Scott Robison wrote:

> On May 17, 2014 8:40 PM, "Simon Slavin"  wrote:
> > Just want to check: this is what it sounds like, right ?  Your user has
> a window open where they are scrolling through the table, ordered by
> SomeColumn.  You need to know whether the window needs to change to reflect
> the new row.  Is that right ?
>
> In a nutshell. Further it needs to work in an environment where I control
> the data model but not the view or controller, so when a new record comes
> in, I need to know which row it is per current sort order so the model can
> advertise the new row to the view.
>
> > > SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY
> SomeColumn
> >
> > The second SELECT doesn't need the ORDER BY.  It will have no effect.
>
> Sorry, typo. I meant <=, fingers and brain out of sync.
>
> > How many columns does a table have ?  Roughly.  If it's not a lot all
> you need to is create one index for each column that might be chosen as
> 'SomeColumn'.  Or have you already done this and the speed your complaining
> about is the speed with the index
>
> Yes, speed for large collections is inadequate.
>
> > One technique I used to use in the old days (long before SQL) was to
> keep a second table which contained just the rows that were shown on the
> display.  So you'd have your full database on backing store and then
> another table with just 20 rows.  To see whether your new row will effect
> the display, you just compare it with the first and last entries in the
> display table, which is very small so it's fast to do things with.
>
> That would by far make the most sense. Maybe I can invert control between
> the view & model. Thanks for the thought.
>
Just in case anyone is interested: I got pulled off *this* project for a
higher priority project for a few days. Those days gave me time to think.
Instead of using the in memory vector for my main data (6 strings, a 64 bit
int, and a few bits for flags) which *could* get very large for some "known
worst case scenario", I now use a temp table as a disk based vector. The
only index for that table is an integer primary key so I can quickly pull
things into ram as needed.

The indexes are in memory because I do need to know the relative insertion
point of each row for each supported sort order which I can't easily get
from SQLite. Those are small enough that I can keep them in ram even for
our "current worst case" scenario (which is about 250,000 messages in a
single folder; some people don't believe in organizing their mailboxes).
The string based sort columns just have enough info to retrieve the string
in question from the database plus the first X bytes of the utf8 string
rep. Only if those first bytes are identical between two strings do I need
to go to disk, which makes the implementation fast enough (which it wasn't
when I had to keep fetching strings for every comparison during an insert).

I still like the idea of the order statistic tree, but getting away from
the problem for a few days helped me come back to it "refreshed" as it were
and come up with a good enough solution that fit better into the current
codebase.

Thanks for the ideas!

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

Re: [sqlite] Order Statistic Trees

[Scott Robison]

Sorry for the incomplete post, stupid phone.

On May 17, 2014 8:40 PM, "Simon Slavin"  wrote:
> Just want to check: this is what it sounds like, right ?  Your user has a
window open where they are scrolling through the table, ordered by
SomeColumn.  You need to know whether the window needs to change to reflect
the new row.  Is that right ?

In a nutshell. Further it needs to work in an environment where I control
the data model but not the view or controller, so when a new record comes
in, I need to know which row it is per current sort order so the model can
advertise the new row to the view.

> > SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY
SomeColumn
>
> The second SELECT doesn't need the ORDER BY.  It will have no effect.

Sorry, typo. I meant <=, fingers and brain out of sync.

> How many columns does a table have ?  Roughly.  If it's not a lot all you
need to is create one index for each column that might be chosen as
'SomeColumn'.  Or have you already done this and the speed your complaining
about is the speed with the index

Yes, speed for large collections is inadequate.

> One technique I used to use in the old days (long before SQL) was to keep
a second table which contained just the rows that were shown on the
display.  So you'd have your full database on backing store and then
another table with just 20 rows.  To see whether your new row will effect
the display, you just compare it with the first and last entries in the
display table, which is very small so it's fast to do things with.

That would by far make the most sense. Maybe I can invert control between
the view & model. Thanks for the thought.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order Statistic Trees

[Scott Robison]

Sorry, I typoed the second query. Should have been <=.
On May 17, 2014 8:40 PM, "Simon Slavin"  wrote:

>
> On 18 May 2014, at 3:19am, Scott Robison  wrote:
>
> > It is easy enough to read the source data and insert it into a table. It
> is
> > easy enough to query the list of data ordered by any field after the data
> > has been completely read. The hard part is knowing the correct insertion
> > point for a newly read record. Should it go into the current window of
> the
> > data? Does it go *above* the current window? Or is it *below*?
>
> Just want to check: this is what it sounds like, right ?  Your user has a
> window open where they are scrolling through the table, ordered by
> SomeColumn.  You need to know whether the window needs to change to reflect
> the new row.  Is that right ?
>
> > SELECT * FROM SomeTable ORDER BY SomeColumn OFFSET 24 LIMIT 1
> >
> > SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY
> SomeColumn
> >
> > As I understand it, both of these have to execute the full query and
> > calculate the result set in order to do return a result. I need something
> > that gives me log N access (or better) to elements by key and numeric
> index.
>
> The second SELECT doesn't need the ORDER BY.  It will have no effect.
>
> How many columns does a table have ?  Roughly.  If it's not a lot all you
> need to is create one index for each column that might be chosen as
> 'SomeColumn'.  Or have you already done this and the speed your complaining
> about is the speed with the index ?
>
> One technique I used to use in the old days (long before SQL) was to keep
> a second table which contained just the rows that were shown on the
> display.  So you'd have your full database on backing store and then
> another table with just 20 rows.  To see whether your new row will effect
> the display, you just compare it with the first and last entries in the
> display table, which is very small so it's fast to do things with.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order Statistic Trees

[Simon Slavin]

On 18 May 2014, at 3:19am, Scott Robison  wrote:

> It is easy enough to read the source data and insert it into a table. It is
> easy enough to query the list of data ordered by any field after the data
> has been completely read. The hard part is knowing the correct insertion
> point for a newly read record. Should it go into the current window of the
> data? Does it go *above* the current window? Or is it *below*?

Just want to check: this is what it sounds like, right ?  Your user has a 
window open where they are scrolling through the table, ordered by SomeColumn.  
You need to know whether the window needs to change to reflect the new row.  Is 
that right ?

> SELECT * FROM SomeTable ORDER BY SomeColumn OFFSET 24 LIMIT 1
> 
> SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY SomeColumn
> 
> As I understand it, both of these have to execute the full query and
> calculate the result set in order to do return a result. I need something
> that gives me log N access (or better) to elements by key and numeric index.

The second SELECT doesn't need the ORDER BY.  It will have no effect.

How many columns does a table have ?  Roughly.  If it's not a lot all you need 
to is create one index for each column that might be chosen as 'SomeColumn'.  
Or have you already done this and the speed your complaining about is the speed 
with the index ?

One technique I used to use in the old days (long before SQL) was to keep a 
second table which contained just the rows that were shown on the display.  So 
you'd have your full database on backing store and then another table with just 
20 rows.  To see whether your new row will effect the display, you just compare 
it with the first and last entries in the display table, which is very small so 
it's fast to do things with.

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[famat]

Federal University Oye Ekiti conduct Third Matriculation this February visit
http://www.fuoye.edu.ng for more details 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/ORDER-BY-issue-v3-8-2-amalgamtion-tp73605p73704.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Labar, Ken]

Hello Simon, Clemens, and Richard,

Thank you for your help.


> Simon: "What are you seeing from your SELECT that you weren't expecting ?"
0 | Batt
0 | ClockBatt
0 | Batt
0 | BP
0 | ORP
0 | Ref
0 | pH
0 | pH
0 | DO
...


> Simon: "Can you reduce your INSERTs to just two rows, and still get results 
> you consider to be incorrect from the SELECT ?"
Yes, I reduced the table to 2 rows by:
DELETE FROM userparameter WHERE hepi_parameter_id !=32961 AND hepi_parameter_id 
!= 32881;

select (rtTableSort < 1000) as selected, abbrString from userParameter order by 
abbrString;

results:

0 | ORP

0 | DO


> DRH: "Can you try recompiling with all compiler optimizations turned off and 
> see if you still get the error?"

I have turned off all optimizations (FYI: only C++ optimizations were on before)


> DRH: ", can you compile with -DSQLITE_DEBUG then run the query *after* first 
> running "PRAGMA vdbe_debug=ON" and then send us the output?"
I have been struggling to enable SQLITE_DEBUG with our uCOS-II VFS port.
(namely our code does not have stdout or printf as compiled now. I love 
embedded;-) I'm working on a solution for debugging, more soon).


> Clemens: "What is the EXPLAIN QUERY PLAN output for this query on the 
> handheld?"
0|0|0|SCAN TABLE userParameter
0|0|0|USE TEMP B-TREE FOR ORDER BY

Thank you,
Ken

ken labar | Embedded Firmware Engineer
Hach Hydromet | www.hachhydromet.com | 
kla...@hach.com

Innovating technology behind better data




Please be advised that this email may contain confidential 
information.  If you are not the intended recipient, please notify us 
by email by replying to the sender and delete this message.  The 
sender disclaims that the content of this email constitutes an offer 
to enter into, or the acceptance of, any agreement; provided that the 
foregoing does not invalidate the binding effect of any digital or 
other electronic reproduction of a manual signature that is included 
in any attachment.

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Richard Hipp]

On Sun, Feb 2, 2014 at 11:58 PM, Labar, Ken  wrote:
>
>
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work
at the PC level.

And earlier:


> -  IAR C compiler

Can you try recompiling with all compiler optimizations turned off and see
if you still get the error?

If that fails to resolve the issue, can you compile with -DSQLITE_DEBUG
then run the query *after* first running "PRAGMA vdbe_debug=ON" and then
send us the output?

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Clemens Ladisch]

Labar, Ken wrote:
> [...]
> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

What is the EXPLAIN QUERY PLAN output for this query on the handheld?

When it works, it uses the index on the sort column to ensure that records
are returned in the correct order:
0|0|0|SCAN TABLE userParameter USING INDEX idxAbbrString


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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Simon Slavin]

On 3 Feb 2014, at 4:58am, Labar, Ken  wrote:

> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>  
>  
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

Thanks for test data and SELECT command, which allow us to test our own 
installations.

What are you seeing from your SELECT that you weren't expecting ?

Can you reduce your INSERTs to just two rows, and still get results you 
consider to be incorrect from the SELECT ?  If so, which two INSERTs are shown 
in the wrong order ?  If not, at what point does the problem go away ?

> I’m wondering if we are short on RAM, and the sort engine is failing quietly, 
> returning the un-sorted query with SQLITE_OK.

If SQLite is doing that, then there is a serious bug in it which will be 
corrected very quickly.  SQLite is not designed to fail 'quietly'.  In the 
unlikely event that it runs out of a resource it should be returning an error.

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Labar, Ken]

Hello Simon,

Below is the query, and the schema I’m trying to debug.
FYI: I’m signed up for the daily digest, so please CC 
kla...@hach.com on replies for a faster response.

CREATE TABLE userParameter (
hepi_parameter_id INT NOT NULL
,hepi_category INT NOT NULL
,hepi_number INT NOT NULL
,preferredUnit INT
,rtTableSort INT
,rtGraphSort INT
,rtGridSort INT
,dpTableSort INT
,dpGraphSort INT
,dpGridSort INT
,name_deviceString_id INT
,abbr_deviceString_id INT
,units_deviceString_id INT
,nameString TEXT COLLATE BINARY
,abbrString TEXT COLLATE BINARY
,unitsString TEXT
);

CREATE INDEX idxHepi_parameter_id ON userParameter (hepi_parameter_id);
CREATE INDEX idxRtTableSort ON userParameter (rtTableSort);
CREATE INDEX idxDpTableSort ON userParameter (dpTableSort);
CREATE INDEX idxNameString ON userParameter (nameString COLLATE BINARY);
CREATE INDEX idxAbbrString ON userParameter (abbrString COLLATE BINARY);

INSERT INTO "userParameter" 
VALUES(32785,1,1,1,0,1000,1000,1000,1000,1000,2,24,127,'Temperature','Temp','°C');
INSERT INTO "userParameter" 
VALUES(32786,1,2,1,1000,1000,1000,1000,1000,1000,2,24,128,'Temperature','Temp','°F');
INSERT INTO "userParameter" 
VALUES(32787,1,3,1,1000,1000,1000,1000,1000,1000,2,24,129,'Temperature','Temp','°K');
INSERT INTO "userParameter" 
VALUES(32817,3,1,1,1000,1000,1000,1000,1000,1000,138,125,139,'Resistivity','Res','kΩ-cm');
INSERT INTO "userParameter" 
VALUES(32833,4,1,1,1000,1000,1000,1000,1000,1000,140,141,137,'Specific 
Conductivity','SpCond','mS/cm');
INSERT INTO "userParameter" 
VALUES(32834,4,2,1,1000,1000,1000,1000,1000,1000,140,141,142,'Specific 
Conductivity','SpCond','µS/cm');
INSERT INTO "userParameter" 
VALUES(32849,5,1,1,1000,1000,1000,1000,1000,1000,143,144,145,'Salinity','Sal','psu');
INSERT INTO "userParameter" 
VALUES(32850,5,2,1,1000,1000,1000,1000,1000,1000,143,144,146,'Salinity','Sal','ppt');
INSERT INTO "userParameter" 
VALUES(32865,6,1,1,1000,1000,1000,1000,1000,1000,147,148,149,'Total Dissolved 
Solids','TDS','g/L');
INSERT INTO "userParameter" 
VALUES(32881,7,1,1,1000,1000,1000,1000,1000,1000,203,203,204,'DO','DO','%SAT');
INSERT INTO "userParameter" 
VALUES(32897,8,1,1,1000,1000,1000,1000,1000,1000,203,203,196,'DO','DO','mg/L');
INSERT INTO "userParameter" 
VALUES(32929,10,1,1,1000,1000,1000,1000,1000,1000,6,6,213,'pH','pH','units');
INSERT INTO "userParameter" 
VALUES(32945,11,1,1,1000,1000,1000,1000,1000,1000,6,6,214,'pH','pH','mV');
INSERT INTO "userParameter" 
VALUES(33249,30,1,1,1000,1000,1000,1000,1000,1000,223,224,214,'Reference','Ref','mV');
INSERT INTO "userParameter" 
VALUES(32961,12,1,1,1000,1000,1000,1000,1000,1000,7,7,214,'ORP','ORP','mV');
INSERT INTO "userParameter" 
VALUES(32977,13,1,1,1000,1000,1000,1000,1000,1000,8,8,240,'Depth','Depth','meters');
INSERT INTO "userParameter" 
VALUES(32978,13,2,1,1000,1000,1000,1000,1000,1000,8,8,241,'Depth','Depth','feet');
INSERT INTO "userParameter" 
VALUES(33377,38,1,1,1000,1000,1000,1000,1000,1000,794,794,242,'Pressure','Pressure','psig');
INSERT INTO "userParameter" 
VALUES(33378,38,2,1,1000,1000,1000,1000,1000,1000,794,794,250,'Pressure','Pressure','psia');
INSERT INTO "userParameter" 
VALUES(32993,14,1,1,1000,1000,1000,1000,1000,1000,257,257,259,'TDG','TDG','mmHg');
INSERT INTO "userParameter" 
VALUES(32994,14,2,1,1000,1000,1000,1000,1000,1000,257,257,250,'TDG','TDG','psia');
INSERT INTO "userParameter" 
VALUES(33009,15,1,1,1000,1000,1000,1000,1000,1000,10,268,272,'Turbidity','Turb','NTU');
INSERT INTO "userParameter" 
VALUES(33010,15,2,1,1000,1000,1000,1000,1000,1000,10,268,273,'Turbidity','Turb','FNU');
INSERT INTO "userParameter" 
VALUES(33025,16,1,1,1000,1000,1000,1000,1000,1000,287,288,294,'Chlorophyll 
a','CHL','µg/L');
INSERT INTO "userParameter" 
VALUES(33041,17,1,1,1000,1000,1000,1000,1000,1000,287,288,245,'Chlorophyll 
a','CHL','V');
INSERT INTO "userParameter" 
VALUES(32913,9,1,1,1000,1000,1000,1000,1000,1000,14,299,259,'Barometric 
Pressure','BP','mmHg');
INSERT INTO "userParameter" 
VALUES(33057,18,1,1,1000,1000,1000,1000,1000,1000,331,332,334,'Rhodamine 
WT','RWT','ppb');
INSERT INTO "userParameter" 
VALUES(33073,19,1,1,1000,1000,1000,1000,1000,1000,331,332,245,'Rhodamine 
WT','RWT','V');
INSERT INTO "userParameter" 
VALUES(33089,20,1,1,1000,1000,1000,1000,1000,1000,343,344,345,'Phycocyanin','PCY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33105,21,1,1,1000,1000,1000,1000,1000,1000,343,344,245,'Phycocyanin','PCY','V');
INSERT INTO "userParameter" 
VALUES(33121,22,1,1,1000,1000,1000,1000,1000,1000,352,353,345,'Phycoerythrin','PRY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33137,23,1,1,1000,1000,1000,1000,1000,1000,352,353,245,'Phycoerythrin','PRY','V');
INSERT INTO 

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Simon Slavin]

On 1 Feb 2014, at 3:53am, Labar, Ken  wrote:

> Upgrading  from  3.7.14.1 to 3.8.2 our previously sorted queries are no 
> longer sorted.
> Testing this same database with the win7 binary 3.8.2 the query is correctly 
> sorted.

Does your query include an ORDER BY clause ?  If not, then you cannot rely on 
sorted results.  Even if the query returns sorted results during testing, 
changes in the data that may happen at your client's site (running ANALYZE, 
more rows, rows inserted in a different order, different version of your 
operating system or disk operating system) may mean that one of your customer 
units starts returning differently sorted results for no apparent reason.

Your fix is to have an appropriate ORDER BY clauses in each SELECT.  This will 
guarantee correct results in all future versions of SQLite no matter what 
hardware and OS you're using.

On the other hand, if you have a query that contains an ORDER BY clause and 
your results are not in that order, please post your schema and the query, 
since that suggests a serious bug in SQLite and we'd love to know about it.

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

Re: [sqlite] order of = in join

[Klaas V]

Anbrus wrote:

"what's the easiest way to generate a set of fixed number of rows like
VALUES does but in an order of your choice?"

Order them before with the command 'sort'

NAME
   sort - sort lines of text files

SYNOPSIS
   sort [OPTION]... [FILE]...

DESCRIPTION
   Write sorted concatenation of all FILE(s) to standard output.

   Mandatory arguments to long options are mandatory for short options too. 
 Ordering options:

   -b, --ignore-leading-blanks
  ignore leading blanks

   -d, --dictionary-order
  consider only blanks and alphanumeric characters

   -f, --ignore-case
  fold lower case to upper case characters

HTH,
Klaas `Z4us` V



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

Re: [sqlite] Order of rows in a VALUES select expression

[Scott Robison]

I am not in front of a sqlite equipped system at the moment, but wouldn't
something like this probably work?

select * from (values ...) order by ...


On Thu, Jan 30, 2014 at 1:28 PM, Zsbán Ambrus  wrote:

> The future release sqlite 3.8.3 adds VALUES statements as a shortcut
> form of SELECT statements.  This shortcut simply creates a results set
> made of any number of rows, any number of (unnamed) columns in each,
> and all the values given by separate explicit expressions.
>
> My question is whether sqlite guarantees that the order of result rows
> from such a statement is the same as the order they are listed in the
> statement.  The docs at "http://www.sqlite.org/draft/lang_select.html;
> seem to imply that the order is undefined, the engine is free to
> choose whatever order it wishes.  Further, you can't even use an
> explicit ORDER BY clause on a VALUES statement.  If this is the case,
> what's the easiest way to generate a set of fixed number of rows like
> VALUES does but in an order of your choice?
>
> Ambrus
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] order of = in join

[Keith Medcalf]

What version of SQLite and what indexes have you created? (and, if you have 
relevant indexes, have you run analyze?)

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of E. Timothy Uy
>Sent: Thursday, 30 January, 2014 09:25
>To: General Discussion of SQLite Database
>Subject: [sqlite] order of = in join
>
>#1 - f.term = t.term
>SELECT m.term AS term, m.definition AS definition
>FROM
>(SELECT t.term, e.definition
>FROM Terms_content t
>INNER JOIN Favorites f ON f.term = t.term
>LEFT JOIN TermEntries te ON te.termid = t.docid
>LEFT JOIN Entries e ON e.docid = te.entryid) AS m
>LEFT JOIN Favorites f ON f.term = m.term"
>
>#2 - t.term = f.term
>SELECT m.term AS term, m.definition AS definition
>FROM
>(SELECT t.term, e.definition
>FROM Terms_content t
>INNER JOIN Favorites f ON t.term = f.term
>LEFT JOIN TermEntries te ON te.termid = t.docid
>LEFT JOIN Entries e ON e.docid = te.entryid) AS m
>LEFT JOIN Favorites f ON f.term = m.term
>
>
>Here the Terms_content table is big, maybe 300k rows, while the Favorites
>table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30
>ms.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] order of = in join

[Zsbán Ambrus]

On 1/30/14, E. Timothy Uy  wrote:
> #1 - f.term = t.term
> Query #1 takes 300 ms, and query #2 takes 30 ms.

Can you show the schema for the tables?  Is it possible that the two
equals comparisons use different affinity or collation, which changes
the semantics of your statement?

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

Re: [sqlite] order of = in join

[Clemens Ladisch]

E. Timothy Uy wrote:
> Here the Terms_content table is big, maybe 300k rows, while the Favorites
> table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms.

Which SQLite version?
What is the output of EXPLAIN QUERY PLAN for both queries?


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

Re: [sqlite] order of = in join

[Simon Slavin]

On 30 Jan 2014, at 4:24pm, E. Timothy Uy  wrote:

> Here the Terms_content table is big, maybe 300k rows, while the Favorites
> table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms.

Have you run ANALYZE on the database ?

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

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

[James K. Lowden]

On Thu, 19 Dec 2013 15:14:24 +0100
Dominique Devienne  wrote:

> The selling point of SQL is to declaratively tell the engine what you
> need, and let it choose the optimal implementation. So saying that
> ORDER BY doesn't know about LIMIT as a matter of fact seems
> completely wrong to me.

It may help to remember that ORDER BY, properly understood, is a
post-processing clause.  It takes a table object as input and writes a
cursor as output.  The use of ORDER BY in other ways has caused quite a
bit of teeth-gnashing on this list.  

Most queries that use LIMIT could be expressed without it.  I was glad
to see SQLite reward your use of max().  :-)  

Looking at your queries, 

> sqlite> select max(id) from t100m limit 1;

LIMIT is redundant. 

> sqlite> select 1 where exists (select id from t100m order by id desc);
> sqlite> select 1 where exists (select id from t100m where id <
>   500*1000 order by id desc);
> sqlite> select 1 where exists (select id from t100m where id = 13
>   order by id desc);

ORDER BY in subquery unnecessary, probably deleterious.  I suggest
"SELECT 1" in an EXISTS clause for clarity of intent.  

You might want to try

select * from t100m as t 
where exists (
select 1 from t100m 
having max(id) = t.id
);

which I think is more along the lines of what you want.  I doubt it
will be any faster, though.  Without an index SQLite must scan the
table.  

HTH.  

--jkl





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

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

[Dominique Devienne]

On Thu, Dec 19, 2013 at 12:34 PM, Clemens Ladisch wrote:

> Dominique Devienne wrote:
> > In the two queries below, there's a 5x performance difference.
>
> > select max(id) from t100m limit 1;
> > select id from t100m order by id desc limit 1;
>
> > But logically, it seems to me that the limit 1 on the order by is
> logically
> > equivalent to a min or max depending on ascending or descending ordering,
> > and is "optimize-able" by using an appropriate transformation.
>
> SQLite's query optimizer does not implement this transformation, so the
> MAX can throw away any smaller records, while the ORDER BY does not know
> about the LIMIT and keeps all sorted records in a temporary table.
>

The selling point of SQL is to declaratively tell the engine what you need,
and let it choose the optimal implementation. So saying that ORDER BY
doesn't know about LIMIT as a matter of fact seems completely wrong to me.
The intent is clearly stated in the query, and the optimizer sees *both*
the ORDER BY and the LIMIT and thus has the opportunity to implement it in
a more efficient way that it does now. Obviously a query optimizer is a
very tricky beast to implement. I'm merely reporting this
"non-optimization", in part to make this optimization use-case known, in
the hope that it might be supported in the future, and in part in case I
misses another way to achieve the same results.

This query is efficient only if there is an index that allows the
> database to look up the largest value without touching any other
> records.
>

That's something else. There are no indices in my example queries, yet the
logically equivalent query is 5x faster.

With an index, in my contrived examples, both queries are instantaneous
(10m rows instead of 100m, otherwise I run out of memory). But typically
our order by columns are not indexed, and even worse some queries are
ordered using the result of a custom SQL function.

> This use case is not as bogus as it seems. We have tree UI components,
> > where the child nodes of a tree node is determined by a query, with some
> of
> > these "child queries" having order by clauses. The UI needs to know if
> the
> > parent node is expendable or not, and for that we currently add the
> limit 1
> > clause to the "child query", but the limit 1 is applied *after* the
> > ordering (or after the union all, see below), which forces getting all
> the
> > children rows and columns, a major performance issue.
>
> This sounds as if you're working on the wrong abstraction level.
> I would redesign the code so that tree nodes can return another query.
>

That's side-stepping the point I'm raising. Plus it opens the door for
inconsistencies between the two queries (i.e. false positives or worse
false negatives), and from experience it leads to more bugs. Using a single
query has drawbacks performance-wise, but at least the results are always
consistent. Our current mitigation is more along the lines of having our
code have more knowledge about the query structure by breaking it down into
clauses and such, to avoid query re-parsing.

Hmmm, I think I might have stumbled on what I need, namely the EXISTS
condition, which seems to exhibit the short-circuiting semantic I was
looking for!

sqlite> select count(*) from t100m;
1
CPU Time: user 0.171601 sys 0.00
sqlite> select max(id) from t100m limit 1;
0
CPU Time: user 15.132097 sys 0.00
sqlite> select id from t100m order by id desc limit 1;
0
CPU Time: user 78.343702 sys 0.00
sqlite>
sqlite> select 1 where exists (select id from t100m order by id desc);
1
CPU Time: user 0.00 sys 0.00
sqlite> select 1 where exists (select id from t100m where id < 500*1000
order by id desc);
CPU Time: user 12.823282 sys 0.00
sqlite> select 1 where exists (select id from t100m where id = 13 order by
id desc);
CPU Time: user 12.729682 sys 0.00
sqlite> select 1 where exists (select id from t100m where id > 0
order by id desc);
CPU Time: user 13.525287 sys 0.00


> Alternatively, if you can afford to confuse your users, allow all nodes
> to be expandable, and remove the [+] only when an actual attempt to
> expand returns no records.
>

That's been ruled out as unacceptable.


> > Also note that quite a few of our queries are "union" or "union all"
> > queries, joining rows from several different tables, so in this specific
> > use case, any query returning a row would be enough to stop executing the
> > other queries and without the need to union all the inner result sets.
>
> Why "inner"?  Are you using a subqueries?  In that case, read
> .
>

Yes we are. The queries return "base models", i.e. primary "data" result
sets, which can be further ordered, filtered, and/or aggregated depending
on the state of the UI component. We try to avoid inner queries, but
sometimes we must use them.

Thanks for your input Clemens. It challenged me into finding my own

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

[Clemens Ladisch]

Dominique Devienne wrote:
> In the two queries below, there's a 5x performance difference.

> select max(id) from t100m limit 1;
> select id from t100m order by id desc limit 1;

> But logically, it seems to me that the limit 1 on the order by is logically
> equivalent to a min or max depending on ascending or descending ordering,
> and is "optimize-able" by using an appropriate transformation.

SQLite's query optimizer does not implement this transformation, so the
MAX can throw away any smaller records, while the ORDER BY does not know
about the LIMIT and keeps all sorted records in a temporary table.

This query is efficient only if there is an index that allows the
database to look up the largest value without touching any other
records.

> This use case is not as bogus as it seems. We have tree UI components,
> where the child nodes of a tree node is determined by a query, with some of
> these "child queries" having order by clauses. The UI needs to know if the
> parent node is expendable or not, and for that we currently add the limit 1
> clause to the "child query", but the limit 1 is applied *after* the
> ordering (or after the union all, see below), which forces getting all the
> children rows and columns, a major performance issue.

This sounds as if you're working on the wrong abstraction level.
I would redesign the code so that tree nodes can return another query.

Alternatively, if you can afford to confuse your users, allow all nodes
to be expandable, and remove the [+] only when an actual attempt to
expand returns no records.

> Also note that quite a few of our queries are "union" or "union all"
> queries, joining rows from several different tables, so in this specific
> use case, any query returning a row would be enough to stop executing the
> other queries and without the need to union all the inner result sets.

Why "inner"?  Are you using a subqueries?  In that case, read
.


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

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[Fabian Büttner]

Thanks. I think using GROUP BY without aggregates is a strange way to 
remove duplicates, anyway.

Not intentional.  SQLite simply fails to recognize that by using the GROUP
BY in descending order it could avoid the ORDER BY clause.  This is an
optimization that we have never considered because it has never come up
before.



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

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[Yuriy Kaminskiy]

Fabian Büttner wrote:
> Hi,
> 
> I have been thinking about a question on stackoverflow
> (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by),
> where some SQL framework removes duplicates from results using GROUP BY
> instead of DISTINCT.
> I don't want to discuss that this might not be a good idea. However, the
> core of that problem is the creation of temp b-trees when using ORDER BY
> ... DESC after GROUP BY.
> I wondered if the construction of a temp b-tree in the third query is
> intentional / by design?

I'd guess just "missing optimization opportunity". I think this fragment of code
is responsible for that optimization: src/select.c, sqlite3Select():

=== cut ===
  /* If there is both a GROUP BY and an ORDER BY clause and they are
  ** identical, then disable the ORDER BY clause since the GROUP BY
  ** will cause elements to come out in the correct order.  This is
  ** an optimization - the correct answer should result regardless.
  ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  ** to disable this optimization for testing purposes.
  */
  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
pOrderBy = 0;
  }
=== cut ===

Adding DESC to pOrderBy "breaks" sqlite3ExprListCompare(,) (note: just changing
sqlite3ExprListCompare to ignore it would be insufficient and will likely result
in *breakage*).

> I am using sqlite 3.8.1.
> 
> sqlite> PRAGMA legacy_file_format=OFF;
> 
> sqlite> create table test1 (x INTEGER);
> sqlite> create index test1_idx on test1(x);
> sqlite> explain query plan select x from test1 group by x order by x;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test1 USING COVERING
> INDEX test1_idx
> 
> create table test2 (x INTEGER);
> sqlite> create index test2_idx on test2(x);
> sqlite> explain query plan select x from test2 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test2 USING COVERING
> INDEX test2_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> create table test3 (x INTEGER);
> sqlite> create index test3_idx on test3(x desc);
> sqlite> explain query plan select x from test3 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> To double check:
> 
> sqlite> explain query plan select x from test3 order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 
> 
> Regards
> Fabian

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

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[Richard Hipp]

On Mon, Oct 21, 2013 at 5:27 AM, Fabian Büttner wrote:

> Hi,
>
> I have been thinking about a question on stackoverflow (
> http://stackoverflow.com/**questions/19236363/select-**
> distinct-faster-than-group-by
> )**, where some SQL framework removes duplicates from results using GROUP
> BY instead of DISTINCT.
> I don't want to discuss that this might not be a good idea. However, the
> core of that problem is the creation of temp b-trees when using ORDER BY
> ... DESC after GROUP BY.
> I wondered if the construction of a temp b-tree in the third query is
> intentional / by design?
>

Not intentional.  SQLite simply fails to recognize that by using the GROUP
BY in descending order it could avoid the ORDER BY clause.  This is an
optimization that we have never considered because it has never come up
before.



>
> I am using sqlite 3.8.1.
>
> sqlite> PRAGMA legacy_file_format=OFF;
>
> sqlite> create table test1 (x INTEGER);
> sqlite> create index test1_idx on test1(x);
> sqlite> explain query plan select x from test1 group by x order by x;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test1 USING COVERING INDEX
> test1_idx
>
> create table test2 (x INTEGER);
> sqlite> create index test2_idx on test2(x);
> sqlite> explain query plan select x from test2 group by x order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test2 USING COVERING INDEX
> test2_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
>
> create table test3 (x INTEGER);
> sqlite> create index test3_idx on test3(x desc);
> sqlite> explain query plan select x from test3 group by x order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test3 USING COVERING INDEX
> test3_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
>
> To double check:
>
> sqlite> explain query plan select x from test3 order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test3 USING COVERING INDEX
> test3_idx
>
>
> Regards
> Fabian
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Order By clause in aggregate functions?

[Simon Slavin]

On 21 Jul 2013, at 11:47pm, Igor Tandetnik  wrote:

> On 7/21/2013 5:01 PM, Simon Slavin wrote:
>> I had to fake it.  The parameter I passed to my aggregate function was a 
>> string as follows:
>> 
>> theOrder||':'||theValue
>> 
>> My function extension had to split the values into two parts
> 
> Couldn't you just pass two parameters, separately?

D'oh.  Yes.  The book I was working on had only examples with one parameter, 
but if group_concat() can take two, then so could mine.  That project is long 
dead and buried, but I'll know better for next time.  Thanks.

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

Re: [sqlite] Order By clause in aggregate functions?

[Igor Tandetnik]

On 7/21/2013 5:01 PM, Simon Slavin wrote:

I had to fake it.  The parameter I passed to my aggregate function was a string 
as follows:

theOrder||':'||theValue

My function extension had to split the values into two parts


Couldn't you just pass two parameters, separately?
--
Igor Tandetnik

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

Re: [sqlite] Order By clause in aggregate functions?

[ss griffon]

On Sun, Jul 21, 2013 at 1:29 PM, Petite Abeille wrote:

>
> On Jul 21, 2013, at 10:15 PM, ss griffon  wrote:
>
> > I'm writing an extension to SQLite that adds some aggregate functions.
> > Some of them, require that the rows passed to the aggregate function
> > be sorted.  It seems as if lots of data bases (MySQL, PostgreSQL)
> > support an ORDER BY clause in their aggregate functions.  Does SQLite
> > support anything like this?
>
> Short answer: no.
>
> Longer answer:  there is a family of SQL:2003 functions referred as
> 'window function' [1] and/or 'analytic function' [2]  which work that way:
>
>   :: =
>OVER ( [ PARTITION BY , ... ]
>   [ ORDER BY  ] )
>
> Sadly none of this is supported by SQLite.
>
> The latest issue of NoCOUG (Northern California Oracle Users Group) has a
> nice little primer on analytics, around page 21:
>
> "Wielding the Sword of Analytics"
> http://www.nocoug.org/Journal/NoCOUG_Journal_201308.pdf
>
>
> [1] http://en.wikipedia.org/wiki/Select_(SQL)#Window_function
> [2] http://www.oracle-base.com/articles/misc/analytic-functions.php
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Thanks for the quick response and the links.  Not a big deal that it's not
supported, I just thought I would ask to make sure.  In my functions I'll
cache the data and sort before calculating the result.

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

Re: [sqlite] Order By clause in aggregate functions?

[Simon Slavin]

On 21 Jul 2013, at 9:29pm, Petite Abeille  wrote:

> Short answer: no.

Right.

I had to fake it.  The parameter I passed to my aggregate function was a string 
as follows:

theOrder||':'||theValue

My function extension had to split the values into two parts, then sort by its 
orders, then do the maths.

The ability to ensure supply order to an aggregated function might be a very 
useful thing to put into SQLite 4.

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

Re: [sqlite] Order By clause in aggregate functions?

[Petite Abeille]

On Jul 21, 2013, at 10:15 PM, ss griffon  wrote:

> I'm writing an extension to SQLite that adds some aggregate functions.
> Some of them, require that the rows passed to the aggregate function
> be sorted.  It seems as if lots of data bases (MySQL, PostgreSQL)
> support an ORDER BY clause in their aggregate functions.  Does SQLite
> support anything like this?

Short answer: no.

Longer answer:  there is a family of SQL:2003 functions referred as 'window 
function' [1] and/or 'analytic function' [2]  which work that way:

  :: =
   OVER ( [ PARTITION BY , ... ]
  [ ORDER BY  ] )

Sadly none of this is supported by SQLite. 

The latest issue of NoCOUG (Northern California Oracle Users Group) has a nice 
little primer on analytics, around page 21:

"Wielding the Sword of Analytics"
http://www.nocoug.org/Journal/NoCOUG_Journal_201308.pdf


[1] http://en.wikipedia.org/wiki/Select_(SQL)#Window_function
[2] http://www.oracle-base.com/articles/misc/analytic-functions.php

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

Re: [sqlite] ORDER of export on a SELECT

[Gert Van Assche]

Thanks for the advice, both. In the mean time we know that it indeed
something else causing the problem.

gert


2013/5/2 Simon Slavin 

>
> On 2 May 2013, at 9:26am, Gert Van Assche  wrote:
>
> > I have a table with 2 fields that need to be exported to 2 TXT files but
> > the order of the lines in the export should be exactly the same as in the
> > table.
>
> Tables do not have order.  Really.  A table is a set of rows, not an
> ordered set of rows.  If you do not specify ORDER BY on a unique set of
> values, the same SELECT can returns the same rows in different orders.
>
> > I tried to do it like this:
> > SELECT [FieldA] FROM [T1] ORDER BY rowid;
> > and
> > SELECT [FieldB] FROM [T1] ORDER BY rowid;
> > but the rowid order is not followed. The two TXT files that are created
> are
> > not in sync.
>
> If you have unique values in rowid, and really are doing those two
> commands in the same transaction (so nothing can write to the table between
> them), and getting rows returned in different orders, something is weird
> about your setup.
>
> In your software do
>
> SELECT [FieldA],[FieldB] FROM [T1] ORDER BY rowid
>
> Use your software to make one text file from the first values and another
> from the second values.  It will be faster too !
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER of export on a SELECT

[Simon Slavin]

On 2 May 2013, at 9:26am, Gert Van Assche  wrote:

> I have a table with 2 fields that need to be exported to 2 TXT files but
> the order of the lines in the export should be exactly the same as in the
> table.

Tables do not have order.  Really.  A table is a set of rows, not an ordered 
set of rows.  If you do not specify ORDER BY on a unique set of values, the 
same SELECT can returns the same rows in different orders.

> I tried to do it like this:
> SELECT [FieldA] FROM [T1] ORDER BY rowid;
> and
> SELECT [FieldB] FROM [T1] ORDER BY rowid;
> but the rowid order is not followed. The two TXT files that are created are
> not in sync.

If you have unique values in rowid, and really are doing those two commands in 
the same transaction (so nothing can write to the table between them), and 
getting rows returned in different orders, something is weird about your setup.

In your software do

SELECT [FieldA],[FieldB] FROM [T1] ORDER BY rowid

Use your software to make one text file from the first values and another from 
the second values.  It will be faster too !

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

Re: [sqlite] ORDER of export on a SELECT

[Richard Hipp]

On Thu, May 2, 2013 at 4:26 AM, Gert Van Assche  wrote:

> All, I
>
> I have a table with 2 fields that need to be exported to 2 TXT files but
> the order of the lines in the export should be exactly the same as in the
> table.
> I tried to do it like this:
>  SELECT [FieldA] FROM [T1] ORDER BY rowid;
> and
>  SELECT [FieldB] FROM [T1] ORDER BY rowid;
> but the rowid order is not followed. The two TXT files that are created are
> not in sync.
>

"ORDER BY rowid" should always be honored.  If you have a case where it is
not, that is a bug.  Please send us sufficient information to reproduce the
problem and we will look into it.



>
> Is this normal or is there another way to address this?
>
> thanks for your help,
>
> Gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Order of columns in group by statement affects query performance

[James K. Lowden]

On Fri, 26 Apr 2013 11:12:15 +0200
Hick Gunter  wrote:

> It is not unreasonable to assume that in a well designed SQL
> Statement the GROUP BY clause will be backed up by the necessary
> index and an identical ORDER BY clause

That is an entirely unreasonable assumption.  Order may or not matter.
I've often written GROUP BY queries ordered by the aggregate, or to
select the maximum.  

> Any index that covers all the GROUP BY fields is a "good" index
> because it allows aggregates to be computed "on the fly" as opposed
> to in a temporary table.

Agreed.  I hope it's clear now that "covers" is order-independent.  It
so happens, apparently, that the order in which the column names appear
in the GROUP BY determine whether or not the index is used.  Thats
unfortunate, because it makes two equivalent queries perform very
differently.  

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Hick Gunter]

Do you have any experience with SQLite virtual tables? I guess not.

There are 20 issues here:

1) The abstract problem of choosing an Index for optimizing GROUP BY

2) the SQLite implementation (which I was referring to)

Ad 1)

Any index that covers all the GROUP BY fields is a "good" index because it 
allows aggregates to be computed "on the fly" as opposed to in a temporary 
table.

ORDER BY clause and multiple indices may complicate the matter


Ad 2)

SQLite attempts to handle virtual tables the same as native tables (which is 
one of the main reasons we chose SQLite). The VT interface does not allow 
publication of indexes nor creation of native indexes on virtual tables. The 
aOrderBy table of the interface implies an ordered list of fields, therefore 
SQLite would have to call xBestIndex n! times to discover the least costly 
index to use.

It is not unreasonable to assume that in a well designed SQL Statement the 
GROUP BY clause will be backed up by the necessary index and an identical ORDER 
BY clause (at least unintentionally by the programmer virtue of laziness 
resulting in copy-paste of the field list).

Thus the aOrderBy array being used for ORDER BY and GROUP BY in the VT 
interface.



http://www.sqlite.org/vtab.html

2.3 The xBestIndex Method
SQLite uses the xBestIndex method of a virtual table module to determine the 
best way to access the virtual table. The xBestIndex method has a prototype 
like this:

  int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);

...

Before calling this method, the SQLite core initializes an instance of the 
sqlite3_index_info structure with information about the query that it is 
currently trying to process. This information derives mainly from the WHERE 
clause and **ORDER BY or GROUP BY** clauses of the query, but also from any ON 
or USING clauses if the query is a join.


-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Donnerstag, 25. April 2013 16:34
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Order of columns in group by statement affects query 
performance

On Thu, 25 Apr 2013 10:29:34 +0200
Hick Gunter <h...@scigames.at> wrote:

> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from
> hints in the virtual table description).

That might be so, in some limited sense.  It's obviously false in general 
because they mean different things and have different effects.

> If you have an index that covers the GROUP BY clause in field order,
> then aggregate functions need store only the current value; if not,
> then you need an ephemeral table to hold the aggregate values.

Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an index 
ordered B,A.  By permuting the order of the columns in the GROUP BY clause, it 
finds a match for the index and uses it.

Yes, the problem is O(n^2), where n is the number of columns in the GROUP BY, 
but n is always small; even 7 columns could be checked in less than 50 
iterations.

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Simon Slavin]

On 25 Apr 2013, at 4:23pm, Jay A. Kreibich  wrote:

>  Except there is no such thing as "GROUP BY order".  SQL Golden Rule:
>  If there is no ORDER BY, the rows have no order.  According to SQL, 
>  neither the groups, nor the rows within a group (as they are fed into
>  aggregates) have a defined order.  Any query that makes assumptions
>  about the ordered result of a GROUP BY is broken.
> 
>  Use the out-of-order index.

GROUP BY on multiple columns means that the values in all those columns have to 
be the same for the rows to be included in the same GROUP.  It says nothing 
about the order those groups should appear in in the results of the SELECT.

Okay.  So adding this to what went by upthread, I was wrong.  Column order in 
the GROUP BY clause doesn't matter.  Therefore the upthread comment that GROUP 
BY A,B means exactly the same as GROUP BY B,A is correct.

So if there's an index which features those columns in any order it can be 
used, not matter what order the columns appear in in the GROUP BY clause.

So it was perfectly reasonable for the OP to wonder why an index was used for 
one order but not another.

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Jay A. Kreibich]

On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall:
> 2013/4/25 James K. Lowden 
> 
> >
> > Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
> > index ordered B,A.  By permuting the order of the columns in the GROUP
> > BY clause, it finds a match for the index and uses it.
> >
> > Yes, the problem is O(n^2), where n is the number of columns in the
> > GROUP BY, but n is always small; even 7 columns could be checked in
> > less than 50 iterations.
> >
> 
> I believe its O(n!), but still doable for small n.  I don't know the inner
> workings of the query optimizer but mabye instead of asking/check for a
> index of every permutation of the columns in the group by, it could just
> check if an index exists which covers all columns (even the sorting order
> doesn't matter). (the virtual table api needs an addition for that to work)

  Permutations are O(N!), but that's not really what you want.  Given a
  set of GROUP BY terms you want, generally, the index with the most
  terms in any initial order.  You don't need a full match for the
  index to be a win.  For example, GROUP BY A,B,C,D,E is likely to
  get a performance boost from an index on (A,D,B) and, *in general*,
  that should be a bigger win than an index on (B,C).

  Of course, since this is a query optimizer, there are always edge
  cases... For example, if there is an index over (E) that has 99%
  unique values, it is likely a better choice than (A,D,B)... it
  depends on the distribution of the index.  Similarly, if any GROUP BY
  term maps to a unique index... boom, you're done.

  As with most things having to do with query optimization, the problem
  quickly explodes.  On the other hand, SQLite must already have
  assumptions about index costs (with or without ANALYZE), so at least
  there's an existing set of weights and assumptions to work from.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Jay A. Kreibich]

On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall:
> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints
> in the virtual table description).

  They're not the same clause, they don't do the same thing.

  Now, it is true that most database systems implement the first step
  of a GROUP BY by sorting the query using semantics that are similar to
  ORDER BY.  That way all of the rows in a related group are next to
  each other, and they're easier to process.  I assume SQLite does the
  same thing.

  It is, however, as they say, "an implementation detail."

> IF you have an index that covers the GROUP BY clause in any other order,
> then you still have the guarantee that all rows belonging to the same
> group will be retrieved together, but the result rows will be ordered
> in index order and not GROUP BY order.

  Except there is no such thing as "GROUP BY order".  SQL Golden Rule:
  If there is no ORDER BY, the rows have no order.  According to SQL, 
  neither the groups, nor the rows within a group (as they are fed into
  aggregates) have a defined order.  Any query that makes assumptions
  about the ordered result of a GROUP BY is broken.

  Use the out-of-order index.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Daniel Winter]

2013/4/25 James K. Lowden 

>
> Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
> index ordered B,A.  By permuting the order of the columns in the GROUP
> BY clause, it finds a match for the index and uses it.
>
> Yes, the problem is O(n^2), where n is the number of columns in the
> GROUP BY, but n is always small; even 7 columns could be checked in
> less than 50 iterations.
>

I believe its O(n!), but still doable for small n.  I don't know the inner
workings of the query optimizer but mabye instead of asking/check for a
index of every permutation of the columns in the group by, it could just
check if an index exists which covers all columns (even the sorting order
doesn't matter). (the virtual table api needs an addition for that to work)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[James K. Lowden]

On Thu, 25 Apr 2013 10:29:34 +0200
Hick Gunter  wrote:

> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from
> hints in the virtual table description).

That might be so, in some limited sense.  It's obviously false in
general because they mean different things and have different effects.  

> If you have an index that covers the GROUP BY clause in field order,
> then aggregate functions need store only the current value; if not,
> then you need an ephemeral table to hold the aggregate values.

Nonsense.  The query parser sees GROUP BY A,B.  The optimizer sees an
index ordered B,A.  By permuting the order of the columns in the GROUP
BY clause, it finds a match for the index and uses it.  

Yes, the problem is O(n^2), where n is the number of columns in the
GROUP BY, but n is always small; even 7 columns could be checked in
less than 50 iterations.  

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Hick Gunter]

AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the 
virtual table description).

If you have an index that covers the GROUP BY clause in field order, then 
aggregate functions need store only the current value; if not, then you need an 
ephemeral table to hold the aggregate values.

In more detail:

IF you have an index that covers the GROUP BY clause in field order, then 
retrieving rows in index order guarantees that all rows belonging to the same 
group will be retrieved in one block AND makes the output rows come out sorted 
too. This allows SQLite to keep current aggregate values in registers.

IF you do not have an index that covers the GROUP BY clause, then rows will be 
retrieved in some deterministic order other than by group. You need to retrieve 
and update the current aggregate values for the group each row is in. This 
forces SQLite to keep current aggregate values in an ephemeral table.

IF you have an index that covers the GROUP BY clause in any other order, then 
you still have the guarantee that all rows belonging to the same group will be 
retrieved together, but the result rows will be ordered in index order and not 
GROUP BY order. This would probably require code paths presumably shared by 
GROUP BY and ORDER BY processing to be split.


-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Donnerstag, 25. April 2013 01:55
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Order of columns in group by statement affects query 
performance

On Wed, 24 Apr 2013 17:46:00 +0100
Simon Slavin <slav...@bigfraud.org> wrote:

> On 24 Apr 2013, at 5:14pm, Igor Tandetnik <i...@tandetnik.org> wrote:
> > Note though that the query doesn't have an ORDER BY clause. It
> > doesn't request rows in any particular order. SQLite could, in
> > principle, reorder columns in GROUP BY to take advantage of the
> > index. I suppose the optimizer just happens to miss this particular
> > opportunity.
>
> But the GROUP BY clause has an order:
>
> >> Query 1:  SELECT A,B,count(*) from tableTest group by A,B Query 2:
> >> SELECT A,B,count(*) from tableTest group by B,A

The order in which the columns appear syntactically in the GROUP BY clause is 
meaningless in SQL.

Igor is correct that the query processor could use any index beginning with B,A 
or A,B, should it so choose.

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[James K. Lowden]

On Wed, 24 Apr 2013 17:46:00 +0100
Simon Slavin  wrote:

> On 24 Apr 2013, at 5:14pm, Igor Tandetnik  wrote:
> > Note though that the query doesn't have an ORDER BY clause. It
> > doesn't request rows in any particular order. SQLite could, in
> > principle, reorder columns in GROUP BY to take advantage of the
> > index. I suppose the optimizer just happens to miss this particular
> > opportunity.
> 
> But the GROUP BY clause has an order:
> 
> >> Query 1:  SELECT A,B,count(*) from tableTest group by A,B
> >> Query 2:  SELECT A,B,count(*) from tableTest group by B,A

The order in which the columns appear syntactically in the GROUP BY
clause is meaningless in SQL.  

Igor is correct that the query processor could use any index beginning
with B,A or A,B, should it so choose.  

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Simon Slavin]

On 24 Apr 2013, at 5:14pm, Igor Tandetnik  wrote:

> Note though that the query doesn't have an ORDER BY clause. It doesn't 
> request rows in any particular order. SQLite could, in principle, reorder 
> columns in GROUP BY to take advantage of the index. I suppose the optimizer 
> just happens to miss this particular opportunity.

But the GROUP BY clause has an order:

>> Query 1:  SELECT A,B,count(*) from tableTest group by A,B
>> Query 2:  SELECT A,B,count(*) from tableTest group by B,A

which is pretty much the same as having two different ORDER BY clauses.  An 
index which is ideal for one of those is not ideal for the other.  I don't find 
it weird that the optimiser would decide to use an index for one of them but 
not the other.

To the original poster: do an ANALYZE, then see if you get different timings 
afterwards.

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Igor Tandetnik]

On 4/24/2013 11:49 AM, Larry Brasfield wrote:

*Daniel Winter wrote:0*

Table:  Column A int, Column B int, Column C int
One Index:  A,B  (combined)

Query 1:  SELECT A,B,count(*) from tableTest group by A,B
Query 2:  SELECT A,B,count(*) from tableTest group by B,A

Query 1 will use the index, while query 2 will not. (which makes Query 1 a
lot faster with bigger tables). Both querys will result with the same

data.

I do not really understand why it doesn't use the index for both querys.


The index has row references pre-sorted according to the criteria specified
for it.  That is why it can be used to speed up a query which sorts by
those criteria.


Note though that the query doesn't have an ORDER BY clause. It doesn't 
request rows in any particular order. SQLite could, in principle, 
reorder columns in GROUP BY to take advantage of the index. I suppose 
the optimizer just happens to miss this particular opportunity.

--
Igor Tandetnik

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

Re: [sqlite] Order of columns in group by statement affects query performance

[Larry Brasfield]

*Daniel Winter wrote:0*
> I discovered that the order of columns in a group by affects the
> performance of a query. Is this expected?

Yes.

> For example:
>
> Table:  Column A int, Column B int, Column C int
> One Index:  A,B  (combined)
>
> Query 1:  SELECT A,B,count(*) from tableTest group by A,B
> Query 2:  SELECT A,B,count(*) from tableTest group by B,A
>
> Query 1 will use the index, while query 2 will not. (which makes Query 1 a
> lot faster with bigger tables). Both querys will result with the same
data.
> I do not really understand why it doesn't use the index for both querys.

The index has row references pre-sorted according to the criteria specified
for it.  That is why it can be used to speed up a query which sorts by
those criteria.  There is no mystery as to why it does not help to speed up
other queries which do not sort by those criteria.  Looking at the query
plan would show the index used for your first query and not for the second.

A similar effect would be seen with joins which are typically processed as
a sifted merge of sorted components.

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

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Richard Hipp]

This issue is now recorded as http://www.sqlite.org/src/info/2500cdb9be

On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:

> I have a problem with a field name being ambiguous when it wasn't before.
>  Is this a bug or a feature?
>
> Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the ORDER
> BY in the below query:
>
> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
> title COLLATE NOCASE ASC
>
> I claim "title" shouldn't be ambiguous, since the first SELECT expression
> was named "title".  What's the consensus?
>
> -- Perry Wagle (wa...@mac.com)
>
> PS.  The SQL is autogenerated, and I didn't write the generator.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Perry Wagle]

On Apr 11, 2013, at 5:56 AM, Stephen Chrzanowski  wrote:

> Looking at the query, in the CLI, what if you rename the three bolded
> "title" fields to something else?  I think the system is getting messed up
> because a field in a table is called "title" and you're naming a query
> called "title" but you're not distinctly stating which "title" you want to
> query against, as in a table field or your query.  Not to mention it looks
> like you're doing the SORT BY twice against the same field in the same
> manor.  Mind you I have NOT tested this against anything.

I already tried that, and it failed.  I didn't try harder because the second
occurrence of title is there because the generating code supplies it, it
seems to be pulling fields out by name, the string "title" occurs about 150
times in the code, and I don't know which to change.  It also seems to
indicate that I need to rename all the names external to the query, not
just "title".  I will need to experiment harder.

Thanks!

> 
> sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS * title*,
> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url AS
> url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> folders.parent != 4 GROUP BY bms.id ORDER BY *title *COLLATE NOCASE ASC, 
> *title
> *COLLATE NOCASE ASC;
> 
> 
> 
> 
> On Wed, Apr 10, 2013 at 3:32 PM, Perry Wagle  wrote:
> 
>> 
>> On Apr 10, 2013, at 5:33 AM, Richard Hipp  wrote:
>> 
>>> On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:
>>> 
 I have a problem with a field name being ambiguous when it wasn't
>> before.
 Is this a bug or a feature?
 
 Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
 uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the
>> ORDER
 BY in the below query:
 
 SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
 bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
 (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
 moz_favicons.id = moz_places.favicon_id) END) AS favicon,
>> moz_places.url
 AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
 frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
 lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE
>> place_id
 = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
 WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
 moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
 moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
 folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
 title COLLATE NOCASE ASC
 
 I claim "title" shouldn't be ambiguous, since the first SELECT
>> expression
 was named "title".  What's the consensus?
 
>>> 
>>> There were enhancements to name resolution in 3.7.16.  Did you try it
>>> there?
>> 
>> See below:
>> 
>> SQLite version 3.7.16.1 2013-03-29 13:44:34
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE
>> (CASE bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS
>> title, (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
>> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
>> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
>> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
>> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
>> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
>> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
>> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
>> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
>> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
>> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
>> title COLLATE NOCASE ASC;
>> Error: ambiguous column name: title
>> 
>>> 
>>> --
>>> D. Richard Hipp
>>> 

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Stephen Chrzanowski]

Looking at the query, in the CLI, what if you rename the three bolded
"title" fields to something else?  I think the system is getting messed up
because a field in a table is called "title" and you're naming a query
called "title" but you're not distinctly stating which "title" you want to
query against, as in a table field or your query.  Not to mention it looks
like you're doing the SORT BY twice against the same field in the same
manor.  Mind you I have NOT tested this against anything.

sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS * title*,
(CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url AS
url, moz_places.visit_count AS visit_count, moz_places.frecency AS
frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
= moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
folders.parent != 4 GROUP BY bms.id ORDER BY *title *COLLATE NOCASE ASC, *title
*COLLATE NOCASE ASC;




On Wed, Apr 10, 2013 at 3:32 PM, Perry Wagle  wrote:

>
> On Apr 10, 2013, at 5:33 AM, Richard Hipp  wrote:
>
> > On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:
> >
> >> I have a problem with a field name being ambiguous when it wasn't
> before.
> >> Is this a bug or a feature?
> >>
> >> Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
> >> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the
> ORDER
> >> BY in the below query:
> >>
> >> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
> >> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
> >> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> >> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> >> moz_favicons.id = moz_places.favicon_id) END) AS favicon,
> moz_places.url
> >> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> >> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> >> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE
> place_id
> >> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> >> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> >> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> >> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> >> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
> >> title COLLATE NOCASE ASC
> >>
> >> I claim "title" shouldn't be ambiguous, since the first SELECT
> expression
> >> was named "title".  What's the consensus?
> >>
> >
> > There were enhancements to name resolution in 3.7.16.  Did you try it
> > there?
>
> See below:
>
> SQLite version 3.7.16.1 2013-03-29 13:44:34
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE
> (CASE bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS
> title, (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
> title COLLATE NOCASE ASC;
> Error: ambiguous column name: title
>
> >
> > --
> > 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Perry Wagle]

On Apr 10, 2013, at 12:40 PM, Richard Hipp  wrote:

> On Wed, Apr 10, 2013 at 3:37 PM, Perry Wagle  wrote:
> 
>> 
>> On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski 
>> wrote:
>> 
>>> Has the schema changed on one of the underlying table between 19 and 20
>>> that includes a field called Title?
>> 
>> I did the query (see my previous response) with a different version of
>> sqlite3 (3.7.16.1) on a places database on a system running firefox 19
>> (which works), and got the error.
>> 
> 
> Thanks.  I have your issue and can reproduce the problem.
> 
> We are chasing some higher priority issues right this moment, so it might
> be a while before I have a chance to look into this.

Ok, I understand.

Any suggestions of things to look into for work-arounds?  My current workaround 
is a kludge, is fragile, and is not general enough (add a last field in the 
select for threadx, and sort on that).  The receiving code is complex, and 
expects the first column to be named "title".


> 
> 
> -- 
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Richard Hipp]

On Wed, Apr 10, 2013 at 3:37 PM, Perry Wagle  wrote:

>
> On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski 
> wrote:
>
> > Has the schema changed on one of the underlying table between 19 and 20
> > that includes a field called Title?
>
> I did the query (see my previous response) with a different version of
> sqlite3 (3.7.16.1) on a places database on a system running firefox 19
> (which works), and got the error.
>

Thanks.  I have your issue and can reproduce the problem.

We are chasing some higher priority issues right this moment, so it might
be a while before I have a chance to look into this.


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

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Perry Wagle]

On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski  wrote:

> Has the schema changed on one of the underlying table between 19 and 20
> that includes a field called Title?

I did the query (see my previous response) with a different version of sqlite3 
(3.7.16.1) on a places database on a system running firefox 19 (which works), 
and got the error.

> 
> 
> On Wed, Apr 10, 2013 at 8:33 AM, Richard Hipp  wrote:
> 
>> On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:
>> 
>>> I have a problem with a field name being ambiguous when it wasn't before.
>>> Is this a bug or a feature?
>>> 
>>> Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
>>> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the
>> ORDER
>>> BY in the below query:
>>> 
>>> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
>>> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
>>> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
>>> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
>>> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
>>> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
>>> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
>>> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE
>> place_id
>>> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
>>> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
>>> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
>>> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
>>> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
>>> title COLLATE NOCASE ASC
>>> 
>>> I claim "title" shouldn't be ambiguous, since the first SELECT expression
>>> was named "title".  What's the consensus?
>>> 
>> 
>> There were enhancements to name resolution in 3.7.16.  Did you try it
>> there?
>> 
>> --
>> 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Perry Wagle]

On Apr 10, 2013, at 5:33 AM, Richard Hipp  wrote:

> On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:
> 
>> I have a problem with a field name being ambiguous when it wasn't before.
>> Is this a bug or a feature?
>> 
>> Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
>> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the ORDER
>> BY in the below query:
>> 
>> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
>> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
>> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
>> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
>> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
>> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
>> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
>> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
>> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
>> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
>> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
>> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
>> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
>> title COLLATE NOCASE ASC
>> 
>> I claim "title" shouldn't be ambiguous, since the first SELECT expression
>> was named "title".  What's the consensus?
>> 
> 
> There were enhancements to name resolution in 3.7.16.  Did you try it
> there?

See below:

SQLite version 3.7.16.1 2013-03-29 13:44:34
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE 
bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title, (CASE 
moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT 'moz-anno:favicon:' || 
moz_favicons.url FROM moz_favicons WHERE moz_favicons.id = 
moz_places.favicon_id) END) AS favicon, moz_places.url AS url, 
moz_places.visit_count AS visit_count, moz_places.frecency AS frecency, bms.id 
AS id, bms.dateAdded AS dateAdded, bms.lastModified AS lastModified, (SELECT 
max(visit_date) FROM moz_historyvisits WHERE place_id = moz_places.id) AS 
visit_date FROM (SELECT fk AS id FROM moz_bookmarks WHERE parent IN (6068538) 
GROUP BY fk HAVING count(*) = 1) AS t, moz_places, moz_bookmarks AS folders, 
moz_bookmarks AS bms WHERE t.id = moz_places.id AND t.id = bms.fk AND 
bms.parent = folders.id AND folders.parent != 4 GROUP BY bms.id ORDER BY title 
COLLATE NOCASE ASC, title COLLATE NOCASE ASC;
Error: ambiguous column name: title

> 
> -- 
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Stephen Chrzanowski]

Has the schema changed on one of the underlying table between 19 and 20
that includes a field called Title?


On Wed, Apr 10, 2013 at 8:33 AM, Richard Hipp  wrote:

> On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:
>
> > I have a problem with a field name being ambiguous when it wasn't before.
> >  Is this a bug or a feature?
> >
> > Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
> > uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the
> ORDER
> > BY in the below query:
> >
> > SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
> > bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
> > (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> > 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> > moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
> > AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> > frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> > lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE
> place_id
> > = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> > WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> > moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> > moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> > folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
> > title COLLATE NOCASE ASC
> >
> > I claim "title" shouldn't be ambiguous, since the first SELECT expression
> > was named "title".  What's the consensus?
> >
>
> There were enhancements to name resolution in 3.7.16.  Did you try it
> there?
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

[Richard Hipp]

On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle  wrote:

> I have a problem with a field name being ambiguous when it wasn't before.
>  Is this a bug or a feature?
>
> Firefox 19 uses Sqlite 3.7.14.1 which does what it should.  Firefox 20
> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the ORDER
> BY in the below query:
>
> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE
> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title,
> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT
> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE
> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url
> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS
> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS
> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id
> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks
> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t,
> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id =
> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND
> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC,
> title COLLATE NOCASE ASC
>
> I claim "title" shouldn't be ambiguous, since the first SELECT expression
> was named "title".  What's the consensus?
>

There were enhancements to name resolution in 3.7.16.  Did you try it
there?

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

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

[Dominique Devienne]

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy  wrote:
> On 03/14/2013 12:09 AM, Dominique Devienne wrote:
>> I stumbled upon
>>
http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed
>>
>> Is this just happenstance in this case or will it work every-time there
are
>> similar "pseudo cycles" that can be "broken" by processing the ON DELETE
>> CASCADE in a given order?
>>
>> If it works every-time, what's the underlying mechanism that guarantees
>> that? Some kind of topological sort between tables based on foreign keys?
>> Or perhaps the fact that FKs are enforced "lazily", after all rows have
>> been deleted?
>
> In this case, the statement deletes a row from the parent
> table. Which increments the counter by 2, as there are now
> 2 child rows without a parent. The ON DELETE CASCADE logic
> causes it to delete the row from the uncle table, which
> decrements the counter. Then the same again for the child
> table. The counter is then zero and the statement is committed.

Makes perfect sense. Thank you for this precision. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

[Dan Kennedy]

On 03/14/2013 12:09 AM, Dominique Devienne wrote:

I stumbled upon
http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand
tried a simplified version in SQLite3, which appears to work fine (see
below).

Is this just happenstance in this case or will it work every-time there are
similar "pseudo cycles" that can be "broken" by processing the ON DELETE
CASCADE in a given order?

If it works every-time, what's the underlying mechanism that guarantees
that? Some kind of topological sort between tables based on foreign keys?
Or perhaps the fact that FKs are enforced "lazily", after all rows have
been deleted?


I don't see any reason this won't work in SQLite.

During statement execution, SQLite uses a single counter to
keep track of whether or not it should raise an FK constraint
error instead of committing the results once the statement
has finished. The counter is incremented each time an FK
violation is introduced into the db, and decremented each time
one is removed. If the counter is greater than zero when the
statement is done executing, throw an error.

In this case, the statement deletes a row from the parent
table. Which increments the counter by 2, as there are now
2 child rows without a parent. The ON DELETE CASCADE logic
causes it to delete the row from the uncle table, which
decrements the counter. Then the same again for the child
table. The counter is then zero and the statement is committed.


This 4 years old SO post indicated both MySQL(InnoDB) and PostgreSQL failed
a similar test.


Surely they pass now though...





Just curious to know this is a behavior I can rely on or not.

Thanks, --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1
sqlite> create table parent (id text primary key);
sqlite> create table child  (id text primary key,
...> parent text references parent(id) on delete cascade);
sqlite> create table uncle  (id text primary key,
...> parent text references parent(id) on delete cascade,
...> child  text references  child(id) on delete restrict);
sqlite> insert into parent values ('daddy');
sqlite> insert into child  values ('cindy', 'daddy');
sqlite> insert into uncle  values ('bobby', 'daddy', 'cindy');
sqlite> .header on
sqlite> select * from parent;
id
daddy
sqlite> select * from child;
id|parent
cindy|daddy
sqlite> select * from uncle;
id|parent|child
bobby|daddy|cindy
sqlite> delete from parent where id = 'daddy';
sqlite> select * from parent;
sqlite> select * from child;
sqlite> select * from uncle;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Simon Slavin]

On 15 Jan 2013, at 1:58pm, Ryan Johnson  wrote:

> On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:
>> i would like to "seed random" a request, so i could do use some pagination
>> system with a order by random(), is this possible sqlite ? when a tried to
>> use "order by 1234567892" it always return the same order

sqlite> CREATE TABLE myTable (ax TEXT, myOrder INTEGER);
sqlite> INSERT INTO myTable (ax) VALUES ('fred');
sqlite> INSERT INTO myTable (ax) VALUES ('wilma');
sqlite> INSERT INTO myTable (ax) VALUES ('barney');
sqlite> INSERT INTO myTable (ax) VALUES ('betty');
sqlite> INSERT INTO myTable (ax) VALUES ('pebbles');
sqlite> INSERT INTO myTable (ax) VALUES ('bamm bamm');
sqlite> SELECT * FROM myTable;
fred|
wilma|
barney|
betty|
pebbles|
bamm bamm|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
wilma|
bamm bamm|
fred|
barney|
pebbles|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
pebbles|
fred|
wilma|
barney|
bamm bamm|

> That's because you told sqlite that the distinguishing feature of a tuple is 
> the constant '1234567892' . The sorting routine will see "t1 < t2 = false" 
> and "t1 > t2 = false" (implying t1 = t2) no matter which two tuples it 
> examines. If sqlite uses a stable sorting algorithm, that will leave the 
> input untouched. A very expensive no-op, in other words.
> 
> With random(), t1 < t2 is undefined (because it turns out different every 
> time it's evaluated), and you end up with tuples in a random order (assuming 
> the sorting routine doesn't crash; code usually assumes values don't change 
> while being sorted). Note that shuffling directly is more efficient than 
> "sorting" in this way, though sqlite lacks the necessary "shuffle" operator 
> to do it in-database.

If you're going to search and seek you actually have to have the random values 
stored, because you need to get consistent results when you look for/at a 
value.  One way to get rows in a random order is to put an extra column in your 
table, with an INTEGER affinity and random values.  When you want to shuffle 
the order just update the values in the table.  Then you can do an "ORDER BY 
myOrder" and everything comes out consistently:

sqlite> UPDATE myTable SET myOrder=random();
sqlite> SELECT * FROM myTable ORDER BY myOrder;
barney|-8843778138112958223
bamm bamm|-7611867930025301398
pebbles|-5851529134925141201
wilma|-2528762072213537603
betty|853939383612226875
fred|8581781187410411890

There's no problem with ordering by these 'huge numbers'.

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[François-xavier Jacobs]

Indeed shuffle was my goal

i solve it by doing order by "((rowid * "+seed.longValue()+" ) %
"+BIG_PRIME_NUMBER+")";

as long ass the seed doe not change result is the same, so i can shuffle
with LIMT

Regards


2013/1/15 Ryan Johnson 

> On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:
>
>> Hi everyone
>>
>> i would like to "seed random" a request, so i could do use some pagination
>> system with a order by random(), is this possible sqlite ? when a tried to
>> use "order by 1234567892" it always return the same order
>>
> That's because you told sqlite that the distinguishing feature of a tuple
> is the constant '1234567892' . The sorting routine will see "t1 < t2 =
> false" and "t1 > t2 = false" (implying t1 = t2) no matter which two tuples
> it examines. If sqlite uses a stable sorting algorithm, that will leave the
> input untouched. A very expensive no-op, in other words.
>
> With random(), t1 < t2 is undefined (because it turns out different every
> time it's evaluated), and you end up with tuples in a random order
> (assuming the sorting routine doesn't crash; code usually assumes values
> don't change while being sorted). Note that shuffling directly is more
> efficient than "sorting" in this way, though sqlite lacks the necessary
> "shuffle" operator to do it in-database.
>
> Ryan
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Clemens Ladisch]

François-xavier Jacobs wrote:
> when a tried to use "order by 1234567892" it always return the same order

Because this is the *same* value for all records.

> i would like to "seed random" a request, so i could do use some pagination
> system with a order by random(), is this possible sqlite ?

Calls to random() are never repeatable.

I'd suggest to
1) read all IDs of the table into memory;
2) shuffle these IDs randomly; and
3) explicitly load the records for one 'page' of ID values.

Alterntively to step 3, you could save the indexes of the shuffled
array entries back into a new field in the table so that you have
a column that you can do an ORDER BY on.


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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Igor Tandetnik]

On 1/14/2013 9:48 AM, François-xavier Jacobs wrote:

i would like to "seed random" a request, so i could do use some pagination
system with a order by random(), is this possible sqlite ? when a tried to
use "order by 1234567892" it always return the same order


random() returns a different value on every call. So the rows for which 
it just happens to return a smaller value get sorted before rows for 
which it happens to return a larger value.


When you do "order by 1234567892", each row gets associated with the 
same value, so the ORDER BY clause doesn't induce any particular order. 
It doesn't matter whether the value is large or small. I'm not sure what 
good you expected such a clause to achieve.


I'm also really curious as to what line of reasoning has led you to 
conclude that using a large constant value would do something different 
than using a small constant value. What kind of magic do you believe 
"huge numbers" possess that small numbers don't?

--
Igor Tandetnik

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Marc L. Allen]

If you literally used "ORDER BY 1234567892" then there's nothing in the record 
being sorted.

I can't recall is SQLite allows order by aliases, but something like..

Select ..., random() as X
Order by X

Might work, as long as random() is executed for each row.

(Sorry.. don't have a quick SQLite engine available to test)

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of François-xavier Jacobs
Sent: Monday, January 14, 2013 9:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] order by "a huge number" does not work, but "random" well why 
?

Hi everyone

i would like to "seed random" a request, so i could do use some pagination 
system with a order by random(), is this possible sqlite ? when a tried to use 
"order by 1234567892" it always return the same order 
___

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Ryan Johnson]

On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:

Hi everyone

i would like to "seed random" a request, so i could do use some pagination
system with a order by random(), is this possible sqlite ? when a tried to
use "order by 1234567892" it always return the same order
That's because you told sqlite that the distinguishing feature of a 
tuple is the constant '1234567892' . The sorting routine will see "t1 < 
t2 = false" and "t1 > t2 = false" (implying t1 = t2) no matter which two 
tuples it examines. If sqlite uses a stable sorting algorithm, that will 
leave the input untouched. A very expensive no-op, in other words.


With random(), t1 < t2 is undefined (because it turns out different 
every time it's evaluated), and you end up with tuples in a random order 
(assuming the sorting routine doesn't crash; code usually assumes values 
don't change while being sorted). Note that shuffling directly is more 
efficient than "sorting" in this way, though sqlite lacks the necessary 
"shuffle" operator to do it in-database.


Ryan

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

Re: [sqlite] order by clause should come after union not before

[Keith Medcalf]

 select * 
   from (select * 
   from hbc 
   order by cmc desc 
  limit 10) as ss
union
 select * 
   from hbc 
  where qph>0 
 or hctl=1;



---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of YAN HONG YE
> Sent: Friday, 15 June, 2012 03:57
> To: sqlite-users@sqlite.org
> Subject: [sqlite] order by clause should come after union not before
> 
> select *  from hbc order by cmc desc limit 10
> union
> select * from hbc where qph>0
> union
> select * from hbc where hctl=1
> 
> this sql cmd cause the error:
> order by clause should come after union not before
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] order by clause should come after union not before

[Igor Tandetnik]

YAN HONG YE  wrote:
> select *  from hbc order by cmc desc limit 10
> union
> select * from hbc where qph>0
> union
> select * from hbc where hctl=1
> 
> this sql cmd cause the error:
> order by clause should come after union not before

select * from hbc where rowid in (select rowid from hbc order by cmc desc limit 
10)
union
select * from hbc where qph>0
union
select * from hbc where hctl=1;

A shorter, though not strictly equivalent, version:

select * from hbc where
rowid in (select rowid from hbc order by cmc desc limit 10)
or qph>0 or hctl=1;

If there are several identical rows in hdc, then the original version keeps 
just one copy of them, while this version may return more than one.

The third variant is equivalent to the second. Depending on which indexes you 
have on the table and which SQLite version you are using, it may be more 
efficient:

select * from hbc where rowid in (
select rowid from (select rowid from hbc order by cmc desc limit 10)
uinon
select rowid from hbc where qph>0
union
select rowid from hbc where hctl=1
);

-- 
Igor Tandetnik

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

Re: [sqlite] order by clause should come after union not before

[Jay A. Kreibich]

On Fri, Jun 15, 2012 at 08:10:20AM -0400, Kevin Benson scratched on the wall:
> On Fri, Jun 15, 2012 at 5:56 AM, YAN HONG YE  wrote:
> >
> > select *  from hbc order by cmc desc limit 10
> > union
> > select * from hbc where qph>0
> > union
> > select * from hbc where hctl=1
> >
> > this sql cmd cause the error:
> > order by clause should come after union not before
> >
> 
> http://www.sqlite.org/lang_select.html
> 
> ORDER BY and LIMIT clauses may only occur at the end of the entire compound
> SELECT.

  You can, however, convert the first SELECT from a clause in the
  compound statement to a sub-select within the first clause.  In that
  case the ORDER BY/LIMIT will be enfoced by the sub-select.

  You should also collapse the second and third clause into a single
  SELECT...   "select * from hbc where aph>0 or hctl=1"  That will also
  avoid duplicate rows if a specific row meets both conditions.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users