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 concern, and those 
designers would dread 

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

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[dean gwilliam]

Again, thank you for your advice.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Common Table Expression"

[big stone]

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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issues after upgrade to 3.8.2

[Richard Hipp]

On Wed, Dec 25, 2013 at 3:55 PM, Denis Gladkikh wrote:

> Assertion failed!
>
> Program: ...Windows\GoogleMusic\bin\x86\Debug\AppX\sqlite3.DLL
> File: sqlite3.c
> Line: 79461
>
> Expression: pExpr->iTable==0 || pExpr->iTable==1
>
> Program: ...Windows\GoogleMusic\bin\x86\Debug\AppX\sqlite3.DLL
> File: sqlite3.c
> Line: 62181
>
> Expression: ([1])==pEnd || p[0].db==p[1].d




>
> My environment is:
>
> SQLite for Windows Runtime (for VS 2012)
>
> http://visualstudiogallery.msdn.microsoft.com/23f6c55a-4909-4b1f-80b1-25792b11639e
> I use my own fork of https://github.com/praeclarum/sqlite-net for
> connection.
>
> I'm on Windows x64, when I'm building amd64 bit app - everything seems
> fine, but in case of x86 on Windows 8.1 x64 - I'm getting this error. Did
> not have a chance to verify x86 on Win8 x86.
>

This (together with the nature of the asserts that fail) suggest some kind
of compiler malfunction.  Do you get the same problems if you disable
optimizations?  When compilers make mistakes, it is usually in the
optimizer, and so if you disable optimizations sometimes things will work
better.

The problem might also be memory corruption in your application.

If you can provide a test case we can look further.  But without a
reproducible test case, there isn't much we can do.



>
> It looks like that SQLite fails to do insert in one of my tables, which has
> following trigger:
>
> CREATE TRIGGER insert_userplaylistentry AFTER INSERT ON UserPlaylistEntry
>   BEGIN
>
> update [UserPlaylist]
> set
> [SongsCount] = [SongsCount] + 1,
> [Duration] = [UserPlaylist].[Duration] + (select s.[Duration] from
> [Song] as s where s.[SongId] = new.[SongId]),
> [ArtUrl] = case when nullif([UserPlaylist].[ArtUrl], '') is null
> then (select s.[AlbumArtUrl] from [Song] as s where s.[SongId] =
> new.[SongId]) else [UserPlaylist].[ArtUrl] end,
> [LastPlayed] = (select case when [UserPlaylist].[LastPlayed] >
> s.[LastPlayed] then [UserPlaylist].[LastPlayed] else s.[LastPlayed] end
> from [Song] as s where s.[SongId] = new.[SongId]),
> [OfflineSongsCount] = [UserPlaylist].[OfflineSongsCount] +
> coalesce( (select 1 from CachedSong cs where new.[SongId] = cs.[SongId]) ,
> 0),
> [OfflineDuration] = [UserPlaylist].[OfflineDuration] + coalesce(
> (select s.[Duration] from [Song] as s inner join [CachedSong] as cs on
> s.SongId = cs.SongId where s.[SongId] = new.[SongId]), 0)
> where [PlaylistId] = new.PlaylistId;
>
>   END;
>
>
> If I remove highlighted rows - insert works.
> If somebody is interesting to take deeper look in this problem - I can send
> my database.
>
> Btw, by default all Windows Store applications are x86, so if this is
> global issue - it can affect a lot of Windows Store developers.
>
> --
> Thanks,
> Denis Gladkikh
> http://outcoldman.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] select only records with fields that BEGIN with a certain sub-string

[Kees Nuyt]

On Thu, 26 Dec 2013 11:59:10 +, dean gwilliam  
wrote:

>Thank you all for your helpful advice.

An alternative is PRAGMA table_info(yourtable);

http://sqlite.org/pragma.html#pragma_table_info

It resturns a result set, one row per column in yourtable.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] Issues after upgrade to 3.8.2

[Denis Gladkikh]

Hi All,

After upgrade from 3.8.1 to 3.8.2 I started to get asserts in debug build
and {"Attempted to read or write protected memory. This is often an
indication that other memory is corrupt."} in Relase when I have x86 app on
amd64 Windows 8.1. These are asserts:

---
Microsoft Visual C++ Runtime Library
---
Assertion failed!

Program: ...Windows\GoogleMusic\bin\x86\Debug\AppX\sqlite3.DLL
File: sqlite3.c
Line: 79461

Expression: pExpr->iTable==0 || pExpr->iTable==1

For information on how your program can cause an assertion
failure, see the Visual C++ documentation on asserts

(Press Retry to debug the application - JIT must be enabled)
---
Abort   Retry   Ignore
---


---
Microsoft Visual C++ Runtime Library
---
Assertion failed!

Program: ...Windows\GoogleMusic\bin\x86\Debug\AppX\sqlite3.DLL
File: sqlite3.c
Line: 62181

Expression: ([1])==pEnd || p[0].db==p[1].db

For information on how your program can cause an assertion
failure, see the Visual C++ documentation on asserts

(Press Retry to debug the application - JIT must be enabled)
---
Abort   Retry   Ignore
---


My environment is:

SQLite for Windows Runtime (for VS 2012)
http://visualstudiogallery.msdn.microsoft.com/23f6c55a-4909-4b1f-80b1-25792b11639e
I use my own fork of https://github.com/praeclarum/sqlite-net for
connection.

I'm on Windows x64, when I'm building amd64 bit app - everything seems
fine, but in case of x86 on Windows 8.1 x64 - I'm getting this error. Did
not have a chance to verify x86 on Win8 x86.

It looks like that SQLite fails to do insert in one of my tables, which has
following trigger:

CREATE TRIGGER insert_userplaylistentry AFTER INSERT ON UserPlaylistEntry
  BEGIN

update [UserPlaylist]
set
[SongsCount] = [SongsCount] + 1,
[Duration] = [UserPlaylist].[Duration] + (select s.[Duration] from
[Song] as s where s.[SongId] = new.[SongId]),
[ArtUrl] = case when nullif([UserPlaylist].[ArtUrl], '') is null
then (select s.[AlbumArtUrl] from [Song] as s where s.[SongId] =
new.[SongId]) else [UserPlaylist].[ArtUrl] end,
[LastPlayed] = (select case when [UserPlaylist].[LastPlayed] >
s.[LastPlayed] then [UserPlaylist].[LastPlayed] else s.[LastPlayed] end
from [Song] as s where s.[SongId] = new.[SongId]),
[OfflineSongsCount] = [UserPlaylist].[OfflineSongsCount] +
coalesce( (select 1 from CachedSong cs where new.[SongId] = cs.[SongId]) ,
0),
[OfflineDuration] = [UserPlaylist].[OfflineDuration] + coalesce(
(select s.[Duration] from [Song] as s inner join [CachedSong] as cs on
s.SongId = cs.SongId where s.[SongId] = new.[SongId]), 0)
where [PlaylistId] = new.PlaylistId;

  END;


If I remove highlighted rows - insert works.
If somebody is interesting to take deeper look in this problem - I can send
my database.

Btw, by default all Windows Store applications are x86, so if this is
global issue - it can affect a lot of Windows Store developers.

--
Thanks,
Denis Gladkikh
http://outcoldman.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[dean gwilliam]

Thank you all for your helpful advice.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[RSmith]

This is actually awesome to know, thanks Stephen, I always thought at least 1 step is needed - I'm going to immediately implement 
this in some functions!


On 2013/12/26 13:30, Stephan Beal wrote:



There are probably a few approaches that would work, but I can think of
none quicker/more efficient than maintaining a list of column names
("SELECT * from t WHERE 1 LIMIT 1" will produce it real quick)

If you want JUST the column names, you can change WHERE 1 to WHERE 0 (and,
optionally, remove the LIMIT). You don't actually need any result rows -
you just need to prepare the statement, and then you can fetch the column
names regardless of whether or not the query would produce any results (you
don't even need to step() it).



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

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[Stephan Beal]

On Thu, Dec 26, 2013 at 12:21 PM, RSmith  wrote:

> There are probably a few approaches that would work, but I can think of
> none quicker/more efficient than maintaining a list of column names
> ("SELECT * from t WHERE 1 LIMIT 1" will produce it real quick)


If you want JUST the column names, you can change WHERE 1 to WHERE 0 (and,
optionally, remove the LIMIT). You don't actually need any result rows -
you just need to prepare the statement, and then you can fetch the column
names regardless of whether or not the query would produce any results (you
don't even need to step() it).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[Petite Abeille]

On Dec 26, 2013, at 11:49 AM, dean gwilliam  wrote:

> ...is this possible or should I return the whole lot and subject it to a 
> regexp filter to get my cut-down list?

http://www.sqlite.org/lang_expr.html#like

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

[sqlite] select only records with fields that BEGIN with a certain sub-string

[dean gwilliam]

...is this possible or should I return the whole lot and subject it to a 
regexp filter to get my cut-down list?

Any advice much appreciated and BTWmerry Christmas to you all!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Programming in SQL vs. a procedural computer language

[Klaas V]

Tobias wrote "... simpler just to have afterwards some things to do in my 
program? "

What is 'simple' is an off topic discussion. More important is the reason of 
making changes by you and/or your colleagues. 
I've seen coding in a procedural language I could only figure out by reading 
the pseudo assembler code. 

To create a view rather than writing one or more sub-selects is a suggestion 
worth to be taken in consideration for more reasons than simplicity and 
readability of the code. Advantages are reuse of the project by others without 
having to write a more or less complex SQL statement. Just a simple 

SELECT bar from foo where bar ... ; 

will do where foo is the view created. Please give me one advantage of your 
sub-select over view 'foo' .

Especia;;y woth SQLite we have a library with a lot of 'stored procedures' 
(Oracle terminology) and on top of it the possibility to code your own. You can 
move some safety coding to a preprocessor written in your favorite procedural 
language, in my case it's the platform independent (sort of) Chipmunk Basic in 
cooperation with Bash shell scripting.

Happy holidays,
Klaas `Z4us` V

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