Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Richard Hipp 
On 9/7/18, Firecore  wrote:
>
> It looks like the latest public version of sqlite v3.24.0 has an issue with
> processing queries with ORDER BY and LIMIT against some datasets.

Thanks for the bug report.  The trouble ticket can be found at

   https://www.sqlite.org/src/tktview/9936b2fa443fec03ff25f9b822528c20a2200a49
-- 
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] sql UPDATE schema

2018-09-07 Thread Simon Slavin 
On 8 Sep 2018, at 1:48am, Amno Jeeuw  wrote:

> the application generates the following schema represented on this message
> box:
> db_snapshot3.png
> 

This command is incorrect.  It should end in

WHERE id = 

The '=' is missing.  The GUI program you are using has a bug.

Since the SQLite development team did not write that program, this mailing list 
is not the best place to find help you with it.  I suggest you contact a 
support group for that piece of software.

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

Re: [sqlite] sql UPDATE schema

2018-09-07 Thread Keith Medcalf 

Your picture of the SQL statement is missing an operator -- your expression 
after WHERE is malformed.


---
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 Amno Jeeuw
>Sent: Friday, 7 September, 2018 18:49
>To: SQLite
>Subject: [sqlite] sql UPDATE schema
>
> I have a database table named company that looks like this:
> db_snapshot2.png
>ew?usp=drive_web>
>
>When the application is asked to change the value of ArbolOne to
>ArbolOn
> db_snapshot1.png
>DTq8D/view?usp=drive_web>
>
>
>the application generates the following schema represented on this
>message
>box:
> db_snapshot3.png
>ew?usp=drive_web>
>
>which produces this Exception error:
> db_snapshot4.png
>8DzKjqPrqd5nTOQIRgYV/view?usp=drive_web>
>
>I am attaching a snip of the code, in case you'd like to know it.
>Having said that, I'd like to know if this is a bug or if I have not
>understand the information found in here
>.
>
>Any help would be much appreciated.
>
>*ArbolOne
>Using Fire Fox and Thunderbird.
>Developing for Android using Java, C/C++, HTM/CSS and JS as our
>platform has been exciting and most rewarding.
>[ Sí ]*
>___
>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] Can you use ORDER BY clause in aggregate functions?

2018-09-07 Thread Keith Medcalf 
>Can you use ORDER BY clause in aggregate functions? It seems that you
>cannot; it is only available for window functions.
>However, sometimes is useful using ORDER BY with aggregate functions
>that aren't window functions, such as GROUP_CONCAT function.
>Therefore is the suggestion to add it if it doesn't already.

group_concat is already a window function ...




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

[sqlite] sql UPDATE schema

2018-09-07 Thread Amno Jeeuw 
 I have a database table named company that looks like this:
 db_snapshot2.png


When the application is asked to change the value of ArbolOne to ArbolOn
 db_snapshot1.png



the application generates the following schema represented on this message
box:
 db_snapshot3.png


which produces this Exception error:
 db_snapshot4.png


I am attaching a snip of the code, in case you'd like to know it.
Having said that, I'd like to know if this is a bug or if I have not
understand the information found in here
.

Any help would be much appreciated.

*ArbolOne
Using Fire Fox and Thunderbird.
Developing for Android using Java, C/C++, HTM/CSS and JS as our
platform has been exciting and most rewarding.
[ Sí ]*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Tim Streater 
On 07 Sep 2018, at 20:20, Richard Hipp  wrote:

> On 9/7/18, Firecore  wrote:
>>
>> It's easily reproduced with the sqlite3 client and db file available here
>> (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.

Running macOS Mavericks:

With SQLite version 3.14.0 2016-08-08 13:40:27 (as supplied with the OS) it 
worked instantly.

With SQLite version 3.24.0 2018-06-04 19:24:41 (downloaded from sqlite.org 
today) the reported issue occurred.



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

Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Richard Hipp 
On 9/7/18, Stephen Chrzanowski  wrote:
> I can reproduce this issue with the SQLite3.exe under Win7.
It depends on compile-time options.  I have a repro case now.
-- 
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] [EXTERNAL] Can you use ORDER BY clause in aggregate functions?

2018-09-07 Thread Hick Gunter 
I am not sure what you are trying to achieve. Can you give an example?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sql...@zzo38computer.org
Gesendet: Freitag, 07. September 2018 21:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Can you use ORDER BY clause in aggregate functions?

Can you use ORDER BY clause in aggregate functions? It seems that you cannot; 
it is only available for window functions.
However, sometimes is useful using ORDER BY with aggregate functions that 
aren't window functions, such as GROUP_CONCAT function.
Therefore is the suggestion to add it if it doesn't already.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Stephen Chrzanowski 
I can reproduce this issue with the SQLite3.exe under Win7.  The first
sqlite3 was pulled from a sqlite3.exe I have somewhere in my path, I then
extracted the 3.24.0 version to the Ramdrive and executed from there.  On
the first run, r:\sqlite3.exe did not exist.

R:\>sqlite3 lock_issue.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
B2A4025D165B97BF07B4489B30E4EC6F|/local/californication.S1.E4.mp4|1536215989.63828|778FF248-0A9E-44BE-BDAE-1B413A0C55A0|401460374.0

R:\>sqlite3.exe
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

R:\>sqlite3.exe lock_issue.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
Error: interrupted <- Pressed CTRL-C after about 5 seconds.
sqlite>


On Fri, Sep 7, 2018 at 3:20 PM, Richard Hipp  wrote:

> On 9/7/18, Firecore  wrote:
> >
> > It's easily reproduced with the sqlite3 client and db file available here
> > (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.
>
> --
> 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

[sqlite] Can you use ORDER BY clause in aggregate functions?

2018-09-07 Thread sqlite 
Can you use ORDER BY clause in aggregate functions? It seems that you cannot; 
it is only available for window functions.
However, sometimes is useful using ORDER BY with aggregate functions that 
aren't window functions, such as GROUP_CONCAT function.
Therefore is the suggestion to add it if it doesn't already.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Richard Hipp 
On 9/7/18, Firecore  wrote:
>
> It's easily reproduced with the sqlite3 client and db file available here
> (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

I cannot reproduce it.  It always runs instantly for me.  I also ran
it under valgrind with no issues reported.

-- 
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] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread David Raymond 
Other notes:

Order by ASC works ok.
Removing ORDER BY works ok.
Removing limit works ok.
*Limit of more than 1 works ok.

But order by DESC, limit 1 sits forever


As to work around, leave out the limit and just fetch one row, or do "...limit 
2" and just use the first.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Firecore
Sent: Friday, September 07, 2018 1:32 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Some queries with ORDER BY and LIMIT lock DB

Hi all -

It looks like the latest public version of sqlite v3.24.0 has an issue with 
processing queries with ORDER BY and LIMIT against some datasets. In essence, 
sqlite3_step returns SQLITE_LOCKED for some of these statments even if there 
are no other active connections. We were able to reproduce the issue both on 
iOS via C API and on Mac OS using the console sqlite3 client posted on the 
Downloads page (https://www.sqlite.org/download.html).

It's easily reproduced with the sqlite3 client and db file available here 
(https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

sqlite3 lock_issue.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, 
meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;


We've investigated the problem here and found the following which may be 
helpful:

 * The db file is valid (at least PRAGMA INTEGRITY_CHECK returns OK).

 * The issue doesn't reproduce if the query doesn't contain ORDER BY or LIMIT 
clause. 

 * The issue doesn't reproduce on sqlite v3.22.0 and older 
(https://www.sqlite.org/2018/sqlite-tools-osx-x86-322.zip).

 * The issue still reproduces after re-creating db from dump using sqlite 
v3.24.0 on MacOS:

sqlite3 lock_issue.db
sqlite> pragma integrity_check;
ok
sqlite> .output backup.db
sqlite> .dump
sqlite> .q
sqlite3 fixed.db
sqlite> .read backup.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;


  * The issue doesn't reproduce after creating index for FileIndex.Enabled and 
issuing ANALYZE after it:

sqlite3 lock_issue.db
sqlite> CREATE INDEX FileIndex_Enabled ON FileIndex(Enabled DESC);
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;

sqlite> ANALYZE;
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;


Do you know what may be going on here, and how we can work around it?

Thanks in advance!

James

—
Firecore, LLC
https://firecore.com
___
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] Some queries with ORDER BY and LIMIT lock DB

2018-09-07 Thread Firecore 
Hi all -

It looks like the latest public version of sqlite v3.24.0 has an issue with 
processing queries with ORDER BY and LIMIT against some datasets. In essence, 
sqlite3_step returns SQLITE_LOCKED for some of these statments even if there 
are no other active connections. We were able to reproduce the issue both on 
iOS via C API and on Mac OS using the console sqlite3 client posted on the 
Downloads page (https://www.sqlite.org/download.html).

It's easily reproduced with the sqlite3 client and db file available here 
(https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

sqlite3 lock_issue.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, 
meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;


We've investigated the problem here and found the following which may be 
helpful:

 * The db file is valid (at least PRAGMA INTEGRITY_CHECK returns OK).

 * The issue doesn't reproduce if the query doesn't contain ORDER BY or LIMIT 
clause. 

 * The issue doesn't reproduce on sqlite v3.22.0 and older 
(https://www.sqlite.org/2018/sqlite-tools-osx-x86-322.zip).

 * The issue still reproduces after re-creating db from dump using sqlite 
v3.24.0 on MacOS:

sqlite3 lock_issue.db
sqlite> pragma integrity_check;
ok
sqlite> .output backup.db
sqlite> .dump
sqlite> .q
sqlite3 fixed.db
sqlite> .read backup.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;


  * The issue doesn't reproduce after creating index for FileIndex.Enabled and 
issuing ANALYZE after it:

sqlite3 lock_issue.db
sqlite> CREATE INDEX FileIndex_Enabled ON FileIndex(Enabled DESC);
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;

sqlite> ANALYZE;
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, 
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from 
meta_tvshow
...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
...> WHERE meta_tvshow.MetaID='80349'
...> ORDER BY FileIndex.Enabled DESC
...> LIMIT 1;


Do you know what may be going on here, and how we can work around it?

Thanks in advance!

James

—
Firecore, LLC
https://firecore.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report: "Is Null" where term propagated to a virtual table causes wrong query results

2018-09-07 Thread Josef Kučera 

Hello,
I have found an obscure bug in the virtual table processing.

The schema looks like this:
CREATE TABLE t1 (id int, value text);
INSERT INTO t1 VALUES(1,'try');
CREATE TABLE t2 (ctx int, id int, value text);
INSERT INTO t2 VALUES(1,1,'good');
INSERT INTO t2 VALUES(2,2,'evil');

The following query works ok if t1 and t2 are real:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where t1.value is 
null;

2|2|evil||

If they are virtual (and the module implements ISNULL processing) the 
result is wrong:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where t1.value is 
null;

1|1|good||
2|2|evil||

Looking at WhereTrace I can see the ISNULL term applied to t1 without 
the L flag. The virtual module correctly returns 0 records for t1 
search, but t2 records are returned anyway.


As a workaround I can use a unary operator, effective blocking the 
ISNULL term processing in virtual module:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where +t1.value is 
null;

2|2|evil||

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

Re: [sqlite] SQLiteJava

2018-09-07 Thread Kevin Rushforth 

[Bcc'ing the openjfx-dev list]

I see no indication that there is anything relating to JavaFX in your 
question, so there seems to be no need to include the openjfx-dev list.


-- Kevin


On 9/6/2018 3:21 PM, AmnoJeeuw wrote:

I have a database table named company that looks like this:

snapshot2


When the application is asked to change the value of ArbolOne to ArbolOn,

db_snapshot1


the application generates the following schema represented on this 
message box:

db_snapshot3


which produces this Exception error:
db_snapshot4


I am attaching a snip of the code, in case you'd like to know it.
Having said that, I'd like to know if this is a bug or if I have not 
understand the information found in here 
.


Any help would be much appreciated.



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

[sqlite] Sqlite Doc Error ?

2018-09-07 Thread J Decker 
https://www.sqlite.org/limits.html
*Maximum Length Of An SQL Statement*
 The maximum number of bytes in the text of an SQL statement is limited to
SQLITE_MAX_SQL_LENGTH which defaults to 100. You can redefine this
limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.


but from the sources (3.24)
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 10
#endif
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users