Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 6:25 PM, Petite Abeille  wrote:

> 
> On Jan 25, 2014, at 6:05 AM, Keith Medcalf  wrote:
> 
>> Read the docs.  It explains how recursive CTEs are computed and how UNION 
>> ALL vs UNION work in CTEs.
> 
> 
> Hmmm… perhaps… doing is believing… so will experiment once the next SQLite 
> release is officially out.
> 

So… for the record… as it stands in 3.8.3, SQLite will happily recurse forever.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Keith Medcalf]

>remark 2:
>-
>I'm using Keith buildMSVC.cmd file to compile (that I'm using without
>knowing what it does exactly)
>
>I have one more compile error than with 21th version.
>"c1 : fatal error C1083: Impossible d'ouvrir le fichier source :
>'CSVImport.c' "
>
>==> Maybe it's normal.

Yes.  If you delete file containing the source code then that code cannot be 
compiled.  

CSVImport.c is code written by Michael D. Black which implements a stand-alone 
CSV import utility, just as there are other source files (other than just the 
amalgamation) which implement a number of add-in features if you enable them 
with

BuildMSVC ext

the default build (without ext) contains the base engine only without any 
extensions.





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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 6:05 AM, Keith Medcalf  wrote:

> Read the docs.  It explains how recursive CTEs are computed and how UNION ALL 
> vs UNION work in CTEs.


Hmmm… perhaps… doing is believing… so will experiment once the next SQLite 
release is officially out.

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 2:37 AM, James K. Lowden  wrote:

> Funny, we find ourselves on the opposite side of the compexity question
> this time. 

Ehehehe… yes… the irony is duly noted :)

But, ok, then, let welcome our new VALUES overlord. May it have a long and 
prosperous carrier! 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Elefterios Stamatogiannakis]

On 25/01/14 18:41, James K. Lowden wrote:

On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis  wrote:


Wouldn't it be better instead of creating a new concept "row
constructor", to use the existing row constructors, also known as
virtual tables?


Perhaps.  I didn't make up the term; it exists in various other DBMSs,
sometimes called table value constructor.  I just wanted to make clear
that it's not new, and opens the potential for (I would say) better
queries, because they can be expressed in terms of tuples instead of
just scalars.



Yes i see what you mean and i agree. Furthermore, and to carry on a 
little more that train of thought, i hope that tuples will become 
possible as return values from SQLite's user defined functions.


If ever that becomes the case, then i suggest to go for "tuple 
generators" instead of materialized tuples as the return primitive. 
Materialized tuples as a primitive, have a lot of nasty side effects in 
a DB engine.


l.

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis  wrote:

> Wouldn't it be better instead of creating a new concept "row 
> constructor", to use the existing row constructors, also known as 
> virtual tables?

Perhaps.  I didn't make up the term; it exists in various other DBMSs,
sometimes called table value constructor.  I just wanted to make clear
that it's not new, and opens the potential for (I would say) better
queries, because they can be expressed in terms of tuples instead of
just scalars.  

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

Re: [sqlite] "Common Table Expression"

[Elefterios Stamatogiannakis]

On 25/01/14 03:37, James K. Lowden wrote:

On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille  wrote:


It's exactly the same as "SELECT ?", but a little bit easier to
write. (It behaves like with INSERT, but is now available in every
place where a SELECT would be allowed.)


Hmmm? seems rather pointless to me.

select 1 as value /* from thin air */ union all
select 2 as value /* from thin air */
? etc ?

Seems to be good enough. No point in hijacking a totally unrelated
construct. I would drop such complication if I had a say. There is
already a perfectly fine construct to conjure constants out of thin
air: select.


VALUES is a row constructor.  You should be able to do

VALUES ( ( 'a', 'b', 'c' ) , ('d', 'e', 'f') )
as ( A, B, C ) as T



Wouldn't it be better instead of creating a new concept "row 
constructor", to use the existing row constructors, also known as 
virtual tables?


If we had the option of using virtual tables without first creating 
them, and we also were able to have them at the front of the query 
(automatically prepending a select * from ...) then VALUES above, could 
be a virtual table [*].


So think of a virtual table named VALUES that gets as parameters the 
values that you want it to emit. Then you can select from it, insert 
from it and so on.


l.

[*] In addition if we permitted queries as parameters in virtual tables, 
it would also enable virtual table composition.


An example of how this is done in madIS is below (XMLPARSE and FILE are 
virtual tables):


XMLPARSE FILE 'xmldata.xml.gz';

or expanded:

select * from (XMLPARSE select * from FILE('xmldata.xml'));



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

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,

With the version sqlite-amalgamation-201401242258   of this night.

 I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.06 sec (3.32 sec previous beta of 21rst) 8% faster
1 nested with = 1.7 sec   (1.7 sec previous beta of 21rst) 0%
3 nested with = 2.65 sec (2.5 previous beta of 21rst) 5% faster
(1 nested with which could be 3 nested with) = 1.05 sec (1.09 sec
previous beta of 21rst) 3% faster

remark1 :
-
On my non-cte workload, it 24th  version seems quicker by  3% faster than 21rst)

==> It's curious, were there speed improvement commits since beta 2 ?


remark 2:
-
I'm using Keith buildMSVC.cmd file to compile (that I'm using without
knowing what it does exactly)

I have one more compile error than with 21th version.
"c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' "

==> Maybe it's normal.

*** full compile report ***
>buildMSVC.cmd
CompileOptions=/nologo /O2 /GLFAy /fp:precise
Creating library file: SQLite3.dll
sqlite3.c
   Création de la bibliothèque SQLite3.lib et de l'objet SQLite3.exp
Génération de code en cours
Fin de la génération du code
Creating executable;   CSVImport.exe
CSVImport.c
c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' :
No such file or directory
Creating executable:   SQLite3d.exe
shell.c
Génération de code en cours
Fin de la génération du code
Creating executable:   SQLite3s.exe
shell3x.c
shell3x.c(146240) : warning C4005: 'ArraySize' : redéfinition de macro
shell3x.c(8545) : voir la définition précédente de 'ArraySize'
Génération de code en cours
Fin de la génération du code
Copying Build to DIST
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Keith Medcalf]

>>> (2) What about cycles? How does one deal with them?

>> With cycles, you probably want to use UNION instead of UNION ALL to
>> stop at duplicate records.

>Hmmm... not quite sure how this would play out in practice... how would the
>recursion known when to stop?

>Say,  given a circular hierarchy such as A-> B -> A when does the
>recursion stop?

UNION ALL means union all rows, even duplicates.
UNION means union all DISTINCT rows.

So it is not so much a matter of stopping as of not adding (hence recursing) 
from row A once it has already been seen.

Read the docs.  It explains how recursive CTEs are computed and how UNION ALL 
vs UNION work in CTEs.




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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille  wrote:

> > It's exactly the same as "SELECT ?", but a little bit easier to
> > write. (It behaves like with INSERT, but is now available in every
> > place where a SELECT would be allowed.)
> 
> Hmmm? seems rather pointless to me.
> 
> select 1 as value /* from thin air */ union all
> select 2 as value /* from thin air */ 
> ? etc ?
> 
> Seems to be good enough. No point in hijacking a totally unrelated
> construct. I would drop such complication if I had a say. There is
> already a perfectly fine construct to conjure constants out of thin
> air: select. 

VALUES is a row constructor.  You should be able to do 

VALUES ( ( 'a', 'b', 'c' ) , ('d', 'e', 'f') ) 
as ( A, B, C ) as T

which is a little more direct than a union of SELECTs.  You should also
eventually be able to say

WHERE ( A.a, A.b ) = values ( 'a', 'b' )
or 
FROM T MINUS VALUES ('a', 'b')

and such.  Gets you to more of a row-based way of dealing with the
data. 

Funny, we find ourselves on the opposite side of the compexity question
this time. 

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 24, 2014, at 11:32 PM, Clemens Ladisch  wrote:

> It's exactly the same as "SELECT …", but a little bit easier to write.
> (It behaves like with INSERT, but is now available in every place
> where a SELECT would be allowed.)

Hmmm… seems rather pointless to me.

select 1 as value /* from thin air */ union all
select 2 as value /* from thin air */ 
… etc …

Seems to be good enough. No point in hijacking a totally unrelated construct. I 
would drop such complication if I had a say. There is already a perfectly fine 
construct to conjure constants out of thin air: select. 

> 
>> (2) What about cycles? How does one deal with them?
> 
> With cycles, you probably want to use UNION instead of UNION ALL to
> stop at duplicate records.

Hmmm… not quite sure how this would play out in practice… how would the 
recursion known when to stop?

Say,  given a circular hierarchy such as A-> B -> A…. when does the recursion 
stop?



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

Re: [sqlite] "Common Table Expression"

[Clemens Ladisch]

Petite Abeille wrote:
> On Jan 24, 2014, at 2:31 PM, Richard Hipp  wrote:
>> Please see http://www.sqlite.org/draft/lang_with.html for draft
>> documentation of the new Common Table Expression implementation for SQLite
>> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.
>
> (1) What is this VALUES( … ) construct? What purpose does it serves?

It's exactly the same as "SELECT …", but a little bit easier to write.
(It behaves like with INSERT, but is now available in every place
where a SELECT would be allowed.)

> (2) What about cycles? How does one deal with them?

With cycles, you probably want to use UNION instead of UNION ALL to
stop at duplicate records.


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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 24, 2014, at 2:31 PM, Richard Hipp  wrote:

> Please see http://www.sqlite.org/draft/lang_with.html for draft
> documentation of the new Common Table Expression implementation for SQLite
> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.

(1) What is this VALUES( … ) construct? What purpose does it serves? 

(2) What about cycles? How does one deal with them?

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

Re: [sqlite] "Common Table Expression"

[E.Pasma]

Op 24 jan 2014, om 17:31 heeft E.Pasma het volgende geschreven:


Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven:


Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for  
SQLite

3.8.3.  Comments, criticism, and typo-corrections are appreciated.

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


Hello, I like to give some feedback.

The LIMIT clause in the 2nd example is written at the end of the  
line and it looks as if it only applies to the last part of the  
UNION . This looks sneaky as it applies after the union. It looks  
better if LIMIT is wriiten on a new line.


The text about recursion sais:
"""The "select-stmt" after the AS keyword must be  ..."""
I checked this in the current draft (of 23 01 2014) and it does not  
matter if the recursive statement comes right after te AS keyword,  
or if there other (recursive or non-recursive) statements that come  
first.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.



The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a  
temporary table that it is generated only once?


Finally congratulations for the improved speed. Sudoku is FOUR times  
faster as in last weeks version!


Thanks very much, Edzard.

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


Excuses, I must come back on first post and be less enthusiastic (but  
still very much). When comparing speeds I used different inputs. And  
the meaninglessness of the RECURSIVE keyword fad confused me a bit. My  
remaning remarks and important queation are:


The LIMIT clause in the 2nd example is written at the end of the line  
and it looks as if it only applies to the last part of the UNION .  
This looks sneaky as it applies after the union. It looks better if  
LIMIT is wriiten on a new line.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.


The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a temporary  
table that it is generated only once?

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,

On my 3 level of with example, my systems seems to "hang" if  :
- I put a "distinct" keyword in goods definition,
- or if I replace "union all" per a simple "union" .



** failure mode 1 (no union all in the neighbors) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union  select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union  select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;


** failure mode 2 (distinct in the goods definition) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select distinct  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Dan Kennedy]

On 01/25/2014 01:00 AM, big stone wrote:

AND NOT EXISTS (
  SELECT 1 FROM ok AS lp
  WHERE ind=lp.c and  z.z = substr(s, n, 1)
)


s/ok/goods/ and s/lp.c/lp.r/

Dan.


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

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,


I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7 sec
3 nested with = 2.65 sec
(1 nested with which could be 3 nested with) = 1.09 sec

** 3 nested with version **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select * from neighbors where r <>n)

,input(sud) AS (
   VALUES(
  
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

Hi,

The speedest version of the sudoku, staying in the limit of lisibility
would include 3 nested "with",

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7sec

(1 nested with which could be 3 nested with) = 1.09 sec (see below 'annexe
1')

3 nested with   well I get a "MemoryError: " event on the simplest
example. (see below  'annexe 2')
==> nobody in real life would do that, would he ?

Anyway, when I was trying to implement "fake" CTE, I was :
- creating views when the CTE was like " x as (select ...)"
- creating table when the cte was like "x(r) as (select ...)"
- creating table with index when the cte was like (x(r INTEGER

*PRIMARY KEY) as (select ...)"*


*==> So, if the if sudoku solving at the maximum speed with only virtual
table was a DEAD or ALIVE situation, I may suggest "embrassing and
extending" the CTE standard with a similar trick.*


 example of "optimized" stupid-brut force sudoku 1 with **

drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)
insert into ok select distinct * from goods;

-- easy   (0 sec)
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79'
-- medium (2 sec)
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
-- hard   (200 s)
'8..36..7..9.2...5...7...457.1...3...168..85...1..94..'

WITH RECURSIVE input(sud) AS (
   VALUES(
 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* A table filled with digits 1..9, inclusive. */
digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;

 same with 3 nested with ... blow up even on the simplest case (bug of
me ? ) ***
drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)


,input(sud) AS (
   VALUES(
-- 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79')
),

/* A table filled with digits 1..9, inclusive. */
--digits(z, lp) AS (
--VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
--),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[E.Pasma]

Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven:


Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for  
SQLite

3.8.3.  Comments, criticism, and typo-corrections are appreciated.

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


Hello, I like to give some feedback.

The LIMIT clause in the 2nd example is written at the end of the line  
and it looks as if it only applies to the last part of the UNION .  
This looks sneaky as it applies after the union. It looks better if  
LIMIT is wriiten on a new line.


The text about recursion sais:
"""The "select-stmt" after the AS keyword must be  ..."""
I checked this in the current draft (of 23 01 2014) and it does not  
matter if the recursive statement comes right after te AS keyword, or  
if there other (recursive or non-recursive) statements that come first.


As hoped, the RECURSIVE keyword is there only for compatibility. We  
can leave it out if we don't care about it.



The sudoku example uses a recursive definition for the DIGITS  
enumeration. The version using VALUES (of Big Stone?) is still much  
faster. Is there any hint to convert a view like DIGITS to a temporary  
table that it is generated only once?


Finally congratulations for the improved speed. Sudoku is FOUR times  
faster as in last weeks version!


Thanks very much, Edzard.

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

Re: [sqlite] "Common Table Expression"

[Kevin Benson]

On Fri, Jan 24, 2014 at 10:09 AM, Clemens Ladisch wrote:

> Richard Hipp wrote:
> > Please see http://www.sqlite.org/draft/lang_with.html for draft
> > documentation of the new Common Table Expression implementation for
> SQLite
> > 3.8.3.  Comments, criticism, and typo-corrections are appreciated.
>
> Duplicated "the":
>
>   * If a UNION operator connects the the initial-select ...
>

IS where SHOULD BE were

* An ordinary common table expression works as if it where a view ...


IS where SHOULD BE it were

* Then the row containing 2 is used as if where the complete content ...


IS ORDERBY SHOULD BE ORDER BY

* The "ORDERBY checkin.mtime DESC" term ...


IS by all mtime, SHOULD BE all by mtime,

* ... thousands of ancestors, sort them by all mtime, ...


IS breath SHOULD BE breadth

* 5 occurrences on this documentation page


IS undertaking SHOULD BE undertaken

* ... is undertaking by ...


IS organization. SHOULD BE organization:

* ...within that organization.


IS string SHOULD BE string:

* ... Thus the input string

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Clemens Ladisch]

Richard Hipp wrote:
> Please see http://www.sqlite.org/draft/lang_with.html for draft
> documentation of the new Common Table Expression implementation for SQLite
> 3.8.3.  Comments, criticism, and typo-corrections are appreciated.

Duplicated "the":

  * If a UNION operator connects the the initial-select ...


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

Re: [sqlite] "Common Table Expression"

[Richard Hipp]

Please see http://www.sqlite.org/draft/lang_with.html for draft
documentation of the new Common Table Expression implementation for SQLite
3.8.3.  Comments, criticism, and typo-corrections are appreciated.

-- 
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] "Common Table Expression"

[Darren Duncan]

On 1/17/2014, 8:24 AM, Jan Nijtmans wrote:

Not necessary. I noticed that CTE was just merged to SQLite's trunk,
so it apparently will be part of SQLite 2.8.3.


Ahh great, I look forward to seeing that released in February (regular schedule) 
or whenever.  The greater maintainability of code due to the ability to refactor 
selects into named and reusable subcomponents, meaning bringing a benefit to SQL 
we take for granted with typical application languages, is coming to pass.  That 
and the ability to have recursion, also taken for granted before.  And no, VIEWs 
are not the same, those require creation of separate schema objects, while 
someone with read-only access to a db can use WITH, especially beneficial for 
ad-hoc reports. -- Darren Duncan


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

Re: [sqlite] "Common Table Expression"

[Nico Williams]

On Fri, Jan 17, 2014 at 2:05 PM, Richard Hipp  wrote:
> On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille 
> wrote:
>> On Jan 17, 2014, at 7:47 PM, big stone  wrote:
>> > - I just did my first recursive CTE under Ipython notebook.
>>
>> Finally! We can solve sudoku puzzles in SQL :P
>
> Dan Kennedy, who created the common-table-expression that just found its
> way onto trunk, suggests the following query for computing the Mandelbrot
> Set as ascii-art:

Excellent!  Thank you!  I've been using recursive triggers to compute
recursive queries.  I can now stop that hackiness.

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

Re: [sqlite] "Common Table Expression"

[Richard Hipp]

On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille wrote:

>
> On Jan 17, 2014, at 7:47 PM, big stone  wrote:
>
> > - I just did my first recursive CTE under Ipython notebook.
>
> Finally! We can solve sudoku puzzles in SQL :P
>

Dan Kennedy, who created the common-table-expression that just found its
way onto trunk, suggests the following query for computing the Mandelbrot
Set as ascii-art:

WITH RECURSIVE
  xaxis(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM xaxis WHERE x<110),
  yaxis(y) AS ( VALUES(1) UNION ALL SELECT y+1 FROM yaxis WHERE y< 30),
  m(iter, xpix, ypix, x, y) AS (
SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
UNION ALL
SELECT iter+1, xpix, ypix,
x*x-y*y + ((xpix * 3.5 / (SELECT count(*) FROM xaxis) - 2.5)),
2.0*x*y + ((ypix * 2.0 / (SELECT count(*) FROM yaxis) - 1.0))
FROM m
WHERE (x*x + y*y) < 4.0 AND iter<50
  ),
  m2(iter, xpix, ypix) AS (
SELECT max(iter), xpix, ypix FROM m GROUP BY xpix, ypix
  ),
  a(t) AS (
SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '' )
FROM m2 GROUP BY ypix
  )
SELECT substr(rtrim(t),15) FROM a;

Copy/paste the above query into a file called "mandelbrot.txt" then run:

  ./sqlite3 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 17, 2014, at 7:47 PM, big stone  wrote:

> - I just did my first recursive CTE under Ipython notebook.

Finally! We can solve sudoku puzzles in SQL :P

http://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/

> Thanks a lot, dear SQLite team !

And enlighten sponsor. Thanks!

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

Re: [sqlite] "Common Table Expression"

[big stone]

Yes !

It's in trunk.

With Keith Medcalf's help, I suceeded to compile it.
=> Thanks a lot Keith !


I can confirm :
- the sqlite3.dll  works under python3 by swapping the default one,
- I just did my first recursive CTE under Ipython notebook.


==> I would never have imagined that to happen so quickly !
Thanks a lot, dear SQLite team !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Jan Nijtmans]

2013/12/31 big stone :
> Hello "CTE in SQLite" fans,
>
> To get CTE in SQLite, I guess we must answer by the example the fears
> expressed by Simon and Rsmith.

Not necessary. I noticed that CTE was just merged to SQLite's trunk,
so it apparently will be part of SQLite 2.8.3.

I'm not familiar with CTE, I didn't try it yet, just had a quick
glance at the code. I'm wondering if there isn't a #define
missing here (see patch below):

Regards,
 Jan Nijtmans


Index: src/sqliteInt.h
==
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -3355,10 +3355,11 @@
 #ifndef SQLITE_OMIT_CTE
   With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
   void sqlite3WithDelete(sqlite3*,With*);
   void sqlite3WithPush(Parse*, With*, u8);
 #else
+#define sqlite3WithAdd(x,y,z,z1,z2)
 #define sqlite3WithPush(x,y,z)
 #define sqlite3WithDelete(x,y)
 #endif

 /* Declarations for functions in fkey.c. All of these are replaced by
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

The interest of this "forward lateral" move was its good
standardization/effort ratio.

For performance/effort ratio, I would have expect people to push SQLite4
and its 2x to 10x promise.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

Hello,

On Jan 6, 2014, at 6:51 AM, James K. Lowden  wrote:

> You're welcome to your opinion, of course.  But you're really not answering 
> my point, and I object to your assertion that I'm clinging to 1986.  

Apologies about that. The 1986 reference was more pointed to SQLite itself, not 
you personally :)

> Evolution for its own sake is not improvement, right?

Agree.

>  I simply said that new syntax absent new functionality doesn't add anything, 
> which you haven't denied.  

Yes, agree, it’s a bit of a lateral move at first sight.

> Can we not agree that just toying with the syntax is a waste of time and 
> makes things worse, not better?  

No. This is where we part ways. In this very specific case, introducing named 
subqueries is a great improvement in syntax and flexibility of expression. And 
yes, syntax matters  :)

Think about it more as a compound effect: the ‘with’ clause by itself is not 
much to talk about, but combined with windowing functions (which tend to 
required quite a bit of subqueries) and the ability to decompose logic 
bottom-up (as oppose to inside-out with traditional subqueries) does create a 
much more expressive, comprehensible, and comprehensive language altogether. 

Or at least such is my experience as a practitioner of the dark art of writing 
SQL :D

> Like you, I'd prefer to see SQLite create new functionality,
> not simply offer new keywords to accomplish what it already does.
> There's a case for MERGE and windowing functions, I agree.  Also better
> constraint violation error messages, and bona fide atomic commits.  

Sure thing. More generally, in my opinion, SQLite, the implementation, hasn’t 
keep up with SQL, the ‘standard’. 

> We could have a long, long talk if you wanted to (which I doubt, so I
> won't start) about how well and poorly SQL fits into the modern
> environment, what assumptions it carries forward from the days of COBOL
> and APL and PROLOG.  I know a bit about it.  

I would love to have that talk, but perhaps not over email, which tend to be 
tedious. Perhaps over a drink one day :)

> I'm working on making it obsolete lest my grandchildren have to learn it.  

Like many of its forebears, SQL will outlive us all, with warts, blemishes, and 
all. Best to use it than to fight it in my opinion.

And, on that note, I wish you a Happy New Year.

Cheers,

PA.


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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 4, 2014, at 11:34 PM, RSmith  wrote:

> . I think in America the term "Captain Obvious" is used for the author of 
> such a statement.

This sounds like a job for ObviousMan!

http://treesflowersbirds.files.wordpress.com/2010/01/obviousman.jpg

>  things _are_ moving forward and are changing almost daily,

I would argue that, as far as SQL itself goes, they are not. Sure, bug fixes, 
implementation details, optimizations, tests, etc, etc… but when was the last 
time there was an actual, palpable *SQL* enhancement in SQLite? I personally 
cannot recall.

P.S.

Just for fun, look up ‘Tabibitosan method’ for an entertaining example of  
‘modern’ SQL...

http://www.orchestrapit.co.uk/?tag=tabibitosan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[RSmith]

On 2014/01/05 00:03, Petite Abeille wrote:
Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause is a change for the better. As is merge. As are 
windowing functions. SQLite cannot pretend it’s 1986 forever. It has to move with the times or it will become ossified, obsolete, 
a mummy. 


But of course, things evolve and SQLite (like any other system) needs to keep up with times!  This is however much the same as 
telling somebody that they must breathe else they will slowly lose oxygen and suffocate. I think in America the term "Captain 
Obvious" is used for the author of such a statement.


To be clear: the statement is overly obvious and does nothing to aid the point - we are not disagreeing about the need to evolve, we 
both (along with many other distinguished members of this list) feel that it should evolve - AND I am happy to report that it is 
evolving as is evidenced by the regular updates and releases and oft-added functionality. Changes must be planned and reasonable 
though.


The point of contention is _how_ it should evolve, not _whether_ it should evolve - so your statement urging to push forward is a 
moot point - things _are_ moving forward and are changing almost daily, whether you like it or not, even though it may not move in 
the format and time-step you envisioned.
(I am quite sure you already know all this and merely appealed to the general wisdom of "change is good" to try and support your 
unrelated point, so please forgive my dissertation).


Lastly, a small digression:  I put it to you that if SQLite stops evolving completely today, and stays as is (with exception of 
bug-fixes), it will STILL last longer as the DB of choice for 90% of current implementations long after you and I have met our 
demise. If you do not agree on this point you may have no real idea of the actual width of implementation of SQLite3. It is this 
same width of implementation that makes us disagree on what should be added and what not. I will forgo another iteration on my POV 
as I think James K.L. made it very clear already.


May you (and all other list members of course) have an awesome 2014!
Ryan

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 4, 2014, at 8:05 PM, James K. Lowden  wrote:

> That's an aesthetic judgement.  Even if I agreed, it doesn't change the
> fact that every language feature is an element of complexity,
> and redundant language features are needless complexity.  

Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause 
is a change for the better. As is merge. As are windowing functions.

SQLite cannot pretend it’s 1986 forever. It has to move with the times or it 
will become ossified, obsolete, a mummy.





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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Wed, 1 Jan 2014 23:23:04 +0100
Petite Abeille  wrote:

> Yes, a ?with? clause is just syntax sugar providing named subqueries.
> But this sugar open the door to drastically different ways to write
> queries, bringing structure, clarity of thoughts and purpose to
> otherwise labyrinthine constructs. 

That's an aesthetic judgement.  Even if I agreed, it doesn't change the
fact that every language feature is an element of complexity,
and redundant language features are needless complexity.  

WITH only creates another way to express something already
expressible in fewer words.  If the CTE appears only once in the query,
hardly anything is changed except that the subquery appears first.  

I have no objection to new syntax.  But it must add functionality to the
language.  It must allow the programmer to express the same query more
clearly and *succinctly*.  

> Think about ?with? clause as modern SQL syntax, in the same way as
> ANSI join syntax brings clarity of purpose to what otherwise would be
> a shambles of cross-purpose ?where? clauses.

Oh, I have lots of complaints about SQL!  ;-)   But the two aren't
really so analogous, because FROM ... ON added something new to the
language.  

The old FROM A, B WHERE has a pretty good basis in relational algebra.
But RA doesn't define outer joins (really the union of a join and
antijoin), and the syntax had problems expressing them. WHERE had (and
has) no way to refer to the inner table, so ON was invented.  

> Oh, and, also? Happy New Year! :)

And you the same.  :-)  

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Thu, 2 Jan 2014 01:29:52 +
Simon Slavin  wrote:

> Hmm.  Even
> 
> update t set i = i + 1 - 1
> 
> with i being UNIQUE might be a good test case.

Well, that actually works:

sqlite> create table t (t int primary key);
sqlite> insert into t values (1);
sqlite> insert into t values (2);
sqlite> update t set t = t + 1 - 1;
sqlite> select * from t;
t 
--
1 
2 

presumably because "t + 1 - 1" is fully evaluated before it is applied
to the row.  

Another example I used off-list:

sqlite> delete from t;
sqlite> insert into t values (-1);
sqlite> insert into t values ( 1);
sqlite> update t set t = -t;
Error: column t is not unique

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

Re: [sqlite] "Common Table Expression"

[big stone]

(sorry keyboard fall on the floor)

Now :
- I wouldn't let someone use SQL without allowing him to use CTE,
- I need to use ubiquitous SQL motors, which are only TWO on windows
(Access and SQLite) , and they still don't have CTE.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

Hello,

When bigger brains created CTEs in SQL:99, I suppose they discussed a long
moment the technical merit of CTEs.

In my own experience :
- the maintenance burden of my queries dropped significantly because of
them,
- I stopped harrassing (myself or a central database administrator) for
views (creation or deletion).

Now :
- I wouldn't let someone use SQL without allowing him to use CTE,
- I need to use ubiquitous SQL motors, which are only TWO :
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Simon Slavin]

On 1 Jan 2014, at 9:55pm, James K. Lowden  wrote:

>   sqlite> update i set i = random();

Yeah.  You have to do it properly.  Mark the rows which will be obsoleted, make 
up the new rows, and check the resulting union for consistency.  Which means 
that you need to temporarily have enough memory (or filespace) free for all the 
new rows.  Hmm.  Even

update t set i = i + 1 - 1

with i being UNIQUE might be a good test case.

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 1, 2014, at 10:55 PM, James K. Lowden  wrote:

>> CTE ... bring capabilities to the users, by simplifying
>> the use of the underlying tool.
> 
> CTEs would add complexity, not simplify.  

Nonsense, dear Sir :)

Yes, a ‘with’ clause is just syntax sugar providing named subqueries. But this 
sugar open the door to drastically different ways to write queries, bringing 
structure, clarity of thoughts and purpose to otherwise labyrinthine 
constructs. 

Think about ‘with’ clause as modern SQL syntax, in the same way as ANSI join 
syntax brings clarity of purpose to what otherwise would be a shambles of 
cross-purpose ‘where’ clauses.

In short, a bit of syntactical sugar can do wonders for one’s diet :D

Oh, and, also… Happy New Year! :)


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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Wed, 1 Jan 2014 11:04:57 +0100
big stone  wrote:

> You're right :
> *"*CTEs ... add exactly zero to SQLite's capability."
> 
> This is also right :
> "C Language ... add exactly zero to Intel X86 processor capability".

Dennis Ritchie said C is an idealized assembler over an idealized
machine.  ;-)

>  CTE ... bring capabilities to the users, by simplifying
> the use of the underlying tool.

CTEs would add complexity, not simplify.  

You seem to think 

with V as [query]
select ... from V;

is better than

select ... from ([query]) as V;

It's not.  You may like it better, but objectively the query is only
rearranged.  It's not shorter.  

If implemented, readers of SQLite queries would be forced to master two
syntaxes instead of one.  That's more complex, not simpler.  

I suspect we differ on this point, that you think it's good to have
more than one syntax for the very same query.  Is that the case?
Because that would be a distinct disagreement on what "good" is,
independent of CTEs per se.  

You may claim

with V as [query]
select ... from V as a join V as b ... ;

is an improvement over

create view V as [query];
select ... from V as a join V as b ... ;

because the schema doesn't become "cluttered" with the V name.  

Because that form of query is rare, the number of such views is small,
hence any "cluttering" is minor.  Not to mention the named view has
advantages of its own, including communicating what the query means (in
the name) and potential reuse.  

ISTM your case rests on a slender reed: it's good to have inline
views, to avoid single-purpose real views.  Or, perhaps, that freedom of
expression is good.  Do you really think SQLite syntax should be
altered on that basis?  

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Tue, 31 Dec 2013 22:20:15 +
Simon Slavin  wrote:

> > Meanwhile, here's a much more important failing that cannot be
> > worked around within SQL without a temporary table:
> > 
> > sqlite> create table i ( i int primary key );
> > sqlite> insert into i values (1);
> > sqlite> insert into i values (2);
> > sqlite> update i set i = i + 1;
> > SQL error: column i is not unique
> 
> Is that not actually a serious bug in SQLite ?  

Well, a *bug* is defined as behavior in exception to the
documentation; I don't know if this behavior is documented.  

Definitely it's a flaw, and IMO the worst one.  As I said, it affects
any UPDATE of a column with a unique constraint or index.  

> My understanding of SQL is that checks for consistency should occur
> at the end of a transaction, not at each row of an UPDATE.  

Correct.  That's what the standard says, and what atomicity requires.  

The user interacts with the DBMS on a the basis of sets, a statement at
a time.  The above update statement can logically be executed, and the
user by definition has no control over *how* it is executed.  

> I hadn't realised this problem existed.

It's known to the developers; see the thread in around 6 September 2013
with the subject "UPDATE question".  

Since we're on the topic, let me describe a possible shortcut.  

The only general successful implementation strategy (within SQLite)
would be to write the updated rows to a temporary table, delete them
from the target table, and insert from the temporary.  But that's not
always necessary.  

In the update statement above, the query engine can ascertain that the
output of the update is greater than the input.  By working "backwards"
over the index -- updating the "last" row first -- the row-by-row
update could succeed.  With "just a little" work, the in-place update
algorithm could decide whether to work "backwards" or "forwards", and
succeed more often than it now does.  By brute force it could try one
and then the other before giving up.  

Will that always work?  No.  Consider

sqlite> update i set i = random();
or 
sqlite> update i set i = (select min(t) from T where i.i = T.i);

Nevertheless, a slightly more intelligent algorithm for in-place
updates would solve the class of problem for which the output is a
determinisitic function of the same column.  It might be easier to
implement within the existing framework than a general
copy-delete-insert strategy.  Whether it's worthwhile to bother, I
don't know.  

Happy New Year.  

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

Re: [sqlite] "Common Table Expression"

[RSmith]

You're right :
*"*CTEs ... add exactly zero to SQLite's capability."

This is also right :
"C Language ... add exactly zero to Intel X86 processor capability".

In both case :
- "adding zero capability" to the underlying tool is a physical constraint,
- CTE (or C Language) bring capabilities to the users, by simplifying the
use of the underlying tool.


I'm trying to no longer weigh in on this subject since previous attempts have been met with some sharp criticism, but I cannot help 
point out the fantastically large difference between the above statements.  James is saying that CTE's do not add any functionality 
(so long as recursive queries are absent of course) to the SQL itself (not some higher-order other language that uses SQL in some way).


At a stretch you might say, when considering loop improvements, that "CTE is to SQL  _as_ a For-loop statement is to C (as opposed 
to a while-loop)", but it's hard to see how "CTE is to SQL _as_ C is to X86" can ever be a valid analogy.


You (Bigstone) have provided great suggestions on how it might be implemented, why it should be implemented and indeed clear 
explanations to the benefit of using CTE SQL.  Responders have pointed out that while this is true, unless recursive query 
capability is also added, the only advantage will be semi-better legibility at the cost of significant added size, overhead and 
code. Also that we'd all like it to work easier, just not at that price.


It is not a suggestion that is shot down or in any way invalid - just not feasible currently, but might well be so in future pending 
developments. I also applaud the "Friends of CTE in SQLite" initiative, but starting on the development before the goal has been 
shown to be appealing to the devs is much like building a sandcastle and then pointing out how easy it was to urge the activity when 
everybody else has brought surf-boards.


Also, the above explains what seems to be the current ruling opinion and is not a law or a set-in-stone working model laid down by 
anyone, least of all me.


That said, it's hard to see how this subject has carried so far.

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

Re: [sqlite] "Common Table Expression"

[Constantine Yannakopoulos]

On Tue, Dec 31, 2013 at 8:59 PM, James K. Lowden
wrote:

> Recursive queries are a unique feature of CTEs.  They are not supported
> in SQLite.  If and when they are, CTEs will be required.
>

Not necessarily. Oracle has had the START WITH CONNECT BY syntax for
recursive queries since the eighties:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm#SQLRF52315

I think I prefer that over recursive CTES if implementing recursive queries
is the only goal. On the other hand CTEs _are_ part of the SQL:1999
standard and Oracle also supports them.

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi James K. Lowden,

You're right :
*"*CTEs ... add exactly zero to SQLite's capability."

This is also right :
"C Language ... add exactly zero to Intel X86 processor capability".

In both case :
- "adding zero capability" to the underlying tool is a physical constraint,
- CTE (or C Language) bring capabilities to the users, by simplifying the
use of the underlying tool.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Simon Slavin]

On 31 Dec 2013, at 10:05pm, James K. Lowden  wrote:

> Meanwhile, here's a much more important failing that cannot be worked
> around within SQL without a temporary table:
> 
>   sqlite> create table i ( i int primary key );
>   sqlite> insert into i values (1);
>   sqlite> insert into i values (2);
>   sqlite> update i set i = i + 1;
>   SQL error: column i is not unique

Is that not actually a serious bug in SQLite ?  My understanding of SQL is that 
checks for consistency should occur at the end of a transaction, not at each 
row of an UPDATE.  I hadn't realised this problem existed.

I wonder whether it would be possible to fix this in SQLite4, if not before.  
SQLite4 implements PRIMARY KEYs properly, of course, but I don't know whether 
that means there'd have to be one fix or two.

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Tue, 31 Dec 2013 20:43:20 +0100
big stone  wrote:

> To get CTE in SQLite, I guess we must answer by the example the fears
> expressed by Simon and Rsmith.
> 
> I propose the following method :
>   - unproven-demand :
...
>   - code size + performance increase fear :

It seems to me you have more substantial hurdle, namely 

0.  undemonstrated utility

(See my reply to you on 31 Dec 2013 13:59:26 -0500 for more details.) 

Whether or not CTEs are wanted and easily implemented, they add exactly
zero to SQLite's capability.  All they do is afford another form or
equivalent expression for the same query.  That is: complexity without
power.  

Meanwhile, here's a much more important failing that cannot be worked
around within SQL without a temporary table:

sqlite> create table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique

The workaround is:

0.  BEGIN TRANSACTION
1.  Create a temporary table T to hold the new values with 
CREATE TEMPORARY TABLE T AS SELECT ...
2.  Delete rows from main table M based on the old values
3.  INSERT INTO M SELECT * FROM T;
4.  COMMIT TRANSACTION;
5.  DROP TABLE T;

That only works in SQLite because a user-defined transaction locks the
database.  In most DBMSs the same SQL leaves the main table M open to
update by another process, making the results indeterminate.  

The problem crops up anytime unique constraints are in force, including
updating primary keys.  I know of no more important flaw in SQLite.  

--jkl


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

Re: [sqlite] "Common Table Expression"

[big stone]

Hello "CTE in SQLite" fans,

To get CTE in SQLite, I guess we must answer by the example the fears
expressed by Simon and Rsmith.

I propose the following method :
  - unproven-demand :
==> publish on this mailing list external "CTE for SQLite"
implementations,
==> if there is demand :
. they should become popular and improve quickly,
. the number of implementations should grow.

  - code size + performance increase fear :
==>  from the external implementations :
. develop knowledge of the algorithm to implement in SQLite,
. implement it in a true SQLite 'fossil' branch, with those two
goals in mind.

Here is my first implementation in Python (very rough and unclean, please
apologize) :

http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb

I encourage other "CTE in SQlite" fans to :
 - create and publish theirs, from this first example,
 - provide links to other existing ones,
 - then to start 'CTE' SQlite patches in Fossil.


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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Thu, 26 Dec 2013 20:23:33 +0100
big stone  wrote:

> Indeed, '1' CTE can be replaced by the creation of 'N' temporary
> views (or tables), and their deletion after the CTE request.
> 
> CTE is :
> - a syntaxic simplification :
>. the SQL creator don't have to care about those intermediate
> views, . these intermediate views don't grow and multiply on your
> database schema.

Let's enumerate the uses of a CTE:

1.  Used once, instead of a subquery
2.  Used multiple times, instead of a view or repeated subquery
3.  Used in a recursive query 

Of these, only #3 gains any benefit from a CTE.  

Unless the (CTE) query is used more than once, it has no advantage over
a subquery.  

Case #2 is rare, and a view serves as well.  Only a small fraction of
queries use a subquery more than once.  The small number of such views
will not materially affect the schema namespace.  

Recursive queries are a unique feature of CTEs.  They are not supported
in SQLite.  If and when they are, CTEs will be required.  

I for one would like to see recursive updates supported, but I
recognize it's somewhat specialized behavior and non-trivial to
implement.  Support for atomic updates is more important.  

--jkl


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

Re: [sqlite] "Common Table Expression"

[Sylvain Pointeau]

I agree with David, CTE is just wonderful, a big help to avoid re-typing
many times the same sub-query and a performance improvement as well. Yes we
can workaround it but it is ugly and leads to un-maintainable code. I am
using it every days in my job and I can just tell that it has been proven
to be really useful.

... as well as merge ...

Yes I also have the feeling that you are ditching all new SQL feature
requests, saying that it is useless because it can be done in another way.
At least I would have liked to see a little interest and understanding on
how it could be useful to developers (us!).

I know SQLite is lite, but it should look forward to the SQL 2003,
developers are using all 2003 features in their daily job (oracle or sql
server), we would like to see it as well (not all but a part of it, even
with some limitations) in our sqlite wonderful app or scripts.

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

Re: [sqlite] "Common Table Expression"

[big stone]

As a proof of concept, I programmed a translation layer  from "CTE"  to
"SQLite" :

"with x as (y)... select z"
into
"drop view if exists x;create temp view x as y; ...; select z"

"with x(d) as (y), ...   select z"
into
"drop table if exists x;create temp table x(d) as y;insert into x y; ...
;select z"


Issues discovered  :
-
* the use of a  "table" in the "x1(d1)" syntax case
   ==> SQLite "ultra-light CTE" could accept "*only 'with x as (y)'  syntax*"
?

* impossible to remove the existence of those temporary views/tables until
the last "select" of the CTE is closed.
   ==> SQLite "ultra-light CTE" could accept "*only **ONE active CTE per
session*" ?

* it's 200 hundred lines of application code (with no checks) :
. no less present in memory of a complete system system than the SQLite
DLL,
. not belonging to the "application" domain.

My hope
-
* The size of a true patch to get an SQLite "ultra-light CTE" (as a
translation layer) :
   . is confirmed in the ballpark of acceptability,
   . is considered in 2014 possible improvements of SQLite.
* 25% of SQLite users improve their life by switching their complex SQL
queries to simpler "CTE".

Happy new year and best whishes for 2014 !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[David de Regt]

RSmith - I said "often", not "entirely". :)

Discussion about how to better use SQLite for an already working implementation 
or for a proposed implementation is a great and proper use of the list.  Coming 
onto the list and asking how to store a simple branch-and-leaf tree structure 
in SQL is not.  Complaining about bugs in the "datetime" implementation when 
you clearly haven't read the data types help page to understand that it's being 
stored as text is also not a proper use of the list.  The SQLite docs are 
really quite good, some of the best I've seen on open source projects.

Yours was what I read as the shoot-down, not Simon's, FWIW.  Most people aren't 
designing their own ORMs/sql wrappers, they're using existing ones, often with 
limitations.  One could argue that they "should" write their own, but then that 
same argument could be used against the existence of all of managed code or 
reusable libraries, so that's a bit of a red herring.  Saying to comment the 
code isn't as helpful either.  You can comment things as much as you want, but 
if you have a 500 character complicated subquery, on which you want to then do 
a couple different things, writing "/* same thing as above*/" doesn't really 
help with either code maintainability or query optimization.  Being able to 
refactor duplicate code into a common function is kind of the cornerstone of 
programming languages.

CTEs aren't part of a variable-based system, they're a very simple 
#include-type syntax, that simultaneously gives a great hint to the QO that 
that statement should be executed as a separate subquery.  For whatever reason, 
it may actually be very complicated to implement this sort of thing into 
SQLite, but bringing in all sorts of things that should be unrelated is, well, 
irrelevant to the question at hand.

SQLite seems to be slowly moving in the direction of there being a bunch of 
compile-type options for advanced features (FTS, etc.)  CTE could be one of 
those, if it turns out to be a monster to implement.  I just have no idea about 
the underlying complexity of the VM to know whether that's really necessary or 
not.  I would assume it would be fairly simple, and also provide a great 
workaround mechanism for users experiencing difficulty getting the QO to do 
what they want under other circumstances.  However, that may be wrong on both 
counts. :)

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,


I know the focus of SQLite people on "size", and "testing".

Wouldn't a basic implementation of CTE in fact :
- help the "size" of the global embedded system to be smaller ?
- without impacting the "testing" ?

Indeed, a "simple" implementation of CTE :

* could rely only on SQLite internal functionalities that are already
available :
   . don't change the motor,
   . just translate the sql.
* would not require lots of work on development and testing :
. sql start by "with " => pre-translate,
. sql does not start by "with " => do business as usual.
* would decrease the size of the GLOBAL embedded system (SQLite+Application) :
   . less calls (and characters) to generate from the Applicatin
towards SQLite to create/delete those views
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[RSmith]

Sorry, this struck a bit of a sore spot with me, so I apologize for the small 
rant...  Feel free to completely ignore it.
You have every right challenging the views of anyone - It is welcome even (I think - cannot speak for everyone else though, but I 
appreciate it). A rant however is probably less effective, but let's see...



CTEs are important for two reasons://.. many valid points to be addressed 
below...//

Non-bug-related posts to this list often take the form of one of the following 
few categories:
1. Underqualified programmers asking for query help to do their jobs that a 
qualified programmer could easily do.  Doesn't belong on the list -- I'm sure 
there's a #sql-newbies list somewhere for things like this, and there should be 
a form-letter answer forwarding people to that.


I'm sorry, are you saying this list is only for bugs and feature requests and helping people use SQLite in normal and mundane ways 
do not belong here?  If this is true, I shall take my leave immediately because I have no bugs to report and the dev-list is ample 
forum for new functionality requests - and kindly point me to where helping people use SQL/SQLite (or gaining such help) is 
acceptable, THAT be the list I meant to sign up with.



2. Feature requests from underqualified programmers that don't realize the 
right way to do something.  Doesn't really belong on the list, though they 
mostly get shot down pretty quick or someone points out the obvious answer.  
Whatever, doesn't take up much mental/email bandwidth.  I can go either way on 
this.


How would you tell that you don't know the right way to do something?  Put another way, how would you know that the only way you 
know how to do something is not the right way or not the best way? You cannot know something until you find it out, so if you are 
unaware as to whether you seek a fix to how SQLite works, an understanding of how it works, or whether the way you do it is wrong, 
how would you know which list to choose to post on?


That said, once you do ask and offer up your way of doing it, if it does get shown to be incorrect or that a better way exists - 
that is a helpful learning experience. I think the words "shot down" is unfair. The only times I have seen direct harsh wording of 
the kind you allude to used on here was in reply to persistant insistance on very obviously incorrect methodology, or rants.



3. True feature requests that are not implemented in SQLite and would be useful 
to a set of users/developers in some way/shape/form and is not directly 
workaroundable.

What I don't like is how often #3 requests gets shot down as being stupid.  
Yes, often a feature request doesn't really fit with the general mantra of 
SQLite, and it can be easily described as such.  However, many things are in a 
pretty grey area.  For example, CTEs would fit fairly nicely with the general 
mantra of SQLite, since it allows for making things smaller/simpler/more 
explicit for the QO, but it's being shot down as a non-useful feature that can 
be worked around.  Well, can it?  Or does sqlite perform the subselect multiple 
times if you mention the same query a couple different times in subtly 
different ways (case sensitive, etc.)?  There are important nuances here before 
completely dismissing something out of hand.


Again I am sorry, I do not think myself, Simon or anyone else "shot down the request as being stupid" or in fact do so "regularly".  
In point of fact, I took great pains to explain that it is a good request and pretty valid too, and even now I admit to you it would 
be a nice addition - what I explained was why it probably won't get added and then continued to offer alternate methods of achieving 
what the OP was hoping to achieve. Is this not precisely what contributors here are for?  Never once did I insinuate stupidity on 
the part of the OP for even daring to utter such a silly request (paraphrasing a bit, I know) - Not sure how this invoked the rant, 
but I sincerely hope you do not maintain this opinion.



Just because there is another way to do something doesn't mean it's not a valid feature 
request to be prioritized with the rest of the feature requests.  Saying "this is a 
potential future feature someday, but due to [some architecture issue] it's actually 
quite complicated to implement, and, as such, is unlikely to ever be actually 
implemented" is a completely valid answer from a project management perspective.  
Simply dismissing something out of hand without a thorough explanation of why, however, 
isn't quite as valid.


Nobody dismissed the idea out of hand nor has the power to do so, though it's a hard sell but if it gains favour universally, or 
even just in significant numbers, I'm sure the push for the feature might become paramount and as such likely to be implemented. As 
of now I count only 2 requests for it and I am not even sure if the above is a real request for it or just a vehicle for delivering 
an opinion. In 

Re: [sqlite] "Common Table Expression"

[David de Regt]

Sorry, this struck a bit of a sore spot with me, so I apologize for the small 
rant...  Feel free to completely ignore it.

CTEs are important for two reasons:
1. Simplification of query syntax.  One can argue that this isn't terribly 
important in a system designed as an embedded database, rather than a BI-grade 
data mining target. (though I'm sure many people are also using it as such).  
But, whatever.
2. Query optimization.  If I have to use the same subselect more than once in a 
query, it is a good optimization to tell the query parser to take a certain set 
of results, store them in a temporary resultset for this single query, then use 
that as a target of the second query.  So, yes, you could break out a CTE into 
create temporary table/do final query/drop temporary table, but that adds a 
layer of complexity that's not necessary in most database engines, and hence 
aren't found in most ORMs.  Yes, you could add custom code to support this, but 
when it often makes sense to do exactly what CTEs are meant to do, it seems 
like a no-brainer from a theoretical support standpoint.

While a query optimizer can potentially deduce the usefulness of the right 
order to do subqueries in, often times, as a programmer, I know that I need a 
single query that will reduce a large dataset to a very small one, and then I 
need a few nontrivial operations over the very small dataset.  Just saying "use 
multiple subselects" doesn't give any useful feedback about whether that will 
be properly optimized or not, and what caveats there are to the optimization 
process.  It also leaves you with a disgustingly long query in many cases.

Non-bug-related posts to this list often take the form of one of the following 
few categories:
1. Underqualified programmers asking for query help to do their jobs that a 
qualified programmer could easily do.  Doesn't belong on the list -- I'm sure 
there's a #sql-newbies list somewhere for things like this, and there should be 
a form-letter answer forwarding people to that.
2. Feature requests from underqualified programmers that don't realize the 
right way to do something.  Doesn't really belong on the list, though they 
mostly get shot down pretty quick or someone points out the obvious answer.  
Whatever, doesn't take up much mental/email bandwidth.  I can go either way on 
this.
3. True feature requests that are not implemented in SQLite and would be useful 
to a set of users/developers in some way/shape/form and is not directly 
workaroundable.

What I don't like is how often #3 requests gets shot down as being stupid.  
Yes, often a feature request doesn't really fit with the general mantra of 
SQLite, and it can be easily described as such.  However, many things are in a 
pretty grey area.  For example, CTEs would fit fairly nicely with the general 
mantra of SQLite, since it allows for making things smaller/simpler/more 
explicit for the QO, but it's being shot down as a non-useful feature that can 
be worked around.  Well, can it?  Or does sqlite perform the subselect multiple 
times if you mention the same query a couple different times in subtly 
different ways (case sensitive, etc.)?  There are important nuances here before 
completely dismissing something out of hand.

Just because there is another way to do something doesn't mean it's not a valid 
feature request to be prioritized with the rest of the feature requests.  
Saying "this is a potential future feature someday, but due to [some 
architecture issue] it's actually quite complicated to implement, and, as such, 
is unlikely to ever be actually implemented" is a completely valid answer from 
a project management perspective.  Simply dismissing something out of hand 
without a thorough explanation of why, however, isn't quite as valid.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of RSmith
Sent: Thursday, December 26, 2013 5:37 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] "Common Table Expression"

This reminds me of a plan to add RADAR dishes to cars to monitor other traffic 
and avoid collisions - brilliant idea but the detrimental effect on 
aerodynamics and limiting size-factor of already-built garages all over the 
world stifled enthusiasm.

Probably "Temporary Views" would be the exact thing that can achieve the same 
as CTE.  Further simplification might be implemented on your code, if in fact 
you are designing a system and not using some other SQLite-reliant system (in 
which case CTE might really help you).

To emphasize what Simon said: SQLite does not support a full syntactic script 
engine with variables and the like and isn't likely to expand by doing it 
and/or include CTE for the simple reason that the cost tradeoff in DB-Engine 
size vs. added functionality is non-sensical.  It has to function in many cases 
as a DB engine on embedded systems where space is a real c

Re: [sqlite] "Common Table Expression"

[RSmith]

This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant idea but the 
detrimental effect on aerodynamics and limiting size-factor of already-built garages all over the world stifled enthusiasm.


Probably "Temporary Views" would be the exact thing that can achieve the same as CTE.  Further simplification might be implemented 
on your code, if in fact you are designing a system and not using some other SQLite-reliant system (in which case CTE might really 
help you).


To emphasize what Simon said: SQLite does not support a full syntactic script engine with variables and the like and isn't likely to 
expand by doing it and/or include CTE for the simple reason that the cost tradeoff in DB-Engine size vs. added functionality is 
non-sensical.  It has to function in many cases as a DB engine on embedded systems where space is a real concern, and those 
designers would dread the idea of devoting more memory in the name of readability - and it is probably fair to extend that sentiment 
to designers with non-embedded systems (I know it is true for me).


One might probably add a compile-time switch enabling or disabling (or including) a CTE component so that the feature and related 
space-consumption might be optional, but if you prefer CTE for your SQL, I am confident that compile-time switching won't be your 
favourite thing either.  Further to this, the effort / pleasure ratio of adding it would probably prove larger than comfortable.


As an aside, proper use of comment-blocks and inline commenting (which both your code and SQLite allows) can make anything as clear 
you'd like.


Have a great day!
Ryan

On 2013/12/26 21:05, Simon Slavin wrote:

On 26 Dec 2013, at 6:57pm, big stone  wrote:


"sub-select" method :
- allows you to do only a part of what you can do in CTE,
- becomes more and more difficult to read as you add tables and treatment
in your sql.

With CTE in SQLite, it would be possible to:
- decompose your SQL treatment in clean intermediate steps,
- make your global algorithm "visible",
- do easily things currently impossible in 1 query with sub-selects.

It looks like you want VIEWs rather than sub-selects.  VIEWs enable all the 
things you listed above.



They're a way of saving a SELECT command so it can be used as if the results 
are a table.

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] "Common Table Expression"

[Simon Slavin]

On 26 Dec 2013, at 7:23pm, big stone  wrote:

> '1' CTE can be replaced by the creation of 'N' temporary views (or
> tables), and their deletion after the CTE request.

Just a quick clarification that a VIEW does not greatly increase the amount of 
data stored in a database.  The thing that is saved when you create a VIEW is 
the SELECT command.  No data is copied out of tables.  When the VIEW is used in 
a SELECT the SQLite engine reconstructs the stored SELECT command, then 
optimizes the entire command taking into account things the VIEWs have in 
common with the rest of the SELECT.

> I whish to have CTE in SQLite for the simplification it brings.

Sorry, you're unlikely to get the specific CTE grammar you've found elsewhere 
in SQLite.  It seems to do the same job using existing features.

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,

Indeed, '1' CTE can be replaced by the creation of 'N' temporary views (or
tables), and their deletion after the CTE request.

CTE is :
- a syntaxic simplification :
   . the SQL creator don't have to care about those intermediate views,
   . these intermediate views don't grow and multiply on your database
schema.
- let the SQL motor free to handle the request the way it prefers.

I whish to have CTE in SQLite for the simplification it brings.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Simon Slavin]

On 26 Dec 2013, at 6:57pm, big stone  wrote:

> "sub-select" method :
> - allows you to do only a part of what you can do in CTE,
> - becomes more and more difficult to read as you add tables and treatment
> in your sql.
> 
> With CTE in SQLite, it would be possible to:
> - decompose your SQL treatment in clean intermediate steps,
> - make your global algorithm "visible",
> - do easily things currently impossible in 1 query with sub-selects.

It looks like you want VIEWs rather than sub-selects.  VIEWs enable all the 
things you listed above.



They're a way of saving a SELECT command so it can be used as if the results 
are a table.

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,


"sub-select" method :
- allows you to do only a part of what you can do in CTE,
- becomes more and more difficult to read as you add tables and treatment
in your sql.

With CTE in SQLite, it would be possible to:
- decompose your SQL treatment in clean intermediate steps,
- make your global algorithm "visible",
- do easily things currently impossible in 1 query with sub-selects.

As an example :

"""
With
   -- aggregate detail informations you need
   product as (select )
   , target(item, date, target) as (select )
   , actual(item, date, actual  ) as (select )

   -- prepare a virtual 'result' comparison table, step1
   , comparison(item, date, actual, target) as (
select item, date, sum(0.0), sum(target) from target
 UNION ALL
 select item, date, sum(actual), sum(0.0) from actuals)

  -- compactify on the same line target and actual figures
  , result(item, date, actual, target) as (
  select item, date, sum(actual), sum(target)
  from comparison
  group by item, date)

-- final request like if everything was simple from the start
 select * from results inner join product on results.item = product.item
"""
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Simon Slavin]

On 26 Dec 2013, at 3:27pm, big stone  wrote:

> Does SQLite plan to implement soon a "Common Table Expression" subset ?

Common Table Expressions are implemented as sub-SELECTs, as documented in the 
'select-stmt' part of this page:



You can do things like this:

SELECT * FROM company WHERE id IN (
   SELECT company FROM wages WHERE salary > 45000);

and this:

SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

The implementation is the same as that used in MySQL, allowing for other 
differences between the two engines.

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