On Jan 25, 2014, at 6:25 PM, Petite Abeille petite.abei...@gmail.com wrote:
On Jan 25, 2014, at 6:05 AM, Keith Medcalf kmedc...@dessus.com 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…
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%
On 25/01/14 03:37, James K. Lowden wrote:
On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille petite.abei...@gmail.com 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.)
On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis est...@gmail.com 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
On 25/01/14 18:41, James K. Lowden wrote:
On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis est...@gmail.com 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
On Jan 25, 2014, at 2:37 AM, James K. Lowden jklow...@schemamania.org 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
On Jan 25, 2014, at 6:05 AM, Keith Medcalf kmedc...@dessus.com 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.
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
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
___
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
On Fri, Jan 24, 2014 at 10:09 AM, Clemens Ladisch clem...@ladisch.dewrote:
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
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
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
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
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
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
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,
On Jan 24, 2014, at 2:31 PM, Richard Hipp d...@sqlite.org 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
Petite Abeille wrote:
On Jan 24, 2014, at 2:31 PM, Richard Hipp d...@sqlite.org 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.
On Jan 24, 2014, at 11:32 PM, Clemens Ladisch clem...@ladisch.de 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
On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille petite.abei...@gmail.com 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.
(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
2013/12/31 big stone stonebi...@gmail.com:
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
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
On Jan 17, 2014, at 7:47 PM, big stone stonebi...@gmail.com 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
On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille petite.abei...@gmail.comwrote:
On Jan 17, 2014, at 7:47 PM, big stone stonebi...@gmail.com 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
On Fri, Jan 17, 2014 at 2:05 PM, Richard Hipp d...@sqlite.org wrote:
On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille
petite.abei...@gmail.comwrote:
On Jan 17, 2014, at 7:47 PM, big stone stonebi...@gmail.com wrote:
- I just did my first recursive CTE under Ipython notebook.
Finally! We can
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
Hello,
On Jan 6, 2014, at 6:51 AM, James K. Lowden jklow...@schemamania.org 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
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
On Thu, 2 Jan 2014 01:29:52 +
Simon Slavin slav...@bigfraud.org 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
On Wed, 1 Jan 2014 23:23:04 +0100
Petite Abeille petite.abei...@gmail.com 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
On Jan 4, 2014, at 8:05 PM, James K. Lowden jklow...@schemamania.org 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
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,
On Jan 4, 2014, at 11:34 PM, RSmith rsm...@rsweb.co.za 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
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
(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
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
On Tue, Dec 31, 2013 at 8:59 PM, James K. Lowden
jklow...@schemamania.orgwrote:
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
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
On Tue, 31 Dec 2013 22:20:15 +
Simon Slavin slav...@bigfraud.org 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);
On Wed, 1 Jan 2014 11:04:57 +0100
big stone stonebi...@gmail.com 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
On Jan 1, 2014, at 10:55 PM, James K. Lowden jklow...@schemamania.org 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
On 1 Jan 2014, at 9:55pm, James K. Lowden jklow...@schemamania.org 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
On Thu, 26 Dec 2013 20:23:33 +0100
big stone stonebi...@gmail.com 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
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 :
On Tue, 31 Dec 2013 20:43:20 +0100
big stone stonebi...@gmail.com 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
On 31 Dec 2013, at 10:05pm, James K. Lowden jklow...@schemamania.org 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);
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
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
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
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
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.
Hello,
Does SQLite plan to implement soon a Common Table Expression subset ?
CTE would allow to write much more readable SQLite 'SQL' code.
It doesn't seem complex to do, as long as the 'RECURSIVE' part of CTE is
ignored.
Regards,
___
sqlite-users
On 26 Dec 2013, at 3:27pm, big stone stonebi...@gmail.com 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:
http://www.sqlite.org/lang_select.html
You
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
On 26 Dec 2013, at 6:57pm, big stone stonebi...@gmail.com 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
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
On 26 Dec 2013, at 7:23pm, big stone stonebi...@gmail.com 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.
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
...@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
61 matches
Mail list logo
