Re: [sqlite] Documentation of valid ORDER BY terms after UNION?

[David Raymond]

The order by terms can still be complex expressions, they just have to match 
exactly (to all sub-queries in the join I believe)

create table table1 (field1, field2);
select field1 > 0 as foo, field2 + 42 as bar from table1
union all 
select field1 > 0 as foo, field2 - 42 as bar from table1...

order by foo; -> OK as simple column name.
order by foo > 0; -> Not OK as an expression on a column name.
order by field1 > 0; -> OK as it exactly matches a term in all of the sub 
queries, (so basically the same as ordering by a column name)


Ok, hmm, apparently it still works if it's only used in 1 of the unions.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table table1 (field1 int, field2 int);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> insert into table1 values (1, 1), (2, 2), (-1, -1), (7, 7);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> select field1 > 0, field2, 1 as term from table1 union all select 
field1 > 1, field2, 2 as term from table1 order by field1 > 1, term, field2;
--EQP-- 1,0,0,SCAN TABLE table1
--EQP-- 1,0,0,USE TEMP B-TREE FOR ORDER BY
--EQP-- 2,0,0,SCAN TABLE table1
--EQP-- 2,0,0,USE TEMP B-TREE FOR ORDER BY
--EQP-- 0,0,0,COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
field1 > 0|field2|term
0|-1|1
0|-1|2
0|1|2
1|1|1
1|2|1
1|7|1
1|2|2
1|7|2
Run Time: real 0.016 user 0.00 sys 0.00

So it named the final result column based on the name given in the first 
subquery,
let us put an expression from the second sub query into the order by term,
and used it simply as "order by the first column" when it unioned everything.

So yes, it looks like you can *only* order by straight up columns any not any 
expression of their values.

However... you can put "an expression" in there, but rather than being an 
actual expression, it serves only to identify which final result column you 
want to order by. To do so it goes through the various sub queries in order, 
trying to match the text of your order term to a result field name, then to a 
result field expression, and if finds it, it translates that to a final result 
column of the union for the ordering. And if it doesn't find a match it gives 
the error you received.

(Brain is melted at end of the day, so hopefully that all made a little sense. 
I'm sure someone can re-word that better than I just did.)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of no...@null.net
Sent: Monday, May 15, 2017 4:31 PM
To: SQLite mailing list
Subject: [sqlite] Documentation of valid ORDER BY terms after UNION?

I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:

"However, if the SELECT is a compound SELECT, then ORDER BY
expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column. "

If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions.


[1] https://www.sqlite.org/lang_select.html

-- 
Mark Lawrence
___
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] Documentation of valid ORDER BY terms after UNION?

[nomad]

On Mon May 15, 2017 at 09:58:31PM +0100, Simon Slavin wrote:
> 
> On 15 May 2017, at 9:30pm, no...@null.net wrote:
> 
> >SELECT
> >1 AS col
> >UNION ALL
> >SELECT
> >0 AS col
> >ORDER BY
> >col > 0 DESC;
> 
> Out of interest, intuitively rather than reading documentation, which
> do you think should be done first ?  Should SQL do both SELECTs and
> the UNION ALL, then ORDER the result ?  Or should SQL apply the ORDER
> BY to the second SELECT only ?

Intuitively (or according the union knowledge I can usually hold in my
brain) I think of the above as follows, purely because I know there can
only be one ORDER by statement:

(
SELECT
1 AS col
UNION ALL
SELECT
0 AS col
)
ORDER BY
col > 0 DESC;

> > I've read "The ORDER BY clause" of [1] and I *think* that the following
> > is what I am running into:
> > 
> >"However, if the SELECT is a compound SELECT, then ORDER BY
> >expressions that are not aliases to output columns must be exactly
> >the same as an expression used as an output column. "
> 
> Right.  So the problem is that the "AS col" clauses apply to the
> individual SELECT queries, not to the results of the UNION.  The
> UNION command unites the two individual queries but SQL doesn’t
> provide any way to name the resulting column(s).

Except that you *can* use the first SELECT aliases on their own...
which shouldn't be possible at all if SQL doesn't provide a way to name
the resulting columns.

> Here’s another question about intuition, rather than reading
> documentation.  How many columns should this query return ?  Or
> should it result in an error ?
> 
> >SELECT
> >1 AS betty
> >UNION ALL
> >SELECT
> >0 AS carlos;

Ideally this would be an error, but I already know that it isn't so I
can't really say what my intuition thinks :-)

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

Re: [sqlite] Documentation of valid ORDER BY terms after UNION?

[Simon Slavin]

On 15 May 2017, at 9:30pm, no...@null.net wrote:

>SELECT
>1 AS col
>UNION ALL
>SELECT
>0 AS col
>ORDER BY
>col > 0 DESC;

Out of interest, intuitively rather than reading documentation, which do you 
think should be done first ?  Should SQL do both SELECTs and the UNION ALL, 
then ORDER the result ?  Or should SQL apply the ORDER BY to the second SELECT 
only ?

> I've read "The ORDER BY clause" of [1] and I *think* that the following
> is what I am running into:
> 
>"However, if the SELECT is a compound SELECT, then ORDER BY
>expressions that are not aliases to output columns must be exactly
>the same as an expression used as an output column. "

Right.  So the problem is that the "AS col" clauses apply to the individual 
SELECT queries, not to the results of the UNION.  The UNION command unites the 
two individual queries but SQL doesn’t provide any way to name the resulting 
column(s).

Here’s another question about intuition, rather than reading documentation.  
How many columns should this query return ?  Or should it result in an error ?

>SELECT
>1 AS betty
>UNION ALL
>SELECT
>0 AS carlos;


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

[sqlite] Documentation of valid ORDER BY terms after UNION?

[nomad]

This works:

SELECT
1 AS col
ORDER BY
col > 0 DESC;

The following fails with "Error: 1st ORDER BY term does not match any
column in the result set."

SELECT
1 AS col
UNION ALL
SELECT
0 AS col
ORDER BY
col > 0 DESC;

I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:

"However, if the SELECT is a compound SELECT, then ORDER BY
expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column. "

If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions. One could also perhaps explicitly suggest
what appears to be a valid workaround:

SELECT
*
FROM
(SELECT
1 AS col,
1 > 0 AS truth
UNION ALL
SELECT
0 AS col,
0 > 0 AS truth
) x
ORDER BY
x.col > 0 DESC;

[1] https://www.sqlite.org/lang_select.html

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

[sqlite] System.Data.SQLite version 1.0.105.1 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.105.1 (with SQLite 3.18.0) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

Re: [sqlite] -shm and -wal files left after trivially simple select statement from command line

[Rob Willett]

Richard, J Decker,

Just to close this down.

The command was

select * from device;

We used .quit and Ctrl-D to exit the shell

The command sequence was as simple as

sqlite3 DATABASE.sqlite

We knew about things like less and head and so we avoided them.

The version of Sqlite seems to be the problem, the standard one with 
Ubuntu 14.04 LTS is 3.8.2 which we know is old, but until now seems to 
have been fine.


Upgrading our production servers to Ubuntu 16 LTS is a massive 
undertaking. We have nine of them with test and preprod so upgrading and 
testing everything again is many, many weeks work :(


We've upgraded Sqlite CLI to the latest 3.18.0 and are trying this out 
in a nice safe environment.


Thanks

Rob

On 15 May 2017, at 12:38, Richard Hipp wrote:


On 5/15/17, Richard Hipp  wrote:

On 5/15/17, Rob Willett  wrote:


As part of the testing we noticed that -shm and -wal files were 
being
left after we used sqlite3 on the command line. This puzzled us as 
we

didn't see any errors in our test scripts.


How is the command being ended.  Are you inserting a ".quit" command?
Are you somehow causing the shell to terminate prematurely, perhaps 
by
piping the output into a filter (such as "head") that closes the 
input

connection early?  What version of the SQLite shell is running?  What
is the query?


Never mind - it appears that JDecker already knew the problem and the
solution

--
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

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

Re: [sqlite] -shm and -wal files left after trivially simple select statement from command line

[Richard Hipp]

On 5/15/17, Richard Hipp  wrote:
> On 5/15/17, Rob Willett  wrote:
>>
>> As part of the testing we noticed that -shm and -wal files were being
>> left after we used sqlite3 on the command line. This puzzled us as we
>> didn't see any errors in our test scripts.
>
> How is the command being ended.  Are you inserting a ".quit" command?
> Are you somehow causing the shell to terminate prematurely, perhaps by
> piping the output into a filter (such as "head") that closes the input
> connection early?  What version of the SQLite shell is running?  What
> is the query?

Never mind - it appears that JDecker already knew the problem and the
solution

-- 
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] -shm and -wal files left after trivially simple select statement from command line

[Richard Hipp]

On 5/15/17, Rob Willett  wrote:
>
> As part of the testing we noticed that -shm and -wal files were being
> left after we used sqlite3 on the command line. This puzzled us as we
> didn't see any errors in our test scripts.

How is the command being ended.  Are you inserting a ".quit" command?
Are you somehow causing the shell to terminate prematurely, perhaps by
piping the output into a filter (such as "head") that closes the input
connection early?  What version of the SQLite shell is running?  What
is the query?

-- 
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] -shm and -wal files left after trivially simple select statement from command line

[Rob Willett]

Thanks for this. This seems to be the old version of SQLite that comes 
with Ubuntu. We'll upgrade.


Just to be clear we do close the connection as all we do is open the 
database using sqlite3 from the command line, do a select and close 
sqlite3 from the command line.


We do not do anything that modifies the database.

We'll have to upgrade the version of SQLite, test and then upgrade our 
database.


Thanks

Rob

On 15 May 2017, at 12:09, J Decker wrote:

that was an old issue... current sqlite version do not have this 
issue.

You MUST close the connection though, for the files to get deleted.

http://sqlite.1065341.n5.nabble.com/journal-files-not-always-removed-td83700.html#a83705

On Mon, May 15, 2017 at 3:01 AM, Rob Willett 


wrote:


Hi,

We've encountered what we think is an odd situation and we can't find 
any
explanation for why this is. We're also not sure if its a problem or 
not.


A brief summary is that we are doing a major database upgrade and are
doing (lots) of testing. The box is a Linux box running Ubuntu 14.04 
LTS
Server. This is a standard box we have used for a few years, we 
*might*

move to 16 LTS shortly.

As part of the testing we noticed that -shm and -wal files were being 
left
after we used sqlite3 on the command line. This puzzled us as we 
didn't see

any errors in our test scripts.

We then narrowed this problem down to doing a simple SELECT statement 
on a

table.

e.g. This is our starting state. Note that nothing is touching these
database as they are isolated in a directory, so there is no other 
process

playing with them.

root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66892
-rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
-rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38 
accountmanagement.sqlite.orig


root@demonotifications:/jambuster/notifications/upgrade_test# cp
accountmanagement.sqlite.orig accountmanagement.sqlite

root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3
accountmanagement.sqlite
-- Loading resources from /root/.sqliterc

SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from device;
UUID isValid 
Version

   WhatAmI Application
---  --
--  --  --
<  1   1.1
   ios_app JambusterForLondon
.. Hundreds more lines of device data which looks correct
<  1   1.1
   ios_app JambusterForLondon

sqlite> .exit
root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66924
-rw-r--r-- 1 root root32768 May 15 10:48 
accountmanagement.sqlite-shm
-rw-r--r-- 1 root root0 May 15 10:48 
accountmanagement.sqlite-wal

-rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
-rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38 
accountmanagement.sqlite.orig

root@demonotifications:/jambuster/notifications/upgrade_test


As can be seen above, we have accountmanagement.sqlite-shm and
accountmanagement.sqlite-wal files left which is odd. All of this 
from a

select statement.

If we do an pragma integrity check we get

  integrity_check
  ---
  ok

All the reading we have done seems to indicate that having -shm and 
-wal
files indicates an error, if this was our code, we'd be looking deep 
into
that but a simple select statement shouldn't cause this sort or 
error, can

it?

The select statement has a number of foreign key delete cascades but 
thats
it, and as we are not deleting anything this shouldn't be an issue 
anyway.


Do we even have a problem? Going back into the database and closing 
it
again, just with the command line utility sqlite3 doesn't clear the 
files.


Any help or suggestions welcomed.

Thanks

Rob
___
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] -shm and -wal files left after trivially simple select statement from command line

[J Decker]

that was an old issue... current sqlite version do not have this issue.
You MUST close the connection though, for the files to get deleted.

http://sqlite.1065341.n5.nabble.com/journal-files-not-always-removed-td83700.html#a83705

On Mon, May 15, 2017 at 3:01 AM, Rob Willett 
wrote:

> Hi,
>
> We've encountered what we think is an odd situation and we can't find any
> explanation for why this is. We're also not sure if its a problem or not.
>
> A brief summary is that we are doing a major database upgrade and are
> doing (lots) of testing. The box is a Linux box running Ubuntu 14.04 LTS
> Server. This is a standard box we have used for a few years, we *might*
> move to 16 LTS shortly.
>
> As part of the testing we noticed that -shm and -wal files were being left
> after we used sqlite3 on the command line. This puzzled us as we didn't see
> any errors in our test scripts.
>
> We then narrowed this problem down to doing a simple SELECT statement on a
> table.
>
> e.g. This is our starting state. Note that nothing is touching these
> database as they are isolated in a directory, so there is no other process
> playing with them.
>
> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
> total 66892
> -rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
> -rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
> -rw-r--r-- 1 root root 34217984 May 13 20:38 accountmanagement.sqlite.orig
>
> root@demonotifications:/jambuster/notifications/upgrade_test# cp
> accountmanagement.sqlite.orig accountmanagement.sqlite
>
> root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3
> accountmanagement.sqlite
> -- Loading resources from /root/.sqliterc
>
> SQLite version 3.8.2 2013-12-06 14:53:30
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select * from device;
> UUID isValid Version
>WhatAmI Application
> ---  --
> --  --  --
> <  1   1.1
>ios_app JambusterForLondon
> .. Hundreds more lines of device data which looks correct
> <  1   1.1
>ios_app JambusterForLondon
>
> sqlite> .exit
> root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
> total 66924
> -rw-r--r-- 1 root root32768 May 15 10:48 accountmanagement.sqlite-shm
> -rw-r--r-- 1 root root0 May 15 10:48 accountmanagement.sqlite-wal
> -rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
> -rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
> -rw-r--r-- 1 root root 34217984 May 13 20:38 accountmanagement.sqlite.orig
> root@demonotifications:/jambuster/notifications/upgrade_test
>
>
> As can be seen above, we have accountmanagement.sqlite-shm and
> accountmanagement.sqlite-wal files left which is odd. All of this from a
> select statement.
>
> If we do an pragma integrity check we get
>
>   integrity_check
>   ---
>   ok
>
> All the reading we have done seems to indicate that having -shm and -wal
> files indicates an error, if this was our code, we'd be looking deep into
> that but a simple select statement shouldn't cause this sort or error, can
> it?
>
> The select statement has a number of foreign key delete cascades but thats
> it, and as we are not deleting anything this shouldn't be an issue anyway.
>
> Do we even have a problem? Going back into the database and closing it
> again, just with the command line utility sqlite3 doesn't clear the files.
>
> Any help or suggestions welcomed.
>
> Thanks
>
> Rob
> ___
> 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] -shm and -wal files left after trivially simple select statement from command line

[Rob Willett]

Hi,

We've encountered what we think is an odd situation and we can't find 
any explanation for why this is. We're also not sure if its a problem or 
not.


A brief summary is that we are doing a major database upgrade and are 
doing (lots) of testing. The box is a Linux box running Ubuntu 14.04 LTS 
Server. This is a standard box we have used for a few years, we *might* 
move to 16 LTS shortly.


As part of the testing we noticed that -shm and -wal files were being 
left after we used sqlite3 on the command line. This puzzled us as we 
didn't see any errors in our test scripts.


We then narrowed this problem down to doing a simple SELECT statement on 
a table.


e.g. This is our starting state. Note that nothing is touching these 
database as they are isolated in a directory, so there is no other 
process playing with them.


root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66892
-rw-r--r-- 1 root root 34217984 May 15 10:38 accountmanagement.sqlite
-rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38 
accountmanagement.sqlite.orig


root@demonotifications:/jambuster/notifications/upgrade_test# cp 
accountmanagement.sqlite.orig accountmanagement.sqlite


root@demonotifications:/jambuster/notifications/upgrade_test# sqlite3 
accountmanagement.sqlite

-- Loading resources from /root/.sqliterc

SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from device;
UUID isValid Version 
WhatAmI Application
---  --  
--  --  --
<  1   1.1 
ios_app JambusterForLondon

.. Hundreds more lines of device data which looks correct
<  1   1.1 
ios_app JambusterForLondon


sqlite> .exit
root@demonotifications:/jambuster/notifications/upgrade_test# ls -lt
total 66924
-rw-r--r-- 1 root root32768 May 15 10:48 
accountmanagement.sqlite-shm
-rw-r--r-- 1 root root0 May 15 10:48 
accountmanagement.sqlite-wal

-rw-r--r-- 1 root root 34217984 May 15 10:47 accountmanagement.sqlite
-rw-r--r-- 1 root root58567 May 15 10:31 upgradedb.sql
-rw-r--r-- 1 root root 34217984 May 13 20:38 
accountmanagement.sqlite.orig

root@demonotifications:/jambuster/notifications/upgrade_test


As can be seen above, we have accountmanagement.sqlite-shm and 
accountmanagement.sqlite-wal files left which is odd. All of this from a 
select statement.


If we do an pragma integrity check we get

  integrity_check
  ---
  ok

All the reading we have done seems to indicate that having -shm and -wal 
files indicates an error, if this was our code, we'd be looking deep 
into that but a simple select statement shouldn't cause this sort or 
error, can it?


The select statement has a number of foreign key delete cascades but 
thats it, and as we are not deleting anything this shouldn't be an issue 
anyway.


Do we even have a problem? Going back into the database and closing it 
again, just with the command line utility sqlite3 doesn't clear the 
files.


Any help or suggestions welcomed.

Thanks

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