Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

[Simon Slavin]

On 9 Mar 2018, at 4:09am, sanhua.zh  wrote:

> Here is the sample SQL I tried:
> ```
> CREATE TABLE t(i INTEGER);
> INSERT INTO t VALUES(1);
> WITH cte AS(SELECT i FROM t)
> UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
> ```
> 
> 
> Note that the sample WITH CLAUSE make no sense. It's just for testing. But 
> still failed.
> Did I use it in a wrong way? What's the suggesting rule?

The table called "cte" is not saved in the database.  Think of it as like a 
temporary index used for an ORDER BY, and deleted immediately the statement 
ends.  Therefore you cannot INSERT or DELETE into it either.  You are expected 
to SELECT from it, not to modify it to change how the WITH command works.

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

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

[sanhua.zh]

I find that a UPDATE statement with WITH CLAUSE always fails, although I use 
the syntax as SQLite syntax suggested.
Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They 
all run in a SELECT statement.


Here is the sample SQL I tried:
```
CREATE TABLE t(i INTEGER);
INSERT INTO t VALUES(1);
WITH cte AS(SELECT i FROM t)
UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
```


Note that the sample WITH CLAUSE make no sense. It's just for testing. But 
still failed.
Did I use it in a wrong way? What's the suggesting rule?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

[sanhua.zh]

I find that a UPDATE statement with WITH CLAUSE always fails, although I use 
the syntax as SQLite syntax suggested.
Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They 
all run in a SELECT statement.


Here is the sample SQL I tried:
```
CREATE TABLE t(i INTEGER);
INSERT INTO t VALUES(1);
WITH cte AS(SELECT i FROM t)
UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
```


Note that the sample WITH CLAUSE make no sense. It's just for testing. But 
still failed.
Did I use it in a wrong way? What's the suggesting rule?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting a non-unique column name in a WHERE clause

[Balaji Ramanathan]

OK, sorry for spamming the list.  I figured it out a few minutes after I
sent that email.  The trick is to say:

where country <> "country:1"

It took me a bit of sleuthing, but using .mode insert helped because I
could see that the resulting insert query had these postfixed column names
enclosed in double-quotes.  I had tried single quotes (sqlite interpreted
those as strings to compare country against, so that did not work), but not
double-quotes in my various attempts.

But I have another question.  In the example I posted, the column was only
repeated twice and the repeated column had a ":1" at the end, but in my
actual query, I have some columns repeated multiple times, and I find that
some of the repeated columns have postfixes like ":52449101",
":3016716403", ":197485499", ":3551085770", etc.  Where do these postfixes
come from?  If I know a column is going to be repeated n times in my view,
can I predict what the column names in the resulting output will be?

Thank you.

Balaji Ramanathan

On Thu, Mar 8, 2018 at 9:41 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Hi,
>
> Interesting problem I have run into.  I have a table called TRIP that
> has various columns including origin and destination.  Both of these are
> foreign key columns, referring to placeid's in the table PLACE that
> contains things like placename, latitude, longitude, country, etc.
>
> So, the table structures are:
>
> CREATE TABLE Trip (
> TripID integer PRIMARY KEY ASC,
> ...
> Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
> DELETE Cascade,
> Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade
> ON DELETE Cascade,
> }
>
> CREATE TABLE Place (
> PlaceID integer PRIMARY KEY ASC,
> PlaceName text NOT NULL,
> Latitude float,
> Longitude float,
> Elevation float,
> Country text,
> )
>
> I have a view that pulls out all the columns of TRIP as well as
> details of the origin and destination using two joints with PLACE.
>
> CREATE VIEW TripDetailsRaw AS select *
> from Trip
> inner join Place P1 on Trip.Origin = P1.PlaceID
> inner join Place P2 on Trip.Destination = P2.PlaceID;
>
> When I look at the output of this view (I type in "select * from
> tripdetailsraw" at the sqlite command line, I see one set of columns from
> P1 and another set of columns from P2 with ":1" appended to the repeated
> column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
> add a WHERE clause to my select that references these columns.
>
> In the view itself, I can add a WHERE clause such as:
>
> WHERE p1.country <> p2.country
>
> There is no problem there and the view produces the filtered rows
> without any issues.
>
> But when I try to use the view in a query, those column names don't
> work.  So, when I try to say "select * from TripDetailsRaw WHERE p1.country
> <> p2.country", sqlite complains that those columns don't exist.
> Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
> WHERE clause to refer to the country corresponding to P2 either.  In short,
> I don't know how to refer to whatever column names sqlite is using
> internally for these non-unique columns (even though, in the command line,
> they are revealed to me as "country" and "country:1").
>
> Anyways, long description of a problem that has probably been
> encountered and solved many times before.  If someone could give me the
> answer or point me to the documentation that will allow me to figure the
> answer out by myself, I would appreciate it.  Thank you.
>
> Balaji Ramanathan
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting a non-unique column name in a WHERE clause

[Igor Tandetnik]

On 3/8/2018 10:41 PM, Balaji Ramanathan wrote:

CREATE VIEW TripDetailsRaw AS select *
from Trip
inner join Place P1 on Trip.Origin = P1.PlaceID
inner join Place P2 on Trip.Destination = P2.PlaceID;

 When I look at the output of this view (I type in "select * from
tripdetailsraw" at the sqlite command line, I see one set of columns from
P1 and another set of columns from P2 with ":1" appended to the repeated
column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
add a WHERE clause to my select that references these columns.


Don't use "select *" in the view. Explicitly select columns you need, assign 
aliases to them as needed. As in

select p1.country as OriginCountry, p2.country as DestinationCountry, ...

--
Igor Tandetnik


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

[sqlite] Selecting a non-unique column name in a WHERE clause

[Balaji Ramanathan]

Hi,

Interesting problem I have run into.  I have a table called TRIP that
has various columns including origin and destination.  Both of these are
foreign key columns, referring to placeid's in the table PLACE that
contains things like placename, latitude, longitude, country, etc.

So, the table structures are:

CREATE TABLE Trip (
TripID integer PRIMARY KEY ASC,
...
Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
}

CREATE TABLE Place (
PlaceID integer PRIMARY KEY ASC,
PlaceName text NOT NULL,
Latitude float,
Longitude float,
Elevation float,
Country text,
)

I have a view that pulls out all the columns of TRIP as well as details
of the origin and destination using two joints with PLACE.

CREATE VIEW TripDetailsRaw AS select *
from Trip
inner join Place P1 on Trip.Origin = P1.PlaceID
inner join Place P2 on Trip.Destination = P2.PlaceID;

When I look at the output of this view (I type in "select * from
tripdetailsraw" at the sqlite command line, I see one set of columns from
P1 and another set of columns from P2 with ":1" appended to the repeated
column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
add a WHERE clause to my select that references these columns.

In the view itself, I can add a WHERE clause such as:

WHERE p1.country <> p2.country

There is no problem there and the view produces the filtered rows
without any issues.

But when I try to use the view in a query, those column names don't
work.  So, when I try to say "select * from TripDetailsRaw WHERE p1.country
<> p2.country", sqlite complains that those columns don't exist.
Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
WHERE clause to refer to the country corresponding to P2 either.  In short,
I don't know how to refer to whatever column names sqlite is using
internally for these non-unique columns (even though, in the command line,
they are revealed to me as "country" and "country:1").

Anyways, long description of a problem that has probably been
encountered and solved many times before.  If someone could give me the
answer or point me to the documentation that will allow me to figure the
answer out by myself, I would appreciate it.  Thank you.

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

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[E.Pasma]

Hello Adrián, as you say

 (I wonder whether the performance is very different from what one  
gets by manually inserting the WHERE clause in the base case of the  
recursive CTE.)


I wonder too. Still the trick is meant to make a view (without  
manually inserted predicates inside)


Thanks for the reply. E. Pasma

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

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[Adrián Medraño Calvo]

Hello Peter,

thank you for your response.

> 
> On 2. Mar 2018, at 06:30, petern  wrote:
> 
> Some observations.  It seems the WHERE pushdown optimization you cited only
> applies to subqueries with existing WHERE clause.  In your example without
> WHERE, the SELECT specifies the whole table as the left hand side of the
> UNION.  Scanning the whole table is likely more efficient than using an
> index to retrieve every row.  Do you have a better example of the problem?

I’m not sure that’s the case.  For example:

sqlite> .eqp on;
sqlite> WITH RECURSIVE
   ...> eqgrseq(initial, next) AS (SELECT v, v
   ...> FROM   t
   ...> WHERE 1 = 1
   ...> UNION
   ...> SELECT eqgrseq.initial, t.v
   ...> FROM   eqgrseq
   ...> JOIN   t
   ...> ON(t.v = eqgrseq.next + 1))
   ...> SELECT eqgrseq.initial, eqgrseq.next
   ...> FROM   eqgrseq
   ...> WHERE  eqgrseq.initial = 1;
--EQP-- 2,0,0,SCAN TABLE t
--EQP-- 3,0,0,SCAN TABLE eqgrseq
--EQP-- 3,1,1,SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)
--EQP-- 1,0,0,COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)
--EQP-- 0,0,0,SCAN SUBQUERY 1

The where expression is not pushed to the non-recursive case either.

> [Another suggestion in the form of a question:  Is the more efficient UNION
> ALL completely ruled out because of duplicates?]


You are right, it would make this query more performant without
changing its meaning.

Sincerely,
Adrián.

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

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[Adrián Medraño Calvo]

> On 2. Mar 2018, at 21:39, Dan Kennedy  wrote:
> 
> On 03/01/2018 05:37 PM, Adrián Medraño Calvo wrote:
>> Dear SQLite,
>> 
>> The following SQL script shows a query selecting data from a recursive CTE 
>> and filtering it.  I expected the optimizer to apply the filter to the 
>> recursive CTE directly, and indeed the documentation of pushDownWhereTerms 
>> (src/select.c:3833) indicates this possibility when various conditions are 
>> satisfied.  As far as I can see, the conditions are satisfied, but the query 
>> is nonetheless not optimized.  This indicates a misunderstanding on my part, 
>> or an oversight in SQLite.
>> 
>> -- A table containing some numbers.
>> CREATE TABLE t (v INT PRIMARY KEY);
>> INSERT INTO t
>> VALUES (0), (1), (2), (3), (4), (5);
>> 
>> -- Recursive query relating a number a sequence of numbers from "t" equal or
>> -- greater than it.
>> EXPLAIN QUERY PLAN
>> WITH RECURSIVE
>> eqgrseq(initial, next) AS (SELECT v, v
>>  FROM   t
>>  UNION
>>  SELECT eqgrseq.initial, t.v
>>  FROM   eqgrseq
>>  JOIN   t
>>  ON (t.v = eqgrseq.next + 1))
>> SELECT eqgrseq.initial, eqgrseq.next
>> FROM   eqgrseq
>> WHERE  eqgrseq.initial = :initial;
> 
> 
> It's quite a special case really. You can push the WHERE term down only 
> because it refers to a column that is always copied without modification 
> from the initial result set into any recursive results. You could not 
> push down a term like:
> 
>  WHERE eqgrseq.next = :next:
> 
> Dan.

Indeed… pushing the WHERE clause would absolutely be wrong, as it would
prevent generating part of the results.  I was blind to that, thank you
for pointing it out.

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

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[Adrián Medraño Calvo]

On 2. Mar 2018, at 15:55, E.Pasma  wrote:
> 
> 
>> Adrián Medraño Calvo wrote:
>>> The following SQL script shows a query selecting data from a  
>>> recursive
>>> CTE and filtering it.  I expected the optimizer to apply the filter  
>>> to
>>> the recursive CTE directly, and indeed the documentation of
>>> pushDownWhereTerms (src/select.c:3833) indicates this possibility  
>>> when
>>> various conditions are satisfied.
>> 
> Clemens Ladisch wrote:
> 
>> Rule 22 of  forbids
>> subquery flattening in this case.  I suspect pushDownWhereTerms() is  
>> not
>> called at all.
>> 
> 
> Hello, "push down where terms" into a complex view can sometimes be  
> achieved by correlation. The view/CTE must then be wrapped in a new  
> query that is joinable via indexes. Your example is just perfect to  
> show the trick. E. Pasma.
> 
> 
> .eqp on
> WITH eqgrseq(initial, next) AS (
> SELECT push.v, pull.v
> FROM   t push, t pull
> WHERE  pull.v IN (
> WITH RECURSIVE r AS (
> SELECT push.v
> UNION ALL
> SELECT t.v
> FROM   r
> JOIN   t
> ON t.v = r.v + 1)
> SELECT v FROM r))
> SELECT initial, next
> FROM   eqgrseq
> WHERE  initial = 1; --:initial;
> 
> Output:
> --EQP-- 0,0,0,SEARCH TABLE t AS push USING COVERING INDEX  
> sqlite_autoindex_t_1 (v=?)
> --EQP-- 0,1,1,SEARCH TABLE t AS pull USING COVERING INDEX  
> sqlite_autoindex_t_1 (v=?)
> --EQP-- 0,0,0,EXECUTE CORRELATED LIST SUBQUERY 1
> --EQP-- 4,0,0,SCAN TABLE r
> --EQP-- 4,1,1,SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1  
> (v=?)
> --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
> --EQP-- 1,0,0,SCAN SUBQUERY 2
> 1|1
> 1|2
> 1|3
> 1|4
> 1|5

Dear E. Pasma,

wow, that is remarkable!

The way I interpret it, by wrapping it in this way we extract
the constant column out of the recursive CTE, where the WHERE
clause shall not be pushed (as I learned from Dan), into the
outer query, where it can be pushed.  The trade-off would be
that, due to it being a correlated subquery, the recursive
query would be rerun for each filtered value.  (I wonder
whether the performance is very different from what one gets
by manually inserting the WHERE clause in the base case of the
recursive CTE.)  I see that it could also lead to duplicate
results (that is, with queries different than the example);
I’d say that recursive CTEs using UNION should change to use
UNION ALL plus the DISTINCT keyword on the outer query.

Thank you for your answer.  Sincerely,
Adrián.

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

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[Adrián Medraño Calvo]

Hello Clemens,

thank you for your answer.

> On 2. Mar 2018, at 11:19, Clemens Ladisch  wrote:
> 
> Rule 22 of  forbids
> subquery flattening in this case.  I suspect pushDownWhereTerms() is not
> called at all.

Although this is definitely over my level of understanding of SQLite
optimization, I can affirm that `pushDownWhereTerms` is invoked exactly once
for the above query (checked with a debugger).  It aborts as soon as
it detects that the subquery is recursive, and rightly so (see Dan’s answer).

Thank you,
Adrián.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users