Re: [sqlite] Something broke between version 3.15 and 3.19.3

[Richard Hipp]

On 6/11/17, Balaji Ramanathan  wrote:
>
> Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

SQLite is still working.  It is just picking an inefficient query plan.

The fix is here:  https://www.sqlite.org/src/timeline?c=87aceb417a813a29

-- 
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] parser.y and SQLITE_OMIT_ATTACH

[gwenn]

Sorry,
I didn't know that VACUUM uses ATTACH.


On Sun, Jun 11, 2017 at 1:03 PM, Richard Hipp  wrote:
> Have you tried this?  I don't think it will work.  IIRC, VACUUM uses
> ATTACH internally.
>
> On 6/11/17, gwenn  wrote:
>> Hello,
>>
>> %ifndef SQLITE_OMIT_VACUUM
>> %ifndef SQLITE_OMIT_ATTACH
>> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
>> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
>> %endif  SQLITE_OMIT_ATTACH
>> %endif  SQLITE_OMIT_VACUUM
>>
>> should be
>>
>> %ifndef SQLITE_OMIT_VACUUM
>> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
>> %ifndef SQLITE_OMIT_ATTACH
>> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
>> %endif  SQLITE_OMIT_ATTACH
>> %endif  SQLITE_OMIT_VACUUM
>>
>> Regards.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Something broke between version 3.15 and 3.19.3

[Balaji Ramanathan]

Thank you very much, Dr. Hipp.  I am glad I was not imagining a problem
that nobody else could reproduce.  If you need any additional details or if
you can think of any other way I can help with the investigation, please
let me know.

Balaji Ramanathan

PS:  This is going to sound stupid, but I would like to know how to
download older versions of sqlite.  The sqlite.org website seems to have
links only to downloads for the latest version.  I can read the changelog
and other details for older versions, but I can't seem to find a link to
download the older version of the program itself.

For instance, the latest release before the bug was introduced seems to be
version 3.18.0, released on 3/30.  I can read the changelog and bugfixes at
https://www.sqlite.org/releaselog/3_18_0.html, but no link on that page
seems to lead to a download page.  I wanted to downgrade to an older
version while I wait for this to be fixed, but I can't seem to figure out
how to do that.  Thank you in advance for any pointers.

> Hi,
>
> I maintain a personal database on sqlite.  It is quite small, with
> about 30 tables, and an equal number of views.  One of these views is an
> inner join of the contents of about 15 of these views, producing a summary
> view of my data.  The views combined in this summary view contain about
> 3200 rows each, and the summary view usually runs in about 2 seconds or
> less.
>
> Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

Thanks for the test case!

Bisecting shows that the problem is the optimization introduced here:

   https://www.sqlite.org/src/timeline?c=9e35c89dbe744312

I still do not understand the details.  But we'll be working on it.

--
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] Something broke between version 3.15 and 3.19.3

[Simon Slavin]

On 11 Jun 2017, at 2:56pm, Balaji Ramanathan  
wrote:

> This is going to sound stupid, but I would like to know how to
> download older versions of sqlite.

Although the download page gives links only for the current version of SQLite, 
several older versions are still on the server and you can download them by 
making up the URL yourself.  So, for instance, one of the links currently on 
the Download page is



If you want the version before 3.18.0 you can look it up in



and find that it is 3.17.0 which was released in 2017.  Then you can make up 
the appropriate URL:



and it works !  Hope this helps.

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

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Rowan Worth]

On 9 June 2017 at 22:30, Yuriy M. Kaminskiy  wrote:
>
> Don't know about windows, but on linux no additional "debug privileges"
> needed. You can attach debugger (ptrace syscall) to any process running
> with under same user. Additional privileges needed only for debugging
> processes running under different users (or suid executables).
>

This is generally true, but might not be in the future. The linux kernel
does have an option to limit the processes on which ptrace is effective,
even within processes owned by a specific user. Archlinux at least enables
it by default, I guess time will tell if it sees widespread adoption. I
think it works by allowing ptrace only if invoked by root, or if the target
process is a child of the calling process. I can't find much documentation
on it but here's the arch description:
https://wiki.archlinux.org/index.php/security#ptrace_scope

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

[sqlite] parser.y and SQLITE_OMIT_ATTACH

[gwenn]

Hello,

%ifndef SQLITE_OMIT_VACUUM
%ifndef SQLITE_OMIT_ATTACH
cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
%endif  SQLITE_OMIT_ATTACH
%endif  SQLITE_OMIT_VACUUM

should be

%ifndef SQLITE_OMIT_VACUUM
cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
%ifndef SQLITE_OMIT_ATTACH
cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
%endif  SQLITE_OMIT_ATTACH
%endif  SQLITE_OMIT_VACUUM

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

Re: [sqlite] Something broke between version 3.15 and 3.19.3

[Bart Smissaert]

explain query plan select * from TripDetails

Causes problems as well.

RBS



On Sun, Jun 11, 2017 at 5:27 AM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Hi,
>
> I maintain a personal database on sqlite.  It is quite small, with
> about 30 tables, and an equal number of views.  One of these views is an
> inner join of the contents of about 15 of these views, producing a summary
> view of my data.  The views combined in this summary view contain about
> 3200 rows each, and the summary view usually runs in about 2 seconds or
> less.
>
> Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.
>
> I have produced an anonymized version of my database and loaded it to
> https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg for anybody
> to access.  If you load this database (named test.db) into the sqlite
> command line shell of version 3.15 and run the command "select * from
> TripDetails;", you will see that results appear in under 2 seconds.  If you
> load the exact same database into the command line shell of version 3.19.3
> and run the exact same select statement, it never produces results (or it
> takes so long that I have never had the patience to hang around and see if
> it does produce results).
>
> I have verified that even under version 3.19.3, all the individual
> views that contribute to the summary view produce results by themselves.
> In fact pretty much everything except this summary view seems to work.  So,
> it looks like the massive inner join between these views is the cause of
> the delay or failure in the latest version of sqlite.  I am not sure how or
> why, but I would appreciate it if others on this list who are more
> knowledgeable about these things can take a look and let me know what they
> think.
>
> I am sure my db design leaves a lot to be desired in terms of
> normalization, optimization, etc.  I am open to suggestions on those
> aspects, but my primary concern is that something that worked fine under a
> previous version of sqlite does not work anymore.  Whatever the flaws in
> what I have done, I do expect things to not break simply when I upgrade to
> the latest version of sqlite from a previous version.  I would be open to
> modifying my database in such a way that it is more efficient and faster,
> and perhaps that enables me to produce the results I want from this query
> in the latest version of sqlite.  But to me that is secondary.  I don't
> want to be tweaking my database on an ongoing basis to make it perform well
> with each new release of sqlite.  The symptoms point to some kind of
> regression in sqlite between 3.15 and 3.19.3, and I would like to see if
> there is a fix that does not involve modifying my database.
>
> Thank you very much.
>
> Balaji Ramanathan
> ___
> 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] 3.11.0: Tcl sqlite3 extension can't do variable interpolation _and_ json_extract

[Zach C.]

(To clarify, the '$.hash' as the second parameter of json_extract is
specifying to extract the 'hash' attribute from the root of the JSON object
stored in the json column, per
https://sqlite.org/json1.html#compiling_the_json1_extension )

On Fri, Jun 9, 2017 at 9:04 PM, Zach C.  wrote:

> I did try the colon-style formatting as well, it didn't work. I didn't
> know about [string map], but I tried it too, no luck:
>
> % set map [dict create <> NotImportant]
> <> NotImportant
> % mydb eval [string map $map {SELECT json FROM <> WHERE
> json_extract(json, '$.hash') = $hash}]
> %
>
>
>
> On Fri, Jun 9, 2017 at 7:48 PM, Andreas Kupries  wrote:
>
>> > On 6/9/17, Zach C.  wrote:
>> > > I was partially unclear with using a constant table name here; what I
>> > > actually need as well is the table name as effectively a const that I
>> > > control as well. So more like
>> > >
>> > > mydb eval {$SELECT json FROM $table WHERE json_extract(json,
>> '$.hash') =
>> > > $someId}
>> >
>> > Like this then:
>> >
>> >mydb eval "SELECT json FROM $table WHERE
>> > json_extract(json,'\$.hash')=\$someId"
>> >
>> > Put a backslash \ before every $ that you want passed down into
>> > SQLite.  And not that you definitely want the $ on $someId passed down
>> > into SQLite.  You do *not* want TCL to expand $someId for you.
>>
>> How about using the :-form of variable references ?
>>
>> mydb eval "SELECT json FROM $table WHERE
>> json_extract(json,':.hash')=:someId"
>>
>> Tcl will ignore that form, and Sqlite will expand them.
>> A bit less of quoting hell.
>>
>> Another alternative:
>>
>> set map [list < $table]
>> mydb eval [string map $map {
>> SELECT json
>> FROM <>
>> WHERE json_extract(json,'$.hash')=$someId
>> }]
>>
>> I.e. explicit interpolation of a chosen placeholder, here <>,
>> via [string map], and putting the statement again into {} to prevent
>> Tcl from doing variable expansion.
>>
>> --
>> See you,
>> Andreas Kupries 
>> 
>> Developer @ SUSE (MicroFocus Canada LLC)
>> 
>>
>> Tcl'2017, Oct 16-20, Houston, TX, USA. http://www.tcl.tk/community/tc
>> l2017/
>> EuroTcl 2017, Jul 8-9, Berlin/DE, http://www.eurotcl.tcl3d.org/
>> 
>> ---
>>
>>
>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.11.0: Tcl sqlite3 extension can't do variable interpolation _and_ json_extract

[Zach C.]

I did try the colon-style formatting as well, it didn't work. I didn't know
about [string map], but I tried it too, no luck:

% set map [dict create <> NotImportant]
<> NotImportant
% mydb eval [string map $map {SELECT json FROM <> WHERE
json_extract(json, '$.hash') = $hash}]
%



On Fri, Jun 9, 2017 at 7:48 PM, Andreas Kupries  wrote:

> > On 6/9/17, Zach C.  wrote:
> > > I was partially unclear with using a constant table name here; what I
> > > actually need as well is the table name as effectively a const that I
> > > control as well. So more like
> > >
> > > mydb eval {$SELECT json FROM $table WHERE json_extract(json, '$.hash')
> =
> > > $someId}
> >
> > Like this then:
> >
> >mydb eval "SELECT json FROM $table WHERE
> > json_extract(json,'\$.hash')=\$someId"
> >
> > Put a backslash \ before every $ that you want passed down into
> > SQLite.  And not that you definitely want the $ on $someId passed down
> > into SQLite.  You do *not* want TCL to expand $someId for you.
>
> How about using the :-form of variable references ?
>
> mydb eval "SELECT json FROM $table WHERE
> json_extract(json,':.hash')=:someId"
>
> Tcl will ignore that form, and Sqlite will expand them.
> A bit less of quoting hell.
>
> Another alternative:
>
> set map [list < $table]
> mydb eval [string map $map {
> SELECT json
> FROM <>
> WHERE json_extract(json,'$.hash')=$someId
> }]
>
> I.e. explicit interpolation of a chosen placeholder, here <>,
> via [string map], and putting the statement again into {} to prevent
> Tcl from doing variable expansion.
>
> --
> See you,
> Andreas Kupries 
> 
> Developer @ SUSE (MicroFocus Canada LLC)
> 
>
> Tcl'2017, Oct 16-20, Houston, TX, USA. http://www.tcl.tk/community/
> tcl2017/
> EuroTcl 2017, Jul 8-9, Berlin/DE, http://www.eurotcl.tcl3d.org/
> 
> ---
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.11.0: Tcl sqlite3 extension can't do variable interpolation _and_ json_extract

[Zach C.]

% mydb eval "SELECT json FROM $table WHERE json_extract(json, '\$.hash') =
\$hash"
% mydb eval "SELECT json FROM $table WHERE json_extract(json, '$.hash') =
$hash"
{{"a": "b", "hash": 244718899}}

I know, I _really_ don't want Tcl interpolating $hash directly in here, but
sqlite3's Tcl package does not seem to be doing something with the variable
properly.

I even tried specifying $hash as a variable rather than just using set,
still no luck:

% variable hash 244718899
% $hash
invalid command name "244718899"
% mydb eval "SELECT json FROM $table WHERE json_extract(json, '$.hash') =
$hash"
{{"a": "b", "hash": 244718899}}
% mydb eval {SELECT json FROM NotImportant WHERE json_extract(json,
'$.hash') = $hash}
% mydb eval "SELECT json FROM $table WHERE json_extract(json, '\$.hash') =
\$hash"
%

And setting a variable to the query doesn't like it either:

% set query "SELECT json FROM $table WHERE json_extract(json, '\$.hash') =
\$hash"
SELECT json FROM NotImportant WHERE json_extract(json, '$.hash') = $hash
% mydb eval $query
%


On Fri, Jun 9, 2017 at 10:45 AM, Richard Hipp  wrote:

> On 6/9/17, Zach C.  wrote:
> > I was partially unclear with using a constant table name here; what I
> > actually need as well is the table name as effectively a const that I
> > control as well. So more like
> >
> > mydb eval {$SELECT json FROM $table WHERE json_extract(json, '$.hash') =
> > $someId}
>
> Like this then:
>
>mydb eval "SELECT json FROM $table WHERE
> json_extract(json,'\$.hash')=\$someId"
>
> Put a backslash \ before every $ that you want passed down into
> SQLite.  And not that you definitely want the $ on $someId passed down
> into SQLite.  You do *not* want TCL to expand $someId for you.
>
> >
> > The problem is this will cause $table to be interpolated as a SQL string,
> > which it is not: it is a table name. But even if I _do_ make the table
> name
> > a literal, as in this query:
> >
> > % set hash 24471899
> > 24471899
> > % mydb eval {SELECT json FROM NotImportant WHERE json_extract(json,
> > '$.hash') = $hash}
> >
> > _and_ I'm using curly-brace evaluation, the json_extract fails.
> >
> > It isn't JSON1 because if I _directly_ interpolate (introducing the
> > possibility of SQL injection) it works fine.
> >
> > Even if I make the JSON path its own variable and do the same, it fails:
> >
> > % set json_path {$.hash}
> > $.hash
> > % mydb eval {SELECT json FROM NotImportant WHERE json_extract(json,
> > $json_path) = $hash}
> > %
> >
> > So I'm kind of at a loss.
> >
>
>
> --
> 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] 3.11.0: Tcl sqlite3 extension can't do variable interpolation _and_ json_extract

[Zach C.]

I was partially unclear with using a constant table name here; what I
actually need as well is the table name as effectively a const that I
control as well. So more like

mydb eval {$SELECT json FROM $table WHERE json_extract(json, '$.hash') =
$someId}

The problem is this will cause $table to be interpolated as a SQL string,
which it is not: it is a table name. But even if I _do_ make the table name
a literal, as in this query:

% set hash 24471899
24471899
% mydb eval {SELECT json FROM NotImportant WHERE json_extract(json,
'$.hash') = $hash}

_and_ I'm using curly-brace evaluation, the json_extract fails.

It isn't JSON1 because if I _directly_ interpolate (introducing the
possibility of SQL injection) it works fine.

Even if I make the JSON path its own variable and do the same, it fails:

% set json_path {$.hash}
$.hash
% mydb eval {SELECT json FROM NotImportant WHERE json_extract(json,
$json_path) = $hash}
%

So I'm kind of at a loss.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parser.y and SQLITE_OMIT_ATTACH

[Richard Hipp]

Have you tried this?  I don't think it will work.  IIRC, VACUUM uses
ATTACH internally.

On 6/11/17, gwenn  wrote:
> Hello,
>
> %ifndef SQLITE_OMIT_VACUUM
> %ifndef SQLITE_OMIT_ATTACH
> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
> %endif  SQLITE_OMIT_ATTACH
> %endif  SQLITE_OMIT_VACUUM
>
> should be
>
> %ifndef SQLITE_OMIT_VACUUM
> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
> %ifndef SQLITE_OMIT_ATTACH
> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
> %endif  SQLITE_OMIT_ATTACH
> %endif  SQLITE_OMIT_VACUUM
>
> Regards.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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