[sqlite] Why sqlite show qualified column names when selecting from views ?

[Jean-Christophe Deschamps]

At 22:28 25/08/2015, you wrote:

>In the next version of SQLite3 the names of all columns will be 
>'columnname'

"Don't trust me" could be more apropriate. 

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Tim Streater]

On 25 Aug 2015 at 19:37, Richard Hipp  wrote:

> On 25/8/15, Simon Slavin  wrote:
>>
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."

> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

So how does that work with:

  create table newtable as select * from oldtable;

Does that mean that, in principle, I could have any random set of column names 
for my new table?

Doing this properly is also going to mean that, for safety, I've got 306 select 
statements to fix up in my application. It's gonna be a bit dull if I have one 
where I select 20 named columns from a table and have to change such as:

  select absid, firstname, lastname, phone, ...

to:

  select absid as absid, firstname as firstname, lastname as lastname, phone as 
phone, ...

Recipe for typos there, ISTM. Grumble, grumble.

--
Cheers  --  Tim

[sqlite] order by not working in combination with random()

[Stephen Chrzanowski]

Somewhat of a devils advocate here, but I'm not sure one can order based on
JUST data.  Take the DATE function, for example.  If, by your words, ORDER
BY should only act on the data, consider this kind of query:

select ItemName,SoldDate from SoldItemDetails order by
SoldDate
> ORDER BY should evaluate the *data* only.  In the case of random(),
> like any other function it gets invoked once, by SELECT, and produces a
> value.  That value is the one that ORDER BY should operate on.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] order by not working in combination with random()

[James K. Lowden]

On Mon, 17 Aug 2015 12:01:58 +0200
Clemens Ladisch  wrote:

> Just because the ORDER BY clause refers to a column of the
> SELECT clause does not mean that the value is not computed
> a second time.

Let's at least recognize that as a bug.  ORDER BY shouldn't interpret
SQL or invoke functions.  It's not even properly part of the SELECT
statement, but is rather just a post-SELECT filter.  Any changes to the
behavior of ORDER BY effected by changing the syntax in semantically
equivalent ways is only further evidence of the bug.  

ORDER BY should evaluate the *data* only.  In the case of random(),
like any other function it gets invoked once, by SELECT, and produces a
value.  That value is the one that ORDER BY should operate on.  

--jkl

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[James K. Lowden]

On Mon, 17 Aug 2015 17:19:49 +0200
Olivier Barthelemy  wrote:

> CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC
> AUTOINCREMENT, storage_implicit BOOLEANCHECK (storage_implicit =
> 'true' OR storage_implicit = 'false'), storage_type TEXT NOT NULL);
> 
> Insert statement
> INSERT INTO VariableDetails (storage_implicit, storage_type) VALUES
> (true, INT_64);
> ( Values are not passed dirrectly in the statement. The boolean in
> particular is passed using sqlite3_bind_int() )

I think you got to the right place with this, but I'm not sure it was
made clear that the reason is evident in the above text.  

The constraint is against the strings 'true' and 'false'.  The insert
statement inserts "true", no quotes; as you say, it's a C symbol
interpreted by the compiler.  It's bound to the prepared statement with
sqlite3_bind_int.  The value in the bound location will be interpreted
as an integer, not as a pointer to a character array!  

--jkl

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 8:52pm, Scott Robison  wrote:

> On Aug 25, 2015 1:02 PM, "Petite Abeille"  wrote:
> 
>> On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
>> 
>>> I vote to change it every release... Stimulate better habits!
>> 
>> Seconded. Keep them on their toes!
> 
> Or randomly generate names after every prepare! Or just leave them
> anonymous.

In the next version of SQLite3 the names of all columns will be 'columnname'.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Petite Abeille]

> On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
> 
> I vote to change it every release... Stimulate better habits!

Seconded. Keep them on their toes!

[sqlite] Why sqlite show qualified column names when selecting from views ?

[R.Smith]

On 2015-08-25 08:37 PM, Richard Hipp wrote:
> On 8/25/15, Simon Slavin  wrote:
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."
>>
> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

I vote to change it every release... Stimulate better habits!

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Stephan Beal]

The behaviour you're asking about is explicitly undefined in sqlite. Today
it might work like you are reporting and tomorrow it might do something
different. The ONLY way to guarantee specific column names is to do what
Simon suggested: always use "as". If you don't, the exact results are
undefined.

- stephan
(Sent from a mobile device, possibly from bed. Please excuse brevity,
typos, and top-posting.)
On Aug 25, 2015 19:52, "sqlite-mail"  wrote:

> First of all thanks for reply !
>
> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake
> when
> we use individual names otherwise I would expected qualified names there
> too.
>
>
> Cheers !
> >  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin"  >
> >Subject: Re: [sqlite] Why sqlite show qualified column names when
> selecting
> >from views ?
> >
> >  On 25 Aug 2015, at 6:13pm, sqlite-mail 
> wrote:
> >
> >
> >>When querying views sqlite shows qualified column names if they are
> >>specified
> >> individually.
> >>
>
> >  Sorry but this has been mentioned a few times here and won't be changed.
> >The SQL standard doesn't mention column names so SQL engines are free to
> do
> >what they want.
> >
> > In SQLite you can depend on column names only if you have specified them
> >using 'AS'. So I would expect, but haven't tested right now ...
> >
> >
> >>SQL: SELECT a.* FROM tbl_view AS a;
> >> Column 0: id
> >> Column 1: name
> >> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> >> Column 0: a.id < only with individual fields
> >> Column 1: a.name <<<
> >>
> >>
>
> >  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> > Column 0: id
> > Column 1: name
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Why sqlite show qualified column names when selecting from views ?

[sqlite-mail]

First of all thanks for reply !  

I can understand your point, what confuses me is that when we query a view
with "a.*" it doesn't qualify the names so I thought that was a mistake when
we use individual names otherwise I would expected qualified names there too.


Cheers !  
>  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Why sqlite show qualified column names when selecting
>from views ?
>
>  On 25 Aug 2015, at 6:13pm, sqlite-mail  wrote:
> 
>  
>>When querying views sqlite shows qualified column names if they are
>>specified
>> individually. 
>> 

>  Sorry but this has been mentioned a few times here and won't be changed.
>The SQL standard doesn't mention column names so SQL engines are free to do
>what they want.
> 
> In SQLite you can depend on column names only if you have specified them
>using 'AS'. So I would expect, but haven't tested right now ...
> 
>  
>>SQL: SELECT a.* FROM tbl_view AS a;
>> Column 0: id
>> Column 1: name
>> SQL: SELECT a.id, a.name FROM tbl_view AS a;
>> Column 0: a.id < only with individual fields 
>> Column 1: a.name <<<
>> 
>> 

>  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> Column 0: id
> Column 1: name
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Richard Hipp]

On 8/25/15, Tim Streater  wrote:
> On 25 Aug 2015 at 19:37, Richard Hipp  wrote:
>
>> On 25/8/15, Simon Slavin  wrote:
>>>
>>> "If there is no AS clause then the name of the column is unspecified and
>>> may
>>> change from one release of SQLite to the next."
>
>> Heed this warning!!!
>>
>> And yet, there are countless tens of thousands of applications in
>> circulation that have ignored this warning and depend on the
>> (unsupported) behavior of whatever version of SQLite they were
>> originally developed on.  So the reality is that we are extremely
>> careful not to change the result column naming algorithms, for fear of
>> breaking billions of cellphone apps.
>
> So how does that work with:
>
>   create table newtable as select * from oldtable;
>
> Does that mean that, in principle, I could have any random set of column
> names for my new table?
>

Yeah.  That's a bummer, isn't it

But in practice, our default column name chooser algorithm does what
you want here, and as I stated before, we are in no hurry to change
it.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 6:52pm, sqlite-mail  wrote:

> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake when
> we use individual names otherwise I would expected qualified names there too.

I understand that this is not what one would expect.  Worse still, as the 
documentation says ...



"If there is no AS clause then the name of the column is unspecified and may 
change from one release of SQLite to the next."

If you expect your own code to know what columns are called use 'AS' for each 
column.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[sqlite-mail]

Hello !  

When querying views sqlite shows qualified column names if they are specified
individually.  

Is this the expected result or a bug ?  

This behavior breaks some of my code that uses column names for other
purposes.  

Cheers !  

output of "test-view-alias"  

SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id?? < only with individual fields 
Column 1: a.name <<<
  

 test-view-alias.c  

#include 
#include "sqlite3.h"

void showStmtColNames(sqlite3 *db, const char *szSQL)
{
??? sqlite3_stmt* stmt;
??? printf("SQL: %s\n", szSQL);
??? int rc = sqlite3_prepare_v2(db, szSQL, -1, , 0);
??? int i, col_count = sqlite3_column_count(stmt);
??? for(i=0; i < col_count; ++i)
??? {
??? ??? printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
??? }
??? sqlite3_finalize(stmt);
}

int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? const char dbname_szSQL[] = ":memory:";
??? int rc = sqlite3_open(dbname_szSQL, );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, );
??? ??? rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, );
??? ??? rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, );
??? ??? 
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
??? ??? 
??? ??? sqlite3_close(db);
??? }
??? return 0;
}  

  

script to compile "test-view-alias.c"  

MYINC=.

gcc -g -O2 \
??? -DSQLITE_DEBUG=1 \
??? -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
??? -DTHREADSAFE=1 \
??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \
??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \
??? -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \
??? -DSQLITE_ENABLE_FTS4=1 \
??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
??? -DSQLITE_ENABLE_RTREE=1 \
??? -DSQLITE_ENABLE_STAT4=1 \
??? -DSQLITE_OMIT_TCL_VARIABLE=1 \
??? -DSQLITE_USE_URI=1 \
??? -DSQLITE_SOUNDEX=1\
??? -o test-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl  

[sqlite] ESC_BAD_ACCESS when using a background thread

[Simon Slavin]

On 25 Aug 2015, at 6:23pm, Roger Binns  wrote:

> Generally you would be better off using something like homebrew:
> 
>  http://brew.sh/
> 
> [snip]
> 
> Instruments isn't too bad, but IIRC does not do the same thing as
> valgrind.  Instruments is a lighter less thorough functionality.
> Simon gave lots of links.

Yeah, Brew is more thorough for the long-term.  The advantages of Instruments 
are (A) it's already installed (B) it is already configured for iOS and 
understands what you program should and shouldn't be doing and (C) if you're 
used to Xcode you'll understand the user-interface it integrates with.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 6:13pm, sqlite-mail  wrote:

> When querying views sqlite shows qualified column names if they are specified
> individually. 

Sorry but this has been mentioned a few times here and won't be changed.  The 
SQL standard doesn't mention column names so SQL engines are free to do what 
they want.

In SQLite you can depend on column names only if you have specified them using 
'AS'.  So I would expect, but haven't tested right now ...

> SQL: SELECT a.* FROM tbl_view AS a;
> Column 0: id
> Column 1: name
> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> Column 0: a.id   < only with individual fields 
> Column 1: a.name <<<


SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
Column 0: id
Column 1: name

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Richard Hipp]

On 8/25/15, Simon Slavin  wrote:
>
> "If there is no AS clause then the name of the column is unspecified and may
> change from one release of SQLite to the next."
>

Heed this warning!!!

And yet, there are countless tens of thousands of applications in
circulation that have ignored this warning and depend on the
(unsupported) behavior of whatever version of SQLite they were
originally developed on.  So the reality is that we are extremely
careful not to change the result column naming algorithms, for fear of
breaking billions of cellphone apps.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT by default

[Richard Hipp]

On 8/25/15, V.Krishn  wrote:
>
> Hi,
>
> Will there be any issue if SQLITE_ENABLE_UPDATE_DELETE_LIMIT was enabled by
>
> default ?
> ( I understand it cannot be used in triggers even though )
>

That won't work with the amalgamation (sqlite3.c).  You have to
download the canonical source code and build it from scratch, editing
the Makefile to insert the extra -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT.

Note that SQLITE_ENABLE_UPDATE_DELETE_LIMIT is one of the compile-time
options for SQLite that *is* extensively and rigorously tested prior
to every release.  So there shouldn't be any issues - other than the
fact that you have to go to the extra trouble of compiling from
canonical sources.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Potential for Segmentation Violation/Fault in sqlite 3.8.11.1

[Richard Hipp]

On 8/25/15, Dan Kennedy  wrote:
> On 08/25/2015 11:36 PM, Bill Parker wrote:
>>
>> The patch file below should catch and handle all conditions where
>> Tcl_Alloc() is called, but are NOT checked for a return value of NULL:
>
> Does Tcl_Alloc() actually return NULL if a malloc fails? I thought if
> memory can not be allocated it calls Tcl_Panic() to report an error
> message and then aborts the process.
>

Sure enough.  http://tmml.sourceforge.net/doc/tcl/Alloc.html says that
you have to use Tcl_AttemptAlloc() if you want a NULL pointer returned
on OOM.  Tcl_Alloc() always panics.  See
http://core.tcl.tk/tcl/artifact/d25497d9849b8704?ln=1089 for the
implementation.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Scott Robison]

On Aug 25, 2015 1:02 PM, "Petite Abeille"  wrote:
>
>
> > On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
> >
> > I vote to change it every release... Stimulate better habits!
>
> Seconded. Keep them on their toes!
>

Or randomly generate names after every prepare! Or just leave them
anonymous. #mostlyjoking

[sqlite] Performance problems on windows

[Simon Slavin]

On 25 Aug 2015, at 9:52am, Jakub Zakrzewski  wrote:

> Thanks for interest. If I can provide any useful information, just tell me, 
> what you need. I cannot reveal the source code but some profiling results or 
> SQL statements are not a secret.

Can you provide a short series of SQL statements which runs more slowly now ?  
Better still one or more single statements which are slower.  Just the SQL 
commands.

We'd ideally like your schema (the table definitions) too, but even just the 
SQL commands would be nice.

Another thing to try would be to do the same thing as your initial tests but to 
run ANALYZE (in both old and new versions of SQLite) before the series of 
statements which runs slower.

Simon.

[sqlite] ESC_BAD_ACCESS when using a background thread

[Simon Slavin]

On 25 Aug 2015, at 10:38am, Jeff M  wrote:

> Looking at Xcode's memory report (and using Instruments), I see that memory 
> does ratchet up, despite my being very careful matching alloc/releases (this 
> app is pre-ARC).  I need to relearn Instruments.

You were tricked.  You don't need to relearn Instruments, you need to learn 
what they do now.  They changed.

For those playing along at home programming iDevices under iOS see



for Instruments in general, and for memory issues in particular see



For Macs under OS X, see



for Instruments in general, and for memory issues in particular see



which discusses the four types of memory problems (unexpected allocation, 
allocated but pointer lost, allocated but unused, and released but then used).  
The tools referenced do what valgrind does, just in Apple's usual graphical 
manner.

For those who are about to point out that valgrind does so much more ... you're 
right.  And there are other Instruments in Xcode which do more too.

Simon.

[sqlite] Performance problems on windows

[Scott Robison]

On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski"  wrote:
>
> Hi,
>
> The slowdown is confirmed by one of our customers. He uses Win 2008
Server x64. I'm testing on Win7 x64.

This might be obvious in which case my apologies for bringing it up but:
are these systems demonstrating slowness perhaps heavily fragmented? Either
the database is fragmented, other non-SQLite files required by the system,
or especially the master file table? I've seen truly abysmal performance on
such systems myself, depending on IO patterns.

>
> I'm sure that you're testing each release carefully. We do too. And we
still have weird bugs ;)
>
> Thanks for interest. If I can provide any useful information, just tell
me, what you need. I cannot reveal the source code but some profiling
results or SQL statements are not a secret.
>
> --
> Gruesse,
> Jakub
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> Sent: Dienstag, 25. August 2015 03:19
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Performance problems on windows
>
> I don't have any clues.
>
> While most of our work happens on Linux, we do measure performance on
Windows from one release to the next (see for example item 23-b on the most
recent release check-list
> https://www.sqlite.org/checklists/private/3081100/index#c23) and it gets
better from one release to the next.  So I don't know why you are seeing a
slowdown.
>
> How do you measure?
>
> On 8/24/15, Jakub Zakrzewski  wrote:
> > Hi All,
> >
> > I finally got a chance to upgrade SQLite for our product from ancient
> > 3.7.16.2. Initial tests on Linux were very promising - ranging from
> > 33% to even 300% (for one degenerated case) speed improvement. So far
so good.
> > Problems begun when I have tested it on Windows. Depending on test
> > case the new version is up to 0.28x slower! In the course of
> > investigation I have managed to improve the performance by adding few
> > missing indexes but this has influenced both old and new versions so
> > the relative performance with
> > 3.8.11.1 is still like one third worse.
> >
> > I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0
> > and the results are quite surprising:
> > 3.7.16.2 -> 3.7.17.0 : ~16% faster
> > 3.7.17.0 -> 3.8.0.0  : ~26% slower
> > 3.7.16.2 -> 3.8.0.0  : ~15% slower
> > 3.7.16.2 -> 3.8.11.1 : ~28% slower
> >
> > We use SQLite as backend ("persistent storage") to an implementation
> > of a state machine. The queries are rather simple and each of them is
> > too fast to measure (SQLite performance timer only has resolution of
> > milliseconds) in the profiler it looks like the actual filesystem
> > functions were taking more time in new versions but that tells me
nothing really.
> >
> > Is there something I can tweak?
> > The page size is set to 4K (NTFS file system), synchonous is OFF,
> > journal_mode=truncated;
> >
> > With 10K objects all versions seem to perform equally: test takes
> > ~45s. For 100K objects it's already [m]:[s]
> > 3.7.16.2 : ~10:55
> > 3.7.17.0 : ~09:30
> > 3.8.0.0  : ~12:46
> > 3.8.11.1 : ~15:08
> >
> > I'm out of ideas here. Can someone help me with further investigation?
> >
> > --
> > Gruesse,
> > Jakub
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/25/2015 02:38 AM, Jeff M wrote:
> I tried to install valgrind (on Mac OS Yosemite), but I can't get
> it to compile.  I don't have the fortitude to work out the install
> issues.

Generally you would be better off using something like homebrew:

  http://brew.sh/

It will take care of dependencies, updates etc and knows how to
compile things.

> Looking at Xcode's memory report (and using Instruments),

Instruments isn't too bad, but IIRC does not do the same thing as
valgrind.  Instruments is a lighter less thorough functionality.
Simon gave lots of links.

> ... this app is pre-ARC

Ouch.

> Among other things, I see many persistent 4K memory blocks
> resulting
from sqlite3MemRealloc

Many of those are likely to be the SQLite cache.  Only closing the
connection will free them.  (SQLite does have a boatload of custom
memory management options, but your underlying issue is very unlikely
in SQLite itself.)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXcpHcACgkQmOOfHg372QTuEgCgjYogcxmCcINsHDj06EUKE8zj
j9oAn1PHfGnz93q9BBXm8LxIoP8I0o2S
=FIdc
-END PGP SIGNATURE-

[sqlite] Compile warnings

[David Bennett]

Fair enough. Bearing in mind that any new code path requires a new test case 
and coverage.



Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org





From: Scott Robison [mailto:sc...@casaderobison.com] 
Sent: Tuesday, 25 August 2015 1:24 AM
To: General Discussion of SQLite Database ; davidb at pfxcorp.com
Subject: Re: [sqlite] Compile warnings



On Aug 24, 2015 6:29 AM, "David Bennett" mailto:davidb 
at pfxcorp.com> > wrote:
>
> I think we've beaten the philosophy to death and we're largely in agreement.
>
> I'm not sure we actually came up with a firm recommendation as to what to do
> about this specific warning in this particular line of code with this
> compiler. Ostrich treatment maybe, and rely on the general Sqlite
> disclaimer?

Well, I haven't inspected the code closely. My discussions have been purely 
philosophical. :)

If the warning has to do with a constant zero expression, then I think I'd 
probably go ahead and add the if statement at this point, giving the optimizer 
the opportunity to completely remove the code fragment, even though that would 
likely just change warnings.

If the expression is not constant, I don't have a strong opinion on how to 
suppress or if to suppress. Given SQLite's focus on performance (and that the 
code is correct either way) I'd probably profile and see if performance was 
impacted and make the decision then. If performance was clearly better with an 
if statement I'd make the change. If not suppress the warning conditionally if 
possible (gcc is a very common choice of compiler and worth having a clean 
build) or ignore it if not.

>
> Regards
> David M Bennett FACS
>
> MD Powerflex Corporation, creators of PFXplus
> To contact us, please call +61-3-9548-9114 or go to
> www.pfxcorp.com/contact.htm  
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
>  
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org 
>  ] On Behalf Of Scott
> Robison
> Sent: Monday, 24 August 2015 8:25 AM
> To: davidb at pfxcorp.com  ; General Discussion 
> of SQLite Database
> mailto:sqlite-users at 
> mailinglists.sqlite.org> >
> Subject: Re: [sqlite] Compile warnings
>
> On Sat, Aug 22, 2015 at 8:07 PM, David Bennett   > wrote:
>
> > Of course that is the aim, as always.
> >
> >
> >
> > In this particular case, maximally portable code (that will compile
> > and execute correctly on all conforming compilers) must (a) ensure
> > that the pointer argument is valid (b) ensure that the length is valid, or
> zero.
> > Where reasonably possible both should be done statically. If
> > conditional code is introduced then it must be tested with all branches
> covered.
> >
>
> Agreed, and in C89 NULL was a valid pointer argument in this context as long
> as the length was zero. That has nothing to do with this particular thread,
> but I referenced it just as a point of "C99 changed the semantics of what is
> valid, invalidating previously valid code". Projects that took advantage of
> that can either modify their code to accommodate the newer standard or leave
> it along claiming it conforms to the intended / desired standard. In that
> case, it was easy to change to code to be compatible with both standards and
> it was done. In this case (gcc warning about possible argument order error
> due to a constant expression) can be equally accommodated, but it's not a
> matter of standards compliance. It *is* a matter of one of the (or perhaps
> *the*) most used compilers bellyaching about something that is not wrong or
> invalid in any way. So is the code modified to suppress the warning, is the
> warning disabled globally, locally, or just ignored? I can see the benefits
> to making the change and to not making the change.
>
> > As always, it may be the case that one or more compilers may issue
> > warnings for code that is fully compliant with the standard and fully
> > tested. Sadly there may even be compilers that compile the code
> > incorrectly (a compiler bug). The question of how to handle undesired
> > warnings or compiler bugs on code that is known to be correct and
> > compliant is always a judgement call. In my opinion the solution
> > chosen should always be as fine-grained as possible (such as a
> > compiler-specific conditional), but the downside is that the code can
> > become littered with references to specific problems in specific compilers
> and thus become harder to work with.
> >
>
> I agree completely. Most of us don't have to worry about this too much
> because we target one platform (most code is not written with portability in
> mind). SQLite is not most projects.
>
>
> > In my opinion changes that are visible to other compilers should be
> > avoided unless the changed code is an equally valid 

[sqlite] football.db - New 2015/16 Seasons - English Premier League, Bundesliga, etc.

[Gerald Bauer]

Hello,

   The plain text football fixtures for leagues, teams, match
schedules, stadiums, and more that you can read with the sportdb gem
into an SQLite database e.g. football.db - thus, the project name ;-)
- now includes the 2015/16 seasons for the English Premier League [1],
the Deutsche Bundesliga [2], and more. Any league missing? Add your
league.

  Use Datafiles [3] to build youself a copy e.g.

   $ sportdb new en2015-16# just the 2015-16 season, please
   $ sportdb new en   # all seasons

 All data, code and samples public domain. Enjoy the beautiful game. Cheers.

[1] https://github.com/openfootball/eng-england/tree/master/2015-16
[2] https://github.com/openfootball/de-deutschland/tree/master/2015-16
[3] https://github.com/openfootball/datafile

[sqlite] Missing Sanity Checks for calls to sqlite3_realloc64() in shell.c

[Bill Parker]

Hello All,

In reviewing code in sqlite 3.8.11.1, I found a pair of calls to
sqlite3_realloc64() which upon return are NOT checked for a return
value of NULL, indicating failure.  The patch file below should
correct this issue:

===

--- shell.c.orig2015-08-22 18:33:38.14600 -0700
+++ shell.c2015-08-22 18:36:40.84800 -0700
@@ -1441,6 +1441,10 @@
   p->aiIndent = (int*)sqlite3_realloc64(p->aiIndent,
nAlloc*sizeof(int));
   abYield = (int*)sqlite3_realloc64(abYield, nAlloc*sizeof(int));
 }
+if ( !p->aiIndent || !abYield ){
+  fprintf(stderr, "out of memory\n");
+  exit(1);
+}
 abYield[iOp] = str_in_array(zOp, azYield);
 p->aiIndent[iOp] = 0;
 p->nIndent = iOp+1;


===

I am attaching the patch file to this bug report...

Questions, Comments, Suggestions, Complaints? :)

Bill Parker (wp02855 at gmail dot com)

[sqlite] Potential for Segmentation Violation/Fault in sqlite 3.8.11.1

[Bill Parker]

Hello All,

In reviewing source code files in sqlite 3.8.11.1, I found some
instances of calls to Tcl_Alloc() which are not checked for a return
value of NULL, indicating failure in directory '/tea/generic', file
'tclsqlite3.c'.  Additionally, in the event of failure, there are
some cases where memset()/memcpy() is called after Tcl_Alloc(), but
in the event that Tcl_Alloc() returns NULL, memset()/memcpy() will
generate a segmentation fault/violation if memset()/memcpy() is called
with a address location pointing to NULL (see test program below
the patch file).

The patch file below should catch and handle all conditions where
Tcl_Alloc() is called, but are NOT checked for a return value of NULL:

===

--- tclsqlite3.c.orig2015-08-22 18:50:01.65600 -0700
+++ tclsqlite3.c2015-08-22 19:12:05.71600 -0700
@@ -380,6 +380,10 @@
   }

   p = (IncrblobChannel *)Tcl_Alloc(sizeof(IncrblobChannel));
+  if( !p ){
+Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+return TCL_ERROR;
+  }
   p->iSeek = 0;
   p->pBlob = pBlob;

@@ -439,6 +443,10 @@
   SqlFunc *p, *pNew;
   int nName = strlen30(zName);
   pNew = (SqlFunc*)Tcl_Alloc( sizeof(*pNew) + nName + 1 );
+  if( !pNew ){
+Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+return NULL;  /*  what should be returned here? */
+  }
   pNew->zName = (char*)[1];
   memcpy(pNew->zName, zName, nName+1);
   for(p=pDb->pFunc; p; p=p->pNext){
@@ -1168,6 +1176,10 @@
 nVar = sqlite3_bind_parameter_count(pStmt);
 nByte = sizeof(SqlPreparedStmt) + nVar*sizeof(Tcl_Obj *);
 pPreStmt = (SqlPreparedStmt*)Tcl_Alloc(nByte);
+if( !pPreStmt ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memset(pPreStmt, 0, nByte);

 pPreStmt->pStmt = pStmt;
@@ -1177,6 +1189,11 @@
 #ifdef SQLITE_TEST
 if( pPreStmt->zSql==0 ){
   char *zCopy = Tcl_Alloc(pPreStmt->nSql + 1);
+  if( !zCopy ) {
+Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+Tcl_Free(pPreStmt);
+return TCL_ERROR;
+  }
   memcpy(zCopy, zSql, pPreStmt->nSql);
   zCopy[pPreStmt->nSql] = '\0';
   pPreStmt->zSql = zCopy;
@@ -1372,6 +1389,10 @@
 p->nCol = nCol = sqlite3_column_count(pStmt);
 if( nCol>0 && (papColName || p->pArray) ){
   apColName = (Tcl_Obj**)Tcl_Alloc( sizeof(Tcl_Obj*)*nCol );
+  if( !apColName ){
+Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+return;
+  }
   for(i=0; i0 ){
 pDb->zAuth = Tcl_Alloc( len + 1 );
+if( !pDb->zAuth ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memcpy(pDb->zAuth, zAuth, len+1);
   }else{
 pDb->zAuth = 0;
@@ -1804,6 +1829,10 @@
   zBusy = Tcl_GetStringFromObj(objv[2], );
   if( zBusy && len>0 ){
 pDb->zBusy = Tcl_Alloc( len + 1 );
+if( !pDb->zBusy ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memcpy(pDb->zBusy, zBusy, len+1);
   }else{
 pDb->zBusy = 0;
@@ -1970,6 +1999,10 @@
   zCommit = Tcl_GetStringFromObj(objv[2], );
   if( zCommit && len>0 ){
 pDb->zCommit = Tcl_Alloc( len + 1 );
+if( !pDb->zCommit ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memcpy(pDb->zCommit, zCommit, len+1);
   }else{
 pDb->zCommit = 0;
@@ -2315,6 +2348,10 @@
   Tcl_IncrRefCount(pScript);

   p = (DbEvalContext *)Tcl_Alloc(sizeof(DbEvalContext));
+  if( !p ){
+Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+return TCL_ERROR;
+  }
   dbEvalInit(p, pDb, objv[2], pArray);

   cd2[0] = (void *)p;
@@ -2458,6 +2495,10 @@
   }
   if( zNull && len>0 ){
 pDb->zNull = Tcl_Alloc( len + 1 );
+if( !pDb->zNULL ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memcpy(pDb->zNull, zNull, len);
 pDb->zNull[len] = '\0';
   }else{
@@ -2513,6 +2554,10 @@
   zProgress = Tcl_GetStringFromObj(objv[3], );
   if( zProgress && len>0 ){
 pDb->zProgress = Tcl_Alloc( len + 1 );
+if( !pDb->zProgress ){
+  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
+  return TCL_ERROR;
+}
 memcpy(pDb->zProgress, zProgress, len+1);
   }else{
 pDb->zProgress = 0;
@@ -2555,6 +2600,10 @@
   zProfile = Tcl_GetStringFromObj(objv[2], );
   if( zProfile && len>0 ){
 pDb->zProfile = Tcl_Alloc( len + 1 );
+if( !pDb->zProfile ){
+  Tcl_SetResult(interp, (char 

[sqlite] Performance problems on windows

[Jakub Zakrzewski]

Hi,

because our product is quite complicated, I have to measure the processing time 
of the entire dataset. I log transitions between states and the difference 
between first transition and the last one is my score. The measurements are 
pretty stable and I don't count in the startup or shutdown time of the entire 
application. As the library is binary compatible I simply exchange the version, 
overwrite my test database and run the tests. So I'm pretty sure the conditions 
are the same.

The slowdown is confirmed by one of our customers. He uses Win 2008 Server x64. 
I'm testing on Win7 x64.

I'm sure that you're testing each release carefully. We do too. And we still 
have weird bugs ;)  

Thanks for interest. If I can provide any useful information, just tell me, 
what you need. I cannot reveal the source code but some profiling results or 
SQL statements are not a secret.

--
Gruesse,
Jakub

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Dienstag, 25. August 2015 03:19
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance problems on windows

I don't have any clues.

While most of our work happens on Linux, we do measure performance on Windows 
from one release to the next (see for example item 23-b on the most recent 
release check-list
https://www.sqlite.org/checklists/private/3081100/index#c23) and it gets better 
from one release to the next.  So I don't know why you are seeing a slowdown.

How do you measure?

On 8/24/15, Jakub Zakrzewski  wrote:
> Hi All,
>
> I finally got a chance to upgrade SQLite for our product from ancient 
> 3.7.16.2. Initial tests on Linux were very promising - ranging from 
> 33% to even 300% (for one degenerated case) speed improvement. So far so good.
> Problems begun when I have tested it on Windows. Depending on test 
> case the new version is up to 0.28x slower! In the course of 
> investigation I have managed to improve the performance by adding few 
> missing indexes but this has influenced both old and new versions so 
> the relative performance with
> 3.8.11.1 is still like one third worse.
>
> I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0 
> and the results are quite surprising:
> 3.7.16.2 -> 3.7.17.0 : ~16% faster
> 3.7.17.0 -> 3.8.0.0  : ~26% slower
> 3.7.16.2 -> 3.8.0.0  : ~15% slower
> 3.7.16.2 -> 3.8.11.1 : ~28% slower
>
> We use SQLite as backend ("persistent storage") to an implementation 
> of a state machine. The queries are rather simple and each of them is 
> too fast to measure (SQLite performance timer only has resolution of 
> milliseconds) in the profiler it looks like the actual filesystem 
> functions were taking more time in new versions but that tells me nothing 
> really.
>
> Is there something I can tweak?
> The page size is set to 4K (NTFS file system), synchonous is OFF, 
> journal_mode=truncated;
>
> With 10K objects all versions seem to perform equally: test takes 
> ~45s. For 100K objects it's already [m]:[s]
> 3.7.16.2 : ~10:55
> 3.7.17.0 : ~09:30
> 3.8.0.0  : ~12:46
> 3.8.11.1 : ~15:08
>
> I'm out of ideas here. Can someone help me with further investigation?
>
> --
> Gruesse,
> Jakub
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Performance problems on windows

[Richard Hipp]

On 8/25/15, Jakub Zakrzewski  wrote:
>  If I can provide any useful information, just tell me,

Perchance, do you have a reproducible test case that you can share?

Have you considered adding timers on individual SQL operations to see
which ones are slower and/or faster?

Can you record all SQL operations (using sqlite3_trace()) as well as
the starting state of the database and reproduce the problem by
running a log of the SQL operations against a copy of the initial
database?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] ESC_BAD_ACCESS when using a background thread

[Jeff M]

On Aug 24, 2015, at 11:50 AM, Roger Binns  wrote:
> On 08/24/2015 03:08 AM, Jeff M wrote:
>> I've checked all of your suggestions and nothing is amiss.
> 
> You ran valgrind and it said everything is fine?  That would be shocking.

You caught me.  I wasn't familiar with valgrind, so I passed over that 
suggestion.  I tried to install valgrind (on Mac OS Yosemite), but I can't get 
it to compile.  I don't have the fortitude to work out the install issues.

Looking at Xcode's memory report (and using Instruments), I see that memory 
does ratchet up, despite my being very careful matching alloc/releases (this 
app is pre-ARC).  I need to relearn Instruments.  Among other things, I see 
many persistent 4K memory blocks resulting from sqlite3MemRealloc, but I 
haven't figured out the call tree to get there.

On Aug 24, 2015, at 11:50 AM, Roger Binns  wrote:
> ... there is [likely] a bug in [your] code related to object/pointer 
> lifetimes or similar.  [C]hanging things changes where collateral damage 
> happens, but doesn't actually fix the issue.

Yup.

Jeff

[sqlite] Compile warnings

[Scott Robison]

Agreed. It's easy for me to say what I think should be done since I'm not
writing or maintaining the code.

On Mon, Aug 24, 2015, 5:53 PM David Bennett  wrote:

> Fair enough. Bearing in mind that any new code path requires a new test
> case and coverage.
>
>
>
> Regards
>
> David M Bennett FACS
>
> *Andl - A New Database Language - andl.org *
>
>
>
>
>
> *From:* Scott Robison [mailto:scott at casaderobison.com]
> *Sent:* Tuesday, 25 August 2015 1:24 AM
> *To:* General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org>; davidb at pfxcorp.com
>
>
> *Subject:* Re: [sqlite] Compile warnings
>
>
>
> On Aug 24, 2015 6:29 AM, "David Bennett"  wrote:
> >
> > I think we've beaten the philosophy to death and we're largely in
> agreement.
> >
> > I'm not sure we actually came up with a firm recommendation as to what
> to do
> > about this specific warning in this particular line of code with this
> > compiler. Ostrich treatment maybe, and rely on the general Sqlite
> > disclaimer?
>
> Well, I haven't inspected the code closely. My discussions have been
> purely philosophical. :)
>
> If the warning has to do with a constant zero expression, then I think I'd
> probably go ahead and add the if statement at this point, giving the
> optimizer the opportunity to completely remove the code fragment, even
> though that would likely just change warnings.
>
> If the expression is not constant, I don't have a strong opinion on how to
> suppress or if to suppress. Given SQLite's focus on performance (and that
> the code is correct either way) I'd probably profile and see if performance
> was impacted and make the decision then. If performance was clearly better
> with an if statement I'd make the change. If not suppress the warning
> conditionally if possible (gcc is a very common choice of compiler and
> worth having a clean build) or ignore it if not.
>
> >
> > Regards
> > David M Bennett FACS
> >
> > MD Powerflex Corporation, creators of PFXplus
> > To contact us, please call +61-3-9548-9114 or go to
> > www.pfxcorp.com/contact.htm
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org
> > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
> > Robison
> > Sent: Monday, 24 August 2015 8:25 AM
> > To: davidb at pfxcorp.com; General Discussion of SQLite Database
> > 
> > Subject: Re: [sqlite] Compile warnings
> >
> > On Sat, Aug 22, 2015 at 8:07 PM, David Bennett 
> wrote:
> >
> > > Of course that is the aim, as always.
> > >
> > >
> > >
> > > In this particular case, maximally portable code (that will compile
> > > and execute correctly on all conforming compilers) must (a) ensure
> > > that the pointer argument is valid (b) ensure that the length is
> valid, or
> > zero.
> > > Where reasonably possible both should be done statically. If
> > > conditional code is introduced then it must be tested with all branches
> > covered.
> > >
> >
> > Agreed, and in C89 NULL was a valid pointer argument in this context as
> long
> > as the length was zero. That has nothing to do with this particular
> thread,
> > but I referenced it just as a point of "C99 changed the semantics of
> what is
> > valid, invalidating previously valid code". Projects that took advantage
> of
> > that can either modify their code to accommodate the newer standard or
> leave
> > it along claiming it conforms to the intended / desired standard. In that
> > case, it was easy to change to code to be compatible with both standards
> and
> > it was done. In this case (gcc warning about possible argument order
> error
> > due to a constant expression) can be equally accommodated, but it's not a
> > matter of standards compliance. It *is* a matter of one of the (or
> perhaps
> > *the*) most used compilers bellyaching about something that is not wrong
> or
> > invalid in any way. So is the code modified to suppress the warning, is
> the
> > warning disabled globally, locally, or just ignored? I can see the
> benefits
> > to making the change and to not making the change.
> >
> > > As always, it may be the case that one or more compilers may issue
> > > warnings for code that is fully compliant with the standard and fully
> > > tested. Sadly there may even be compilers that compile the code
> > > incorrectly (a compiler bug). The question of how to handle undesired
> > > warnings or compiler bugs on code that is known to be correct and
> > > compliant is always a judgement call. In my opinion the solution
> > > chosen should always be as fine-grained as possible (such as a
> > > compiler-specific conditional), but the downside is that the code can
> > > become littered with references to specific problems in specific
> compilers
> > and thus become harder to work with.
> > >
> >
> > I agree completely. Most of us don't have to worry about this too much
> > because we target one platform (most code is not written with
> portability in
> > mind). SQLite is not most