[sqlite] [patch] for test/backup2.test

[Pavel Volkov]

Hello.
Please, make test 'test/backup2.test' more compatible with FreeBSD.
This is patch for it:

--- test/backup2.test.orig  2017-07-07 04:59:34 UTC
+++ test/backup2.test
@@ -143,7 +143,7 @@ do_test backup2-9 {
 #
 if {$tcl_platform(platform)=="windows"} {
   set msg {cannot open source database: unable to open database file}
-} elseif {$tcl_platform(os)=="OpenBSD"} {
+} elseif {$tcl_platform(os)=="OpenBSD"||$tcl_platform(os)=="FreeBSD"} {
   set msg {restore failed: file is encrypted or is not a database}
 } else {
   set msg {cannot open source database: disk I/O error}

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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Keith Medcalf]

select wonkys, fubars, sets, constants, comments, logs, whipitys, doodas
  from (select sum(wonkies == 'WNK') as wonkys,
   sum(fubars == 'FBR') as fubars
  from table_to_summarize
   ) as S1,
   (select sum(code_type == 'SET') as sets,
   sum(code_type == 'CST') as constants,
   sum(code_type == 'CMT') as comments,
   sum(code_type == 'LOG') as logs
  from keys
   ) as S2,
   (select sum(whipity == 'WHP') as whippitys,
   sum(dooda == 'DOO') as doodas
  from another_table_to_get_statistics_from
   ) as S3;

Get statistics from multiple tables with only one table scan of each table.  
For more than one table, simply extend by adding join queries to the from 
clause.  Each join table (in the FROM clause) must return exactly one row.

Note that the outer select can be a select * if your application can find the 
columns by name ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 08:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Any way to avoid scanning a large table several
> times?
> 
> On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf  wrote:
> 
> >
> > select sum(code_type == 'SET') as "#sets",
> >sum(code_type == 'CST') as "#constants",
> >sum(code_type == 'CMT') as #comments",
> >sum(code_type == 'LOG') as "#logs"
> >   from keys;
> >
> > will do a single table scan and return the sums in a single statement
> with
> > no complications ...
> 
> 
> But my 1 line summary puts stats from several tables in columns.
> 
> When my substitute the 4 scalar select expressions with yours, I get this
> error:
> Error 1 "only a single result allowed for a SELECT that is part of an
> expression."
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Keith Medcalf]

That is the one and only whole statement, and I see no references to anything 
else in the initial query.  

So, delete

with
kcounts(t, c) as (
  select code_type, count(*)
   from keys
  group by code_type
),
...
select ...,
   (select c from kcounts where t = 'SET') as "#sets",
   (select c from kcounts where t = 'CST') as "#constants",
   (select c from kcounts where t = 'CMT') as "#comments",
   (select c from kcounts where t = 'LOG') as "#logs",
   ...;

in its entirety and replace with

select sum(code_type == 'SET') as "#sets",
   sum(code_type == 'CST') as "#constants",
   sum(code_type == 'CMT') as #comments",
   sum(code_type == 'LOG') as "#logs"
  from keys;

if you were doing something like

select sum(code_type == 'SET') as sets,
   
   sum(some other field == 'VAL') as somethingelse
  from keys, someothertable
 where keys.correlatingfield = somothertable.correlatingfield;

then you can do that.

So if you problem statement is "Project the following tables (FROM clause) on 
the following conditions (WHERE clause) counting the occurrences of something 
(sum(something=='whatitequals')).

Oftentimes a CTE is a cute but non-productive (ie, highly inefficient) way of 
phrasing a query if that query does not require recursion.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 08:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Any way to avoid scanning a large table several
> times?
> 
> On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf  wrote:
> 
> >
> > select sum(code_type == 'SET') as "#sets",
> >sum(code_type == 'CST') as "#constants",
> >sum(code_type == 'CMT') as #comments",
> >sum(code_type == 'LOG') as "#logs"
> >   from keys;
> >
> > will do a single table scan and return the sums in a single statement
> with
> > no complications ...
> 
> 
> But my 1 line summary puts stats from several tables in columns.
> 
> When my substitute the 4 scalar select expressions with yours, I get this
> error:
> Error 1 "only a single result allowed for a SELECT that is part of an
> expression."
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 4:17 PM, Dominique Devienne 
wrote:

> On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf  wrote:
>
>>
>> select sum(code_type == 'SET') as "#sets",
>>sum(code_type == 'CST') as "#constants",
>>sum(code_type == 'CMT') as #comments",
>>sum(code_type == 'LOG') as "#logs"
>>   from keys;
>>
>> will do a single table scan and return the sums in a single statement
>> with no complications ...
>
>
> But my 1 line summary puts stats from several tables in columns.
>
> When my substitute the 4 scalar select expressions with yours, I get this
> error:
> Error 1 "only a single result allowed for a SELECT that is part of an
> expression."
>


with
kcounts(cset, ccst, ccmt, clog) as (
  select sum(code_type == 'SET'),
 sum(code_type == 'CST'),
 sum(code_type == 'CMT'),
 sum(code_type == 'LOG')
  from well_keys
),
...
select ...,
   (select cset from kcounts) as "#sets",
   (select ccst from kcounts) as "#constants",
   (select ccmt from kcounts) as "#comments",
   (select clog from kcounts) as "#logs",
...;

But if I move it as a CTE view, this also achieves the 3,060ms time
equivalent to Clemens' solution, FWIW.
(w/o the index. If I recreate the index, it stays at 3s, apparently that
formulation makes the planner not use the index...) --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf  wrote:

>
> select sum(code_type == 'SET') as "#sets",
>sum(code_type == 'CST') as "#constants",
>sum(code_type == 'CMT') as #comments",
>sum(code_type == 'LOG') as "#logs"
>   from keys;
>
> will do a single table scan and return the sums in a single statement with
> no complications ...


But my 1 line summary puts stats from several tables in columns.

When my substitute the 4 scalar select expressions with yours, I get this
error:
Error 1 "only a single result allowed for a SELECT that is part of an
expression."
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 3:56 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > It's a group-by query, so despite using the index, all rowids for the
> only
> > 4 different "index entries" must still be counted,
> > and that's still definitely longer to do that than full scanning the
> table
> > once.
>
> So why using GROUP BY?  The top-level query does not really want all
> groups, it wants to determine individual counts.
>
> I estimate that a simple "SELECT count(*) FROM tab WHERE code_type = ?"
> would be faster.
>

And you'd be right! Thanks a bunch. That's helpful.

704ms with the index, 3,010ms w/o it. The latter is still > 2x faster w/o
the 22MB index penalty.

   (select count(*) from well_keys where code_type = 'SET') as "#sets",

   (select count(*) from well_keys where code_type = 'CST') as
"#constants",
   (select count(*) from well_keys where code_type = 'CMT') as
"#comments",
   (select count(*) from well_keys where code_type = 'LOG') as "#logs",


Now though, I'm intrigued as to why the group-by is so much slower than the
"unrolled" count(*)-where... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Keith Medcalf]

select sum(code_type == 'SET') as "#sets",
   sum(code_type == 'CST') as "#constants",
   sum(code_type == 'CMT') as #comments",
   sum(code_type == 'LOG') as "#logs"
  from keys;

will do a single table scan and return the sums in a single statement with no 
complications ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 02:52
> To: General Discussion of SQLite Database
> Subject: [sqlite] Any way to avoid scanning a large table several times?
> 
> I have a view gathering statistics from 3 different tables, one of which
> is
> largish (~ 2M rows).
> 
> The view is similar to this:
> 
> with
> kcounts(t, c) as (
>   select code_type, count(*)
>from keys
>   group by code_type
> ),
> ...
> select ...,
>(select c from kcounts where t = 'SET') as "#sets",
>(select c from kcounts where t = 'CST') as "#constants",
>(select c from kcounts where t = 'CMT') as "#comments",
>(select c from kcounts where t = 'LOG') as "#logs",
>...;
> 
> With all 4 counts, the views takes ~ 6.5s to compute its 1 row (with the
> file in cache, was 13.5s with a cold filesystem cache, while DB is only
> 380MB in size).
> 
> If I remove 3 of the lines, keeping a single "select c from kcounts where
> t
> = ..." that drops to 2.15s, or about one third. (this is inside SQLiteSpy
> using SQLite 3.13.0 BTW).
> 
> As a programmer, I can see this can be done in a single pass, but most
> likely the SQL as written does several passes over kcounts, despite using
> a
> CTE (I had hoped kcounts would be instantiated into an ephemeral table,
> and
> thus accessing a 4 row tables would be "free").
> 
> An explain query plan indeed shows 4x "SCAN TABLE keys" steps, not just 1.
> 
> Is there a way to reformulate this query differently to somehow achieve a
> single pass on the keys table? Or force the query planner to "instantiate"
> the kcounts "CTE view" to achieve that single scan, via some kind of hint?
> If none exists, could one such hint be added? Ideally the planner itself
> would decide kcounts should be instantiated, but I'm happy to tell him it
> should via a hint.
> 
> The speed up is non-negligible at 3x, thus my request for comments from
> experts on this.
> 
> Thanks, --DD
> 
> PS: BTW, I did run ANALYZE and that didn't change the plan or execution
> time.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 3:50 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote:
> >> You could put kcounts into a temporary table.
> >
> > I could it in a table, but then who updates kcounts when keys (or
> keys_tab)
> > changes?
>
> I did not say "table" but "temporary table":
>
> BEGIN;
> CREATE TEMPORARY TABLE kcounts AS SELECT ...;
> SELECT ... FROM kcounts ...;
> DROP TABLE kcounts;
> COMMIT;
>
> > "Users" just "view" these views in any SQLite clients, typically a GUI
> one.
> > And they should remain "dynamic"
> > into case the table contents are modified with DMLs.
>
> SQLite is designed as an embedded database, and expects that additional
> logic, if needed, can be provided by the application.


I never fail to be surprised when every time SQLite doesn't do something
one can reasonably
expect it would do, people always reach for the "lite" or "embedded"
arguments...

Just

  select code_type, count(*)
   from well_keys
  group by code_type

by itself takes 2.01s. Accessing the other two tables takes < 200ms for
both.

The main select itself is just formatting, except of course it "transposes"
the 4 rows from kcounts as columns.
The fact SQLite flattens the kcounts CTE view is in fact "the problem" is
this case. Should it instead "materialize"
it in an "ephemeral table", the query would be 3x faster (Nx faster is not
4 rows, but N+1 rows in fact).

As Richard often points out, there's no bug here, just an optimization
opportunity. The result is correct.

But saying one should resort to temporary tables to work-around a
non-optimization and thus be forced
to abandon nice simple views because SQLite is an "embedded" database is
just "meh", for lack of a better word :) --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Clemens Ladisch]

Dominique Devienne wrote:
> It's a group-by query, so despite using the index, all rowids for the only
> 4 different "index entries" must still be counted,
> and that's still definitely longer to do that than full scanning the table
> once.

So why using GROUP BY?  The top-level query does not really want all
groups, it wants to determine individual counts.

I estimate that a simple "SELECT count(*) FROM tab WHERE code_type = ?"
would be faster.


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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 2:49 PM, Simon Slavin  wrote:

> On 6 Jul 2017, at 12:32pm, Dominique Devienne  wrote:
> > Actually not that much apparently.
> > No Simon, I didn't have an index on code_type.
> > In fact keys is itself a view, and cote_type a case expression.
>
> The index you created is not helpful.  It is not useful for what you’re
> doing.
>

You what evidence do you get that from?

The new plan uses the index though, as I previously indicated, so I'm
surprised you'd say that.

It's a group-by query, so despite using the index, all rowids for the only
4 different "index entries" must still be counted,
and that's still definitely longer to do that than full scanning the table
once.


> I suggest you remove the VIEW layer from your layered query, then
> reconsider what indexes might be helpful.  We can advise you on indexes if
> you post the resulting query.


I don't see how that would make the query any faster. The plan already
shows it scan the "real" table, not the intermediate view (w/o the index
that is). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Clemens Ladisch]

Dominique Devienne wrote:
> On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote:
>> You could put kcounts into a temporary table.
>
> I could it in a table, but then who updates kcounts when keys (or keys_tab)
> changes?

I did not say "table" but "temporary table":

BEGIN;
CREATE TEMPORARY TABLE kcounts AS SELECT ...;
SELECT ... FROM kcounts ...;
DROP TABLE kcounts;
COMMIT;

> "Users" just "view" these views in any SQLite clients, typically a GUI one.
> And they should remain "dynamic"
> into case the table contents are modified with DMLs.

SQLite is designed as an embedded database, and expects that additional
logic, if needed, can be provided by the application.


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

Re: [sqlite] Sqlite problem with opening database

[Chris Locke]

This is actually answered on the system.data.sqlite download page.
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Scroll down to the section, "Using Native Library Pre-Loading".
For some reason (?) on Chrome the text is about 30pt, so you shouldn't be
able to miss it.
Essentially, it says to have the DLLs in the right directory, underneath
the application.

\App.exe (optional, managed-only application executable assembly)
\App.dll (optional, managed-only application library assembly)
\System.Data.SQLite.dll (required, managed-only core assembly)
\System.Data.SQLite.Linq.dll (optional, managed-only LINQ assembly)
\System.Data.SQLite.EF6.dll (optional, managed-only EF6 assembly)
\x86\SQLite.Interop.dll (required, x86 native interop assembly)
\x64\SQLite.Interop.dll (required, x64 native interop assembly)

Some SQL command you're firing off requires the use of the DLL, and some of
your clients are set up correctly (or have that DLL on their system) while
others don't.  Its an easy fix.


Thanks,
Chris

On Thu, Jul 6, 2017 at 1:55 PM, Paul Sanderson  wrote:

> Could your 32 bit app be picking up a 64 bit dll. Could you rename the
> dll's and hard code the location into your library?
>
> May not work for your release code but may help you narrow down the issue.
>
> Paul
>
>
> On Wed, 5 Jul 2017 at 18:19, Simon Slavin  wrote:
>
> >
> >
> > On 5 Jul 2017, at 1:41pm, Gregor Pavuna  wrote:
> >
> > > As it seems there's some sort of server problem(Windows 2012 server).
> My
> > guess is server is caching 64bit files and serving them to 32bit
> operating
> > systems. I tested on my test server with laptop (32bit windows 7) and it
> > works fine. Than i went to client and connected laptop there and it
> didn't
> > work with their files.
> >
> > You are keeping your application on a server ?  Or a library ?  Does the
> > problem go away if you keep your application and libraries on the client
> > computer instead ?
> >
> > > I also googled that out, but couldn't find anything on that topic. Any
> > suggestions?
> >
> > Whatever the problem, it seems that it’s related to the Windows
> > application loading system, not SQLite.  So you might find another forum
> is
> > able to help you better than this one.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> --
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem with opening database

[Paul Sanderson]

Could your 32 bit app be picking up a 64 bit dll. Could you rename the
dll's and hard code the location into your library?

May not work for your release code but may help you narrow down the issue.

Paul


On Wed, 5 Jul 2017 at 18:19, Simon Slavin  wrote:

>
>
> On 5 Jul 2017, at 1:41pm, Gregor Pavuna  wrote:
>
> > As it seems there's some sort of server problem(Windows 2012 server). My
> guess is server is caching 64bit files and serving them to 32bit operating
> systems. I tested on my test server with laptop (32bit windows 7) and it
> works fine. Than i went to client and connected laptop there and it didn't
> work with their files.
>
> You are keeping your application on a server ?  Or a library ?  Does the
> problem go away if you keep your application and libraries on the client
> computer instead ?
>
> > I also googled that out, but couldn't find anything on that topic. Any
> suggestions?
>
> Whatever the problem, it seems that it’s related to the Windows
> application loading system, not SQLite.  So you might find another forum is
> able to help you better than this one.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to avoid scanning a large table several times?

[Simon Slavin]

On 6 Jul 2017, at 12:32pm, Dominique Devienne  wrote:

> Actually not that much apparently.
> No Simon, I didn't have an index on code_type.
> In fact keys is itself a view, and cote_type a case expression.

The index you created is not helpful.  It is not useful for what you’re doing.

I suggest you remove the VIEW layer from your layered query, then reconsider 
what indexes might be helpful.  We can advise you on indexes if you post the 
resulting query.

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

Re: [sqlite] Error Message "The database disk image is malformed"

[Richard Hipp]

On 7/6/17, Manoj Sengottuvel  wrote:
> Hi Richard,
>
> if I do the Vacuum database before the encryption(SEE)  then I didn't have
> any issue like "The database disk image is malformed".
>
> Should I do the vacuum every time before applying the encryption/
> decryption (SEE) ?
>

Running VACUUM should not make any difference.

I don't know what is causing your problem.  We don't have enough
detail to trouble-shoot the application for you.

Can you send example code that causes the SQLITE_CORRUPT error?

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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > with
> > kcounts(t, c) as (
> >   select code_type, count(*)
> >from keys
> >   group by code_type
> > ),
> > ...
> > select ...,
> >(select c from kcounts where t = 'SET') as "#sets",
> >(select c from kcounts where t = 'CST') as "#constants",
> >(select c from kcounts where t = 'CMT') as "#comments",
> >(select c from kcounts where t = 'LOG') as "#logs",
> >...;
> >
> > the SQL as written does several passes over kcounts
>
> However, the subquery is flattened: each pass counts only rows with
> a specific code_type value.
>
> An index on code_type would help _very much_.


Actually not that much apparently.
No Simon, I didn't have an index on code_type.
In fact keys is itself a view, and cote_type a case expression.

That's fine, I created a expression index using

create index idx_keys_code on keys_tab(
   case
   when (code & 1) = 1 then 'SET'
   when (code & 2) = 2 then 'CST'
   when (code & 4) = 4 then 'CMT'
   when (code & 8) = 8 then 'LOG'
   else null
   end
)

The plan correctly changes to "SCAN TABLE keys USING INDEX idx_keys_code",
4x.

And the runtime only drops to ~5.4s closer to 6.5s than 2.15s,
but at the cost of 22MB extra in the DB file for the index. Not sure that a
good tradeof.

> Is there a way to reformulate this query differently to somehow achieve a
> > single pass on the keys table? Or force the query planner to
> "instantiate"
> > the kcounts "CTE view" to achieve that single scan, via some kind of
> hint?
>
> It would be possible to prevent the subquery flattening
> (http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is
> no hint that would force SQLite to recognize that all the instances of
> kcounts are then identical and can be reused.
>
> You could put kcounts into a temporary table.
>

I could it in a table, but then who updates kcounts when keys (or keys_tab)
changes?
I guess one can play with triggers then, to emulate materialized views,
opening a new can of worm.

These are "persistent report views", as already discussed when the thousand
separator was added to printf.
"Users" just "view" these views in any SQLite clients, typically a GUI one.
And they should remain "dynamic"
into case the table contents are modified with DMLs.

Maybe the "hint" to create an ephemeral table for a "CTE view" could be to
prefix the CTE view name with temp. ?

I know that Richard/SQLite are not much into planner hints, but a few
exists already.

What do you think Richard? --DD

> with
> temp.kcounts(t, c) as (
>   select code_type, count(*)
>from keys
>   group by code_type
> ),
> ...
> select ...,
>(select c from kcounts where t = 'SET') as "#sets",
>(select c from kcounts where t = 'CST') as "#constants",
>(select c from kcounts where t = 'CMT') as "#comments",
>(select c from kcounts where t = 'LOG') as "#logs",
>...;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hierarchical Queries with Looping in SQLite DB

[Jean-Luc Hainaut]

On 06/07/2017 08:08, Ashif Ahamed wrote:



When there is some data bringing looping scenario  :

*INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);*

After inserting this loop data , when i trigger the above recursive query
in  SQLite it keeps on running without bringing any results.

Note: In oracle database , this kind of scenario is handled by connect by
nocycle prior or cycle column set is_cycle to '1' default '0'

How to handle this scenario in SQLite?

Can anyone look on into this..


You could use one of the three techniques suggested in this document.

https://www.dropbox.com/s/5tsh7lod3oqv3h5/Tuto19-Recursive-programming.pdf?dl=0

J-L Hainaut

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

Re: [sqlite] Hierarchical Queries with Looping in SQLite DB

[Clemens Ladisch]

Ashif Ahamed wrote:
> I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in
> SQLite database hierarchical queries i.e. with clause queries(common table
> expression)
>
> When there is a loop in the data ,with clause queries in SQLite database is
> running for long with infinite loops

When you use UNION instead of UNION ALL, duplicate rows are ignored.

> WITH LINK(ID, NAME, LEVEL1) AS (
> SELECT ID, NAME, 0  LEVEL1 FROM FOLDER WHERE PARENT IS NULL
> UNION ALL
> SELECT FOLDER.ID , coalesce(LINK.NAME
>  || '/', '') || FOLDER.NAME ,
> LEVEL1 + 1
> FROM LINK INNER JOIN FOLDER ON LINK.ID  = FOLDER.PARENT
> )
> SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID

The level value would prevent the duplicate detection.
Do you really need it?

You could add a LIMIT clause, which at least prevents the infinite loop.


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

Re: [sqlite] List of Warnings when compiling SQLite

[Clemens Ladisch]

bdoom wrote:
> https://pastebin.com/muEvCTz2

> sqlite3.c(16979,5): warning : 'SQLITE_4_BYTE_ALIGNED_MALLOC' is not defined, 
> evaluates to 0 [-Wundef]
> 2>#if SQLITE_4_BYTE_ALIGNED_MALLOC
> 2>^

> Is this anything to worry about?

No.  The C standard says that in the #if expression, identifiers that
are not macros are considered to be the number zero.  (Whether it is
a good idea to use this feature is a different question.)

You get that warning because you did enable -Wundef.
Why did you do that?


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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Clemens Ladisch]

Dominique Devienne wrote:
> with
> kcounts(t, c) as (
>   select code_type, count(*)
>from keys
>   group by code_type
> ),
> ...
> select ...,
>(select c from kcounts where t = 'SET') as "#sets",
>(select c from kcounts where t = 'CST') as "#constants",
>(select c from kcounts where t = 'CMT') as "#comments",
>(select c from kcounts where t = 'LOG') as "#logs",
>...;
>
> the SQL as written does several passes over kcounts

However, the subquery is flattened: each pass counts only rows with
a specific code_type value.

An index on code_type would help _very much_.

> Is there a way to reformulate this query differently to somehow achieve a
> single pass on the keys table? Or force the query planner to "instantiate"
> the kcounts "CTE view" to achieve that single scan, via some kind of hint?

It would be possible to prevent the subquery flattening
(http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is
no hint that would force SQLite to recognize that all the instances of
kcounts are then identical and can be reused.

You could put kcounts into a temporary table.


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

Re: [sqlite] Any way to avoid scanning a large table several times?

[Simon Slavin]

On 6 Jul 2017, at 9:52am, Dominique Devienne  wrote:

> With all 4 counts, the views takes ~ 6.5s to compute its 1 row

Just checking ….

Do you have an index on keys(code_type) ?

Would it not be possible to express this VIEW without the "WITH" construction ? 
 For instance if there is a "SETS" row for every row you want in the VIEW, you 
could just use

SELECT … FROM keys AS kSet
JOIN keys AS Cst WHERE Cst.code_type = kSet.code_type
JOIN …
JOIN …
WHERE code_type = "SETS"

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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 17:01, Domingo Alvarez Duarte wrote:

I already did this before but it was not accepted.

For myself I did a modification on sqlite3 to allow the use of "AS" on 
delete/update statements.


You can see the parser part here 
https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .


Cheers !


Thank you Domingo, but that option is beyond my programming competence.





On 06/07/17 05:16, John McMahon wrote:

Hi

Wondering if someone else can spot the syntax error in the following 
statement. "locns" is an attached database. There are four "AS" terms 
in the statement, they all alias tables.


Ok, found it. It seems that an alias for an "UPDATE" table name is not 
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names 
and attached databases.


John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
  ... >  SET
  ... > tgt.del_date =  (
  ... >  SELECT src.last_del_d
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum),
  ... > tgt.del_qty = (
  ... >  SELECT src.last_del_q
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum)
  ... >  WHERE
  ... > tgt.custnum  = (
  ... >  SELECT src.custnum
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.last_del_d IS NOT NULL
  ... >  ANDsrc.last_del_d > tgt.del_date)
  ... >  ;
Error: near "AS": syntax error



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


--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 16:04, Paul Sanderson wrote:

The SQLite syntax diagrams are my first point of call when looking at an
error in my code like this.

https://sqlite.org/lang_update.html

"AS" and an alias are clearly not part of the statement.


And that is how (with testing) I eventually worked out that I was on the 
wrong track. Thank you, Paul.





Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 6 July 2017 at 06:03, Keith Medcalf  wrote:



Do you know of any implementation of SQL that accepts an AS clause for the
updated table?  I don't think any do.

Some versions have a FROM extension and you CAN specify an alias for the
updated table in that clause, however, as far as I know the update table
cannot be aliased and the "set  = ..." the  must always be
a column in the updated table and while you may be allowed to "adorn" it in
some implementations, any adornments are ignored (or trigger an error
message if they are not the same as the updated table).

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of John McMahon
Sent: Wednesday, 5 July, 2017 21:17
To: SQLite Users
Subject: [sqlite] syntax error near AS

Hi

Wondering if someone else can spot the syntax error in the following
statement. "locns" is an attached database. There are four "AS" terms in
the statement, they all alias tables.

Ok, found it. It seems that an alias for an "UPDATE" table name is not
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names
and attached databases.

John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
... >  SET
... > tgt.del_date =  (
... >  SELECT src.last_del_d
... >  FROM   main.updates AS src
... >  WHERE  src.custnum = tgt.custnum),
... > tgt.del_qty = (
... >  SELECT src.last_del_q
... >  FROM   main.updates AS src
... >  WHERE  src.custnum = tgt.custnum)
... >  WHERE
... > tgt.custnum  = (
... >  SELECT src.custnum
... >  FROM   main.updates AS src
... >  WHERE  src.last_del_d IS NOT NULL
... >  ANDsrc.last_del_d > tgt.del_date)
... >  ;
Error: near "AS": syntax error

--
Regards
 John McMahon
li...@jspect.fastmail.fm


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




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


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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 16:33, Clemens Ladisch wrote:

John McMahon wrote:

an alias for an "UPDATE" table name is not permitted. Is there a particular 
reason for this?


The UPDATE statement affects a single table.  While an alias might be
a convenience, it is not necessary (any naming conflicts in subqueries
can be resolved by using an alias on the table(s) used there).


Thank you Clemens, I see now, the need for no ambiguities when updating.





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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

Re: [sqlite] clusterfuzz-found issue in GDAL, Ubuntu packages

[Even Rouault]

> Aha, I wasn't certain we were allowed to mark it public yet. I don't want
> to upset anyone needlessly, but it would be easier to discuss the bug in
> public.

I've just turned
https://bugs.launchpad.net/ubuntu/+source/sqlite3/+bug/1700937 public

> (Especially since it appears to be 'just' out-of-bound reads. This
> can of course be surprising and have non-obvious consequences, but it
> doesn't immediately lead to e.g. remote code execution.)
> 
> Does this issue sound like it should receive a CVE to ensure other
> consumers of sqlite3 discover it? I'm happy to do the paperwork if so.

Probably a good idea. Will make their own fuzzing efforts easier at least :-) 
Thanks

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 15:03, Keith Medcalf wrote:


Do you know of any implementation of SQL that accepts an AS clause for the 
updated table?  I don't think any do.


No Keith, I don't. My only exposure to SQL is sqlite.



Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, 
as far as I know the update table cannot be aliased and the "set  = ..." the 
 must always be a column in the updated table and while you may be allowed to "adorn" 
it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as 
the updated table).



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

[sqlite] Hierarchical Queries with Looping in SQLite DB

[Ashif Ahamed]

I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in
SQLite database hierarchical queries i.e. with clause queries(common table
expression)

When there is a loop in the data ,with clause queries in SQLite database is
running for long with infinite loops

Consider the below sample data:   (With Out Loop)

CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, null, null);
INSERT INTO FOLDER VALUES(2, 'src', 1);
INSERT INTO FOLDER VALUES(3, 'main', 2);
INSERT INTO FOLDER VALUES(4, 'org', 3);
INSERT INTO FOLDER VALUES(5, 'test', 2);


WITH LINK(ID, NAME, LEVEL1) AS (
SELECT ID, NAME, 0  LEVEL1 FROM FOLDER WHERE PARENT IS NULL
UNION ALL
SELECT FOLDER.ID , coalesce(LINK.NAME
 || '/', '') || FOLDER.NAME ,
LEVEL1 + 1
FROM LINK INNER JOIN FOLDER ON LINK.ID  = FOLDER.PARENT
)
SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID

Above query works fine because there is no loop in data.


When there is some data bringing looping scenario  :

*INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);*


After inserting this loop data , when i trigger the above recursive query
in  SQLite it keeps on running without bringing any results.


Note: In oracle database , this kind of scenario is handled by connect by
nocycle prior or cycle column set is_cycle to '1' default '0'


How to handle this scenario in SQLite?

Can anyone look on into this..

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

Re: [sqlite] clusterfuzz-found issue in GDAL, Ubuntu packages

[Seth Arnold]

[Sorry for the late reply, but I enjoyed a nice long weekend except
for the sunburns. I kept the wider Cc:s since it feels like this can
be opened.]

On Sat, Jul 01, 2017 at 12:52:54PM +0200, Even Rouault wrote:
> Seth, I can turn the Launchpad bug report as public if you wish. I
> marked it privately if Ubuntu felt it was better. I don't care that much
> about disclosing it publicly.

Aha, I wasn't certain we were allowed to mark it public yet. I don't want
to upset anyone needlessly, but it would be easier to discuss the bug in
public. (Especially since it appears to be 'just' out-of-bound reads. This
can of course be surprising and have non-obvious consequences, but it
doesn't immediately lead to e.g. remote code execution.)

Does this issue sound like it should receive a CVE to ensure other
consumers of sqlite3 discover it? I'm happy to do the paperwork if so.

On Sat, Jul 01, 2017 at 11:28:10AM -0400, Richard Hipp wrote:
> A proper fix for the problem can be seen at 
> https://sqlite.org/src/info/66de6f4a

Now this is short and sweet. I like the look of this patch quite a lot
more than the start of the larger transformation.

On Sat, Jul 01, 2017 at 05:40:57PM +0200, Even Rouault wrote:
> > The plain ASCII patch can be seen at
> > https://sqlite.org/src/vpatch?from=0db20efe201736b3&to=66de6f4a9504ec26
> 
> I've just applied this patch on top of 3.11.0. It applies cleanly
> 
> patching file ext/rtree/rtree.c
> Hunk #1 succeeded at 3153 (offset -282 lines).
> patching file ext/rtree/rtreeA.test
> 
> and I confirm that it solves the issue !

Very good news! Thank you both.


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

[sqlite] List of Warnings when compiling SQLite

[bdoom]

https://pastebin.com/muEvCTz2

Is this anything to worry about? It's a massive list of warnings. That isn't
even all of them. However, they are all for #define macros which seem to not
be found anywhere. I even downloaded a copy from sqlite.org of SQLite.c and
could not find those macros in the files. What does this mean?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/List-of-Warnings-when-compiling-SQLite-tp96485.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 17:01, Domingo Alvarez Duarte wrote:

I already did this before but it was not accepted.

For myself I did a modification on sqlite3 to allow the use of "AS" on 
delete/update statements.


You can see the parser part here 
https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .


Cheers !


Thank you Domingo, but that option is beyond my programming competence.





On 06/07/17 05:16, John McMahon wrote:

Hi

Wondering if someone else can spot the syntax error in the following 
statement. "locns" is an attached database. There are four "AS" terms 
in the statement, they all alias tables.


Ok, found it. It seems that an alias for an "UPDATE" table name is not 
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names 
and attached databases.


John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
  ... >  SET
  ... > tgt.del_date =  (
  ... >  SELECT src.last_del_d
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum),
  ... > tgt.del_qty = (
  ... >  SELECT src.last_del_q
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum)
  ... >  WHERE
  ... > tgt.custnum  = (
  ... >  SELECT src.custnum
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.last_del_d IS NOT NULL
  ... >  ANDsrc.last_del_d > tgt.del_date)
  ... >  ;
Error: near "AS": syntax error



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


--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203


--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 16:33, Clemens Ladisch wrote:

John McMahon wrote:

an alias for an "UPDATE" table name is not permitted. Is there a particular 
reason for this?


The UPDATE statement affects a single table.  While an alias might be
a convenience, it is not necessary (any naming conflicts in subqueries
can be resolved by using an alias on the table(s) used there).


Thank you Clemens, I see now, the need for no ambiguities when updating.





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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203


--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 16:04, Paul Sanderson wrote:

The SQLite syntax diagrams are my first point of call when looking at an
error in my code like this.

https://sqlite.org/lang_update.html

"AS" and an alias are clearly not part of the statement.


And that is how (with testing) I eventually worked out that I was on the 
wrong track. Thank you, Paul.





Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 6 July 2017 at 06:03, Keith Medcalf  wrote:



Do you know of any implementation of SQL that accepts an AS clause for the
updated table?  I don't think any do.

Some versions have a FROM extension and you CAN specify an alias for the
updated table in that clause, however, as far as I know the update table
cannot be aliased and the "set  = ..." the  must always be
a column in the updated table and while you may be allowed to "adorn" it in
some implementations, any adornments are ignored (or trigger an error
message if they are not the same as the updated table).

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of John McMahon
Sent: Wednesday, 5 July, 2017 21:17
To: SQLite Users
Subject: [sqlite] syntax error near AS

Hi

Wondering if someone else can spot the syntax error in the following
statement. "locns" is an attached database. There are four "AS" terms in
the statement, they all alias tables.

Ok, found it. It seems that an alias for an "UPDATE" table name is not
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names
and attached databases.

John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
... >  SET
... > tgt.del_date =  (
... >  SELECT src.last_del_d
... >  FROM   main.updates AS src
... >  WHERE  src.custnum = tgt.custnum),
... > tgt.del_qty = (
... >  SELECT src.last_del_q
... >  FROM   main.updates AS src
... >  WHERE  src.custnum = tgt.custnum)
... >  WHERE
... > tgt.custnum  = (
... >  SELECT src.custnum
... >  FROM   main.updates AS src
... >  WHERE  src.last_del_d IS NOT NULL
... >  ANDsrc.last_del_d > tgt.del_date)
... >  ;
Error: near "AS": syntax error

--
Regards
 John McMahon
li...@jspect.fastmail.fm


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




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


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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203


--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] syntax error near AS

[John McMahon]

On 06/07/2017 15:03, Keith Medcalf wrote:


Do you know of any implementation of SQL that accepts an AS clause for the 
updated table?  I don't think any do.


No Keith, I don't. My only exposure to SQL is sqlite.



Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, 
as far as I know the update table cannot be aliased and the "set  = ..." the 
 must always be a column in the updated table and while you may be allowed to "adorn" 
it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as 
the updated table).



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203


--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

[sqlite] Any way to avoid scanning a large table several times?

[Dominique Devienne]

I have a view gathering statistics from 3 different tables, one of which is
largish (~ 2M rows).

The view is similar to this:

with
kcounts(t, c) as (
  select code_type, count(*)
   from keys
  group by code_type
),
...
select ...,
   (select c from kcounts where t = 'SET') as "#sets",
   (select c from kcounts where t = 'CST') as "#constants",
   (select c from kcounts where t = 'CMT') as "#comments",
   (select c from kcounts where t = 'LOG') as "#logs",
   ...;

With all 4 counts, the views takes ~ 6.5s to compute its 1 row (with the
file in cache, was 13.5s with a cold filesystem cache, while DB is only
380MB in size).

If I remove 3 of the lines, keeping a single "select c from kcounts where t
= ..." that drops to 2.15s, or about one third. (this is inside SQLiteSpy
using SQLite 3.13.0 BTW).

As a programmer, I can see this can be done in a single pass, but most
likely the SQL as written does several passes over kcounts, despite using a
CTE (I had hoped kcounts would be instantiated into an ephemeral table, and
thus accessing a 4 row tables would be "free").

An explain query plan indeed shows 4x "SCAN TABLE keys" steps, not just 1.

Is there a way to reformulate this query differently to somehow achieve a
single pass on the keys table? Or force the query planner to "instantiate"
the kcounts "CTE view" to achieve that single scan, via some kind of hint?
If none exists, could one such hint be added? Ideally the planner itself
would decide kcounts should be instantiated, but I'm happy to tell him it
should via a hint.

The speed up is non-negligible at 3x, thus my request for comments from
experts on this.

Thanks, --DD

PS: BTW, I did run ANALYZE and that didn't change the plan or execution
time.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error Message "The database disk image is malformed"

[Manoj Sengottuvel]

Hi Richard,

if I do the Vacuum database before the encryption(SEE)  then I didn't have
any issue like "The database disk image is malformed".

Should I do the vacuum every time before applying the encryption/
decryption (SEE) ?

Let me know How to restore the malformed database?


Regards,

Manoj

On Wed, Jul 5, 2017 at 4:29 PM, Manoj Sengottuvel 
wrote:

> Hi Richard,
>
> I have encrypted  the db using SEE. After that I got the message - Error
> Message "The database disk image is malformed".
>
>
>
>
> Regards,
>
> Manoj
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] syntax error near AS

[Domingo Alvarez Duarte]

I already did this before but it was not accepted.

For myself I did a modification on sqlite3 to allow the use of "AS" on 
delete/update statements.


You can see the parser part here 
https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .


Cheers !


On 06/07/17 05:16, John McMahon wrote:

Hi

Wondering if someone else can spot the syntax error in the following 
statement. "locns" is an attached database. There are four "AS" terms 
in the statement, they all alias tables.


Ok, found it. It seems that an alias for an "UPDATE" table name is not 
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names 
and attached databases.


John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
  ... >  SET
  ... > tgt.del_date =  (
  ... >  SELECT src.last_del_d
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum),
  ... > tgt.del_qty = (
  ... >  SELECT src.last_del_q
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum)
  ... >  WHERE
  ... > tgt.custnum  = (
  ... >  SELECT src.custnum
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.last_del_d IS NOT NULL
  ... >  ANDsrc.last_del_d > tgt.del_date)
  ... >  ;
Error: near "AS": syntax error



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