Re: [sqlite] Result set column names

[Graham Holden]

Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
wrote:

> The SQL-92 standard actually says:
>
> |Syntax Rules
> |
> |9) Case:
> |
> |   b) If the i-th  in the  does not
> | specify an  and the  of that
> |  is a single , then the
> |  of the i-th column of the result is C.
> |

Presumably the third line of clause (b) of the standard SHOULD have
read: "is a single  C,", otherwise "is C" has
nothing to refer to.

Graham

(And it's not a transcription error, according to:
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt


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

Re: [sqlite] Result set column names

[Clemens Ladisch]

Keith Medcalf wrote:
> If you do not provide as AS clause to give a result column a name, then
> each implementation is free to provide whatever names it feels like (so
> sayeth the standard).

The SQL-92 standard actually says:
|6.4  
|
| ::= [   ] 
|
|7.9  
|
| ::=
| SELECT [  ]  
|
| ::=
|   
| |  [ {   }... ]
|
| ::=
|   
| |   
|
| ::=  [  ]
|
| ::= [ AS ] 
|
|Syntax Rules
|
|9) Case:
|
|   a) If the i-th  in the  specifies
| an  that contains a  C, then the
|  of the i-th column of the result is C.
|
|   b) If the i-th  in the  does not
| specify an  and the  of that
|  is a single , then the
|  of the i-th column of the result is C.
|
|   c) Otherwise, the  of the i-th column of the  is implementation-dependent and different
| from the  of any column, other than itself, of
| a table referenced by any  contained in the
| SQL-statement.

SQLite's default short_column_names setting implements rule b).


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

Re: [sqlite] wal mode

[Barry]

Even in rollback journal mode, it is not universally safe to use normal
file operations on a SQLite database. See section 1.3 and 1.4 of
https://www.sqlite.org/howtocorrupt.html

If you want to use normal file system operations (or any type of
manipulation not using the SQLite library) on an SQLite database, the most
bombproof method is to take a backup first, then perform whatever file
system operations you want on the backup. This way, you can be sure that
(a) no process is connected to the backup (because it has a different
filename) and (b) there are no hot journals associated with the backup.

You can either use the backup API from an application, or the .backup
command from the SQLite command line interface (CLI). From what you say,
wanting to use scp, it sounds like a manual process or shell script; the
CLI would likely be the easiest choice.

On Sat, 7 Dec 2019 at 04:39, MM  wrote:

> On Fri, 6 Dec 2019 at 19:06, Simon Slavin  wrote:
>
> > On 6 Dec 2019, at 6:39pm, MM  wrote:
> >
> > > So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the
> > sqlite3 cli, for all future connections from any tool will use WAL mode
> for
> > this database file?
> >
> > Correct.
> >
> > > What happens when 2 processes that have had their connection open for a
> > while, attempt a UPDATE or INSERT INTO statement at the same time?
> >
> > It depends on which connections have a timeout set:
> >
> > 
> >
> > It is normal to specify a timeout of 10 seconds (or even 10 minutes) for
> > every connection you open.  This means that a process which finds the
> > database locked will enter a delay/retry loop for up to that amount of
> time
> > before returning SQLITE_BUSY.
> >
> > However, note that the default timeout is zero.  Which means that if you
> > don't set a timeout on a connection, and it encounters a locked database,
> > it will immediately return SQLITE_BUSY without retrying.
> >
> > [The above explanation is simplified for clarity.]
> > ___
> >
>
> Thank you.
> After having set WAL mode, ontop of my db file, there will be 2 extra
> files? At all times?
> Up until, I used to scp the db file to another host, and sometimes work on
> the db there and then copy back the db to the main host when no processes
> are running.
> Do I know simply copy the 3 files? the db file, and the 2 others?
> ___
> 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] Crash Bug Report

[D Burgess]

3.30.1 x86_64 Linux

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

Re: [sqlite] Crash Bug Report

[Tim Streater]

On 08 Dec 2019, at 21:55, Simon Slavin  wrote:

> On 8 Dec 2019, at 7:51pm, Yongheng Chen  wrote:
>
>> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE v0 ( v1 ) ;
> sqlite> CREATE TABLE v2 ( v3 ) ;
> sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3
> AS x FROM v2 ;
> sqlite> DROP TRIGGER IF EXISTS x ;
> sqlite> ALTER TABLE v2 RENAME TO t3;
> sqlite>
>
> Not a problem for this version.

macOS 10.14.6:

Third-Mini% sqlite3
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ;
sqlite> CREATE TABLE v2 ( v3 ) ;
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ;
sqlite> ALTER TABLE v2 RENAME TO t3;
zsh: segmentation fault sqlite3
Third-Mini% 

My .sqliterc has:

Third-Mini% more .sqliterc 
.mode column
.width 0
.headers on



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

Re: [sqlite] Crash Bug Report

[Simon Slavin]

simon@178 sqlite-tools-osx-x86-3300100 % ./sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ; 
sqlite> CREATE TABLE v2 ( v3 ) ; 
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ; 
sqlite> ALTER TABLE v2 RENAME TO t3; 
zsh: segmentation fault  ./sqlite3
simon@178 sqlite-tools-osx-x86-3300100 % 

In the current version of SQLite, I obtain a segmentation fault under recent 
version of macOS.  Same OS had no problem when running

SQLite version 3.28.0 2019-04-15 14:49:49

So the problem crept in sometime between 3.28.0 and 3.30.1.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash Bug Report

[Jose Isaias Cabrera]

Yongheng Chen, on Sunday, December 8, 2019 02:51 PM, wrote...
>
> Hi,
>
> We found one crash bug in sqlite, which causes a dead loop and then OOM.
> 
> CREATE TABLE v0 ( v1 ) ;
> CREATE TABLE v2 ( v3 ) ;
> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 AS x 
> FROM v2 ;
> DROP TRIGGER IF EXISTS x ;
> ALTER TABLE v2 RENAME TO t3;
> 
> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.
>
> We reported this bug to one of the developers but didn’t get response. And we 
> reported
> several bugs to the same person one-to-one before (which was required by him) 
> and the
> bugs got fixed but we never got any credits for them. We hope somebody else 
> can help
> us. Thanks.

This definitely crashes in Windows 10:

17:21:58.01>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ;
sqlite> CREATE TABLE v2 ( v3 ) ;
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ;
sqlite> ALTER TABLE v2 RENAME TO t3;

17:23:08.57>

Just fyi. thanks.

josé

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

Re: [sqlite] Crash Bug Report

[Yongheng Chen]

I haven’t tested many versions. But the most up-to-date master branch and the 
release version has this bug.


> On Dec 8, 2019, at 4:55 PM, Simon Slavin  wrote:
> 
> On 8 Dec 2019, at 7:51pm, Yongheng Chen  wrote:
> 
>> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.
> 
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE v0 ( v1 ) ; 
> sqlite> CREATE TABLE v2 ( v3 ) ; 
> sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
> AS x FROM v2 ;
> sqlite> DROP TRIGGER IF EXISTS x ; 
> sqlite> ALTER TABLE v2 RENAME TO t3; 
> sqlite> 
> 
> Not a problem for this version.
> ___
> 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] Crash Bug Report

[Simon Slavin]

On 8 Dec 2019, at 7:51pm, Yongheng Chen  wrote:

> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ; 
sqlite> CREATE TABLE v2 ( v3 ) ; 
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ; 
sqlite> ALTER TABLE v2 RENAME TO t3; 
sqlite> 

Not a problem for this version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Crash Bug Report

[Yongheng Chen]

Hi,

We found one crash bug in sqlite, which causes a dead loop and then OOM.

CREATE TABLE v0 ( v1 ) ; 
CREATE TABLE v2 ( v3 ) ; 
CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 AS x FROM 
v2 ;
DROP TRIGGER IF EXISTS x ; 
ALTER TABLE v2 RENAME TO t3; 

The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.

We reported this bug to one of the developers but didn’t get response. And we 
reported several bugs to the same person one-to-one before (which was required 
by him) and the bugs got fixed but we never got any credits for them. We hope 
somebody else can help us. Thanks. 

Best. 

Yongheng Chen & Rui Zhong
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[James K. Lowden]

On Sat, 7 Dec 2019 05:23:15 +
Simon Slavin  wrote:

> (Your operating system is allowed to do this.  Checking how much
> memory is available for every malloc takes too much time.)

Not really.  Consider that many (all?) operating systems before Linux
that supported dynamic memory returned an error if the requested amount
couldn't be supplied.  Some of those machines had 0.1% of the
processing capacity, and yet managed to answer the question reasonably
quickly.  

The origin of oversubscribed memory rather has its origins in the
changed ratio of the speed of RAM to the speed of I/O, and the price of
RAM.  

As RAM prices dropped, our machines got more RAM and the bigger
applications that RAM supported.  As memory got faster, relatively, the
disk (ipso facto) has gotten slower. Virtual memory -- the hallmark of
the the VAX, 4 decades ago -- has become infeasibly slow both because
the disk is relatively slower than it was, and because more is being
demanded of it to support today's big-memory applications.  Swapping in
Firefox, at 1 GB of memory, who knows why, is a much bigger deal than
Eight Megabytes and Constantly Swapping.  

If too much paging makes the machine too slow (however measured) one
solution is less paging.  One administrative lever is to constrain how
much paging is possible by limiting the paging resource: swap space.
However, limiting swap space may leave the machine underutilized,
because many applications allocate memory they never use.  

Rather than prefer applications that use resources rationally or
administer machines to prevent thrashing, the best-effort, least-effort
answer was lazy allocation, and its infamous gap-toothed cousin, the
OOM.  

Nothing technical mandates oversubscribed memory.  The problem, as
ever, is not with the stars, but with ourselves.  

--jkl


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

[sqlite] Expand a single Tcl variable into several SQL variables

[Ramarro Marrone]

I have these variables in Tcl.

set insert {1 2 3 4 5}
set select {3 5}

I want SQL like this to be executed in SQLite.

insert into tab (col) values (1), (2), (3), (4), (5);
select * from tab where col in (3, 5);

How should change the statements in the below group so they can take
the parameters in the above group?

The best I know is to separate the SQL into several statements.

foreach insertI $insert {db eval {
  insert into safe (col) values ($insertI);
}}
foreach selectI $select {db eval {
  select * from safe where col = $selectI;
}}

But that is more complicated and could be slower.

Another option is to write the SQL before giving the command to SQLite.
In the interest of time, I have written only an unsafe example.

proc map {lambda list} {
   set result {}
   foreach item $list {
  lappend result [apply $lambda $item]
   }
   return $result
}
set unsafeInsert [join [map {x {return "($x)"}} $insert] ", "]
set unsafeSelect "([join $select ", "])"
db eval "
  create temporary table unsafe (col);
  insert into unsafe (col) values $unsafeInsert;
  select * from unsafe where col in $unsafeSelect;
"

I don't like either of these options, even if I safely escape everything
in the second example. Does a better way already exist?

Attached is a coherent Tcl program containing all of the above examples.

Cordially,
Ramarro
#!/usr/bin/env tclsh
package require sqlite3
proc map {lambda list} {
   set result {}
   foreach item $list {
  lappend result [apply $lambda $item]
   }
   return $result
}

set insert {1 2 3 4 5}
set select {3 5}

# This version could be unsafe if I escaped everything properly.
# I am hoping that somebody else has already written that.
set unsafeInsert [join [map {x {return "($x)"}} $insert] ", "]
set unsafeSelect "([join $select ", "])"
sqlite3 db
db eval "
  create temporary table unsafe (col);
  insert into unsafe (col) values $unsafeInsert;
  select * from unsafe where col in $unsafeSelect;
" {
  puts "unsafe:$col"
}

# Here is a safe way that is less convenient for me and that could be slower.
db eval {create temporary table safe (col);}
foreach insertI $insert {db eval {insert into safe (col) values ($insertI);}}
foreach selectI $select {db eval {select * from safe where col = $selectI;} {
  puts "safe:$col"
}}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Keith Medcalf]

On Saturday, 7 December, 2019 16:05, Tim Streater  wrote:

>At various times in various threads on this list it has been stated that
>the column name in a result set is not guaranteed unless one uses AS.
>IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if it
>is, so I can point someone at it. The Xojo documentation doesn't mwention
>this so either that is a potential problem for all us Xojo users or
>they've worked around it somehow in their SQLite wrapper.

Currently the default is that 

pragma short_column_names=1;
pragma full_column_names=0;

and the rules as described for the pragma full_column_names is in effect.  The 
use of the pragma's is deprecated.

https://sqlite.org/pragma.html#pragma_full_column_names

Anonymous columns are named "columnX" where x is the column number.  For 
example, "values (1,2),(3,4);" generates a two column table with columns named 
column1 and column2 and two rows (1,2) and (3,4).

You may have duplicate column names in a result set:

create table a(id);
create table b(id);
create table c(id);
insert into a values (1);
insert into b values (1);
insert into c values (1);
select * from a, b, c;

you will get a table that is the result of the join and the first column will 
be named "id", the second "id", and the third "id".  Which one of these 
represents which id from which table depends on the nesting order that the 
optimizer selects when the query is prepared.  If you care, then you need to 
explicitly select the columns by name and give them an output column name using 
AS:

select a.id as a_id, b.id as b_id, c.id as c_id from a,b,c;

You may also use the result column metadata function described at
https://sqlite.org/c3ref/column_database_name.html
to get metadata about the underlying database, table, and column provided that 
you compiled the version of the SQLite3 library you are using with the 
appropropriate SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined.

The naming of columns in the result set is NOT GUARANTEED unless you use the AS 
clause to provide a name.  If you do not provide as AS clause to give a result 
column a name, then each implementation is free to provide whatever names it 
feels like (so sayeth the standard).  In other words, in *ALL* SQL 
implementations the only time you have guaranteed result column names is if you 
use the AS clause to give the result set columns names.  IN ALL OTHER CASES AND 
FOR ALL IMPLEMENTATIONS OF ALL SQL INTERFACES the name given the result set 
column is a non-specified implementation detail.

However, there is a general reliance on the rules as outlined above so they are 
unlikely to change.  It is possible that a "pragma random_result_names" might 
be introduced at some point in the future to assist in finding code that relies 
on "implementation details" much as "pragma reverse_unordered_selects" assists 
in finding where reliance is placed on the ordering of result sets when no 
order by is used.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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