date:20200105

Re: [sqlite] A hang in Sqlite

2020-01-05 Thread Dan Kennedy



On 6/1/63 13:44, Yongheng Chen wrote:

Hi,

We found a test case that hangs Sqlite:
—
CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ;
WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 ) SELECT 
'x' from v0;
—

This seems triggering a dead loop. However, since v0 is empty, it might not 
enter a dead loop I think ? We are not sure whether this is a bug.


Not a bug this time, I think.

The "v0" in the CTE refers to the CTE itself, not the empty temporary 
table. This makes it a recursive CTE, and a (very nearly) infinite loop:


  https://www.sqlite.org/lang_with.html

Thanks for all the work you've been doing on SQLite!

Dan.






Yongheng & Rui
___
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] A hang in Sqlite

2020-01-05 Thread Yongheng Chen

Hi,

We found a test case that hangs Sqlite:
—
CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ;
WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 ) SELECT 
'x' from v0;
—

This seems triggering a dead loop. However, since v0 is empty, it might not 
enter a dead loop I think ? We are not sure whether this is a bug.

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

[sqlite] Root cause of an assertion failed.

2020-01-05 Thread Yongheng Chen

Hi,

We noticed that an assertion is converted back to a conditional in this check 
in(https://www.sqlite.org/src/info/4d0b9109f7a5312d4e136395e08b11dad64d746bc106ad44d47675e5b1dcb4ef
 
).
 We are surprised that this assertion exists for over 10 years. We tried to 
understand why such a condition is hardto be triggered but failed due to 
unfamiliarity of sqlite codebase. Currently our guessing is that this will be 
triggers by natural join(which uses group by implicitly),  a table with primary 
key and an IN expression. Could anyone help explain this? Thanks!


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

Re: [sqlite] Performance regression since 3.19.0

2020-01-05 Thread Richard Hipp

How does the performance compare with this:

CREATE TABLE t1 (a TEXT, b TEXT);
CREATE TABLE t2 (a TEXT, b TEXT);
CREATE INDEX t1_a ON t1 (a,b);
CREATE INDEX t2_a ON t2 (a,b);

SELECT *
FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
WHERE t1.a='123';


On 1/5/20, Peter Inglesby  wrote:
> Is there any more information I could provide?

Run "ANALYZE;" on a database that contains actual data, then send us
the output of ".fullschema"

-- 
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] Update SQLITE

2020-01-05 Thread Keith Medcalf


On Sunday, 5 January, 2020 16:39, gideo...@lutzvillevineyards.com wrote:

>I have the following SQLITE query :
>
>SELECT   BlokkeklaarAliasnaam,
  BlokkeklaarKultivar,
  sum(BlokkeklaarSkatting)
>FROM Blokkeklaar
>GROUP BY BlokkeklaarAliasnaam,
  BlokkeklaarKultivar;
>
>I cannot figure out how to update a column (i.e.
>BlokkeklaarSkattingKultAliasnaam) with the value of
>sum(BlokkeklaarSkatting)
>in the above statement.

>This refers to a single table.

First of all, you cannot perform updates with a SELECT statement -- you need an 
UPDATE statement for that ;)

UPDATE Blokkeklaar as O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar);

You may use triggers so that Blokkeklaar.BlokkeklaarSkattingKultAliasnaam is 
always kept up-to-date (assuming that you start from an empty table or run the 
above update once to make it current before making further updates, after which 
you never need to run the above update again):

CREATE TRIGGER ins_Blokkeklaar AFTER INSERT ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;

CREATE TRIGGER del_Blokkeklaar AFTER DELETE ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar;
END;

CREATE TRIGGER upd_Blokkeklaar AFTER UPDATE OF BlokkeklaarSkatting, 
BlokkeklaarAliasnaam, BlokkeklaarKultivar ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar
   AND (OLD.BlokkeklaarAliasnaam IS NOT NEW.BlokkeklaarAliasnaam
OR OLD.BlokkeklaarKultivar IS NOT NEW.BlokkeklaarKultivar);
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;

You will, of course, need an index on Blokkeklaar (BlokkeklaarAliasnaam, 
BlokkeklaarKultivar) unless you cherish slowness due to table scans -- it will 
be even faster if this is a covering index such as Blokkeklaar 
(BlokkeklaarAliasnaam, BlokkeklaarKultivar, BlokkeklaarSkatting).

And you may, of course, use == and <> in place of IS and IS NOT if the 
correponding columns are constrained NOT NULL in the table definition.  
Otherwise, the above treats NULL as a distinct value.  If you want NULL to be 
indistinct you will need to make some changes.

Also, note that the sum(...) aggregate returns NULL if there is nothing to sum 
or all the values to sum are NULL.  If you want that to be 0.0 instead, then 
change all uses of sum(...) to total(...)

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



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

[sqlite] Performance regression since 3.19.0

2020-01-05 Thread Peter Inglesby

Hi,

I've encountered a performance regression that was introduced in
41c27bc0ff1d3135 (3.19.0 2017-04-18 11:20:19).  Following the guidance in
"How To Report Bugs Against SQLite" on the wiki, I'm reporting it here.

With these tables and indexes:

CREATE TABLE t1 (a TEXT, b TEXT);
CREATE TABLE t2 (a TEXT, b TEXT);
CREATE INDEX t1_a ON t1 (a);
CREATE INDEX t1_b ON t1 (b);
CREATE INDEX t2_a ON t2 (a);
CREATE INDEX t2_b ON t2 (b);

this query now returns very slowly for large amounts of data:

SELECT *
FROM (SELECT * FROM t1 WHERE a = '123') s1
LEFT OUTER JOIN (SELECT * FROM t2 WHERE a = '123') s2
ON s1.b = s2.b;

This is the query plan after the regression:

0|0|0|SEARCH TABLE t1 USING INDEX t1_a (a=?)
0|1|1|SEARCH TABLE t2 USING INDEX t2_b (b=?)

And this is the query plan before the regression:

1|0|0|SEARCH TABLE t2 USING INDEX t2_a (a=?)
0|0|0|SEARCH TABLE t1 USING INDEX t1_a (a=?)
0|1|1|SEARCH SUBQUERY 1 AS s2 USING AUTOMATIC COVERING INDEX (b=?)

I have verified that the performance problem exists in the latest
checkout.  Is there any more information I could provide?

Thanks for your work on a great tool!

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

[sqlite] Update SQLITE

2020-01-05 Thread gideon.e

I have the following SQLITE query :

 

SELECT

   BlokkeklaarAliasnaam,

   BlokkeklaarKultivar,

   sum(BlokkeklaarSkatting)

FROM

   Blokkeklaar

GROUP BY

   BlokkeklaarAliasnaam,

   BlokkeklaarKultivar;

 

 

I cannot figure out how to update a column (i.e.
BlokkeklaarSkattingKultAliasnaam) with the value of sum(BlokkeklaarSkatting)
in the above statement.

This refers to a single table.

 

Regards

 

 

 

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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-05 Thread Keith Medcalf

On Sunday, 5 January, 2020 04:42, Richard Hipp  wrote:

>On 1/5/20, Keith Medcalf  wrote:

>> Hrm.  Inconsistent/incorrect results.  Consider:

>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);

>> select * from a, b, c using (id); -- very strange result

>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3

>PostgreSQL and MySQL process the query as follows:

>   SELECT * FROM a, (b JOIN c USING(id));

>SQLite processes the query like this:

>   SELECT * FROM (a,b) JOIN c USING (id);

>I don't know which is correct.  Perhaps the result is undefined.

>Note that both MySQL and SQLite do allow you to use parentheses, as
>shown in my examples, to define the order of evaluation.  PostgreSQL
>does not, sadly.

>MS-SQL does not (as far as I can tell) support the USING syntax on a
>join.

Aha!  So as far as SQLite is concerned the syntax "... JOIN  USING ()" is 
effectively binding the using expression for the nested nested loop descent 
into into table  only and does not bind against the immediately preceeding 
LHS JOIN table.  

By adding some indexes and order by that cause the nesting order to change it 
appear that "a, b join c using (id)" is always processed as selecting the first 
lexically named id column irrepective of nesting order (that is "a, b join c 
using (id)" always becomes "a, b, c where a.id == c.id" and that "b, a join c 
using (id)" always becomes "a, b, c where b.id == c.id" even when the optimizer 
chooses to re-arrange the nesting order (such as by additional indexes and 
order by's)).

Since the "id" column to use is ambiguous for descent into "c" should not an 
"ambiguous column name" error be thrown?  The ambiguity only does not exist if 
ALL columns named "id" (for all tables that could be in an outer loop 
respective to "c") are constrained equal -- that is a,b,c using (id) -> a, b, c 
where a.id == b.id and a.id == c.id and b.id == c.id.

Since "natural join" devolves into a using, does not the same problem exist 
there as well?

Since any change is likely to have an effect on already existing and functional 
applications, could the behaviour be documented somewhere perhaps?

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

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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

2020-01-05 Thread Simon Slavin

On 5 Jan 2020, at 5:01pm, Amer Neely  wrote:

> But the question still remains, why the different results?

The optimizer gets improved from time to time in SQLite versions.  SQLite 
solved the problem faster by breaking down your query differently: deciding 
whether to do one scan or use two indexes, which table to search first, etc..

Your query was appears to violate the SQL standard, by failing to define the 
JOIN.  It should probably have resulted in a syntax error, but it didn't.  And 
it just happened that one version of SQLite interpreted the query one way, 
another interpreted it another way.

I advise you change your software to use one of the queries DRH supplied:

>   SELECT * FROM a, (b JOIN c USING(id));
> 
>   SELECT * FROM (a,b) JOIN c USING (id);

This will remove the ambiguity, meaning that however SQLite interprets the 
query in the future, or even if you switch to a different SQL engine, you get 
the result you wanted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-05 Thread Amer Neely

> On 1/5/20, Keith Medcalf  wrote:
>>
>> Hrm.  Inconsistent/incorrect results.  Consider:
>>
>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);
>>
>> select * from a, b, c using (id); -- very strange result
>>
>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3
> 
> PostgreSQL and MySQL process the query as follows:
> 
>SELECT * FROM a, (b JOIN c USING(id));
> 
> SQLite processes the query like this:
> 
>SELECT * FROM (a,b) JOIN c USING (id);
> 
> I don't know which is correct.  Perhaps the result is undefined.
> 
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
> 
> MS-SQL does not (as far as I can tell) support the USING syntax on a join.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

Ahh. More to learn. Thank you for the use of parentheses, I will have to
check my queries for that.

i did manage to get the query working by grouping on artists.artistid.
-- 
Amer Neely
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

2020-01-05 Thread Amer Neely

> On Saturday, 4 January, 2020 18:31, Amer Neely 
> wrote:
> 
>> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>> and web-based environment for several years. So far I'm happy and
>> impressed with SQLite, but I recently noticed some odd behaviour with
>> one of my queries.
>> Using the command-line in a shell (Mac High Sierra) I get a particular
>> result from a query. The exact same query in a Perl script gives me a
>> different result. To my mind it is a simple query, getting the 5 latest
>> additions to my music library.
>> Command-line:
>> select artists.artist, artists.artistid, cds.title, cds.artistid,
>> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>> cds using (artistid) group by artists.artistid order by cds.id desc
>> limit 5;
>> gives me the correct result. However, in a Perl script it gives me a
>> different result. How is that possible? Could it be a Perl::DBI issue?
>> Many thanks for anyone able to shed some light on this.
> 
> Your select does not constrain artists so the result is non-deterministic
> in that the result will depend on how the query planner decides to
> execute the query.  That is, you have not specified any join constraints
> on artists.
> 
> SELECT * FROM A, B JOIN C USING (D);
> 
> means
> 
> SELECT *
>  FROM A, B, C
> WHERE B.D == C.D;
> 
> if you thought it meant
> 
> SELECT *
>  FROM A, B, C
> WHERE A.D == B.D
>   AND B.D == C.D;
> 
> then that is likely the reason for the discrepancy.
> 
> 
Thank you for your time and consideration. I have managed to 'solve'
this problem by constraining on artists.artistid by grouping on that.
But the question still remains, why the different results?


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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Igor Korot)

2020-01-05 Thread Amer Neely


-- > What is your version of Perl and the SQLite module?

This is perl 5, version 18, subversion 2 (v5.18.2) built for
darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2013, Larry Wall

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.

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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-05 Thread Richard Hipp

On 1/5/20, Richard Hipp  wrote:
>
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
>

Apparently, in PostgreSQL you have to say:

   SELECT * FROM (SELECT * FROM a, b) AS x JOIN c USING(id);

-- 
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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-05 Thread Richard Hipp

On 1/5/20, Keith Medcalf  wrote:
>
> Hrm.  Inconsistent/incorrect results.  Consider:
>
> create table a(id integer primary key, a);
> insert into a values (1,1), (2,1), (3,1);
> create table b(id integer primary key, b);
> insert into b values (1,2), (3,2), (4,2);
> create table c(id integer primary key, c);
> insert into c values (1,3), (4,3), (5,3);
>
> select * from a, b, c using (id); -- very strange result
>
> id  a   id  b   c
> --  --  --  --  --
> 1   1   1   2   3
> 1   1   3   2   3
> 1   1   4   2   3

PostgreSQL and MySQL process the query as follows:

   SELECT * FROM a, (b JOIN c USING(id));

SQLite processes the query like this:

   SELECT * FROM (a,b) JOIN c USING (id);

I don't know which is correct.  Perhaps the result is undefined.

Note that both MySQL and SQLite do allow you to use parentheses, as
shown in my examples, to define the order of evaluation.  PostgreSQL
does not, sadly.

MS-SQL does not (as far as I can tell) support the USING syntax on a join.

-- 
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] A hang in Sqlite

[sqlite] A hang in Sqlite

[sqlite] Root cause of an assertion failed.

Re: [sqlite] Performance regression since 3.19.0

Re: [sqlite] Update SQLITE

[sqlite] Performance regression since 3.19.0

[sqlite] Update SQLITE

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Igor Korot)

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

14 matches

Site Navigation

Mail list logo

Footer information