[sqlite] [PATCH][really] add missing autoconf feature detection

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Use of some function/features protected by #ifdefs, but lacks autoconf magic 
> to
> automatically enable them when possible. Of course, they can be manually
> enabled, but it is not very likely. And unused code tends to bitrot.

Oops, last time patch attachment with mime-type text/* worked :-(
Inlining instead:

===

Subject: autodetect and automatically use pread/pwrite

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>

Index: sqlite3-3.7.14/configure.ac
===
--- sqlite3-3.7.14.orig/configure.ac
+++ sqlite3-3.7.14/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64])

 #
 # By default, we use the amalgamation (this may be changed below...)
@@ -284,6 +284,18 @@ if test "$SQLITE_THREADSAFE" = "1"; then
 fi

 ##
+# Auto-detect pread/pread64
+#
+AC_ARG_ENABLE(pread, AC_HELP_STRING([--disable-pread], [Disable pread(2) use]))
+if test x$enable_pread != xno; then
+   if test $ac_cv_func_pread64,$ac_cv_func_pwrite64 = yes,yes; then
+   AC_DEFINE(USE_PREAD64, 1, [Define if you want to use pread64 
function])
+   elif test $ac_cv_func_pread,$ac_cv_func_pwrite = yes,yes; then
+   AC_DEFINE(USE_PREAD, 1, [Define if you want to use pread 
function])
+   fi
+fi
+
+##
 # Do we want to allow a connection created in one thread to be used
 # in another thread.  This does not work on many Linux systems (ex: RedHat 9)
 # due to bugs in the threading implementations.  This is thus off by default.
===

Subject: autodetect strchrnul

Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>

Index: sqlite3-3.8.7.1/configure.ac
===
--- sqlite3-3.8.7.1.orig/configure.ac
+++ sqlite3-3.8.7.1/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul])

 #
 # By default, we use the amalgamation (this may be changed below...)
===

Subject: autodetect posix_fallocate

Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>

Index: sqlite3-3.8.7.1/configure.ac
===
--- sqlite3-3.8.7.1.orig/configure.ac
+++ sqlite3-3.8.7.1/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul posix_fallocate])

 #
 # By default, we use the amalgamation (this may be changed below...)

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

[sqlite] [PATCH] add missing autoconf feature detection

[Yuriy Kaminskiy]

Use of some function/features protected by #ifdefs, but lacks autoconf magic to
automatically enable them when possible. Of course, they can be manually
enabled, but it is not very likely. And unused code tends to bitrot.


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

Re: [sqlite] When to open/close connections

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 29 Apr 2014, at 2:24pm, Drago, William @ MWG - NARDAEAST 
>  wrote:
> 
>> Does closing the connection force, or at least encourage, the OS to write to 
>> disk whatever it might have been caching?
> 
> Closing a connection calls fclose() on the database file (as long as fopen() 
> was called because at least one operation was done on the database).  So the 
> operating system does everything it would normally do in response to fclose().

No, sqlite does NOT use fopen/fclose, neither on unix, nor on windows, it uses
lower-level interface (open(2)/close(2) on unix). AFAIK, close(2) normally does
not attempt to write/flush/sync anything (it could be more complex with unusual
fs, e.g. NFS, but sqlite is incompatible with NFS anyway). (No idea about MS
Windows specifics).

> This means closing a connection can take a long time because it does lots of 
> things.  For example under Windows/Mac/Unix if the file has been changed it 
> changes the 'last modified' date on the file.  Consequently you would not 
> want to keep opening and closing a connection in a program that made a lot of 
> changes to a SQLite database.

AFAIK, on unix, "last modified" (st_mtime) is updated when write(2) is called,
and not delayed till close(2). (Again, no idea about MS Windows specifics).

I have not instrumented sqlite (and thus could be wrong), but I'd guess only
"expensive" thing in database "connection" close is memory deallocation. On
other hand, database "connection" open is much more expensive, as it requires
re-reading and re-parsing database schema and various other things.

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

Re: [sqlite] When to open/close connections

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 28 Apr 2014, at 11:11pm, RSmith  wrote:
> 
>> Second approach is better when you rarely access the database, also it will 
>> make sure releases happen (or at least provide immediate errors if not), but 
>> keeping a connection open is much better when hundreds of accesses happen in 
>> terms of speed - especially loads of small queries, large queries won't 
>> matter either way.
> 
> I add two other reasons to Ryan's: if you are extremely short of memory (for 
> example in a tiny low-power device) or if your file system lacks proper 
> flushing because although flush() is documented in reality it does nothing.

s/flush/sync/.
However, I don't see just how closing/opening db will make it any better.
Database/journal is written and synced at transaction end, not at "connection"
close.

> But as others are saying, on a desktop/laptop computer your only result will 
> be to slow things down.

Yep. Also, if you keep connection open, you can save some time on
keeping/caching prepared statements (you must finalize all statements before you
close "connection")

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

Re: [sqlite] Out of memory error for SELECT char();

[Yuriy Kaminskiy]

Eduardo Morras wrote:
> On Sat, 8 Mar 2014 14:09:17 -0500
> Richard Hipp  wrote:
>> It isn't really running out of memory
>>
>> The implementation of char() allocates 4 bytes of output buffer for
>> each input character, which is sufficient to hold any valid unicode
>> codepoint. But with zero input characters, that means it tries to
>> allocate a zero-byte output buffer.  sqlite3_malloc() returns NULL
>> when asked to allocate zero bytes, at which point the char()
>> implementation thinks that the malloc() failed and reports the
>> output-of-memory error.
> 
> It's OS dependant. From malloc FreeBSD man page [...]

malloc() behavior wrt 0-byte allocation is OS-dependent.
sqlite3_malloc() is not: it will return NULL on any OS, regardless of malloc()
implementation.

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

Re: [sqlite] Trying to use in-memory database

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Wed, Feb 19, 2014 at 5:25 PM, Jeff Archer <
> jsarc...@nanotronicsimaging.com> wrote:
> 
>> Long time SQLite user but I don't think I have ever tried to do an
>> in-memory database before.
>> Just upgraded to 3.8.3.1 but I am not having any other failures with
>> existing code so I don't think that is any part of the problem.
>> I am trying to open the database with sqlite3_open16() using a filename of
>> L":MEMORY:".  I am getting a result code of 14 which is SQLITE_CANTOPEN.
>> It does open if I use a real filename.
> 
> The magic name is case-sensitive and lower-case.  Use L":memory:" and it
> should work for you.

... and also note that passing L"..." string constants (and everything else of
`wchar_t *` type) to sqlite3_*16() *very* bad idea (on nearly all non-windows
platforms wchar_t is 32-bit int ucs-32, sqlite3_*16() expects 16-bit/utf-16,
depending on platform endianness it will try to open ":" [which will silently
succeed, but open *on-disk* database in current directory, extremely nasty!] or
"" [which will likely fail]).

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

Re: [sqlite] Once again about random values appearance

[Yuriy Kaminskiy]

James K. Lowden wrote:
> On Fri, 14 Feb 2014 08:32:02 +0400
> Max Vlasov  wrote:
> 
>> From: Max Vlasov 
>> To: General Discussion of SQLite Database 
>> Reply-To: General Discussion of SQLite Database
>>  Date: Fri, 14 Feb 2014 08:32:02 +0400
>> Subject: Re: [sqlite] Once again about random values appearance
>>
>> On Fri, Feb 14, 2014 at 6:35 AM, James K. Lowden
>> wrote:
>>
> select id, (select id from TestTable where id = abs(random() %
> 100)) as rndid from TestTable where id=rndid
>>> On Thu, 13 Feb 2014 07:26:55 -0500
>>> Richard Hipp  wrote:
>>>
 It is undefined behavior, subject to change depending the specific
 version of SQLite, compile-time options, optimization settings,
 and the whim of the query planner.
>>> It should be defined.
>>>
>>> In the above query, random() should be evaluated once.  In the SQL
>>> model the user provides inputs once, and the system evaluates them
>>> once.
>>>
>>>
>> Once for the query or once for the row?
> 
> Once for the query.  
> 
> As a user you have no control how the system evaluates your query.
> The evaluation may change over time with different implementations, but
> the semantics of the query do not.  
> 
> Not long ago on this list we discussed 
> 
>   SELECT *, datetime('now') from T;
> 
> and the behavior was that the datetime function was called per-row,
> resulting in different times on different rows.  It was changed, the
> rationale IIRC to be compatible with the SQL standard and other DBMSs.  
> 
> Like datetime, random() is a nondeterministic function taking constant
> (but void) input.  It should be evaluated once, as provided. Where it
> appears in your query should not matter.  That would make it consistent
> with how other functions work, and with the SQL standard.  

Good in theory, bad in practice.
CURRENT_TIME = CURRENT_TIME was /almost always/ true before change (when it
happened to be false, it was /surprising/ and /counter-intuitive/ exception),
and just become /always/ true after change (no surprising change in behavior!).

RANDOM() = RANDOM() is /always false/ now, it will become /always true/ after
your suggested change.
It have much higher potential to break existing code, e.g.
   SELECT * FROM t ORDER BY RANDOM() LIMIT 1
will be completely broken (well, sure, it is not best way to select random
row(s) from table - it is overly expensive and unportable - but still much more
likely to be used - and relied on - in existing code).

Besides, datetime function behavior is specified by SQL standard - and RANDOM()
is not.

Then again, I, too, don't quite like how OP's queries - such as
   SELECT r <> r FROM (SELECT RANDOM() AS r FROM t)
behavior depends on whether query flattening was used or not (even though one
can write it off as "documented undefined behavior").
Or, that `SELECT RANDOM() AS r FROM t ORDER BY r` gives quite unexpected result.
I just don't think that same approach as with datetime function would be good in
this case.

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

Re: [sqlite] LIKE operator and collations

[Yuriy Kaminskiy]

Constantine Yannakopoulos wrote:
> I have a case where the user needs to perform a search in a text column of
> a table with many rows. Typically the user enters the first n matching
> characters as a search string and the application issues a SELECT statement
> that uses the LIKE operator with the search string:
> 
> SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'
> 
> According to the LIKE optimization this statement will use an index so it
> will be fast.

FWIW, sqlite only use index if pattern is string constant or placeholder, it
won't try to use index optimization with more complex constant expression.
I.e.
   SELECT * FROM ATable WHERE AColumn LIKE :SearchString
and append % to your search string outside of sql.
(Well, it does not matter, as I think your following question does not have
acceptable solution).

> The application is used by Greek users. The greek alphabet has some letters
> that are visually identical to corresponding latin letters when in
> capitals, (e.g. A, E, I, O, B, H, K, etc), some that are different but
> match deterministically to a single latin letter (e.g. Σ = S, Φ = F, Ω = O
> etc.) and some that don't have a deterministic match (e.g. Γ = Gh or Yi).

You may want to look at ICU extension, but then sqlite won't use index.

Another option may be fts extension (likely, with custom tokenizer function).

> The table contains strings that consist of words that can be written in
> either latin or greek characters; sometimes even mixed (the user changed
> input locale midword before typing the first non-common letter). I have a
> request that the search should match strings that are written with either
> latin or greek or mixed letters, e.g. "MIS" should match "MISKO" (all
> latin), "ΜΙΣΚΟ" (all greek) or "MIΣΚΟ" (first two letters latin, rest
> greek). I thought of using a custom collation that does this type of
> comparison, have the column use that collation and create an index on that
> column to speed up the search but I discovered that the LIKE operator
> either will not use collations other than BINARY and NOCASE (pragma
> case_sensitive_like) or (if overloaded to perform custom matching) will not

I think "will not use index" is a bug that was fixed in latest sqlite version;
however, "ignoring collation" is intended behavior, and not easy to change :-(.

> use an index, and, worse yet, its behaviour will be the same to all string
> comparisons regardless of collation. So, a full table scan seems inevitable.
> I was wondering whether it is realistic to ask for the LIKE operator to use
> by default the assigned collation of a column. I assume that an index on

From first look, it won't be easy. Probably impossible without changing current
sqlite interface for user-defined collation/function/etc. Besides, it will break
compatibility with existing code, so would require some new PRAGMA to enable.

> that column is using by default the specified collation of the column for
> comparisons, so a LIKE clause like the aforementioned can use the index and
> perform a fast search while using the "mixed" comparison I need. This would
> transparently solve my problem and make the case_sensitive_like pragma
> redundant, but for backward compatibility this behaviour could be activated
> by a new pragma.
> 
> Are there any details I am missing that prevent this from being implemented?
> 
> Thanks in advance.
> 
> --Constantine.


RSmith wrote:
[...]
> It will be a matter of finding the most acceptable deficit... Whether it
> be size, time waste, upgrade cost etc.  By the way, I don't think
> upgrading the table schemata need to be a real hard thing... some
> scripts can take care of that in minimum amount of time. (Test them
> thoroughly though). Also, another poster here had developed a full set
> of international collations and comparison mechanisms as a loadable
> extension to SQLite - Nunicode by Aleksey Tulinov I think... link here:
>
> https://bitbucket.org/alekseyt/nunicode

Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in
ICU extension), sqlite won't use index for optimization.

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/4/2014 5:51 PM, Yuriy Kaminskiy wrote:
>> Igor Tandetnik wrote:
>>> On 2/4/2014 11:57 AM, Yuriy Kaminskiy wrote:
>>>> Phew. Do you speak C? Enjoy.
>>>>
>>>>   printf("insert...\r"); fflush(stdout);
>>>>   for(i = 0; i < 1000; i++) {
>>>>  rc = sqlite3_bind_int(ins_sth, 1, i);
>>>>  assert(rc == SQLITE_OK);
>>>>  rc = sqlite3_step(ins_sth);
>>>>  assert(rc == SQLITE_DONE);
>>>
>>> As expected, I get an assertion here, on 925th iteration of the loop.
>>> Are you seeing something different?
>>
>> Yes. I see only 10-second pauses on 252th, 364th, etc iterations, and
>> *no*
>> assertion failures.
> 
> Well, what I'm seeing is definitely different from what you are seeing.
> 
> What version of SQLite are you running? What does sqlite3_libversion
> return?

I've checked on 3.8.2, 3.8.1, 3.7.14, 3.7.7; results was exactly same. I've
added sqlite3_libversion() output, and it matches expected version in all cases.
This is on linux/i386/32-bit.

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/4/2014 11:57 AM, Yuriy Kaminskiy wrote:
>> Phew. Do you speak C? Enjoy.
>>
>>  printf("insert...\r"); fflush(stdout);
>>  for(i = 0; i < 1000; i++) {
>> rc = sqlite3_bind_int(ins_sth, 1, i);
>> assert(rc == SQLITE_OK);
>> rc = sqlite3_step(ins_sth);
>> assert(rc == SQLITE_DONE);
> 
> As expected, I get an assertion here, on 925th iteration of the loop.
> Are you seeing something different?

Yes. I see only 10-second pauses on 252th, 364th, etc iterations, and *no*
assertion failures.
After completion, ttt.db3 contains exactly 1000 records with 0...999, without
any misses.
Same after I increased number of iterations to 1.

FWIW, if I swap two "COMMIT" lines, it results in assertion failure (as
expected) on sql_exec(dbh, "COMMIT"), so assertion enabled and error detection
works.

> The only thing is that rc == SQLITE_IOERR at this point, and not
> SQLITE_BUSY as I stated earlier. If I enable extended result codes (see
> sqlite3_extended_result_codes), then the error is SQLITE_IOERR_BLOCKED.
> This is actually documented at
> http://www.sqlite.org/c3ref/busy_handler.html :

grep for SQLITE_IOERR_BLOCKED in source code have not shown anything but false
positives. There are some code that *checks* for IOERR_BLOCKED, but there are no
code that can *set* IOERR_BLOCKED. But maybe I've overlooked something,
corrections welcomed.

> The SQLITE_BUSY error is converted to SQLITE_IOERR_BLOCKED when SQLite
> is in the middle of a large transaction where all the changes will not
> fit into the in-memory cache. SQLite will already hold a RESERVED lock
> on the database file, but it needs to promote this lock to EXCLUSIVE so
> that it can spill cache pages into the database file without harm to
> concurrent readers. If it is unable to promote the lock, then the
> in-memory cache will be left in an inconsistent state and so the error
> code is promoted from the relatively benign SQLITE_BUSY to the more
> severe SQLITE_IOERR_BLOCKED. This error code promotion forces an
> automatic rollback of the changes. See the CorruptionFollowingBusyError
> < http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError >
> wiki page for a discussion of why this is important.

I also verified that sqlite3_get_autocommit is 0 before sql_exec("COMMIT"), and
1 afterwards, so no "automatic rollback" either.

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/4/2014 5:23 AM, Yuriy Kaminskiy wrote:
>> How sqlite is supposed to behave when
>> *) there are read-only transaction;
>> *) there are update transaction on other connection;
>> *) cache space is exhausted by update transaction;
>> *) sqlite was not able to upgrade RESERVED lock to EXCLUSIVE due to
>> presence of SHARED lock, even after waiting for busy_timeout;
>> ?
> 
> SQLite should return SQLITE_BUSY error for the statement that triggered
> the cache spill. From the documentation of sqlite3_step:

> SQLITE_BUSY means that the database engine was unable to acquire the
> database locks it needs to do its job. If the statement is a COMMIT or
> occurs outside of an explicit transaction, then you can retry the
> statement. If the statement is not a COMMIT and occurs within an
> explicit transaction then you should rollback the transaction before
> continuing.
> 
> This second part is precisely for the case where a statement other than
> a COMMIT wants to acquire EXCLUSIVE lock due to cache overflow, and
> fails. If I recall previous discussions of this topic correctly, SQLite
> cannot guarantee that the in-memory cache is in a good state at this
> point, and so the only safe course of action is to discard it by rolling
> back.

But in fact, it *does not return error* (except for COMMIT).
Verified on sqlite 3.7.7 and 3.8.2.

>> It can either fail update statement (but I don't see that in test
>> below: it
>> sleeps for busy_timeout, but *does not* return error), or keep on
>> storing data
>> in memory [it *does not* write anything] (thus using over specified
>> cache size?
>> [and eventually overflow virtual memory?]), or what?
> 
> It's supposed to return an error. There might be some flaw in your test;
> I myself don't speak Perl (and am too lazy to conduct a test of my own).

Phew. Do you speak C? Enjoy.

#include 
#include 
#include 
#include 
int main(int argc, char *argv[])
{
sqlite3 *dbh;
sqlite3 *dbh2;
sqlite3_stmt *sth;
sqlite3_stmt *sth2;
sqlite3_stmt *ins_sth;
char *errmsg;
int rc;
int i, j;
rc = sqlite3_open("ttt.db3", );
assert(rc == SQLITE_OK);
rc = sqlite3_open("ttt.db3", );
assert(rc == SQLITE_OK);
rc = sqlite3_busy_timeout(dbh, 1);
assert(rc == SQLITE_OK);
rc = sqlite3_busy_timeout(dbh2, 1);
assert(rc == SQLITE_OK);
#define sql_exec(dbh, sql) do { \
rc = sqlite3_exec(dbh, sql, NULL, NULL, ); \
printf("exec: %p, '%s' -> %d\n", dbh, sql, rc); \
assert(errmsg == NULL); \
sqlite3_free(errmsg); \
} while(0)
sql_exec(dbh, "PRAGMA page_size = 1024");
sql_exec(dbh, "PRAGMA cache_size = 4");
sql_exec(dbh, "CREATE TABLE IF NOT EXISTS t (i)");
sql_exec(dbh2, "PRAGMA cache_size = 4");
rc = sqlite3_prepare_v2(dbh, "SELECT * FROM t", -1, , NULL);
assert(rc == SQLITE_OK);
rc = sqlite3_prepare_v2(dbh2, "SELECT * FROM t", -1, , NULL);
assert(rc == SQLITE_OK);
rc = sqlite3_prepare_v2(dbh, "INSERT INTO t VALUES(?)", -1, _sth, NULL);
assert(rc == SQLITE_OK);
sql_exec(dbh, "BEGIN IMMEDIATE");
sql_exec(dbh2, "BEGIN");
rc = sqlite3_step(sth2);
assert(rc == SQLITE_DONE || rc == SQLITE_ROW);
j = 0;
printf("insert...\r"); fflush(stdout);
for(i = 0; i < 1000; i++) {
rc = sqlite3_bind_int(ins_sth, 1, i);
assert(rc == SQLITE_OK);
rc = sqlite3_step(ins_sth);
assert(rc == SQLITE_DONE);
j += sqlite3_changes(dbh);
printf("insert: %04d\r", i); fflush(stdout);
rc = sqlite3_reset(ins_sth);
assert(rc == SQLITE_OK);
}
printf("insert done\n");
printf("j = %04d\n", j);
i = 0;
printf("select...\r"); fflush(stdout);
while((rc = sqlite3_step(sth)) == SQLITE_ROW) {
i++;
printf("select: %04d\r", i); fflush(stdout);
}
printf("select done\n");
printf("i = %04d\n", i);
assert(rc == SQLITE_DONE);
rc = sqlite3_reset(sth2);
assert(rc == SQLITE_OK);
rc = sqlite3_reset(sth);
assert(rc == SQLITE_OK);
sql_exec(dbh2, "COMMIT");
sql_exec(dbh, "COMMIT");
rc = sqlite3_finalize(sth); sth = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_finalize(sth2); sth2 = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_finalize(ins_sth); ins_sth = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_close(dbh); dbh = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_close(dbh2); dbh2 = NULL;
assert(rc == SQLITE_OK);
return 0;
}

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/3/2014 3:21 PM, Yuriy Kaminskiy wrote:
>> Igor Tandetnik wrote:
>>> On 2/3/2014 1:07 PM, Baruch Burstein wrote:
>>>> 1) How does a transaction affect SELECTs? If I start a transaction
>>>> and do
>>>> an UPDATE/DELETE/INSERT, what data will a SELECT in the same
>>>> transaction
>>>> see?
>>>
>>> The new data. A transaction always sees its own changes.
>>>
>>>> What about a SELECT in a different connection?
>>>
>>> Depends. In journal mode, the reader transaction is blocked while a
>>> writer is in progress; you won't be able to run that SELECT statement
>>
>> Erm, wrong.
>>
>> [1] sqlite> create table t (i);
>> [1] sqlite> insert into t values (1);
>> [1] sqlite> begin;
>> [1] sqlite> insert into t values (2);
>> [2] sqlite> begin;
>> [2] sqlite> /* as you see, there are no problem in starting transaction
>> concurrently with pending update in [1]: */
>> [2] sqlite> select * from t;
>> 1
>> [2] sqlite> /* as you see, SELECT is also successful and returns
>> unmodified data: */
> 
> Ah, right. I oversimplified. [1] holds a reserved lock, indicating
> intention to write eventually; changes are parked in an in-memory cache
> for now. A reserved lock allows new readers to acquire shared locks.
> 
> At some future point, the writer wants to commit, or else the amount of
> changes becomes large enough that they cannot be held in RAM and need to
> be spilled to disk. At this point, the writer would escalate to PENDING
> lock, wait until all current readers clear while not allowing any new
> ones, then escalate once more to EXCLUSIVE lock, and hold it until the
> transaction is committed or rolled back.

Thanks for correction.

Hmm... that suggests there can be other failure mode (with rollback journal):
(long-enough) concurrent read-only transaction can break (sufficiently-big)
insert transaction, even if read-only transaction will be ended before COMMIT in
update transaction. It would be nasty (and render "BEGIN IMMEDIATE workaround"
problematic)...

...however, I was not able to reproduce it in test.

How sqlite is supposed to behave when
*) there are read-only transaction;
*) there are update transaction on other connection;
*) cache space is exhausted by update transaction;
*) sqlite was not able to upgrade RESERVED lock to EXCLUSIVE due to presence of
SHARED lock, even after waiting for busy_timeout;
?

It can either fail update statement (but I don't see that in test below: it
sleeps for busy_timeout, but *does not* return error), or keep on storing data
in memory [it *does not* write anything] (thus using over specified cache size?
[and eventually overflow virtual memory?]), or what?

>> There are *different* restriction in rollback journal mode: you cannot
>> *COMMIT*
>> in [1] while transaction in [2] is still active.
> 
> Well, there are *both* sets of restrictions - the kind that I described,
> and the kind that you describe. Which set of restrictions applies

> depends on where in its lifecycle the writer is. If it's still holding a
> RESERVED lock, then new readers are allowed, and the writer is blocked
> until they clear. If it's holding a PENDING or EXCLUSIVE lock, then
> readers are blocked until the writer clears.

-- 
=== test.pl ===
#!/usr/bin/perl -l
use blib;
use IO::Handle;
use DBI;
DBI -> trace( 1 );
my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/ttt.db3","","", {
RaiseError=>1,
});
my $dbh2 = DBI->connect ( "dbi:SQLite:dbname=/tmp/ttt.db3","","", {
RaiseError=>1,
});
$dbh->do("pragma page_size = 1024;");
$dbh->do( "pragma cache_size = 4; ");
$dbh2->do( "pragma cache_size = 4; ");
$dbh->do("CREATE TABLE IF NOT EXISTS t (i)");
my $sth = $dbh->prepare( "SELECT * FROM t" );
my $sth2 = $dbh2->prepare( "SELECT * FROM t" );
my $ins_sth = $dbh->prepare("INSERT INTO t VALUES (?)");
$dbh->func( 10_000, 'busy_timeout' );
$dbh2-> do( "BEGIN;");
# acquire SHARED lock in dbh2:
$sth2->execute();
$sth2->fetch();
$dbh-> do( "begin;" );
my $j = 0;
for my $i (1..1_000) { $j += $ins_sth->execute($i); }
print STDERR "j = $j\n";
$sth2->finish();
my $i = 0;
$sth->execute();
while($sth->fetch) { $i++; }
$sth->finish;
print STDERR "i = $i\n";
# release SHARED lock in dbh2; fails if next two lines swapped
$dbh2->do("COMMIT");
$dbh->do("commit");
$dbh->disconnect;
$dbh2->disconnect;
___END___

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/3/2014 1:07 PM, Baruch Burstein wrote:
>> 1) How does a transaction affect SELECTs? If I start a transaction and do
>> an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
>> see?
> 
> The new data. A transaction always sees its own changes.
> 
>> What about a SELECT in a different connection?
> 
> Depends. In journal mode, the reader transaction is blocked while a
> writer is in progress; you won't be able to run that SELECT statement

Erm, wrong.

[1] sqlite> create table t (i);
[1] sqlite> insert into t values (1);
[1] sqlite> begin;
[1] sqlite> insert into t values (2);
[2] sqlite> begin;
[2] sqlite> /* as you see, there are no problem in starting transaction
concurrently with pending update in [1]: */
[2] sqlite> select * from t;
1
[2] sqlite> /* as you see, SELECT is also successful and returns unmodified 
data: */

There are *different* restriction in rollback journal mode: you cannot *COMMIT*
in [1] while transaction in [2] is still active.
[1] sqlite> COMMIT;
Error: database is locked
[1] sqlite>
[2] sqlite> COMMIT; /* or ROLLBACK; */
[1] sqlite> COMMIT;
[1] sqlite> /* succeed this time */

In WAL mode, there are no such restriction.

(You *can* take any number of SHARED locks when one of transactions already got
RESERVED lock. You cannot upgrade that SHARED lock to RESERVED [required for any
db modification] if other transaction already got RESERVED lock; you cannot
upgrade RESERVED lock to EXCLUSIVE lock while there are other transaction(s)
that acquired SHARED lock [required for COMMIT in rollback journal mode, not
required in WAL mode])

> until the writer transaction commits (in which case SELECT will see new
> data) or rolls back (in which case it will see old data).
> 
> In WAL mode, a reader is allowed to read while a writer transaction is
> in progress. It reads the old data, before any changes.
> 
>> 2) Can 2 connections run 'BEGIN;' ?
> 
> Yes. They are presumed to be readers, until they attempt to run a data
> modification statement. Two readers can happily co-exist.

>> If so, who gets the final say on the data? The first to do the
>> 'COMMIT;'? What will happen when the other does a
>> COMMIT?
> 
> When these two transactions issue a BEGIN statement, each acquires a
> shared lock. The first one to run UPDATE or similar gets a reserved
> lock, which basically says "I'm going to write at some later time; for
> now, I'm waiting for all existing readers to clear". If the second
> transaction tries to run UPDATE, it will get an error when trying to
> obtain its own reserved lock. At this point, the system is in a deadlock
> - no progress can be made until one of the transactions rolls back.
> 
> To avoid this situation, use BEGIN IMMEDIATE or BEGIN EXCLUSIVE for
> transactions that you know will be making changes (not just reading).
> 
> For details, see
> 
> http://www.sqlite.org/lockingv3.html
> http://www.sqlite.org/atomiccommit.html
> http://www.sqlite.org/lang_transaction.html

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

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Yuriy Kaminskiy]

Woody Wu wrote:
> Hi, Simon
> 
> On 7 January 2014 19:32, Simon Slavin  wrote:
> 
>> On 7 Jan 2014, at 10:13am, Woody Wu  wrote:
>>
>>> Thanks for the clear guide.  _busy_timeout is easier to use.  By the
>>> way, i want confirm that if i am not in an explicit transaction, i can 
>>> simply
>>> redo the _step() invoking, right?
>> However, if you are using _timeout() properly, you should never need to

(That's incorrect. _busy_timeout() can simplify program logic, but *it is not
sufficient* by itself. See below.)

> What means "using _timeout()" properly?  The manual says, _timeout() can
> still make _step() returns SQLITE_BUSY or SQLITE_IOERR_BLOCKED if the user
> provided timeout value eventually accumulated.   For example, if I set
> _timeout() to 3000ms, but after that time, the table I was trying to update
> still locked by another process.  This is normal, right?

sqlite can return BUSY immediately if it detects dead-lock condition (e.g. two
connections issues BEGIN; SELECT ...; UPDATE ...; in parallel).

thread Athread B
BEGIN;  BEGIN;
SELECT; /* acquires SHARED lock*/
SELECT; /* also acquires SHARED lock */
 /* (two SHARED locks can coexist!) */
UPDATE; /* upgrades SHARED lock to RESERVED lock */
 /* (one RESERVED lock can coexist with SHARED lock) */
UPDATE; /* tries to upgrade to RESERVED lock and
   *fails*;
   returns SQLITE_BUSY [*immediately*]*/
 /* (only one connection can own RESERVED lock!) */

Note that there are no sense to wait and retry in thread B here, it won't fix
deadlock!
Moreover, if you keep transaction in thread B and thread A will issue COMMIT, it
will fail *too* (after waiting for _busy_timeout), as COMMIT requires EXCLUSIVE
lock, and it cannot be acquired before thread B will release its SHARED lock:

=== If you keep transaction in thread B: ===
thread A:   thread B:
/* still have SHARED lock */
COMMIT; /* tries to upgrade lock to EXCLUSIVE and *fails* [after waiting for
busy_timeout] with SQLITE_BUSY too! (EXCLUSIVE lock cannot coexist with any
other lock) */
===

So, you should either properly handle that (ROLLBACK and retry transaction from
beginning in thread B), or use BEGIN IMMEDIATE with all transaction that *may*
modify database (at some point) [then BEGIN IMMEDIATE acquires RESERVED lock,
and so thread B will wait for _busy_timeout [or return SQLITE_BUSY] before
transaction start).

Note: above scenario apply to ROLLBACK-type journal, WAL is somewhat different
(but, IIRC, above scenario still *can* happen, with slight alterations).

>> recover to an understood situation after a SQLITE_BUSY, since a _BUSY
>> result will always indicate an unrecoverable error.  Therefore you will
>> never need to figure out whether you should be doing a ROLLBACK.  Just
>> _finalize() the operation (then probably _close() and quit the program) and

IMO, _finalize is somewhat overkill (unless you are planning to return error to
user terminate program right after that).
You only need to _reset() all affected (incomplete) statements.

>> it either worked or it didn't.

P.S.
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Thu, Jan 2, 2014 at 3:25 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> Richard Hipp wrote:
>>> Please verify that the alternative optimization checked-in at
>>> http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that
>>> you identify below.  Tnx.
>> Maybe I overlooked something, but from first look it cannot handle
>> placeholders and constant functions, and my patch does?
> 
> OK.  How about http://www.sqlite.org/src/info/9d05777fe2 - does it work
> better for you?

Yes, thanks :-)

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

Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

[Yuriy Kaminskiy]

Richard Hipp wrote:
> Please verify that the alternative optimization checked-in at
> http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that you
> identify below.  Tnx.

Maybe I overlooked something, but from first look it cannot handle placeholders
and constant functions, and my patch does?

(Besides, with b7e39851a7 "Special case" code remains expensive no-op.)

(Probably your patch handles some *other* cases my patch does not. Probably,
they can be applied both :-))

===

EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;

3.8.2 + My patch series:
sqlite> EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;
0|Trace|0|0|0||00|
1|Goto|0|19|0||00|
2|IfNot|1|17|1||00| <<< ?1 check moved out of loop
3|IfNot|2|17|1||00| <<< ?2 check moved out of loop
4|IfNot|3|17|1||00| <<< ?3 check moved out of loop
5|IfNot|4|17|1||00| <<< ?4 check moved out of loop
6|OpenRead|0|2|0|3|00|
7|Rewind|0|17|0||00|
8|Column|0|0|5||00|
9|IfNot|5|16|1||00| <<< only i
10|Column|0|1|6||00|
11|IfNot|6|16|1||00|<<< and j check in the inner loop
12|Column|0|0|7||00|
13|Column|0|1|8||00|
14|Column|0|2|9||00|
15|ResultRow|7|3|0||00|
16|Next|0|8|0||01|
17|Close|0|0|0||00|
18|Halt|0|0|0||00|
19|Transaction|0|0|0||00|
20|VerifyCookie|0|1|0||00|
21|TableLock|0|2|0|t|00|
22|Variable|1|1|0||00|
23|Variable|2|2|0||00|
24|Variable|3|3|0||00|
25|Variable|4|4|0||00|
26|Goto|0|2|0||00|

3.8.2 + b7e39851a7 (but without any other commits from trunk):

sqlite> EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;
0|Trace|0|0|0||00|
1|Goto|0|19|0||00|
2|OpenRead|0|2|0|3|00|
3|Rewind|0|17|0||00|
4|IfNot|1|16|1||00| <<< ?1 check in the inner loop
5|IfNot|2|16|1||00| <<< ?2 check in the inner loop
6|Column|0|0|3||00|
7|IfNot|3|16|1||00| <<< ?3 check in the inner loop
8|IfNot|4|16|1||00| <<< ?4 check in the inner loop
9|Column|0|1|5||00|
10|IfNot|5|16|1||00|
11|IfNot|6|16|1||00|
12|Column|0|0|7||00|
13|Column|0|1|8||00|
14|Column|0|2|9||00|
15|ResultRow|7|3|0||00|
16|Next|0|4|0||01|
17|Close|0|0|0||00|
18|Halt|0|0|0||00|
19|Transaction|0|0|0||00|
20|VerifyCookie|0|1|0||00|
21|TableLock|0|2|0|t|00|
22|Variable|1|1|0||00|
23|Variable|2|2|0||00|
24|Variable|3|4|0||00|
25|Variable|4|6|0||00|
26|Goto|0|2|0||00|



EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;

My patch:
sqlite> EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;
0|Trace|0|0|0||00|
1|Goto|0|14|0||00|
2|Ne|2|12|1||6a|  <<< `= '01-01'` moved out of loop
3|OpenRead|0|2|0|3|00|
4|Rewind|0|12|0||00|
5|Column|0|0|3||00|
6|IfNot|3|11|1||00|   <<< only [i] checked in the inner loop
7|Column|0|0|4||00|
8|Column|0|1|5||00|
9|Column|0|2|6||00|
10|ResultRow|4|3|0||00|
11|Next|0|5|0||01|
12|Close|0|0|0||00|
13|Halt|0|0|0||00|
14|Transaction|0|0|0||00|
15|VerifyCookie|0|1|0||00|
16|TableLock|0|2|0|t|00|
17|String8|0|9|0|now|00|
18|Function|1|9|7|date(-1)|01|
19|Integer|6|8|0||00|
20|Function|3|7|1|substr(2)|02|
21|String8|0|2|0|01-01|00|
22|Goto|0|2|0||00|

b7e39851a7:

sqlite> EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;
0|Trace|0|0|0||00|
1|Goto|0|14|0||00|
2|OpenRead|0|2|0|3|00|
3|Rewind|0|12|0||00|
4|Ne|2|11|1||6a|<<<< ` = '01-01'` check in the inner loop
5|Column|0|0|3||00|
6|IfNot|3|11|1||00|
7|Column|0|0|4||00|
8|Column|0|1|5||00|
9|Column|0|2|6||00|
10|ResultRow|4|3|0||00|
11|Next|0|4|0||01|
12|Close|0|0|0||00|
13|Halt|0|0|0||00|
14|Transaction|0|0|0||00|
15|VerifyCookie|0|1|0||00|
16|TableLock|0|2|0|t|00|
17|String8|0|9|0|now|00|
18|Function|1|9|7|date(-1)|01|
19|Integer|6|8|0||00|
20|Function|3|7|1|substr(2)|02|
21|String8|0|2|0|01-01|00|
22|Goto|0|2|0||00|

> On Thu, Jan 2, 2014 at 9:25 AM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> On 2013/11/04 Yuriy Kaminskiy wrote:
>>> On 2012/04/08 Yuriy Kaminskiy wrote:
>>>> On 2011/12/06 Yuriy Kaminskiy wrote:
>>>>> On 2011/11/03 Yuriy Kaminskiy wrote:
>>>>>> On 2011/11/23 Yuriy Kaminskiy wrote:
>>>>>>> On 2011/10/23 Yuriy Kaminskiy wrote:
>>>>>>>> When WHERE condition is constant, there are no need to evaluate and
>> check it for
>>>>>>>> each row. It works, but only partially:
>>>>>>> ...
>>>>>>>> [In fact, you can move out out loop not only *whole* constant
>> WHERE, but also
>>>>>>>> all constant AND terms of WHERE, like this:
>>>>>>>> SELECT * FROM t WHERE co

Re: [sqlite] [sqlite-dev] sqlite3 db is locked

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 2 Jan 2014, at 2:57pm, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> Simon Slavin wrote:
>>> sqlite3_busy_timeout()
>> Waiting for timeout *cannot* fix any errors that can trigger failure in
>> sqlite3_close. Those are *program logic* errors.
> 
> I am not trying to fix your program logic errors.  I am telling you how to 
> replace the code in your program which doesn't work (the way you handle 
> SQLITE_BUSY in the code you wrote) with code which does work (SQLite's own 
> internal way of handling a busy database).

SQLite internal way of handling busy database DOES NOT handle case of "there are
unfinalized statement(s) left at the moment of sqlite3_close call" (resulting in
SQLITE_BUSY error).

SQLite won't even TRY to retry sqlite3_close internally, no matter if
busy_timeout was set or not (and this is not a bug; it would be *stupid* waste
of time if it would).

On other hand, OP's code (attempt to enumerate and finalize all statements and
retry close()) *can* help (although, it hides real error [lack of statement
finalization] and can result in SIGSEGV/heap corruption if those
forcibly-finalized statements will be used anyhow elsewhere).

(Besides, it is not "my program", it is OP's program).

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

Re: [sqlite] [sqlite-dev] sqlite3 db is locked

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 1 Jan 2014, at 7:43am, Alexander Syvak  wrote:
> 
>> The code in function from the 1st e-mail is used before exiting, so the
>> sqlite3_close is called in fact.
> 
> Please do not cross-post between sqlite-dev@ and sqlite@.  If you need to 
> move from one to the other, start a new thread.
> 
> I can't find the post that started the thread but if you're having trouble 
> using sqlite3_close() then  the database is probably not being closed.  
> Please check the result returned and log an error if it's not SQLITE_OK so 
> you can be sure whether it worked or not.
> 
> If _close() really isn't working for you it's probably because you have an 
> unfinalized statement.  This can happen if sqlite3_finalize() fails (though 
> it really shouldn't) So check and log the result code from sqlite3_finalize() 
> too.
> 
>>>   while ( (rc = sqlite3_close(db)) == SQLITE_BUSY)
> 
> 
> I'm not happy about this.  SQLITE_BUSY should not be a reason to start 
> running other statements.  It should be a reason to back off and announce 
> failure to the user.  To avoid getting _BUSY and then having to write a 
> handler yourself set a good timeout (ten seconds ?) using
> 
> sqlite3_busy_timeout()

Waiting for timeout *cannot* fix any errors that can trigger failure in
sqlite3_close. Those are *program logic* errors.

> after the database is opened.  From then on you can treat SQLITE_BUSY the 
> same as other failure results.

But you *cannot*.
Sure, there are cases when longer timeout can help. But not in all.
Even with larger timeout, SQLITE_BUSY often requires some or other special
handling (ROLLBACK transaction *and retry* in case of step()/exec(), detect
unfinished statements in case of close(), etc).
(Sometimes you can avoid some special cases by using BEGIN IMMEDIATE,
close_v2(), etc appropriately).

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

Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

[Yuriy Kaminskiy]

Woody Wu wrote:
> Hi, Simon
> 
> I upload the source code onto my dropbox:
> https://www.dropbox.com/s/9shhshi0wn3e717/downloadfile.c  Please have a
> look at it.
> 
> The same test program run without a problem on my pc Linux after complied
> natively.  But I think I should not dout my cross-compiler, which is
> CodeBench ARM eabi compiler. With the same complier and the toolchain, I
> have been buit a whole target ARM system including kernel, 1000 open source
> applications, even including a tiny X window.

Well, compiler bugs are sometimes very rarely triggered, and by completely
innocent code, so I would not exclude this possibility.
FWIW, I don't see anything obviously broken/sigsegv-worthy in above test program
[assuming missing headers contained something like

int sql_exec_v2(sqlite3 *conn, const char *sql) {
// note: sql_exec_v2 expected to return SQLITE_DONE on success
// sqlite3_exec returns SQLITE_OK on success
int sqlerr, ret;
sqlite3_stmt *stmt;
sqlerr = sqlite3_prepare_v2(conn, sql, -1, , NULL);
if (sqlerr != SQLITE_OK) return sqlerr;
ret = sqlite3_step(stmt);
sqlerr = sqlite3_finalize(stmt);
if (sqlerr != SQLITE_OK) return sqlerr;
return ret;
}
int timespec_diff_ms(const struct timespec *ts1, const struct timespec *ts2) {
return (ts2->tv_sec - ts1->tv_sec)*1000 + 
(ts2->tv_nsec-ts1->tv_nsec)/100;
}
#define inst_signal_handler(SIGNAL,HNDL,FOO) signal((SIGNAL),(HNDL))

], but as this is "impossible error" it would make sense to add error checking
for *everything*, including "impossible errors".

> On Tuesday, 31 December 2013, Simon Slavin wrote:
> 
>> On 31 Dec 2013, at 8:41am, Woody Wu >
>> wrote:
>>
>>> Attached is the test program writting in C.
>> Sorry, but attachments don't work here.  If your program is short, please
>> post it as text.  If not, please put it on a web site somewhere.
>>
>>> Anyway, all above errors looks so strange. And, these operations I
>> talking
>>> about are so basic and my real application (another bigger one) really
>>> depends on these.
>> You should not be able to make SQLite corrupt its database that easily.
>>
>>> Pleaes be kindly to check my test program.
>> Can you run your program on the computer you used to send that email
>> message and tell us whether it had the same problem ?

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

Re: [sqlite] Concrete example of corruption

[Yuriy Kaminskiy]

Richard Hipp wrote:
> Please try the changes in the branch at
> http://www.sqlite.org/src/info/8759a8e4d8 and let me know if they
> adequately cover your concerns.

Let's suppose user just did
  cp -b somewhere/else/db opened.db
There *are* still file named opened.db, but it points to *different* file.
Sure, you can also compare stat() and fstat() to check if this is still same
file, ... but then you'll be asked for protection against

  cp opened.db bar
  while true; do
mv opened.db foo
mv bar opened.db
mv opened.db bar
mv foo opened.db
  done

... and there are none. SQLite is responsible for protecting database against
corruption in case of concurrent modification by other SQLite instances. It
cannot protect against concurrent modification by other processes that does not
use SQLite locking protocol. And, IMO, it should not pretend it can.

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

Re: [sqlite] Concrete example of corruption

[Yuriy Kaminskiy]

Warren Young wrote:
> On 12/5/2013 20:31, Stephen Chrzanowski wrote:
[...]
>> File handling is NOT SQLites responsibility
> 
> I'm not sure about that.  SQLite, at least at one time, was billed as a
> competitor for fopen() rather than for Oracle.

But fopen(3) have no locking *at all*. And lower-level locking
(flock/fcntl(F_*LCK)) is only advisory - if "rogue application" decide to ignore
it, locks won't stop it from changing file.

> Maybe all that's needed is a mode where SQLite never creates any
> external files.  Disable the WAL feature, don't create a lock file, etc.

And then there will be question: what if user will open database file in text
editor and corrupt it by saving?
There are no way you can handle such errors.

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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

On 2012/04/08 Yuriy Kaminskiy wrote:
> On 2011/10/23, Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> Yuriy Kaminskiy wrote:
>>>> Yuriy Kaminskiy wrote:
>>>>> When WHERE condition is constant, there are no need to evaluate and check 
>>>>> it for
>>>>> each row. It works, but only partially:
>>>> ...
>>>>> [In fact, you can move out out loop not only *whole* constant WHERE, but 
>>>>> also
>>>>> all constant AND terms of WHERE, like this:
>>>>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>>>>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>>>>> I'll take a shot on that later.]
>>>> Here it goes.
>>>>
>>>> Prerequisite: previous patch.
>>>> Passes quick regression test (make test).
>>>> Possible problem: short-circuits evaluation. Should not be a problem, IMO, 
>>>> as only
>>>> constants references? Please verify.
>>> Ping.
>> Ping.
> Ping.
> For convenience all 3 patches collected below (needed no change for 3.7.11).

Ping. Over 2 years passed since this patch series was first posted.
Updated patch series for 3.8.1 below.
-- 

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>

Part 1: Move whereSplit() to unbreak constant condition elimination.
Test case:
   CREATE TABLE t (i, j, k);
   EXPLAIN SELECT * FROM t WHERE 11

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-10-17 22:57:30.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 22:44:41.0 +0400
@@ -5727,7 +5727,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(>sWC, pWInfo);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(>sWC, pWhere, TK_AND);
   sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */

   /* Special case: a WHERE clause that is constant.  Evaluate the
@@ -5738,6 +5737,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(>sWC, pWhere, TK_AND);
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===

Part 2: optimize "WHERE const AND notconst" too
Test case:
   EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-11-03 23:27:05.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 23:27:59.0 +0400
@@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(>sWC, pWhere, TK_AND);

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int i, j;
+WhereClause * const pWC = >sWC;
+
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+/*if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0]));*/
+  }
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===

Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
There are minor change:
   EXPLAIN SELECT * FROM t WHERE 1 AND 2

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-11-03 22:54:44.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 22:56:18.0 +0400
@@ -5727,20 +5727,13 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(>sWC, pWInfo);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
-
-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(>sWC, pWhere, TK_AND);
+  sqlite3CodeVerifySchema(pParse, -1); /* Insert the 

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[Yuriy Kaminskiy]

Fabian Büttner wrote:
> Hi,
> 
> I have been thinking about a question on stackoverflow
> (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by),
> where some SQL framework removes duplicates from results using GROUP BY
> instead of DISTINCT.
> I don't want to discuss that this might not be a good idea. However, the
> core of that problem is the creation of temp b-trees when using ORDER BY
> ... DESC after GROUP BY.
> I wondered if the construction of a temp b-tree in the third query is
> intentional / by design?

I'd guess just "missing optimization opportunity". I think this fragment of code
is responsible for that optimization: src/select.c, sqlite3Select():

=== cut ===
  /* If there is both a GROUP BY and an ORDER BY clause and they are
  ** identical, then disable the ORDER BY clause since the GROUP BY
  ** will cause elements to come out in the correct order.  This is
  ** an optimization - the correct answer should result regardless.
  ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  ** to disable this optimization for testing purposes.
  */
  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
pOrderBy = 0;
  }
=== cut ===

Adding DESC to pOrderBy "breaks" sqlite3ExprListCompare(,) (note: just changing
sqlite3ExprListCompare to ignore it would be insufficient and will likely result
in *breakage*).

> I am using sqlite 3.8.1.
> 
> sqlite> PRAGMA legacy_file_format=OFF;
> 
> sqlite> create table test1 (x INTEGER);
> sqlite> create index test1_idx on test1(x);
> sqlite> explain query plan select x from test1 group by x order by x;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test1 USING COVERING
> INDEX test1_idx
> 
> create table test2 (x INTEGER);
> sqlite> create index test2_idx on test2(x);
> sqlite> explain query plan select x from test2 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test2 USING COVERING
> INDEX test2_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> create table test3 (x INTEGER);
> sqlite> create index test3_idx on test3(x desc);
> sqlite> explain query plan select x from test3 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> To double check:
> 
> sqlite> explain query plan select x from test3 order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 
> 
> Regards
> Fabian

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

Re: [sqlite] SQLite keeps on searching endlessly

[Yuriy Kaminskiy]

Raheel Gupta wrote:
>> Yes, but they allow the searches to be faster.  You are making it longer
>> to do INSERT but shorter to do SELECT.  Which is best for you depends on
>> your purposes.
>>
> 
> I need the inserts to be faster.
> So which is better ? An Index or a Primary Key ?

Is there any difference between UNIQUE INDEX and PRIMARY KEY *in sqlite*?
[except for special-case INTEGER PRIMARY KEY, which is not applicable here]

> The new INDEX that I created on your suggestion with d,n,s solves the
> problem.
> Now the question is over Primary key whether that will slow it down or not.

Only question is whether you have *other* queries that prefers *old* index.
If not - you can just drop old index and save time on insert.

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

Re: [sqlite] Bug in sqlite3.exe?

[Yuriy Kaminskiy]

Staffan Tylen wrote:
> On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin  wrote:
> 
>> On 24 Sep 2013, at 5:35pm, Staffan Tylen  wrote:
>>
>>> sqlite> .tables
>>> CityCountry Languages
>>> Country Country Official Languages
>>> Country CapitalsCountryLanguage
>> Either don't use spaces in your token names (table names, column names,
>> index names, etc.) or quote them when you use them.  Something like
>>
>> select count(*) from "country official languages";
>>
>> or
>>
>> select count(*) from [country official languages];
>>
>> will probably work.  I avoid all space in token names because they cause
>> problems with other versions of SQL too, and I don't want to get into
>> dangerous habits.

> Well, it's not my database I'm looking at. What puzzles me is that Country
> Languages works but Country Official Languages doesn't, so could there be a
> parsing problem?

No. `Languages` is interpreted as *alias* to table `Country`:

SELECT ... FROM Country Languages
is same as
SELECT ... FROM [Country] AS [Languages]

And
SELECT ... FROM Country Official
is same as
SELECT ... FROM [Country] AS [Official]

And that's why `SELECT ... FROM Country Languages` return exactly same result as
`SELECT FROM Country Official`; if you would've issued just `SELECT ... FROM
Country`, it would've returned same result as well.

> I agree, the names should be quoted ...
>
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] INSERT INTO ???

[Yuriy Kaminskiy]

Bernhard Amann wrote:
>> INSERT INTO  SELECT * FROM ;
>>
>> However, this only works if  already exists, which is actually 
>> quite cumbersome..
>> Is there a way to make the new table 'on the fly"?
> 
> create table newtable as select * from oldtable;

... however, this won't keep constraints, indexes, collation, comments, and will
replace types with "generic" INT/TEXT/NUM/"":

sqlite> CREATE TABLE t (a integer primary key /*foo*/, b text collate nocase
unique, c datetime check (c<>''), d blob);
sqlite> CREATE TABLE u AS SELECT * FROM t;
sqlite> .schema
CREATE TABLE t (a integer primary key /*foo*/, b text collate nocase unique, c
datetime check (c<>''), d blob);
CREATE TABLE u(a INT,b TEXT,c NUM,d);

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> In C there are local variables, where you can save result of impure
>> functions when it is important. There are no local variables in SQL 
>> - with even more extreme example shown in E.Pasma message nearby - 
>> `SELECT strftime('%f') AS q FROM t WHERE q <> q`; 
>> oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q <> q` 
>> trigger that bug too, I've just checked (and it took less than 2
>> seconds to trigger).
> 
> That is version specific.  What version of SQLite are you using?  The current 
> version optimizes out the clause q <> q thusly:

No, it is NOT version specific.

strftime vs. CURRENT_TIME triggers racing differently (strftime trigger in inner
loop [and return *random* rows, easily triggered in shell with big enough
table], CURRENT_TIME trigger in outer loop [and *randomly* return all rows; not
easy to trigger in shell, but still it is triggered by my perl script without
much problem]), but still triggers it.

(By the way, better optimizer should've moved strftime out of inner loop as
well; and even better optimizer should've eliminated all repeated calls for pure
functions with constant arguments; but that's just "missing optimization
opportunity/missing feature", not a "real bug"; and even if that optimization
was implemented, it still would not have fixed racing between DATE('now',$foo)
and DATE('now',$bar), as in OP's query).

> sqlite> .explain
> sqlite> explain select value, current_time as q from x where q <> q and value 
> < 10;
> SELECT item[0] = {0:0}
>item[1] = FUNCTION:current_time() AS q
> FROM {0,*} = x
> WHERE AND(REGISTER(1),LT({0:0},REGISTER(4)))
> END
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000
> 1 Function   0 0 2 current_time(0)  00
> 2 Function   0 0 3 current_time(0)  00

...And as you see, current_time function evaluated *many* times (and there are
no caching *inside* of current_time function, in *any* sqlite version).

> 3 Ne 3 1 272
> 4 Integer10  4 000
> 5 Goto   0 18000
> 6 VOpen  0 0 0 vtab:6E06F0:50B800  00
> 7 Copy   4 7 000
> 8 Integer4 5 000
> 9 Integer1 6 000
> 10VFilter0 16500
> 11IfNot  1 15100
> 12VColumn0 0 800
> 13Function   0 0 9 current_time(0)  00
> 14ResultRow  8 2 000
> 15VNext  0 11000
> 16Close  0 0 000
> 17Halt   0 0 000
> 18Transaction0 0 000
> 19VerifyCookie   0 1 000
> 20Goto   0 6 000
> 
> So you will either get all rows or none.

And? It should ALWAYS return NONE. It does not.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Stephan Beal wrote:
> On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> Sure, there can be several way to interpret CURRENT_* and *('now').
>> However,
>> some of them can be useful (transaction, statement), and others (step) -
>> cannot
>> be. And some (sub-expression, the way it "works" currently) are purely
>> insane.
>>
> 
> i've been following this list since 2006 or 2007 and i can't remember

Oh, yes, yes, "I was on debian [...] list since [...] and can't remember anyone
complaining about broken RNG for two years". So what?

And, by the way, I already complained about this behavior on this list in the
beginning of 2012 year.

> anyone every complaining about the current behaviour before. If the
> behaviour bothers you, use a user-defined function which provides the

It does not bother *me* - I can happily live with knowledge that SQLite
CURRENT_*/*('now') is broken by design and should not be used ever. It should
bother people that use sqlite for something serious.

> per-db/transaction/whatever behaviour your need. You could have implemented

I have not found any way to associate user-defined data with transaction in
sqlite API.

> it in the time you've expended bemoaning the current (well established, if
> perhaps fundamentally flawed) behaviour.
> 
> 
> where `2*2 <> 4`; using them them in product targeted to to general public
>> would
>> be insane; using them *randomly* (as it happens with CURRENT_TIME <>
>> CURRENT_TIME) - beyond insane.
>>
> 
> foo() == foo()
> 
> is never guaranteed to be true unless foo() is a pure function with no
> inputs. i consider the current behaviour to be correct. It would never

And functions in SQL are *expected* to be pure.

> occur to me to compare CURRENT_TIME to itself because it is, deep down

Comparing with itself just a method to *easily demonstrate* this bug.

In real-world, this bug affect any query where CURRENT_TIME used more than once.
E.g. OP's query - `...WHERE day BETWEEN DATE('now',...) AND DATE('now',...)`.
This *real-world* query is affected. Even through it does not *directly* compare
CURRENT_TIME with CURRENT_TIME.

> inside, a C function call which uses time-dependent, system-level state.
> i.e. it's a perfect candidate for races. So avoid them, in the same way
> that nobody should ever (in C) expect (time(0) == time(0)) to match 100% of
> the ... time.

In C there are local variables, where you can save result of impure functions
when it is important. There are no local variables in SQL - with even more
extreme example shown in E.Pasma message nearby - `SELECT strftime('%f') AS q
FROM t WHERE q <> q`; oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q
<> q` trigger that bug too, I've just checked (and it took less than 2 seconds
to trigger).

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> ... and I'd call even that difference between CURRENT_* and *('now') rather
>> "query optimizer artifact" rather than "documented feature one can rely 
>> upon".
>> Anyway, one way or other, it is BROKEN.
> 
> I would agree with you if the labels were different.  But 'CURRENT_TIME' 
> means 'current'.  If those keywords were STATEMENT_TIME, STATEMENT_DATE and 
> STATEMENT_TIMESTAMP then that behaviour would definitely be wrong.

Wow. Are you *REALLY* arguing that
  SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME;
that randomly (!) returning rows any less broken than
  SELECT * FROM t WHERE 2*2 <> 4;
also randomly returning rows?

Sure, there can be several way to interpret CURRENT_* and *('now'). However,
some of them can be useful (transaction, statement), and others (step) - cannot
be. And some (sub-expression, the way it "works" currently) are purely insane.

IMO, 'now' evaluation *MUST* be consistent within statement (think about query
from OP - `... WHERE day BETWEEN date('now','start of month') AND date('now',
'start of month','+1 month')` - unless you have statement-level consistency, it
*randomly* will cover *one* or *two* months).

And there are good arguments that they *should* be consistent within transaction
(point of transaction is that you have *consistent* view of database; evaluating
CURRENT_*/'now' more than once within transaction, obviously, provide
*inconsistent* view).

And while we are talking about "procedural elements", obviously, volatile global
constants and ​impure functions are *more* from "procedural world" than from
"SQL world" (constants are expected to be constant, function from constant
arguments are expected to return same value each time, query optimizer should be
free to reorder and eliminate function calls, flatten queries, reorder joins,
etc; you cannot do that with "volatile constants" or impure functions).

PS sure, if you ask in uni's math dept, you'll find some interpretation of math
where `2*2 <> 4`; using them them in product targeted to to general public would
be insane; using them *randomly* (as it happens with CURRENT_TIME <>
CURRENT_TIME) - beyond insane.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Keith Medcalf wrote:
>>>>> You can easily reproduce this problem if you switch unit from month to
>>>>> millisecond, e.g.
>>>>> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
>>>>> will non-deterministically return rows.
>>>>> IMO, correct [= least surprise] behavior should be "timestamp used for
>>>>> 'now' should cached on first row step, and reused in all following calls
>>>>> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy 
>>>>> :-|.
>>>> That would require a change to SQLite itself.  Without doing that I
>>> believe this will solve those problems by using only one 'now', and also
>>> only running each date() call once (even my previous solutions could
>>> suffer from race conditions since they retrieved 'now' multiple times):
>>>> sqlite> explain select *
>>>>...>   from entry,
>>>>...>(select bdate, (select date(bdate, '+1 month'))
>>> as edate
>>>>...>   From (select date('now', 'localtime', 'start
>>> of month') as bdate) as ttemp limit 1) as tstamp
>>>>...>  where bankdate >= bdate
>>>>...>and bankdate < edate;
>>> Unfortunately, it is not only extremely inconvenient to rewrite queries
>>> this
>>> way, but also this workaround relies on current implementation of query
>>> optimizer. Next version may decide to e.g. flatten query, and break
>>> assumption
>>> that inner date() will be called only once.
>>>
>>>> It is a bit complicated though.  It would be much simpler to do the
>>> date calculation in the host language and pass the boundary values as
>>> parameters to the query.
>>>
>>> In other words, "The way it is implemented currently, sqlite DATE()
>>> function
>>> considered harmful and should not be used." Huh.
>> Not at all.  They may be quite useful for formatting output.  That the 
>> function date('now') returns the date now ought to be expected.  If you 
>> wanted the date at the start of the transaction, one ought to reasonably 
>> expect to call date('start of transaction').
> 
> That `SELECT date('now'), date('now')` can randomly return mismatching results
> is most certainly NOT what anyone would expect.
> 
>> CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue 
>> if you wish to eliminate the repetitive calls to the date function.  If that 
>> doesn't bother you then you ought to be able to replace 'now' with 
>> current_timestamp.
> 
> It *should be* static timestamp. But it is NOT in sqlite.
> 
>>> And same problem: some next version of query optimizer may decide to
>>> change the way this query will be executed.
>> Perhaps, and that is why you have to clearly declare what you want in your 
>> select statement.
> 
> Unfortunately, there are no way to do this in sqlite.
> 
>>> PS from postgresql documentation:
>>> === cut
>>> http://www.postgresql.org/docs/9.3/static/functions-
>>> datetime.html#FUNCTIONS-DATETIME-CURRENT
>>> ===
>>> Since these functions return the start time of the current transaction,
>>> their
>>> values do not change during the transaction. This is considered a
>>> feature: the
>>> intent is to allow a single transaction to have a consistent notion of
>>> the
>>> "current" time, so that multiple modifications within the same
>>> transaction bear
>>> the same time stamp.
>>> === cut ===
>>> *That's* way to go.
>> Well, that is one way to go.  SQLite goes that way too.
>> Instead of asking for datetime('now') which returns the current 'nowness' 
>> value, you can use CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a 
>> static value probably representing the first time is was called in the 
>> transaction (or perhaps statement).
> 
> Unfortunately, sqlite does NOT go this way. Repeated calls to CURRENT_DATE/​
> CURRENT_TIME within same statement still can return *different* results on
> date/time boundary. Race window is smaller, but still present. Enjoy:
> 
> #!/usr/bin/perl
> use DBI;
> #DBI -> trace( 1 );
> my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/t.db3","","" );
> $dbh->do("CREATE TABLE IF NOT EXISTS t (i INTEGER)");
> $dbh->do( "begin immediate" );
> unless(($dbh->selectrow_a

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
 You can easily reproduce this problem if you switch unit from month to
 millisecond, e.g.
 SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
 will non-deterministically return rows.
> 
 IMO, correct [= least surprise] behavior should be "timestamp used for
 'now' should cached on first row step, and reused in all following calls
 [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
> 
>>> That would require a change to SQLite itself.  Without doing that I
>> believe this will solve those problems by using only one 'now', and also
>> only running each date() call once (even my previous solutions could
>> suffer from race conditions since they retrieved 'now' multiple times):
>>> sqlite> explain select *
>>>...>   from entry,
>>>...>(select bdate, (select date(bdate, '+1 month'))
>> as edate
>>>...>   From (select date('now', 'localtime', 'start
>> of month') as bdate) as ttemp limit 1) as tstamp
>>>...>  where bankdate >= bdate
>>>...>and bankdate < edate;
>> Unfortunately, it is not only extremely inconvenient to rewrite queries
>> this
>> way, but also this workaround relies on current implementation of query
>> optimizer. Next version may decide to e.g. flatten query, and break
>> assumption
>> that inner date() will be called only once.
>>
>>> It is a bit complicated though.  It would be much simpler to do the
>> date calculation in the host language and pass the boundary values as
>> parameters to the query.
>>
>> In other words, "The way it is implemented currently, sqlite DATE()
>> function
>> considered harmful and should not be used." Huh.
> 
> Not at all.  They may be quite useful for formatting output.  That the 
> function date('now') returns the date now ought to be expected.  If you 
> wanted the date at the start of the transaction, one ought to reasonably 
> expect to call date('start of transaction').

That `SELECT date('now'), date('now')` can randomly return mismatching results
is most certainly NOT what anyone would expect.

> CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue if 
> you wish to eliminate the repetitive calls to the date function.  If that 
> doesn't bother you then you ought to be able to replace 'now' with 
> current_timestamp.

It *should be* static timestamp. But it is NOT in sqlite.

>> And same problem: some next version of query optimizer may decide to
>> change the way this query will be executed.
> 
> Perhaps, and that is why you have to clearly declare what you want in your 
> select statement.

Unfortunately, there are no way to do this in sqlite.

>> PS from postgresql documentation:
>> === cut
>> http://www.postgresql.org/docs/9.3/static/functions-
>> datetime.html#FUNCTIONS-DATETIME-CURRENT
>> ===
>> Since these functions return the start time of the current transaction,
>> their
>> values do not change during the transaction. This is considered a
>> feature: the
>> intent is to allow a single transaction to have a consistent notion of
>> the
>> "current" time, so that multiple modifications within the same
>> transaction bear
>> the same time stamp.
>> === cut ===
>> *That's* way to go.
> 
> Well, that is one way to go.  SQLite goes that way too.
> Instead of asking for datetime('now') which returns the current 'nowness' 
> value, you can use CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a 
> static value probably representing the first time is was called in the 
> transaction (or perhaps statement).

Unfortunately, sqlite does NOT go this way. Repeated calls to CURRENT_DATE/​
CURRENT_TIME within same statement still can return *different* results on
date/time boundary. Race window is smaller, but still present. Enjoy:

#!/usr/bin/perl
use DBI;
#DBI -> trace( 1 );
my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/t.db3","","" );
$dbh->do("CREATE TABLE IF NOT EXISTS t (i INTEGER)");
$dbh->do( "begin immediate" );
unless(($dbh->selectrow_array("SELECT COUNT(*) FROM t"))) {
$dbh->do("INSERT INTO t VALUES (0)");
$dbh->do("INSERT INTO t VALUES (1)");
$dbh->do("INSERT INTO t VALUES (2)");
$dbh->do("INSERT INTO t VALUES (3)");
$dbh->do("INSERT INTO t VALUES (4)");
}
$dbh->do("commit");
my $sth = $dbh->prepare( << '__E__' );
SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME
__E__
### SELECT * FROM t WHERE strftime('%f') <> strftime('%f')
$dbh-> do( "begin" );
my $i = 0;
while(1) {
$sth -> execute;
if (my $row = $sth->fetch) {
print "Got bug: $sth->{Statement} returned @$row\n";
last;
}
$i++;
}
print "$i iterations\n";
$sth->finish;
$dbh->do("commit");
__END__

(fwiw, only difference with strftime('%f') variant: when it hit bug,
strftime('%f') variant will randomly return one row, but CURRENT_TIME variant
randomly returns *all* rows).

___
sqlite-users mailing list

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said:
>> Keith Medcalf wrote:
>>>> On Thu, 12 Sep 2013 14:01:04 +0100
>>>> Simon Davies <simon.james.dav...@gmail.com> wrote:
>>>>
>>>>> Why not
>>>>> SELECT * FROM "entry" WHERE
>>>>>bankdate >= date('now','start of month')
>>>>>   AND bankdate < date('now','start of month','+1 month')
>>>> The half-open interval strikes again!  :-)
>>> And you are using UTC ...
>>>
>>> Notwithstanding the timezone you want to use,
>>>
>>> explain select *
>>>   from entry
>>>  where bankdate >= date('now', 'start of month')
>>>and bankdate < date('now', 'start of month', '-1 day');
>>>
>>> will generate the following code:
>> And there are another pitfall in this query: date('now') is *RACY*. That
>> is, if you execute this query *exactly* at midnight at month boundary, first
>> and second DATE() invocation may refer to *different* months.
>  
>> And if your query return several rows, and there will be month boundary
>> between sqlite3_step(), your query also can return data from two month.
>  
>> You can easily reproduce this problem if you switch unit from month to
>> millisecond, e.g.
>> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
>> will non-deterministically return rows.
>  
>> IMO, correct [= least surprise] behavior should be "timestamp used for
>> 'now' should cached on first row step, and reused in all following calls
>> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
> 
> That would require a change to SQLite itself.  Without doing that I believe 
> this will solve those problems by using only one 'now', and also only running 
> each date() call once (even my previous solutions could suffer from race 
> conditions since they retrieved 'now' multiple times):
> 
> sqlite> explain select *
>...>   from entry,
>...>(select bdate, (select date(bdate, '+1 month')) as 
> edate
>...>   From (select date('now', 'localtime', 'start of 
> month') as bdate) as ttemp limit 1) as tstamp
>...>  where bankdate >= bdate
>...>and bankdate < edate;

Unfortunately, it is not only extremely inconvenient to rewrite queries this
way, but also this workaround relies on current implementation of query
optimizer. Next version may decide to e.g. flatten query, and break assumption
that inner date() will be called only once.

> It is a bit complicated though.  It would be much simpler to do the date 
> calculation in the host language and pass the boundary values as parameters 
> to the query.

In other words, "The way it is implemented currently, sqlite DATE() function
considered harmful and should not be used." Huh.

> Of course, you could also do the following:
> 
> Select * from entry where bankdate like (select substr(date('now', 
> 'localtime'), 7) + '%');
> 
> But of course this will always do a table/index scan that cannot be optimized 
> away ...

And same problem: some next version of query optimizer may decide to change the
way this query will be executed.

PS from postgresql documentation:
=== cut
http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
===
Since these functions return the start time of the current transaction, their
values do not change during the transaction. This is considered a feature: the
intent is to allow a single transaction to have a consistent notion of the
"current" time, so that multiple modifications within the same transaction bear
the same time stamp.
=== cut ===
*That's* way to go.

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

[sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> On Thu, 12 Sep 2013 14:01:04 +0100
>> Simon Davies  wrote:
>>
>>> Why not
>>> SELECT * FROM "entry" WHERE
>>>bankdate >= date('now','start of month')
>>>   AND bankdate < date('now','start of month','+1 month')
>> The half-open interval strikes again!  :-)
> 
> And you are using UTC ...
> 
> Notwithstanding the timezone you want to use, 
> 
> explain select * 
>   from entry 
>  where bankdate >= date('now', 'start of month') 
>and bankdate < date('now', 'start of month', '-1 day');
> 
> will generate the following code:

And there are another pitfall in this query: date('now') is *RACY*. That is, if
you execute this query *exactly* at midnight at month boundary, first and second
DATE() invocation may refer to *different* months.

And if your query return several rows, and there will be month boundary between
sqlite3_step(), your query also can return data from two month.

You can easily reproduce this problem if you switch unit from month to
millisecond, e.g.
SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
will non-deterministically return rows.

IMO, correct [= least surprise] behavior should be "timestamp used for 'now'
should cached on first row step, and reused in all following calls [until
SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.

[...]

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

Re: [sqlite] to encrypt sqlite db

[Yuriy Kaminskiy]

Etienne wrote:
> - Original message -
> From: Paolo Bolzoni 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] to encrypt sqlite db
> Date: Sun, 1 Sep 2013 18:24:13 +0200
>> On Sun, Sep 1, 2013 at 6:10 PM, Etienne  wrote:
 On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote:
> Ulrich Telle wrote:
>> Am 31.08.2013 22:01, schrieb Etienne:
>>> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>>> salts:
> Well, that's not completely true. The encryption extension coming with
> wxSQLite3 uses a different IV (initial vector) for each database page.
> True is that the IVs are not random, but deduced from the page number.
> However, I don't see much difference between generating an IV
> algorithmic or using a random nonce which is stored at the end of each
> database page
  says:
 | Randomization is crucial for encryption schemes to achieve semantic
 | security, a property whereby repeated usage of the scheme under the
 | same key does not allow an attacker to infer relationships between
 | segments of the encrypted message.

 Without a random IV/nonce, every page is guaranteed to encrypt to the
 same data if the contents and the key have not changed.  Thus, wxSQLite3
 gives an attacker the ability to determine whether any particular page
 has changed, by comparing the old and new versions.  With SEE, rewriting
 a page will encrypt to a different value because the IV changes even for
 otherwise unchanged pages.
> The weak point of probably all SQLite encryption methods is that the
> unencrypted content of the first 16 bytes of a SQLite database file is
> well known.
 Many file formats have fixed parts.  However, this is not a problem with
 properly implemented encryption algorithms.

>>>
>>> In this particular case, you get what you pay for.
>>>
>>
>> Another weird sentence in the mailing list
>> of probably most used DB that is really free.
>
> wxSQLite (relevant part) and SEE are extensions to SQLite.
>
> wxSQLite is free, while SEE is definitively not.
>
> wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is real
encryption.
>
> What is weird???

Weird is to talk about something one have little clue about? **

Have you looked at wxSQLite sources? Obviously, no. Otherwise you'd know there
are more in story than just IV generation - they also alters key per-page
(*edit* and, of course, wxSQLite don't use ECB!).

While wxSQLite encryption have some, erm, strangely/unusually designed parts,
that "strangeness" is about on par with (different) SEE's "strangeness".
I don't see reason to claim SEE offers any more "real" encryption than wxSQLite
(in AES256/SHA256 mode).

Compare both of them with "truly real encryption" schemes, e.g. used by LUKS or
truecrypt; you'll notice several differences at once (e.g. lack of key
strengthening in SEE [apparently, they use password as raw encryption key -
there are no other way to explain "... only the first 256 bytes (RC4) or 16
bytes (AES) will be used..."] - important when you target your product for
crypto-illiterate users; plus, while "silent key truncation" is documented and
can be claimed "not a bug", it can lead to nasty surprises in hands of said
crypto-illiterate users; lack of password salting in both; no way to change
password without re-encrypting whole database in both; very unusual/unsuitable
for FDE encryption modes in SEE; unusual per-page keys and IV generation scheme
in wxSQLite [it is not broken in obvious way, but "crypto" and "unusual"
together sounds troubling]; unbalanced AES128/MD5 algo pair in wxSQLite; so
called "random" nonce in SEE - how was that "random" generated and seeded?***;
and, at last, their use of their own crypto implementations instead of optimized
libraries [compare their speed with e.g. `openssl speed aes-128-cbc`, especially
on CPUs with AES-NI or SSSE3 available]; and maybe more).

*** Obtaining *true* randomness in naturally deterministic computers is not
easy, especially on virtualized and embedded platforms; and crypto-quality PRNG
is even more complex problem;
Debian's openssl, sony ps3 or recent androids disasters comes to mind;
FWIW, from the first look, sqlite's random.c/os_unix.c/os_win.c PRNG seems
unqualified for use as "crypto RNG" (it's [always] badly seeded on windows; it
can be [silently!] *very* badly seeded on some semi-broken configurations on
unix; and, finally, RC4 has rather dubious reputation in crypto circles - it is
very fast and trivial to implement and "can work well in right hands", but at
the same time extremely quirky and easy to misuse in unsafe way; and the way it
is used in sqlite PRNG induce some doubts); nothing terrible wrong in that (I
don't remember it was ever claimed to be 

Re: [sqlite] to encrypt sqlite db

[Yuriy Kaminskiy]

Ulrich Telle wrote:
> Am 31.08.2013 22:01, schrieb Etienne:
   On Sat, 31 Aug 2013 17:17:23 +0200
   Etienne 
 wrote:

   > > On the other hand removing patterns definitely cannot hurt.
   >
   > Precisely.
   >
   > The very first bytes of SQLite files are, AFAIK, well known.

   That's what salt is for, no?

>>> "nonce", "IV", "salt" - call it whatever you want.  Yes.
>>
>> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>> salts:
> 
> Well, that's not completely true. The encryption extension coming with
> wxSQLite3 uses a different IV (initial vector) for each database page.
> True is that the IVs are not random, but deduced from the page number.
> However, I don't see much difference between generating an IV
> algorithmic or using a random nonce which is stored at the end of each
> database page as SEE does according to the documentation to be found
> here: http://www.sqlite.org/see/doc/trunk/www/readme.wiki

Funny, for all encryption modes, used in SEE (rc4, AES-OFB, AES-CCM), *any* IV
reuse ends up in bad [OFB] or even catastrophic [rc4, CCM] way (you must *never*
encrypt any two blocks with same IV with those modes).

But that's just result of inappropriate choice of those encryption modes. There
are encryption modes with *different* properties. Normally disk encryption use
CBC+ESSIV, XTS or LRW, which ain't sensitive to IV reuse (unmangled/plain sector
number as IV works just fine with them).

> In both cases you know the IV - at least if you have access to the code
> generating it (which is the case for wxSQLite3 as it is open source, but
> not for SEE as it is closed source).
> 
> The weak point of probably all SQLite encryption methods is that the
> unencrypted content of the first 16 bytes of a SQLite database file is
> well known. To get a better encryption maybe the first 16 bytes should
> always be kept unencrypted to not give a clue to a known pattern.
> 
>> 2 SQLite DBs built exactly the same way at different times are
>> byte-to-byte identical.
> 
> That's true. However, in real life it probably matters only for
> read-only databases. As soon as different people start to work with such
> an encrypted database, the database files will certainly deviate from
> each other considerably as it is very unlikely that all users perform
> their actions in exactly the same order.
> 
> BTW, you only get identical database files if you use the same
> passphrase. If the passphrase is hardcoded into the software then
> certainly this may impose a problem. However, for an application with
> high security demands you certainly shouldn't hardcode the passphrase
> into the software.

FYI: two LUKS or truecrypt containers with same passphrase *won't* be same (and
they *don't* need to save per-sector nonce/IV separately, unlike SEE).

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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Yuriy Kaminskiy]

Gary Weaver wrote:
> On Aug 15, 2013, at 3:47 PM, ibrahim  wrote:
> 
>> On 15.08.2013 21:39, Gary Weaver wrote:
>>> SQLite varies between file is encrypted/not a DB errors and database disk 
>>> image is malformed. It would seem consistent with SQLite not handling 
>>> concurrent processing in this particular environment or with the version of 
>>> SQLite since 3.7.7 is fine in OS X.
>>>
>>> Is there anything that stands out as something that would keep 30 processes 
>>> from being able to concurrently insert into the same tables?
>> 30 processes being able to concurrently insert into the same tables ???
> 
> Thanks to you and Richard for the links and info. Concurrency is not 
> outlandish to expect, especially when it works in OS X and just not in the 
> Ubuntu vm in Travis, but I now understand that these are expected problems.

There are *two* problems above, one is "expected", and one is not.

1) SQLite won't execute any database modification concurrently, they will be
serialized internally (or return SQLITE_BUSY error, depending on timeouts). That
is, executing requests concurrently won't be any faster than executing them
consequently from single connection. And those locks are not fine-grained - they
are database-wide, not table or page-wide. And this one is "expected problem".

2) But database corruption is certainly an UNEXPECTED problem: no matter how
many processes are trying to access same database, this is not expected to
corrupt database. If database was corrupted as result of concurrent access,
likely reasons are: FS bug, especially with file locking (e.g. many networking
FS [nfs, cifs, etc] are known to be buggy/broken with locking; and anything
FUSE-based is likely also problematic), other kernel/drivers bugs,
malfunctioning hardware. Of course, "SQLite bug" is also possible, but very
unlikely (sqlite is extensively tested in various scenarios, including
concurrent access).
If you use any "unusual" filesystem, that would be the prime suspect. E.g. if
your VM allows transparent access to part of host fs, and you can open
connections to same database on host fs from several guests, that's recipe for
disaster.

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

Re: [sqlite] secure_delete versus journal_mode persist.

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 29 Jan 2013, at 8:19am, Scott Hess  wrote:
> 
>> insert into x values ('SQLite is a software library that implements
>> a self-contained, serverless, zero-configuration, transactional SQL
>> database engine. SQLite is the most widely deployed SQL database engine in
>> the world. The source code for SQLite is in the public domain.');
>> -- Repeat that four or five times.
>>
>> Now, somewhere else, running:
>>  strings trial.db-journal
>> gives me various repetitions of the inserted string present in the journal
>> file.  This applies even after running 'delete from x;', at which point the
>> main database itself will not contain these strings.
> 
> Verified here with version 3.7.12, native with OS X 10.8 installation.
> 
> Can't be helped at all up to closing the connection to the database.
> 
> Once the database connection is closed, still being able to see that text is 
> a consequence of 'journal_mode = persist' or WAL.  I think that this also 
> can't be helped -- it's inherent in the journal file not being wiped -- but 
> this is more debatable than the state before the connection is closed.
> 
> The only way to prevent the data being available from a disk-read with 
> sufficient privileges would be to use "journal_mode = MEMORY".  Might be 
> worth adding a comment to the documentation for the secure_delete PRAGMA.

IMO, both secure_delete and any user-space disk encryption is pointless and
wasteful security theatre.

What you can do with traces in *filesystem* journal (e.g. data=journal)? What
about defragmentation/volume resize/other block relocation?

If you use SSD, what you can do with block relocation always used in normal
work? (If you use HDD - that can also happen; much more rarely, and usually just
before drive dies, but still possibility).

If you want to defend against other users, setting proper permissions is
sufficient; to improve protection - run all potentially vulnerable services on
dedicated accounts, use jail/chroot/containers/VM/etc.

If you want real encryption, you use full-disk encryption (like TrueCrypt, LUKS,
and their ilk), and encrypt everything at once including any temporary files,
swap, hibernation images, etc.

If you want defend against other processes running under same user or against
root, you lost anyway. They can read anything they need right from memory (e.g.
ptrace()), and completely hijack control over your process (keylogger/etc).

User-space database encryption and all "safe data erase" tools only provide you
with warm feeling of safety without any real security.

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

Re: [sqlite] Problem with sqlite3prepare16_v2

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 1/6/2013 7:10 PM, Walter wrote:
>>sqlite3_prepare16_v2 (vMdb, ws.c_str (), ws.size (), , );
> 
> The third parameter of sqlite3_prepare16_v2 is the length of the string
> *in bytes*, not in characters. You are effectively passing only half the
> statement.

Besides, *second* parameters of sqlite3_prepare16_v2 should point to *utf-16*
string, but ws.c_str() returns pointer to zero-terminated *wchar_t* string. And
wchar_t is *platform-dependent* type (and on lot of platforms it is *32-bit*
integer type [usually in ucs-32 encoding, but, again, this may vary]).

If you want to use sqlite3*16* api, you must convert strings to native-endian
utf-16 encoding (with iconv or something) first.

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

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

[Yuriy Kaminskiy]

Jay A. Kreibich wrote:
> On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall:
>> Hi,
>>
>> When I use INSERT OR IGNORE, if insertion fail (record exists),
>> then sqlite3_last_insert_rowid does return nothing. Is exists similar
>> solution which:
>> 1. If insert success then return new rowid
>> 2. If insert fail (record exists) then return rowid of existing record
>>
>> Can I get this information in one command?
> 
>   No.  Mainly because your assumption that one and only one row is
>   responsible for triggering the IGNORE conflict resolution is
>   incorrect.  For example, if a table has two or more UNIQUE indexes,

How sqlite handles case when e.g. (SELECT) expression returned more than one
row, and only one value is expected? (e.g. foo >= (SELECT))
It uses first row and silently ignores remaining ones.
Same applicable in this case: return any random rowid that results in insert
failure. And ignore the rest.
If sqlite decided to be sloppy, it can be at least /consistently/ sloppy.

>   the IGNORE resolution may be triggered by different rows through each
>   index.  

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

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

[Yuriy Kaminskiy]

Clemens Ladisch wrote:
> Krzysztof wrote:
>> When I use INSERT OR IGNORE, if insertion fail (record exists),
>> then sqlite3_last_insert_rowid does return nothing.
> 
> If your unique key is the rowid, then you already know the ID that
> you tried to insert.
> If your unique key is not the rowid, then why do you need the rowid?

To insert it into another table?

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

Re: [sqlite] What is wrong with this queries?

[Yuriy Kaminskiy]

Igor Korot wrote:
> Hi, ALL,
> 
> sqlite> CREATE TABLE leagueplayers(id integer, playerid integer, value 
> integer,
> currvalue double, foreign key(id) references leagues(id), foreign 
> key(playerid)
> references players(playerid));
> sqlite> INSERT INTO leagueplayers VALUES(1,(SELECT playerid,value,currvalue 
> FROM
>  players));
> Error: table leagueplayers has 4 columns but 2 values were supplied
> 
> AFAICT, I am trying to insert 4 values in the table.

No, you
1) insert *two* values: 1 and (SELECT) expression;
2) improperly use (SELECT) expression:
There are no array type in sqlite. Only single column results is allowed for
(SELECT) expression, e.g.:
sqlite> SELECT (SELECT 1,2);
Error: only a single result allowed for a SELECT that is part of an expression

Just only first discovered error can be returned (and in this case it was
inconsistent number of columns for insert).

> Does anybody have an idea what is wrong?

Correct:

INSERT INTO leagueplayers (id, playerid, value, currvalue)
 SELECT 1, playerid,value,currvalue FROM players;

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

Re: [sqlite] sqlite3 db open/close

[Yuriy Kaminskiy]

Durga D wrote:
>What happens if sqlite3_close() called multiple times but
> sqlite3_open_v2() called only once.
> 
> Practically I dint see any malfunction/corruption here. I would like to
> know the behavior of sqlite in this scenario.

About same as

  char *foo = malloc(10);
  free(foo);
  free(foo);

or

   FILE *f = fopen("file", "r");
   fclose(f);
   fclose(f);

Sometimes it does nothing, but display error message (e.g. with some "debug
malloc" modes/libraries). Sometimes second sqlite3_close/free/fclose kills your
program on assertion or sigsegv. Sometimes it corrupts heap internal structures,
and your program may die *at random time* after that.

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

Re: [sqlite] DatabaseError: database disk image is malformed

[Yuriy Kaminskiy]

Larry Knibb wrote:
> On 15 October 2012 12:32, Keith Medcalf  wrote:
>> Define "clients".  Do you mean multiple client processes running on a single 
>> computer against a database hosted on an attached local device, such as on a 
>> Terminal Server for example?  Or do you mean multiple clients connecting 
>> over LANMAN/CIFS/NFS to a database file sitting on a remote fileserver?
> 
> It's the second one. The database file is being accessed over the network.
> 
>> If file locking is working correctly, you cannot have multiple writes to a 
>> database.  Write operations to the database are exclusive.
> 
> As Simon correctly guessed, the database isn't actually being
> corrupted; I'm just getting an error that suggests that it is. So I

Maybe it is not really corrupted now, but if
1) locking is not working properly (when database file is on networked fs), and
2) two writers will modify db at same time,
you *will* corrupt your database.

> suspect the writes are not conflicting, but one is failing (possibly)
> due to another happening at the same time and whatever locking/waiting
> not being observed properly results in this 'malformed' error rather
> than a blocking error.
> 
>> You may wish to try forcing locks to be acquired sooner when updating the 
>> database by using BEGIN IMMEDIATE or perhaps even BEGIN EXCLUSIVE before 
>> updating the database, and COMMIT when you are done.  You will then also 
>> need a busy-timeout so that other readers/writers will wait for that 
>> operation to complete.  You can set the timeout when creating the connection 
>> with sqlite3 in python, or by using PRAGMA busy_timeout
> 
> Thanks - I'll give that a shot.
> 
>> Latency of locking operations over network connections can play havoc, 
>> however.  It is possible if you are using a network mounted database file 
>> that the write operations are not being flushed properly and or the locks 
>> are not propagating in a timely fashion.
>>
>> http://www.sqlite.org/lang_transaction.html
>> http://www.sqlite.org/lockingv3.html
> 
> Reading up now...

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

Re: [sqlite] Unofficial poll

[Yuriy Kaminskiy]

Jay A. Kreibich wrote:
> On Sun, Sep 23, 2012 at 09:25:06PM +0400, Yuriy Kaminskiy scratched on the 
> wall:
>> Jim Dodgen wrote:
> 
>>> I program mostly on Perl on Linux and it is a beautiful fit. Example
>>> is I can have a date field with a  POSIX time value (or offset) in it
>>> or another date related value like "unknown"
>> Very bad example. Standard SQL NULL is much better fit for "unknown".
>> Besides, perl at least have "use strict;" and "use warnings;", sqlite does
>> not.
> 
>   Yet SQLite's types are often more clearly defined than Perl's.  Every
>   SQLite value has a specific, known type that will tell you exactly
>   how the bits are stored.  The only difference with SQLite is that
>   columns are allowed to have mixed types.  Don't confuse this with a
>   loosely typed language, however... again: every SQLite value has a
>   specific and known type.
> 
> 
>   From a formal mathematical sense, a relational NULL is considered a
>   "value-less type."  That is, it is treated as a specific data type
>   that's value domain is the null-set.
> 
>   So, if you want to get real formal, all relational databases allow
>   multiple types (at least two) to be assigned to a row attribute.
>   I know that sounds contrived, but when you start to look at NULL
>   handling in SQL in this way, it suddenly makes a lot more sense.
>   And it means that all SQL databases already deal with disjoint types
>   within a column.
> 
>   If formal theory isn't your way thing, I'd point out that "traditional"
>   database do all kinds of automatic type conversions.  When you input
>   a date in MySQL, you do so as a string.  When you get a date or
>   duration value back, it is usually as a string.  If you compare a
>   date column to a literal string (that, one assumes, represents a
>   date) the database will do its best to covert that string to
>   something that makes sense before doing the comparison.  Similar
>   things can be said of different numeric types... "WHERE floatCol < 3" 
>   will do automatic conversions and get on with it.
>   
>   The typical database has all kinds of automatic rules about dealing
>   with different types involved in the same operation.  SQLite has all
>   these rules as well...  and they're all clearly defined, and they all
>   work pretty much the same way.  The fact that a column is only loosely
>   typed really doesn't come into play in a significant way, except that
>   the conversion rules for a comparison may come up in a JOIN, while
>   other databases would typically only see a converted comparison in
>   a WHERE. 
>   
>   The end result is not mass chaos but, rather, rarely a surprise.
>   SQLite does a lot of type conversion-- just like every other database
>   out there-- to deal with disjoint types.  Those conversion rules are
>   well documented and make sense.
> 
> 
> 
>   I'm a bit of purest, and when I first started using SQLite eight
>   years ago, I was also a bit off-put by what I saw as "fast and loose"
>   typing.  Over many years of using SQLite for all kinds of things, I
>   can say that this has never been an issue.  It has never surprised
>   me, it has never caused problems-- and it occasionally has been darn
>   handy.
> 
> 
> 
>   And finally, for anyone that really wants strong typing, that's easy
>   enough to do.  Just add a check constraint to your column defs:
>   
>   CREATE TABLE t (
>   i  integer   CHECK ( typeof( i ) == 'integer' ),
>   t  text  CHECK ( typeof( t ) == 'text' ),
>   r  float CHECK ( typeof( r ) == 'real' ),
>   b  blob  CHECK ( typeof( b ) == 'blob' )
>   );

Won't help with catching error in
DELETE ... WHERE day >= 2012-03-15 -- oops, just deleted records from 1994 year

Won't help with catching error when (SELECT ...) >= (SELECT ...) suddenly uses
string comparison instead of numerical (9 >= 19 vs '9' >= '19').

Yes, sqlite rules documented, yes, you can always explain why it behaved the way
it did, still nasty surprises happens, especially with newbies (and sometimes
not only with newbies).

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

Re: [sqlite] Unofficial poll

[Yuriy Kaminskiy]

Jim Dodgen wrote:
> On Sun, Sep 23, 2012 at 3:37 AM, Baruch Burstein  wrote:
>> I am curious about the usefulness of sqlite's "unique" type handling, and
>> so would like to know if anyone has ever actually found any practical use
>> for it/used it in some project? I am referring to the typeless handling,
>> e.g. storing strings in integer columns etc., not to the non-truncating
>> system e.g. storing any size number or any length string (which is
>> obviously very useful in many cases).
>> Has anyone ever actually taken advantage of this feature? In what case?
>
> I program mostly on Perl on Linux and it is a beautiful fit. Example
> is I can have a date field with a  POSIX time value (or offset) in it
> or another date related value like "unknown"

Very bad example. Standard SQL NULL is much better fit for "unknown".
Besides, perl at least have "use strict;" and "use warnings;", sqlite does not.

(Not that I expect anything to change here; backward compatibility, requirement
to keep it "lite", etc; like it or not, we have live with current lax typing 
system)
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] Covering index scan optimization

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 15 Sep 2012, at 12:08pm, Elefterios Stamatogiannakis  
> wrote:
> 
>> What i would really like to have in SQLite concerning OLAP, would be bigger 
>> pages,
> 
> You can set pagesize for a new database using a PRAGMA:
> 
> 
> 
> The maximum allowed pagesize is 65536 bytes.  Create a new database file, 
> then issue the PRAGMA before any CREATE commands.  If you have an existing 
> database and want to change it you have to export the data, make a new 
> database and import the data again, but this can all be done in two commands 
> to the shell tool.

Hint: look for VACUUM in above page (well, VACUUM is not much different from
export+import pair internally).

>> and internal page compression in a similar manner that column stores do [^]. 
>> This would greatly alleviate the storage pain of using denormalized DBs 
>> which is a must for OLAP.
> 
> This feature would indeed be suitable for a server-client database engine 
> designed to run on multipurpose computers.  But SQLite is designed more in 
> embedded machines in a single-processor environment.  For example, my TV 
> recorder uses it to list the TV channels and meta-data about its recordings, 
> and I have an extremely low-power GPS device which uses it for Positions of 
> Interest.  The fact that SQLite turns out to be so useful as an embedded DBMS 
> inside, for example, a web browser is just a bonus.  As the documentation 
> says, if you need network-savvy client-server stuff, look elsewhere.
> 
> Simon.

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

Re: [sqlite] classic update join question

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
>> I have a column in table 'alpha' which I would like to populate with data
>> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
>> sqlite, but we can
>>
>> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE
>> beta.term =
>>> alpha.term)
>>
>> Will the database really be doing a select in beta for
>> every single line in alpha?
> 
> Yes - same as when implementing a join. How do you think a join is
> performed - black magic?

Subquery: O(n*log(m)), join: O(n+m). Magic!
Of course, query optimizer sometimes can rewrite subquery as join (or opposite),
but I believe (unverified!) sqlite optimizer cannot do this currently.

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

Re: [sqlite] why no such column in sqlite3 ?

[Yuriy Kaminskiy]

Rob Richardson wrote:
> Put single quotes around Testitem:
> 
>   sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, 
> CAMOUNT ) VALUES ( 5, 2012-08-29, 'Testitem', 300 )");

And around cdate too. There are no dedicated date type in sqlite, 2012-08-29 is
treated as expression ((2012 - 08) - 29). Result will be 1975, not what you
might have expected.

sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT )
VALUES ( 5, '2012-08-29', 'Testitem', 300 )");

And you likely should use sqlite3_prepare_v2, placeholders, sqlite3_bind_int and
sqlite3_bind_text instead of sprintf.

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

Re: [sqlite] how to update the Moving average value

[Yuriy Kaminskiy]

Keith Medcalf wrote:
> You are right Klaas, it should be -2 not -3.  You could always constrain id 
> to (MAXINT >= id >= 3-MAXINT) if you wanted to be sure there would not be an 
> arithmetic overflow.

1) s/MAXINT/INT64_MAX/;
2) it is rather inefficient;
3) it will break on ID discontinuity; and attempt to fix it - something like
  SELECT id, sales_vol,
 (SELECT avg(c.sales_vol)
FROM (SELECT b.sales_vol
FROM tbl b
   WHERE b.id <= a.id ORDER BY id DESC LIMIT 3) c) AS mavg
FROM tbl a ORDER BY id ASC LIMIT -1 OFFSET 2;
will be HORRIBLY inefficient;

I think calculating moving average and similar things in SQL is only good as
exercise or on small dataset.

>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Klaas V
>> Sent: Sunday, 12 August, 2012 12:49
>> To: SQLite MailList
>> Subject: Re: [sqlite] how to update the Moving average value
>>
>> Keith Metcalf wrote:
>>
>>
>>> id  Sales_vol  mov_avg
>>> 11
>>> 22
>>> 33  =(1+2+3)/3
>>> 45  =(2+3+5)/3
>>> 54  =(3+5+4)/3
>>> 62  =(5+4+2)/3
>>> 7
>>> select id, sales_vol, (select avg(sales_vol) as mavg
>>>from tbl b
>>>where b.id between a.id - 3 and a.id)
>>>  from tbl a;
>> - 3 should be - 2. You're working with the last 4 days and might get an error
>> if id=3
>> :P

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

Re: [sqlite] AUTO_INCREMENT error

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> Brandon Pimenta  wrote:
>> CREATE TABLE test (
>> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
>> );
> 
> Make it 
> 
> INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
> 
> Though NOT NULL is redundant - PRIMARY KEY implies it.

Unlike other sql dialects, PRIMARY KEY in sqlite does not imply NOT NULL
constraint (see documentation).
However, INTEGER PRIMARY KEY is very special and unlike other column types - it
enforce type check and attempt to insert NULL will insert autoincremented value
instead.
sqlite> create table x(a text primary key);
sqlite> create table y(b integer primary key);
sqlite> insert into x values (NULL);
sqlite> insert into x values ('a');
sqlite> insert into x values (3);
sqlite> insert into x values (NULL);
sqlite> insert into y values (NULL);
sqlite> insert into y values ('a');
Error: datatype mismatch
sqlite> insert into y values (3);
sqlite> insert into y values (NULL);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a text primary key);
INSERT INTO "x" VALUES(NULL);
INSERT INTO "x" VALUES('a');
INSERT INTO "x" VALUES('3');
INSERT INTO "x" VALUES(NULL);
CREATE TABLE y(b integer primary key);
INSERT INTO "y" VALUES(1);
INSERT INTO "y" VALUES(3);
INSERT INTO "y" VALUES(4);
COMMIT;

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

Re: [sqlite] SQLITE_STATIC and temporary data

[Yuriy Kaminskiy]

Jay A. Kreibich wrote:
> On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall:
>> Dear Friends,
>>
>> So SQLITE_STATIC is meant to be used for data that is static. However,
>> would it still be safe when it is used with data that expires after the
>> sqlite3_step() function?
>>
>> For example:
>>
>> string hello = "hello world";
>>
>> sqlite3_bind(statement, 1, hello.c_str(), hello.size(), SQLITE_STATIC);
>>
>> sqlite3_step(statement);

>> hello = "moo";
>>
>> Would there be anything that can potentially go wrong? I mean, since
>> SQLITE_STATIC is meant to imply static stuff, would sqlite cache the value
>> in such a way that subsequence SELECT_ statements actually use this static
>> value that was passed in through the bind function?
> 
>   It would be better to keep the value valid until _reset() or

No, _reset is NOT enough, as it *won't* clear bindings. You *must* keep data
till either sqlite3_clear_bindings or sqlite3_finalize is (successfully) called.

>   _finalize() is called, but, yes... this use of SQLITE_STATIC is
>   acceptable (and somewhat common).

Above code is certainly incorrect. Maybe you won't be slapped with SIGSEGV
immediately (depending on surrounding code and sqlite3 implementation details),
but still this is incorrect and unsafe. You should insert
   sqlite3_clear_bindings(statement);
or
   sqlite3_bind_null(statement, 1);
before hello = "moo";

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

Re: [sqlite] force read schema after "delete from sqlite_master"

[Yuriy Kaminskiy]

Gabriel Corneanu wrote:
> I have the following scenario: I need to "clear"/"initialize" a db file
> while potential readers are active (polling for data).
> The "normal" way to do it is begin a transaction, drop all tables, recreate
> tables, commit (vacuum to regain space).
> 
> The biggest problem is that dropping a "very large" table (many GB) takes a
> lot of time.

Check
PRAGMA secure_delete;
(and disable if it was enabled by default; it would be rendered ineffective by
your trick anyway).

> I could very well delete the file, but that fails if any reader has it open.
> 
> I tried with
> delete from sqlite_master where type in ("table","view","index")

Probably there are way to make it work (altering PRAGMA schema_version; or
something), but I think it is way to hackerish and unsafe to use such tricks in
anything resembling production code.
Basically, you break consistency of your database image (and then kind-of-"fix"
it with vacuum;).

> (of course after setting writable schema)
> This works very fast, but I have a different problem...
> I can't recreate tables because it looks like sqlite still knows about them
> ?? Even if "select * from sqlite_master" returns nothing??
> 
> Dropping takes the same long time as originally did...
> 
> It WORKS if I do a "vacuum" first, but it won't work inside a transaction...
> I had to wrap everything in a transaction to block readers seeing an
> "empty" file. Therefore I also can't close/reopen etc...
> 
> Test this from shell in a test db:
> 
> 
> create table test(a);
> insert into test values(1);
> 
> select * from sqlite_master;
> 
> pragma writable_schema=1;
> delete from sqlite_master where type="table";
> pragma writable_schema=0;
> 
> select * from sqlite_master;
> 
> create table test(b);
> 
> vacuum;
> create table test(b);
> 
> 
> How can I solve this??
> 
> Any help appreciated.

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

Re: [sqlite] Bug in sqlite3_step

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 7/3/2012 10:05 AM, Unsupported wrote:
>>  // case 1: exception
>>  //verify(sqlite3_prepare_v2(db, "create trigger updater
>> update of result on plugins"
>>  //  " begin"
>>  //  " update mails set kav=case old.result when
>> 'infected' then ? else 0 end where uid=old.uid;"
>>  //  " end;"
>>  //  , -1, , 0) == SQLITE_OK);
>>  //verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);
> 
> You can't use parameters with DDL statements (all kinds of CREATE
> SOMETHING, DROP SOMETHING et al). You can only parameterize DML
> statements - SELECT, INSERT, UPDATE and DELETE.

Hmm... I don't think this is incorrect per se, but where is this documented?
I have not found any warning about this in prepare/bind/lang_expr/... 
documentation.

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Yuriy Kaminskiy]

Paul van Helden wrote:
> Is this correct? Should update triggers not only fire for actual changes? I
> have a large table with a column which contains all NULL values except for
> 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> except it fires for every row.

I'm pretty sure that sqlite3_changes() in this case also returns *all* rows, not
only 4 "really" changed. If you want triggers to only fire for really changed
rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE
clause:
UPDATE table SET column=NULL WHERE column IS NOT NULL;

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

Re: [sqlite] escaping GLOB pattern

[Yuriy Kaminskiy]

nobre wrote:
> "If the optional ESCAPE clause is present, then the expression following the
> ESCAPE keyword must evaluate to a string consisting of a single character.
> This character may be used in the LIKE pattern to include literal percent or
> underscore characters. The escape character followed by a percent symbol
> (%), underscore (_), or a second instance of the escape character itself
> matches a literal percent symbol, underscore, or a single escape character,
> respectively."
> 
> Choose a escape character and put it before any of the meaningful ones in
> your query to escape it.

Contrary to syntax diagram in documentation, ESCAPE clause does NOT work with
GLOB (or [icu ext] REGEXP) [or, rather, "not implemented" - it would work if
3-arg GLOB function were provided - but it is not].

And it won't solve OP problem anyway.

FWIW, I think this should work:

SELECT FROM t
 WHERE
   foo GLOB replace(replace(replace(?, '[','[[]'),'*','[*]'),'?','[?]')||'*'

(but, of course, it looks clumsy and not future-safe).

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

Re: [sqlite] escaping GLOB pattern

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Mon, Jun 18, 2012 at 3:34 AM, Baruch Burstein wrote:
> 
>> Is there a built-in way to escape a GLOB pattern? Will it escape it if I
>> bind it to a parameter in a prepared function instead of embedding it
>> directly in the query string?

no, sqlite3_bind* won't escape glob/like/regexp metacharacters in parameters.

> What do you mean by "escape a GLOB pattern"?

Not an OP, but I'd guess "escape all wildcard characters in string so that it
only match bare string by GLOB". So that e.g. "foo GLOB escape_glob(?)" is
exactly same as "foo = ?".
Of course case above is redundant (just use "="), but it could be useful in more
complex cases, like
   SELECT * FROM t WHERE foo GLOB (escape_glob(?)||'*[a-z]');

(Note: fictional example, there are no escape_glob() function in sqlite 
currently).

(Note: currently sqlite cannot use index with pattern computed in complex
expression [e.g. "foo GLOB (escape_glob(?)||'*')"]; but it /can/ use index in
"foo GLOB ?" when pattern in parameter have prefix match - so if you need
efficiency and your pattern may contain prefix matches, you should compute
pattern [escape, concatenate,...] in C code, not in SQL).

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

Re: [sqlite] Strange behavior with fts4

[Yuriy Kaminskiy]

Philip Bennefall wrote:
> I hate to be cluttering up the list in this fashion, but I have come across 
> an issue that I cannot seem to find a solution for.
> 
> I am using two fts tables, one that uses the normal tokenizer and another 
> that uses the porter stemmer, so that I can search the same dataset with and 
> without porter. For the porter stemmer table, I have set the content option 
> to point to the other fts table. Like this:
> 
> CREATE VIRTUAL TABLE main_brain USING fts4(phrase, response, matchinfo=fts3, 
> tokenize=simple, order=desc);
> 
> CREATE VIRTUAL TABLE porter_brain USING fts4(content="main_brain", phrase, 
> response, matchinfo=fts3, tokenize=porter, order=desc);
> 
> Then I do the following:
> 
> INSERT INTO main_brain(phrase, response) VALUES('hello', 'Hi there!');
> 
> So you'd now think that main_brain should have this content in it, but the 
> porter_brain table should be empty. At least, that is what the documentation 
> on fts4 seems to indicate as it points out that it is my own responsibility 
> to make sure the tables are in sync. But:
> 
> SELECT * FROM porter_brain;
> 
> Produces:
> 
> hello|Hi there!

As porter_brain takes all content from main_brain, full scan result looks
"correct" ("SELECT FROM porter_brain" internally replaced with "SELECT FROM
main_brain"). But before you execute INSERT below, all fts indexes are missing,
so any request that utilize them, like

SELECT * FROM porter_brain WHERE phrase MATCH 'hello';

will fail.

> And:
> 
> SELECT * FROM main_brain;
> 
> Gives the same result. So it seems as though some internal synchronization 
> between these tables is going on. How should I handle this? The documentation
>  suggests a statement like the following in one of its examples:
> 
> INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;

> Where I guess t3 would correspond to porter_brain and t2 would be main_brain 
> in my case. But I don't seem to need to do this at all.

INSERT INTO porter_brain (docid, phrase, response)
   SELECT docid, phrase, response FROM main_brain;

Before you execute this insert statement, porter_brain state is *inconsistent*
and it cannot be used properly.

[FWIW, most natural place for this insert would be AFTER INSERT trigger ... but
as main_brain is VIRTUAL TABLE, it is impossible; you can consider using
external content table for both virtual tables with trigger:

CREATE TABLE tblContent(phrase TEXT, response TEXT);
CREATE VIRTUAL TABLE main_brain USING fts4(content="tblContent", phrase,
response, matchinfo=fts3, tokenize=simple, order=desc);
CREATE VIRTUAL TABLE porter_brain USING fts4(content="tblContent", phrase,
response, matchinfo=fts3, tokenize=porter, order=desc);
CREATE TRIGGER trgSync AFTER INSERT ON tblContent FOR EACH ROW
 BEGIN
  INSERT INTO main_brain (docid, phrase, response)
   VALUES (NEW.rowid, NEW.phrase, NEW.response);
  INSERT INTO porter_brain (docid, phrase, response)
   VALUES (NEW.rowid, NEW.phrase, NEW.response);
   END;
].

> Can anyone throw some light on this?
> 
> Thanks in advance.

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

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Wed, May 30, 2012 at 2:17 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 
>> Pavel Ivanov wrote:
>>> Here is an example when left outer join makes the difference. Example
>>> could seem very artificial but SQLite should count on any possible
>>> usage.
>>>
>>> sqlite> create table Employee (name int);
>>> sqlite> create table Uniform (employeename, inseam, constraint ue
>>> unique (employeename));
>>> sqlite> insert into employee values ("Joe");
>>> sqlite> insert into employee values ("Steve");
>>> sqlite> insert into Uniform values ("Joe", 77);
>>> sqlite> insert into employee values (1);
>>> sqlite> insert into Uniform values (1, 77);
>>> sqlite> insert into Uniform values ("1", 77);
>>> sqlite> select Name from Employee left outer join Uniform on
>>> Employee.name=Uniform.employeename;
>>> Joe
>>> Steve
>>> 1
>>> 1
>>> sqlite> select Name from Employee;
>>> Joe
>>> Steve
>>> 1
>>> sqlite>
>> I'd rather call this sqlite bug.
>> Either "=" should return false, or "UNIQUE" constraint must reject
>> duplicate rows.
>>
> 
> Not a bug because  1!='1' for untyped fields such as Uniform.employeename
> and so the values are unique.

Thanks you for explanation (dynamic/loose type system can cause rather strange
behavior in corner cases :-|; I'd wished sqlite had PRAGMA strict_typing to
reject such ambiguous queries/schemas :-| then again, that would conflict with
"lite" part).

> But Employee.name has type "INT" and so when comparing Employee.name on the
> left to Uniform.employeename on the right, the left field is used to
> determine the comparison type, which means the string '1' is converted to
> 1, and so both 1 and '1' on the right both compare equal to the 1 on the
> left.

So, if someone will implement this optimization :-), it should be applied only
when column types are same.

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

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Yuriy Kaminskiy]

Pavel Ivanov wrote:
> Here is an example when left outer join makes the difference. Example
> could seem very artificial but SQLite should count on any possible
> usage.
> 
> sqlite> create table Employee (name int);
> sqlite> create table Uniform (employeename, inseam, constraint ue
> unique (employeename));
> sqlite> insert into employee values ("Joe");
> sqlite> insert into employee values ("Steve");
> sqlite> insert into Uniform values ("Joe", 77);
> sqlite> insert into employee values (1);
> sqlite> insert into Uniform values (1, 77);
> sqlite> insert into Uniform values ("1", 77);
> sqlite> select Name from Employee left outer join Uniform on
> Employee.name=Uniform.employeename;
> Joe
> Steve
> 1
> 1
> sqlite> select Name from Employee;
> Joe
> Steve
> 1
> sqlite>

I'd rather call this sqlite bug.
Either "=" should return false, or "UNIQUE" constraint must reject duplicate 
rows.

> On Wed, May 30, 2012 at 1:15 PM, Charles Samuels  wrote:
>> On Wednesday, May 30, 2012 10:14:22 f.h. Charles Samuels wrote:
>>> sqlite> select Name from Employee join Uniform on
>>> Employee.name=Uniform.employeename;
>>> Joe
>>> Dave
>>> sqlite> explain query plan select Name from Employee join Uniform on
>>> Employee.name=Uniform.employeename;
>>> 0|0|0|SCAN TABLE Employee (~100 rows)
>>> 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
>>> (employeename=?) (~1 rows)
>> I mean "left outer join" here!
>>
>> sqlite> select Name from Employee left outer join Uniform on
>> Employee.name=Uniform.employeename;
>> Joe
>> Steve
>> Eric
>> Dave
>> sqlite> explain query plan select Name from Employee left outer join Uniform
>> on Employee.name=Uniform.employeename;
>> 0|0|0|SCAN TABLE Employee (~100 rows)
>> 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
>> (employeename=?) (~1 rows)
>>
>> Charles
>> (can never proofread enough.)
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] copy table to another db file

[Yuriy Kaminskiy]

Luuk wrote:
> On 17-05-2012 11:04, YAN HONG YE wrote:
>> I have two db files:
>>
>> sqlite3 *db1;
>> sqlite3 *db2;
>> rc1 = sqlite3_open("myfile1", );
>> rc2 = sqlite3_open("myfile2",  );
>>
>> I want to copy db1.table1 to db2 file, but I don't know how to do?
> 
> sqlite myfile1
> sqlite> attach database 'myfile2' as db2;
> sqlite> create table db2.table1 as select * from main.table1;

... but note that new table will have stripped schema (column names will be
same, but no exact types, and all constraints, indexes, triggers,... will be
stripped):

sqlite> create table t(a int64 not null, b text check(b<>'foobar') collate
nocase /* comment */, c datetime unique, d varchar(64));
sqlite> create table u as select * from t;
sqlite> .schema
CREATE TABLE t(a int64 not null, b text check(b<>'foobar') collate nocase /*
comment */, c datetime unique, d varchar(64));
CREATE TABLE u(a INT,b TEXT,c NUM,d TEXT);

If you want to copy whole database (all tables), you'd better use backup API.

If you want to copy just one table, but need to keep schema:
You can try read original table definition from (SELECT sql FROM sqlite_master
WHERE tbl_name COLLATE NOCASE = ? AND type = 'table'), feed it to new db
connection, and then use
INSERT INTO db2.table1 SELECT * FROM main.table1;
but that may result in problems with some constraints [esp. foreign keys] (and
messing with internal machinery [sqlite_master] may be not very safe with
respect of future sqlite compatibility).

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

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

[Yuriy Kaminskiy]

Kit wrote:
> 2012/5/13, Frank Chang :
>> Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE.
>>
>> F:\sqlite3_6_16>sqlite3.exe mdName.dat
>> SQLite version 3.6.16
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
>> sqlite> .quit
> 
> sqlite> CREATE TABLE PREFIX (a TEXT, b TEXT, c TEXT, d INT);
> sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
> sqlite> INSERT INTO PREFIX SELECT x'52C3B373','M','Ros','10';
> sqlite> INSERT INTO PREFIX VALUES (x'52C3B373','M','Ros','10');
> sqlite> SELECT * FROM PREFIX;
> Rós|M|Ros|10
> Rós|M|Ros|10
> Rós|M|Ros|10

While they look similar at first, they are actually different:
sqlite> SELECT TYPEOF(a), LENGTH(a) FROM prefix;
text|3
blob|4
blob|4

And in sqlite CAST (and other expressions) can be used with INSERT VALUES too:
sqlite> INSERT INTO PREFIX VALUES (CAST(x'52C3B373' AS TEXT),'M','Ros','10');
(resulting in correct 'text|3' row).

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

Re: [sqlite] Getting rowid for last returned row

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 11 May 2012, at 3:36pm, Scott Ferrett
>  wrote:
> 
>> If this is not possible, I can restrict this bit of code to only work on
>> UPDATE statements.  But that still leaves me with the problem of needing
>> the rowid of the row being updated.
> 
> The only supplied function is the one which returns the rowid from the most
> recent INSERT.  There are no equivalents for UPDATE or SELECT.  Short of
> parsing the SQL statements I don't know of a way to figure it out.
> 
> I do have one application which edits SELECT statements as follows: after the
> 'SELECT ' at the beginning, insert 'rowid,'.

SELECT * FROM foo ORDER BY 1; -- oops, adding column breaks ORDER BY
SELECT * FROM foo JOIN bar; -- oops, "rowid" ambiguous without "foo." or "bar."
SELECT * FROM foo GROUP BY t; -- oops, "rowid" is from random row
SELECT * FROM foo UNION SELECT * FROM bar; -- oops

IMO, such SQL statement manipulation is way too fragile to be used in any
semi-general purpose library. You'd better just export low-level blob API and
let library user worry about getting valid row-id, blob handle invalidation
rules and all.

> When it gets the results it
> strips the first column off of the results from the SELECT before returning
> the other columns to the calling function.  This works perfectly in the
> context of that app, which never uses JOIN, knows that every table has a
> "rowid" column, and knows that the calling function will never care about
> values in the "rowid" column.
> 
> Simon.

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

Re: [sqlite] ordering doesn't work the way I expected

[Yuriy Kaminskiy]

William Parsons wrote:
> In my application, I've encountered a problem with ordering where the result 
> doesn't match what I would have expected, and would like some clarification.
> The issue is illustrated by the following:
> 
> % sqlite3 :memory:
> SQLite version 3.7.10 2012-01-16 13:28:40
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table x(a int, b int unique);
> sqlite> insert into x(a) values (1);
> sqlite> insert into x(a) values (4);
> sqlite> insert into x(a) values (3);
> sqlite> insert into x(a) values (5);
> sqlite> insert into x(b) values (6);
> sqlite> insert into x(b) values (8);
> sqlite> insert into x(b) values (7);
> sqlite> insert into x(a) values (2);
> sqlite> select * from x;
> 1|
> 4|
> 3|
> 5|
> |6
> |8
> |7
> 2|
> sqlite> select * from x order by b, a;
> 1|
> 4|
> 3|
> 5|
> 2|
> |6
> |7
> |8
> sqlite> .e
> 
> Why aren't the numbers sorted as I would have expected?  Note that if I 
> remove 
> the unique constraint from column b it works as expected.

Looks similar to this issue/bug: http://www.sqlite.org/src/info/2a5629202f
... and I think it was fixed in trunk [aka what-will-be-3.7.12]:
http://www.sqlite.org/src/info/9870e4c4fe?sbs=0

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

Re: [sqlite] Bug: Memory leak using PRAGMA temp_store_directory

[Yuriy Kaminskiy]

Josh Gibbs wrote:
> I reported this a while ago and forgot about this until today while I
> was doing some debugging and once again got the report of leaked memory.
> 
> I'm using the c amalgamation code from 3.7.10 with VStudio 2010, and
> always start up my databases setting a temp directory to be used in the
> form:
> 
> PRAGMA temp_store_directory = 'my_app_dir_dbtemp'
> 
> This is passed into the 'sqlite3_exec' function.
> 
> On exit, the program is reporting that line 15215 of sqlite.c is where
> the unfreed allocation occurred:void *p = SQLITE_MALLOC( nByte );

1) This pragma is deprecated and should not be used. If you look at
documentation, it should be quite clear. If you look at sources, it should be
even more clear (global variable, that affects all database connections,
unprotected and not thread-safe, etc; you totally don't want to touch it, even
with pole stick).
2) This is not "real" leak - previous value is freed.

> The content of the memory contains my temp path.

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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

On 2011/10/23, Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> Yuriy Kaminskiy wrote:
>>>> When WHERE condition is constant, there are no need to evaluate and check 
>>>> it for
>>>> each row. It works, but only partially:
>>> ...
>>>> [In fact, you can move out out loop not only *whole* constant WHERE, but 
>>>> also
>>>> all constant AND terms of WHERE, like this:
>>>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>>>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>>>> I'll take a shot on that later.]
>>> Here it goes.
>>>
>>> Prerequisite: previous patch.
>>> Passes quick regression test (make test).
>>> Possible problem: short-circuits evaluation. Should not be problem, IMO, as 
>>> only
>>> constants references? Please verify.
>> Ping.
> Ping.
Ping.
For convenience all 3 patches collected below (needed no change for 3.7.11).
-- 
Part 1: Move whereSplit() to unbreak constant condition elimination.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:04:58.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:06:30.0 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   /* Special case: a WHERE clause that is constant.  Evaluate the
   ** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: optimize "WHERE const AND notconst" too

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:41:44.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:48:33.0 +0400
@@ -4636,6 +4636,21 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int j;
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+  }
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: Remove now-redundant sqlite3ExprIsConstantNotJoin call.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 21:23:00.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 21:25:06.0 +0400
@@ -4626,18 +4626,12 @@ WhereInfo *sqlite3WhereBegin(
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);

-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   {
-/* Move const in "WHERE const AND notconst" out of internal loop */
+/* Special case: constant AND subterm of WHERE clause. Evaluate the
+** expression and either jump over all of the code or fall thru.
+*/
 int j;
 for(j=i=0; inTerm; i++){
   if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){

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

Re: [sqlite] table aliases in update

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> Baruch Burstein  wrote:
>> Does sqlite not support table aliases in update statements?
> 
> Indeed it does not.
> 
>> Is there a way
>> to work around this to get the affect of
>>
>> update table1 t1
>>set col1 = col1 * 2
>>where col1 <= (select avg(col1)
>> from table1
>> where col2=t1.col2);
> 
> update table1 set col1 = col1 * 2
> where col1 <= (
> select avg(col1) from table1 t2 where table1.col2=t2.col2);
> 
> "table1" prefix binds to the first occurrence of this table in the statement, 
> in the outer UPDATE clause.

Note: this request is quite inefficient; it is much better rewritten as

UPDATE table1 SET col1 = col1*2
 WHERE rowid in (SELECT t1.rowid
   FROM table1 t1
   JOIN (SELECT avg(col1) AS avgcol1, col2
   FROM table1 GROUP BY col2) t2
  USING (col2)
  WHERE t1.col1 < t2.avgcol1);

(assuming you have indexes on col1 and col2);

On table of ~500 000 random records, generated with
for(0..1_000) { $col2 = rand; for(0..int(rand(1_000))) {
$sth->execute(rand,$col2);
}}

Your query:
CPU Time: user 620.410773 sys 2.216138
0|0|0|SCAN TABLE table1 (~243273 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
0|0|0|SEARCH TABLE table1 AS t2 USING INDEX c2idx (col2=?) (~487 rows)

My query:
CPU Time: user 6.992437 sys 2.056128
0|0|0|SEARCH TABLE table1 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 0
1|0|0|SCAN TABLE table1 USING INDEX c2idx (~486546 rows)
0|0|1|SCAN SUBQUERY 1 AS t2 (~100 rows)
0|1|0|SEARCH TABLE table1 AS t1 USING INDEX c2idx (col2=?) (~162 rows)

Speedup by whooping 8772%!

Of course, this is very content-dependent (in my test table
COUNT(*)/COUNT(DISTINCT col2) == 500; if that ratio lower, effect of conversion
is lower; if there are *very* many distinct col2, my query may be even slower).

And, of course, if you need use subselect result in SET expression, such query
conversion impossible (that's why lack UPDATE ... JOIN is very important :-|
[lack of DELETE ... JOIN is much less important - *any* DELETE ... JOIN can be
converted to DELETE FROM WHERE rowid in (SELECT rowid FROM ... JOIN ...]);

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

Re: [sqlite] [patch] sqlite-3.7.11: valgrind errors in testsuite

[Yuriy Kaminskiy]

Dan Kennedy wrote:
> On 03/31/2012 04:04 PM, Yuriy Kaminskiy wrote:
>> valgrind ./testfixture test/trigger7.test
>>
>> Note: line numbers below are off-by-2.
>>
>> trigger7-2.1... Ok
>> trigger7-2.2...==11533== Invalid read of size 1
>>
>> Seems always reproducible.
> 
> Thanks for reporting this.
> 
> These tests are run with valgrind as part of the release process.
> But this error only shows up when you build with SQLITE_OMIT_LOOKASIDE
> (or disable the lookaside allocator some other way). I think this is
> probably better - perhaps we should disable lookaside when running
> valgrind tests during release testing too.

Yep, I usually prefer lookaside allocator enabled, just disabled it for better
valgrind interaction.
[But there are some major SQLITE_OMIT_LOOKASIDE users in the wild - IIRC,
debian, ubuntu and firefox ships sqlite3 with SQLITE_OMIT_LOOKASIDE]

>> It seems patch below fixes it (but I'm not sure if it is
>> correct/sufficient;
>> codepath seems common, why it is only triggered by this test is not
>> clear).
> 
> The patch looks correct and safe to me. The only possible problem
> is that the modification adds (a tiny amount of) code to one of
> the most performance sensitive functions in the library.
> 
> I think the reason this hasn't shown up before is that Mem.z is
> not valid unless either the MEM_Str or MEM_Blob flag is set on
> the memory cell. So accessing it when these flags are both clear
> is a bug in sqlite3VdbeMemGrow(). The bug is only triggered when
> the 'preserve' argument to sqlite3VdbeMemGrow() is true.
> 
> And the only place where sqlite3VdbeMemGrow() is called with
> the preserve flag set to true and a memory cell that does not
> have either the MEM_Str or MEM_Blob is from the code to do
> EXPLAIN listing of trigger programs.
> 
> This fix changes the EXPLAIN listing code so that it doesn't do
> that:
> 
> http://www.sqlite.org/src/info/c9342ca581?sbs=0

Thank you! That's certainly better.

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

[sqlite] [patch] sqlite-3.7.11: valgrind errors in testsuite

[Yuriy Kaminskiy]

valgrind ./testfixture test/trigger7.test

Note: line numbers below are off-by-2.

trigger7-2.1... Ok
trigger7-2.2...==11533== Invalid read of size 1
==11533==at 0x401FD90: memcpy (mc_replace_strmem.c:482)
==11533==by 0x8098EE2: sqlite3VdbeMemGrow (vdbemem.c:90)
==11533==by 0x80CD503: sqlite3VdbeList (vdbeaux.c:1240)
==11533==by 0x80CD968: sqlite3_step (vdbeapi.c:407)
==11533==by 0x8077181: dbEvalStep (tclsqlite.c:1425)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40F0B09: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==  Address 0x4f4f6c3 is 3 bytes inside a block of size 32 free'd
==11533==at 0x401D79C: free (vg_replace_malloc.c:325)
==11533==by 0x807C0BA: sqlite3_free (malloc.c:473)
==11533==by 0x808CAE2: releaseMemArray (vdbeaux.c:1091)
==11533==by 0x80C96A0: sqlite3VdbeHalt (vdbeaux.c:1643)
==11533==by 0x80C9F94: sqlite3VdbeReset (vdbeaux.c:2344)
==11533==by 0x80CA085: sqlite3_reset (vdbeapi.c:102)
==11533==by 0x80771A8: dbEvalStep (tclsqlite.c:1432)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==
==11533== Invalid read of size 1
==11533==at 0x401FD98: memcpy (mc_replace_strmem.c:482)
==11533==by 0x8098EE2: sqlite3VdbeMemGrow (vdbemem.c:90)
==11533==by 0x80CD503: sqlite3VdbeList (vdbeaux.c:1240)
==11533==by 0x80CD968: sqlite3_step (vdbeapi.c:407)
==11533==by 0x8077181: dbEvalStep (tclsqlite.c:1425)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40F0B09: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==  Address 0x4f4f6c2 is 2 bytes inside a block of size 32 free'd
==11533==at 0x401D79C: free (vg_replace_malloc.c:325)
==11533==by 0x807C0BA: sqlite3_free (malloc.c:473)
==11533==by 0x808CAE2: releaseMemArray (vdbeaux.c:1091)
==11533==by 0x80C96A0: sqlite3VdbeHalt (vdbeaux.c:1643)
==11533==by 0x80C9F94: sqlite3VdbeReset (vdbeaux.c:2344)
==11533==by 0x80CA085: sqlite3_reset (vdbeapi.c:102)
==11533==by 0x80771A8: dbEvalStep (tclsqlite.c:1432)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==
==11533== Invalid read of size 1
==11533==at 0x401FD9F: memcpy (mc_replace_strmem.c:482)
==11533==by 0x8098EE2: sqlite3VdbeMemGrow (vdbemem.c:90)
==11533==by 0x80CD503: sqlite3VdbeList (vdbeaux.c:1240)
==11533==by 0x80CD968: sqlite3_step (vdbeapi.c:407)
==11533==by 0x8077181: dbEvalStep (tclsqlite.c:1425)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40F0B09: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==  Address 0x4f4f6c1 is 1 bytes inside a block of size 32 free'd
==11533==at 0x401D79C: free (vg_replace_malloc.c:325)
==11533==by 0x807C0BA: sqlite3_free (malloc.c:473)
==11533==by 0x808CAE2: releaseMemArray (vdbeaux.c:1091)
==11533==by 0x80C96A0: sqlite3VdbeHalt (vdbeaux.c:1643)
==11533==by 0x80C9F94: sqlite3VdbeReset (vdbeaux.c:2344)
==11533==by 0x80CA085: sqlite3_reset (vdbeapi.c:102)
==11533==by 0x80771A8: dbEvalStep (tclsqlite.c:1432)
==11533==by 0x8079905: DbObjCmd (tclsqlite.c:2275)
==11533==by 0x40668D8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40AA7E8: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x40A866C: ??? (in /usr/lib/libtcl8.5.so.0)
==11533==by 0x4067EA0: TclEvalObjEx (in /usr/lib/libtcl8.5.so.0)
==11533==
==11533== Invalid read of size 1
==11533==at 0x401FDA6: memcpy (mc_replace_strmem.c:482)
==11533==by 0x8098EE2: sqlite3VdbeMemGrow (vdbemem.c:90)
==11533==by 0x80CD503: sqlite3VdbeList (vdbeaux.c:1240)
==11533==by 0x80CD968: 

Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Richard Hipp wrote:
>> On Thu, Feb 23, 2012 at 12:29 PM, Petite Abeille
>> <petite.abei...@gmail.com>wrote:
>>
>>> On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote:
>>>
>>>> sqlite> select 1 from (select *);
>>> Wow, wicked :)
>>>
>>> Confirmed on sqlite3 -version
>>> 3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204
>>>
>>
>> Fixed here:  http://www.sqlite.org/src/info/c8c7846fb9
> 
> This fix triggers SQLITE_NOMEM and test/select1.test select1-16.1 failure when
> lookaside allocation failed (or sqlite compiled with SQLITE_OMIT_LOOKASIDE):
>> select1-16.1...
>> Expected: [1 {no tables specified}]
>>  Got: [1 {out of memory}]
> Maybe sqlite3Malloc() should be changed to NOT return NULL when called with 
> n==0.
Conservative fix:

Index: sqlite3-3.7.11/src/select.c
===
--- sqlite3-3.7.11.orig/src/select.c2012-03-31 12:32:12.0 +0400
+++ sqlite3-3.7.11/src/select.c 2012-03-31 12:34:42.0 +0400
@@ -1258,7 +1258,11 @@ static int selectColumnsFromExprList(
   char *zName;/* Column name */
   int nName;  /* Size of name in zName[] */

-  *pnCol = nCol = pEList ? pEList->nExpr : 0;
+  if( !pEList ){
+*pnCol = 0; *paCol = NULL;
+return SQLITE_OK;
+  }
+  *pnCol = nCol = pEList->nExpr;
   aCol = *paCol = sqlite3DbMallocZero(db, sizeof(aCol[0])*nCol);
   if( aCol==0 ) return SQLITE_NOMEM;
   for(i=0, pCol=aCol; i<nCol; i++, pCol++){

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

Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Thu, Feb 23, 2012 at 12:29 PM, Petite Abeille
> wrote:
> 
>> On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote:
>>
>>> sqlite> select 1 from (select *);
>> Wow, wicked :)
>>
>> Confirmed on sqlite3 -version
>> 3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204
>>
> 
> 
> Fixed here:  http://www.sqlite.org/src/info/c8c7846fb9

This fix triggers SQLITE_NOMEM and test/select1.test select1-16.1 failure when
lookaside allocation failed (or sqlite compiled with SQLITE_OMIT_LOOKASIDE):
> select1-16.1...
> Expected: [1 {no tables specified}]
>  Got: [1 {out of memory}]
Maybe sqlite3Malloc() should be changed to NOT return NULL when called with 
n==0.

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

Re: [sqlite] Support EXTRACT SQL standard function

[Yuriy Kaminskiy]

Roger Andersson wrote:
> On 02/12/12 20:34, Yuriy Kaminskiy wrote:
>> I wonder, how it will be handled if you issue such request at
>> month/year/...
>> change (23:59.59.999 GMT ->  00:00:00.000 GMT)?
>> Is timestamp for current_date/current_time generated once and cached
>> at start of
>> SELECT evaluation?
>>
>> It is certainly *not* cached for different rows:
>> SELECT *, current_date, current_time, current_time FROM t
>> while($row = $sth ->  fetch) { print ++$i," row: @$row"; sleep 5; }
>> 1 row: 0 2012-02-12 19:20:40 19:20:40
>> 2 row: 1 2012-02-12 19:20:40 19:20:40
>> 3 row: 2 2012-02-12 19:20:45 19:20:45
>> 4 row: 3 2012-02-12 19:20:50 19:20:50
>> 5 row: 4 2012-02-12 19:20:55 19:20:55
>> (two first are same due to sqlite [or perl DBI binding?] seems
>> executes one row
>> ahead).
>>
>> But do they use same cached value *within one row*?
>>
>> If not, results may be randomly inconsistent and broken (race condition).
> No idea!

Okey, I looked at sources, and have not found any caching.

If you want trivially triggered case, repetitive execution

SELECT * FROM t WHERE strftime('%f')<>strftime('%f')

needed only about 17 to 100 iteration to hit error and return
non-deterministical row for me.

But same applies to *all other* use of date/time-functions, you'll just need
more (un)luck, and in one happy day

SELECT substr(current_date,...),
   substr(current_date,...),
   substr(current-date,...)

will result in 31,02,2012 instead of 31,01,2012 (or 01,02,2012).
Don't you think 31'th February is lucky day?

So more than one invocation of date/time-related functions per statement is
certainly buggy :-|

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

Re: [sqlite] Support EXTRACT SQL standard function

[Yuriy Kaminskiy]

Roger Andersson wrote:
> On 02/11/12 15:22, Kit wrote:
>> 2012/2/10 Willian Gustavo
>> Veiga:
>>> SQLite is a great database to unit test (TDD) applications. You can
>>> run it
>>> in memory with your tests ...
>>>
>>> I've found a problem when I was unit testing my application. MySQL
>>> (production database) supports EXTRACT SQL standard function. SQLite
>>> don't
>>> support it. It would be great to have support in this standard.
>>> Unfortunately, strftime isn't a solution. It's not a standard.
>> Function strftime is your solution. Write two models. One for MySQL,
>> one for SQLite. These databases are quite different and require
>> different SQL queries.
> Maybe views could be used to handle differences, at least some of them ;-)
> sqlite> create view dateCurrent as
>...> select
>...> substr(date(),0,5) as year,
>...> substr(date(),6,2) as month,
>...> substr(date(),9,2) as day;
> sqlite> .header on
> sqlite> select * from dateCurrent;
> year|month|day
> 2012|02|12
> 
> mysql> create view dateCurrent as
> -> select
> -> substr(current_date,1,4) as year,
> -> substr(current_date,6,2) as month,
> -> substr(current_date,9,2) as day;
> mysql> select * from dateCurrent;
> +--+---+-+
> | year | month | day |
> +--+---+-+
> | 2012 | 02| 12  |
> +--+---+-+

I wonder, how it will be handled if you issue such request at month/year/...
change (23:59.59.999 GMT -> 00:00:00.000 GMT)?
Is timestamp for current_date/current_time generated once and cached at start of
SELECT evaluation?

It is certainly *not* cached for different rows:
SELECT *, current_date, current_time, current_time FROM t
while($row = $sth -> fetch) { print ++$i," row: @$row"; sleep 5; }
1 row: 0 2012-02-12 19:20:40 19:20:40
2 row: 1 2012-02-12 19:20:40 19:20:40
3 row: 2 2012-02-12 19:20:45 19:20:45
4 row: 3 2012-02-12 19:20:50 19:20:50
5 row: 4 2012-02-12 19:20:55 19:20:55
(two first are same due to sqlite [or perl DBI binding?] seems executes one row
ahead).

But do they use same cached value *within one row*?

If not, results may be randomly inconsistent and broken (race condition).

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

Re: [sqlite] How to insert control characters into a table

[Yuriy Kaminskiy]

Kai Peters wrote:
> Hi,
> 
> how can I insert a control character like carriage return?
> 
> Something like:
> 
> update fielddefs set choices = 'Male' || '\r' || 'Female' where id = 2

update ... 'Male' || X'0D' || 'Female' ...

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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> When WHERE condition is constant, there are no need to evaluate and check 
>>> it for
>>> each row. It works, but only partially:
>> ...
>>> [In fact, you can move out out loop not only *whole* constant WHERE, but 
>>> also
>>> all constant AND terms of WHERE, like this:
>>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>>> I'll take a shot on that later.]
>> Here it goes.
>>
>> Prerequisite: previous patch.
>> Passes quick regression test (make test).
>> Possible problem: short-circuits evaluation. Should not be problem, IMO, as 
>> only
>> constants references? Please verify.
> 
> Ping.
Ping.

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote:
> 
>> Look at: SELECT hex(X'1245005679'),hex(X'1245001234');
>> 
>> And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT
>> X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679' >
>> X'1245001234'; 1 -- correct
>> 
>> "LIKE" (in both "native" and "icu" implementations) ignores value length 
>> (sqlite_value_bytes) and stops at NUL character. Compare that with "=" and
>> ">" that compares full value.
>> 
>> Arguable SQL_BLOB should be illegal operand for LIKE/GLOB, but then this
>> should be documented and they should return error when called with BLOB
>> argument instead of returning nonsense.
> 
> The definition of LIKE on the expression page states (not with complete
> clarity) that LIKE operates on strings.  So we're back to an old favourite: a
> decision as to what constitutes a string inside SQLite.  One definition would
> say that a string would terminate with the first 0x00 no matter how many
> bytes are stored (C style strings).  Another would say that the string
> terminates with the last byte stored (Pascal style strings).

One way or other, "=", "LIKE" and "GLOB" results should be consistent.
If string is NUL-terminated, "=" should ignore everything after NUL.
If string is length-terminated, "LIKE" should not ignore bytes after NUL.

If devs have no time to change code and/or make decisions now, those cases
should be at least documented as "function/operation A,B,C (currently) have
undefined behavior on BLOB". Not very nice, but randomly stumbling over such
surprises is certainly worse.

... and few related strangeness: UPPER/LOWER/*TRIM/REPLACE also accept BLOB
argument, but return *TEXT* instead. With somewhat unexpected result:

sqlite> SELECT length(   X'41424300414243313233');
10
sqlite> SELECT length( lower(X'41424300414243313233'));
3
sqlite> SELECT length(CAST(lower(X'41424300414243313233') AS BLOB));
10
sqlite> .mode insert
sqlite> select  rtrim(X'4142430061626333',X'334363');
INSERT INTO table VALUES('ABC');
sqlite> select CAST(rtrim(X'4142430061626333',X'334363') AS BLOB)
INSERT INTO table VALUES(X'414243006162');

(BTW, one can consider that *TRIM, LIKE and GLOB semantic on BLOB should be
different - with BLOB they should operate on *bytes*, not *utf-8 chars*; one
more reason to state "undefined behavior" for now).

Same with || operator (OP_Concat): it takes BLOB, but make result TEXT (leading
to [arguable illegal] TEXT with embedded NUL {or with broken UTF-X encoding}:
X'1234' || X'004567' [also look at
<http://permalink.gmane.org/gmane.comp.db.sqlite.general/68473>, where this
problem triggered invalid .dump])

SUBSTR also documented to operate *on string*, but, unlike above functions, when
supplied with BLOB argument it correctly returns BLOB value.

Not sure if it worth changing code, but certainly should be somehow mentioned in
documentation (including clearly stated "undocumented behavior on BLOB" as 
option).

> I don't think any official definition is stated anywhere in the documentation
> intended for users.  (I haven't looked at comments in the source code.)  And
> I suspect that if DRH wants to decide one way or another, this may point up
> some inconsistencies in the codebase which should be quickly fixed.

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Yuriy Kaminskiy]

Roger Andersson wrote:
>  On 11/09/11 19:42, Yuriy Kaminskiy wrote:
>> Paul Corke wrote:
>>> On 09 November 2011 15:32, hmas wrote:
>>>
>>>> sqlite>  select hex(foocol)  from footable where foocol like
>>>> '98012470700566';
>>>> 39393939393830313234373037303035363600
>>> It looks like there's an extra 00 on the end.
>>>
>>> x'3900' != x'39'
>> That said, it seems LIKE operator is buggy.
>> sqlite>  SELECT X'1245005679' LIKE X'1245001234';
>> 1
>>
> 
> On Windows
> 
> sqlite3.exe
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT X'1245005679',X'1245001234';
> ↕E|↕E

These strings are (intentionally) not printable, no surprise here.

Look at:
SELECT hex(X'1245005679'),hex(X'1245001234');

And compare:
SELECT X'1245005679' LIKE X'1245001234';
1 -- incorrect
SELECT X'1245005679' = X'1245001234';
0 -- correct
SELECT X'1245005679' > X'1245001234';
1 -- correct

"LIKE" (in both "native" and "icu" implementations) ignores value length
(sqlite_value_bytes) and stops at NUL character. Compare that with "=" and ">"
that compares full value.

Arguable SQL_BLOB should be illegal operand for LIKE/GLOB, but then this should
be documented and they should return error when called with BLOB argument
instead of returning nonsense.

Well, not sure, maybe shell.c is also buggy in respect with printing blobs (only
".mode insert" handles SQLITE_BLOB properly [IIRC, NUL-in-middle is only legal
in BLOB], other .mode's treats everything as text and ignores length, and so
arguable buggy), but that's different issue.

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

[sqlite] [bug] LIKE operator ignores rest of string after NUL character (was: select ... where [=] or [like])

[Yuriy Kaminskiy]

Paul Corke wrote:
> On 09 November 2011 15:32, hmas wrote:
> 
>> sqlite> select hex(foocol)  from footable where foocol like
>> '98012470700566';
>> 39393939393830313234373037303035363600
> 
> It looks like there's an extra 00 on the end.
> 
> x'3900' != x'39'

That said, it seems LIKE operator is buggy.
sqlite> SELECT X'1245005679' LIKE X'1245001234';
1

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

Re: [sqlite] Vacuum on sqlite file fills the .wal file and it never shriks since

[Yuriy Kaminskiy]

Richard Hipp wrote:
> On Tue, Nov 8, 2011 at 10:04 AM, Orit Alul  wrote:
> 
>> Hi,
>>
>> I've performed a vacuuming operation (I ran the following command:
>> sqlite3.exe  VACUUM;).
>> It caused the WAL file to be the same size as the db file and it never
>> shrink back.
>>
>> For example I had a db file in the size of 1.8GB, wal file in the size of
>> 1.7MB, shm file in the size of 32KB.
>> I ran the VACUUM; command on the db file, now the db file got down to
>> 1.2GB, wal file got up to 1.2GB and the shm file got up to 9.5MB.
>>
>> Do you have  a clue what is the cause for this strange behavior? Do you
>> know if there's a command that can shrink the files back?
>>
> 
> The WAL file is not truncated (prior to the last database connection
> closing) because overwriting an existing file is faster than appending to a
> file.  Hence reusing an existing WAL is faster than truncating and
> appending.
> 
> But if the file is larger than you like, you can set an upper bound on its
> size using the journal_size_limit pragma.
> http://www.sqlite.org/pragma.html#pragma_journal_size_limit  With this
> pragma setting, the WAL will be truncated down to the requested size after
> each successful checkpoint operation.

IMO, that should be mentioned in documentation. Currently it says this pragma
affects only journal_mode=persist and locking_mode=exclusive.

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

Re: [sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> David wrote:
>>> Simon L wrote 2011-10-25 06:20:
>>>> To reproduce this problem, enter the following 5 SQL statements at the
>>>> SQLite command line.
>>>>
>>>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>>>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>>>> insert into X values (1);
>>>> insert into Y select * from X;
>>>> insert into Y select * from X;
>>>>
>>>>
>>>> When I tried to run the last SQL statement twice,  SQLite produced the
>>>> following error message.
>>>> Error: PRIMARY KEY must be unique
>>>>
>>>>
>>>> Is this a bug? Please advise. Thank you.
> 
>>> This certainly looks like a bug. I got a constraint failure when I tried
>>> it in sqlite 3.7.8.
>>>
>>> But it works fine when you state the column name explicitly in the
>>> select clause.
>>>
>>> Like this:
>>>
>>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>>> insert into X values (1);
>>> insert into Y select id from X;
>>> insert into Y select id from X;
>>>
>>> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
>>> key checks,
>>> which was reported on the mailing list earlier this year:
>>>
>>> http://www.sqlite.org/src/tktview?name=6284df89de
>>>
>>> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
>> Thanks for pointer; root cause, indeed, transfer optimization (it ignores 
>> table
>> INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use 
>> table's
>> ON CONFLICT clause by default; falls back to regular transfer if destination
>> table is not empty and we cannot handle ON CONFLICT resolution);
>>
>> Index: sqlite3-3.7.8/src/insert.c
>> ===
>> --- sqlite3-3.7.8.orig/src/insert.c  2011-10-25 15:20:26.0 +0400
>> +++ sqlite3-3.7.8/src/insert.c   2011-10-25 15:54:54.0 +0400
> 
> Ping.

Okey, I've noticed
http://www.sqlite.org/src/info/6f9898db7f

Won't that result in performance regression in VACUUM? [only on tables with
INTEGER PRIMARY KEY ON REPLACE xxx, obviously; so not *terrible* big deal - but
still]
When we insert into empty table, ON CONFLICT will never trigger, so we can
safely use optimized xfer.

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

Re: [sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> David wrote:
>> Simon L wrote 2011-10-25 06:20:
>>> To reproduce this problem, enter the following 5 SQL statements at the
>>> SQLite command line.
>>>
>>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>>> insert into X values (1);
>>> insert into Y select * from X;
>>> insert into Y select * from X;
>>>
>>>
>>> When I tried to run the last SQL statement twice,  SQLite produced the
>>> following error message.
>>> Error: PRIMARY KEY must be unique
>>>
>>>
>>> Is this a bug? Please advise. Thank you.

>> This certainly looks like a bug. I got a constraint failure when I tried
>> it in sqlite 3.7.8.
>>
>> But it works fine when you state the column name explicitly in the
>> select clause.
>>
>> Like this:
>>
>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>> insert into X values (1);
>> insert into Y select id from X;
>> insert into Y select id from X;
>>
>> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
>> key checks,
>> which was reported on the mailing list earlier this year:
>>
>> http://www.sqlite.org/src/tktview?name=6284df89de
>>
>> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
> 
> Thanks for pointer; root cause, indeed, transfer optimization (it ignores 
> table
> INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use 
> table's
> ON CONFLICT clause by default; falls back to regular transfer if destination
> table is not empty and we cannot handle ON CONFLICT resolution);
> 
> Index: sqlite3-3.7.8/src/insert.c
> ===
> --- sqlite3-3.7.8.orig/src/insert.c   2011-10-25 15:20:26.0 +0400
> +++ sqlite3-3.7.8/src/insert.c2011-10-25 15:54:54.0 +0400

Ping.

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

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Two alternative patches, choose whichever you like.
> 
> Alternative 1: (IMO, preferred; tested)
> Don't lowercase argument of .schema.
> With PRAGMA case_sensitive_like = ON, you just need to use right case for 
> table
> names.
> 
> Index: sqlite3-3.7.8/src/shell.c
> ===
> --- sqlite3-3.7.8.orig/src/shell.c2011-10-23 14:00:50.0 +0400
> +++ sqlite3-3.7.8/src/shell.c 2011-10-23 14:01:14.0 +0400
> @@ -2018,9 +2018,7 @@ static int do_meta_command(char *zLine,
>  data.showHeader = 0;
>  data.mode = MODE_Semi;
>  if( nArg>1 ){
> -  int i;
> -  for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]);
> -  if( strcmp(azArg[1],"sqlite_master")==0 ){
> +  if( sqlite3_strnicmp(azArg[1],"sqlite_master",13+1)==0 ){
>  char *new_argv[2], *new_colv[2];
>  new_argv[0] = "CREATE TABLE sqlite_master (\n"
>"  type text,\n"
> @@ -2034,7 +2032,7 @@ static int do_meta_command(char *zLine,
>  new_colv[1] = 0;
>  callback(, 1, new_argv, new_colv);
>  rc = SQLITE_OK;
> -  }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){
> +  }else if( sqlite3_strnicmp(azArg[1],"sqlite_temp_master",18+1)==0 ){
>  char *new_argv[2], *new_colv[2];
>  new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
>"  type text,\n"
> =

Ping.

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

[sqlite] [patch] shell.c: make written history size tuneable

[Yuriy Kaminskiy]

... with $SQLITE3_HISTSIZE. Positive numbers limits history size, zero - don't
write to history at all (but read existing and keep in memory), negative -
always append to history file (useful when you run few instances of sqlite3 at
time and want to save history from all).
Default - 100, same as currently hardwired.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.7/sqlite3.1
===
--- sqlite3-3.7.7.orig/sqlite3.12011-08-31 23:03:27.0 +0400
+++ sqlite3-3.7.7/sqlite3.1 2011-08-31 23:03:27.0 +0400
@@ -234,6 +234,14 @@ o If the -init option is present, the sp

 o All other command line options are processed.

+.SH ENVIRONMENT
+.TP
+.B $SQLITE3_HISTSIZE
+The maximum number of lines contained in the history file.
+When negative, only append new commands to the history file (useful to prevent
lossing some history when running few sqlite3 instances in parallel).
+When zero, history is not saved.
+The default value is 100.
+
 .SH SEE ALSO
 http://www.sqlite.org/
 .br
Index: sqlite3-3.7.7/src/shell.c
===
--- sqlite3-3.7.7.orig/src/shell.c  2011-08-31 23:02:39.0 +0400
+++ sqlite3-3.7.7/src/shell.c   2011-08-31 23:05:07.0 +0400
@@ -51,6 +51,9 @@
 # define read_history(X)
 # define write_history(X)
 # define stifle_history(X)
+# define append_history(X,Y)
+# define where_history() (0)
+# define using_history()
 #endif

 #if defined(_WIN32) || defined(WIN32)
@@ -2900,11 +2902,23 @@ int main(int argc, char **argv){
   }
 #if defined(HAVE_READLINE) && HAVE_READLINE==1
   if( zHistory ) read_history(zHistory);
 #endif
+  using_history(); /* initialize where_history() */
+  nHistory = where_history();
   rc = process_input(, 0);
   if( zHistory ){
-stifle_history(100);
+const char *history_size_str = getenv("SQLITE3_HISTSIZE");
+int history_size = 100;
+if( history_size_str!=NULL )
+  history_size = strtol(history_size_str, NULL, 0);
+if( history_size>0 ){
+  stifle_history(history_size);
 write_history(zHistory);
+}else if( history_size==0 ){
+  /* do nothing */
+}else{
+  append_history(where_history()-nHistory, zHistory);
+}
 free(zHistory);
   }
   free(zHome);


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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> When WHERE condition is constant, there are no need to evaluate and check it 
>> for
>> each row. It works, but only partially:
> ...
>> [In fact, you can move out out loop not only *whole* constant WHERE, but also
>> all constant AND terms of WHERE, like this:
>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>> I'll take a shot on that later.]
> 
> Here it goes.
> 
> Prerequisite: previous patch.
> Passes quick regression test (make test).
> Possible problem: short-circuits evaluation. Should not be problem, IMO, as 
> only
> constants references? Please verify.

Ping.

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

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

[Yuriy Kaminskiy]

ChingChang Hsiao wrote:
> I can't reply in my system, so I create the problem description again.
> 
> I miss one source code line "char tempString[1024];"in the last email. The
> code dump happened after 4 days' run in a test script not immediately. The
> SQLITE statements seem to be ok. Could be a performance issue?

Core dumps are *never* performance issue.

Indeed, it would be /more efficient/ to prepare statements once (and maybe also
cache prepared statements between function invocations), and just bind different
values in loop, and that's "performance issue", but it is unrelated to 
coredumps.

> ChingChang
> 
> 
> The source code is shown as below,
> 
> 
> char tempString[1024];
> vector dbStatements;
>   dbStatements.push_back( "BEGIN TRANSACTION;" );
>   for ( int x = 0; x < 10; x++ ) {
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='bus' and sample='%d';",
>  ntohd(msg->bus_util[x]),
>  x,
>  x );
> dbStatements.push_back( tempString );
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='icache' and sample='%d';",
>  100.00-ntohd(msg->inst_hit_rate[x]),  // Convert to misses
>  x,
>  x );
> dbStatements.push_back( tempString );
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='dcache' and sample='%d';",

Hmm... One thing to consider: some locales uses different decimal point
separator instead of ".". That may cause problems. E.g.

$ cat >t.c << __EOF__
#include 
#include 
int main() { setlocale(LC_ALL, ""); return printf("%5.2f\n", 123.456) < 0; }
__EOF__
$ gcc t.c && LC_ALL=ru_RU.UTF-8 ./a.out
123,46
   ^ of course, SQL parser won't like this.

But that would likely trigger error *every* time, and not after few hours, so
does not fit your error description.

One more potential problem: NAN and infinity.
printf("%5.2f\n", 1.0/0.0); -> "  inf"
printf("%5.2f\n", 0.0/0.0); -> "  nan"
That would also confuse SQL parser.

If your ntohd function can sometimes return NAN/infinity, that would cause 
problems.

Still, does not fit your error description very well (I'd expect sqlite3_exec to
return error, and don't trigger assertion failure).

Both problem would be avoided by switching to "prepare statement once, then bind
values" pattern.

>  100.00-ntohd(msg->data_hit_rate[x]),  // Convert to misses
>  x,
>  x );
> dbStatements.push_back( tempString );
>   }
>   dbStatements.push_back( "COMMIT;" );
> 
>   // populate the DB
>   vector::iterator dbStatementsIter;
>   SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
>   for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter != 
> dbStatements.end(); dbStatementsIter++ ) {
> oper_db.execw( *(dbStatementsIter) );
>   }
> 
>   dbStatements.clear();
> 
> The core dump is shown as below.
> 
> #0  0x32e94b04 in raise () from /lib/libc.so.6
> #1  0x32e962f4 in abort () from /lib/libc.so.6
> #2  0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
> #3  0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
> #4  0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
> #5  0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so

Note: this is *not* SIGSEGV/SIGBUS, but *assertion failure*; it prints error on
stderr before program termination, it would be useful to look at this message.

And it would be useful to rebuild libsqlite3.mgmt-crd.so with unstripped
debugging symbols.

> #6  0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
> #7  0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
> #8  0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
> #9  0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
> #10 0x10010290 in NpuMessageHandler::processUtilReport (this= out>, msg=,
> nbytes=) at cavium_driver.cpp:1387
> #11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at 
> cavium_driver.cpp:954
> #12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
> #13 0x3278b5cc in ?? () from /lib/libpthread.so.0
> #14 0x32f39b88 in clone () from /lib/libc.so.6

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Teg wrote:
> I'd  like  this  clarified  too. I specifically don't use transactions
> when  I'm  selecting.  In  fact, I'll select, then start a transaction
> later  for  inserting the results.  Would I be better off wrapping the
> whole thing in a transaction?

Cannot be sure without looking at your code/database schema/..., but it looks
your usage pattern is wrong - there are race condition this way (that maybe
alleviated by implicit transaction start by [unfinished] SELECT, but anyway it
is fragile and wrong).

> Wednesday, November 2, 2011, 9:13:20 AM, you wrote:
> 
> BMI> Maybe my memory is fading but this is the first time I've heard
> BMI> anybody say the wrapping a BEBIN around a SELECT was needed.  I'd
> BMI> swear it was always said it wasn't ever needed.

BTW, what is "needed"? Nobody *force* you to use BEGIN/COMMIT around SELECT -
transaction will be started (and ended) implicitly; just if you issue multiple
statement, it is more efficient to take lock once, than take and release lock
(and lot more - check database schema, flush cache, etc) for each statement.

And, of course, if you need consistent database state across many sql
statements, you need to wrap all of them in single transaction.

>> >From the docs
> 
> BMI> http://www.sqlite.org/lang_transaction.html
> 
> BMI> basically, any SQL command other than
> BMI> SELECT) will
> BMI> automatically start a transaction if one is not already in effect
> 
> 
> 
> BMI> Now you're saying SELECT always starts a transaction?

PS
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Black, Michael (IS) wrote:
> Maybe my memory is fading but this is the first time I've heard anybody say
> the wrapping a BEBIN around a SELECT was needed.  I'd swear it was always
> said it wasn't ever needed.
> 
> 
> 
> From the docs
> 
> http://www.sqlite.org/lang_transaction.html

> basically, any SQL command other than 
> SELECT) will automatically start a 
> transaction if one is not already in effect

That's, of course, documentation bug. How can you get consistent results from
SELECT without taking (at least) SHARED lock?

Only difference, that UPDATE/INSERT/DELETE needs RESERVED lock (and then
EXCLUSIVE lock to (auto)commit), and SELECT only needs SHARED lock.

> Now you're saying SELECT always starts a transaction?

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

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

[Yuriy Kaminskiy]

Stephan Beal wrote:
> On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote:
> 
>> second,needless to say that I want to avoid this since it causes mem
>> leaks.)
>>
> 
> Why would it leak? Are you intentionally NOT calling finalize()?
> 
> 
>>sqlite3_prepare_v2(handle, query.str().c_str(),
>> query.str().length()+1, _entrySelectSnumStmt, 0);
>>
> 
> Technically speaking, the length you are passing there is not correct. The
> +1 accounts for the NUL byte at the end of the string, which prepare() does
> not need to see (that said, it seems harmless enough).

No. Take look at prepare_v2 documentation:

** ^If the nByte argument is less than zero, then zSql is read up to the
** first zero terminator. ^If nByte is non-negative, then it is the maximum
** number of  bytes read from zSql.  ^When nByte is non-negative, the
** zSql string ends at either the first '\000' or '\u' character or
** the nByte-th byte, whichever comes first. If the caller knows
   
** that the supplied string is nul-terminated, then there is a small
** performance advantage to be gained by passing an nByte parameter that
** is equal to the number of bytes in the input string including
   
** the nul-terminator bytes.
   

FWIW, it seems, *only* case when it make sense to bother with nByte argument -
when string is *not* nul-terminated; otherwise, it is more efficient to just
pass -1, no matter if length is known or not.

And std::string::c_str() always nul-terminate string.

> Also, because 'query' is-a ostringstream, you are possibly creating 2 unneeded
> std::string copies here and you are definitely invoking undefined behaviour
> with this part:
> 
>sqlite3_prepare_v2(handle, query.str().c_str(),
> 
> The problem is that query.str() returns a COPY of the string, which you
> call c_str() on to get its bytes, and then the copy is destroyed. It is
> "likely to work" on many platforms but it is technically undefined. To fix
> that:
> 
> std::string const & s( query.str() );
> 
> (note that a (const &) created this way is guaranteed to stay alive until
> the end of the scope)

Just get rid of str().length() and don't bother with this :-)

> Then use s.c_str() and s.size() instead of query.str().xxx().

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

[sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

David wrote:
> Simon L wrote 2011-10-25 06:20:
>> To reproduce this problem, enter the following 5 SQL statements at the
>> SQLite command line.
>>
>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>> insert into X values (1);
>> insert into Y select * from X;
>> insert into Y select * from X;
>>
>>
>> When I tried to run the last SQL statement twice,  SQLite produced the
>> following error message.
>> Error: PRIMARY KEY must be unique
>>
>>
>> Is this a bug? Please advise. Thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> This certainly looks like a bug. I got a constraint failure when I tried
> it in sqlite 3.7.8.
> 
> But it works fine when you state the column name explicitly in the
> select clause.
> 
> Like this:
> 
> create table X(id INTEGER primary key ON CONFLICT REPLACE);
> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
> insert into X values (1);
> insert into Y select id from X;
> insert into Y select id from X;
>
> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
> key checks,
> which was reported on the mailing list earlier this year:
> 
> http://www.sqlite.org/src/tktview?name=6284df89de
> 
> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.

Thanks for pointer; root cause, indeed, transfer optimization (it ignores table
INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use table's
ON CONFLICT clause by default; falls back to regular transfer if destination
table is not empty and we cannot handle ON CONFLICT resolution);

Disclaimer: /me is not sqlite dev team member, review carefully, use with care.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/insert.c
===
--- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.0 +0400
+++ sqlite3-3.7.8/src/insert.c  2011-10-25 15:54:54.0 +0400
@@ -1626,6 +1626,7 @@ static int xferOptimization(
   int regAutoinc;  /* Memory register used by AUTOINC */
   int destHasUniqueIdx = 0;/* True if pDest has a UNIQUE index */
   int regData, regRowid;   /* Registers holding data and rowid */
+  int keyConf = pDest->iPKey>=0 ? pDest->keyConf : OE_Default;

   if( pSelect==0 ){
 return 0;   /* Must be of the form  INSERT INTO ... SELECT ... */
@@ -1639,7 +1640,22 @@ static int xferOptimization(
   }
 #endif
   if( onError==OE_Default ){
-onError = OE_Abort;
+if( keyConf==OE_Abort || keyConf==OE_Rollback )
+  /* can be handled - take ON CONFLICT from table declaration */
+  onError = keyConf;
+else {
+  if( keyConf==OE_Default )
+keyConf = OE_Abort;
+  else {
+/* Not default and cannot be handled;
+** fallback to regular transfer if destination is not empty (below)
+*/
+  }
+  onError = OE_Abort;
+}
+  } else {
+/* statement ON CONFLICT overrides table ON CONFLICT */
+keyConf = onError;
   }
   if( onError!=OE_Abort && onError!=OE_Rollback ){
 return 0;   /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
@@ -1766,7 +1782,8 @@ static int xferOptimization(
   iDest = pParse->nTab++;
   regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
   sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
-  if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
+  if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ||
+  (/*pDest->iPKey>=0 && */keyConf!=onError) ) {
 /* If tables do not have an INTEGER PRIMARY KEY and there
 ** are indices to be copied and the destination is not empty,
 ** we have to disallow the transfer optimization because the
@@ -1776,6 +1793,9 @@ static int xferOptimization(
 ** we also disallow the transfer optimization because we cannot
 ** insure that all entries in the union of DEST and SRC will be
 ** unique.
+**
+** Or if destination INTEGER PRIMARY KEY has ON CONFLICT clause
+** that we cannot handle and destination is not empty.
 */
 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
 emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);

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

[sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> When WHERE condition is constant, there are no need to evaluate and check it 
> for
> each row. It works, but only partially:
...
> [In fact, you can move out out loop not only *whole* constant WHERE, but also
> all constant AND terms of WHERE, like this:
> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
> I'll take a shot on that later.]

Here it goes.

Prerequisite: previous patch.
Passes quick regression test (make test).
Possible problem: short-circuits evaluation. Should not be problem, IMO, as only
constants references? Please verify.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:41:44.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:48:33.0 +0400
@@ -4636,6 +4636,21 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int j;
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+  }
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: Remove redundant sqlite3ExprIsConstantNotJoin call. Result should be
equivalent. Feel free to squash with above patch on apply.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 21:23:00.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 21:25:06.0 +0400
@@ -4626,18 +4626,12 @@ WhereInfo *sqlite3WhereBegin(
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);

-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   {
-/* Move const in "WHERE const AND notconst" out of internal loop */
+/* Special case: AND subterm of WHERE clause that is constant. Evaluate the
+** expression and either jump over all of the code or fall thru.
+*/
 int j;
 for(j=i=0; inTerm; i++){
   if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){

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

[sqlite] [patch] constant WHERE elimination (partially) ineffective

[Yuriy Kaminskiy]

When WHERE condition is constant, there are no need to evaluate and check it for
each row. It works, but only partially:
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Goto|0|17|0||00|
2|OpenRead|0|60|0|9|00|
3|Rewind|0|15|0||00|
4|Column|0|0|1||00|
5|Column|0|1|2||00|
6|Rowid|0|3|0||00|
7|Column|0|3|4||00|
8|Column|0|4|5||00|
9|Column|0|5|6||00|
10|Column|0|6|7||00|
11|Column|0|7|8||00|
12|Column|0|8|9|0|00|
13|ResultRow|1|9|0||00|
14|Next|0|4|0||01|
15|Close|0|0|0||00|
16|Halt|0|0|0||00|
...
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Integer|1|1|0||00|
2|IfNot|1|18|1||00|
3|Goto|0|20|0||00|
4|OpenRead|0|60|0|9|00|
5|Rewind|0|18|0||00|
6|IfNot|1|17|1||00|
7|Column|0|0|3||00|
8|Column|0|1|4||00|
9|Rowid|0|5|0||00|
10|Column|0|3|6||00|
11|Column|0|4|7||00|
12|Column|0|5|8||00|
13|Column|0|6|9||00|
14|Column|0|7|10||00|
15|Column|0|8|11|0|00|
16|ResultRow|3|9|0||00|
17|Next|0|6|0||01|
18|Close|0|0|0||00|
19|Halt|0|0|0||00|
[...]
Look at addr 6, there are completely unnecessary IfNot inside of loop: this
condition is already checked out-of-loop.

I've looked at code, and noticed strange thing:
src/where.c, line 4631:
  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
pWhere = 0;
  }
But pWhere *is not used* in any code below this fragment, only *above* this
code. Patch below.

[In fact, you can move out out loop not only *whole* constant WHERE, but also
all constant AND terms of WHERE, like this:
SELECT * FROM t WHERE const1 AND notconst AND const2 ->
SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
I'll take a shot on that later.]

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:04:58.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:06:30.0 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   /* Special case: a WHERE clause that is constant.  Evaluate the
   ** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be

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

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Alternative 2: (partially tested)
> Explicitly use case-insensitive comparison for table/indexes, no matter what
> case_sensitive_like is.
> 
> Index: sqlite3-3.7.8/src/shell.c
> ===
> --- sqlite3-3.7.8.orig/src/shell.c2011-10-23 13:52:44.0 +0400
> +++ sqlite3-3.7.8/src/shell.c 2011-10-23 13:54:13.0 +0400
> @@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
>for(i=1; i<nArg; i++){
> +int j;
> +for(j=0; azArg[i][j]; i++) azArg[i][j] = (char)tolower(azArg[i][j]);

Doh :-( Fixed version:
Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 13:52:44.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 13:54:13.0 +0400
@@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
 }else{
   int i;
   for(i=1; i<nArg; i++){
+int j;
+for(j=0; azArg[i][j]; j++) azArg[i][j] = (char)tolower(azArg[i][j]);
 zShellStatic = azArg[i];
 run_schema_dump_query(p,
   "SELECT name, type, sql FROM sqlite_master "
-  "WHERE tbl_name LIKE shellstatic() AND type=='table'"
+  "WHERE lower(tbl_name) LIKE shellstatic() AND type=='table'"
   "  AND sql NOT NULL", 0);
 run_table_dump_query(p->out, p->db,
   "SELECT sql FROM sqlite_master "
   "WHERE sql NOT NULL"
   "  AND type IN ('index','trigger','view')"
-  "  AND tbl_name LIKE shellstatic()", 0
+  "  AND lower(tbl_name) LIKE shellstatic()", 0
 );
 zShellStatic = 0;
   }
@@ -1790,13 +1792,15 @@ static int do_meta_command(char *zLine,
 callback, , 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; j++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_exec(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "ORDER BY 1",
 callback, , 
   );
@@ -2055,7 +2059,7 @@ static int do_meta_command(char *zLine,
   "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
   " FROM sqlite_master UNION ALL"
   "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
-  "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
+  "WHERE lower(tbl_name) LIKE shellstatic() AND type!='meta' AND sql
NOTNULL "
   "ORDER BY substr(type,2,1), name",
   callback, , );
 zShellStatic = 0;
@@ -2130,13 +2134,15 @@ static int do_meta_command(char *zLine,
 , , 0, 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; j++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_get_table(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "ORDER BY 1",
 , , 0, 
   );

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

[sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Two alternative patches, choose whichever you like.

Alternative 1: (IMO, preferred; tested)
Don't lowercase argument of .schema.
With PRAGMA case_sensitive_like = ON, you just need to use right case for table
names.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 14:00:50.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 14:01:14.0 +0400
@@ -2018,9 +2018,7 @@ static int do_meta_command(char *zLine,
 data.showHeader = 0;
 data.mode = MODE_Semi;
 if( nArg>1 ){
-  int i;
-  for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]);
-  if( strcmp(azArg[1],"sqlite_master")==0 ){
+  if( sqlite3_strnicmp(azArg[1],"sqlite_master",13+1)==0 ){
 char *new_argv[2], *new_colv[2];
 new_argv[0] = "CREATE TABLE sqlite_master (\n"
   "  type text,\n"
@@ -2034,7 +2032,7 @@ static int do_meta_command(char *zLine,
 new_colv[1] = 0;
 callback(, 1, new_argv, new_colv);
 rc = SQLITE_OK;
-  }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){
+  }else if( sqlite3_strnicmp(azArg[1],"sqlite_temp_master",18+1)==0 ){
 char *new_argv[2], *new_colv[2];
 new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
   "  type text,\n"
=

Alternative 2: (partially tested)
Explicitly use case-insensitive comparison for table/indexes, no matter what
case_sensitive_like is.

Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 13:52:44.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 13:54:13.0 +0400
@@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
 }else{
   int i;
   for(i=1; iout, p->db,
   "SELECT sql FROM sqlite_master "
   "WHERE sql NOT NULL"
   "  AND type IN ('index','trigger','view')"
-  "  AND tbl_name LIKE shellstatic()", 0
+  "  AND lower(tbl_name) LIKE shellstatic()", 0
 );
 zShellStatic = 0;
   }
@@ -1790,13 +1792,15 @@ static int do_meta_command(char *zLine,
 callback, , 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; i++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_exec(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "ORDER BY 1",
 callback, , 
   );
@@ -2055,7 +2059,7 @@ static int do_meta_command(char *zLine,
   "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
   " FROM sqlite_master UNION ALL"
   "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
-  "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
+  "WHERE lower(tbl_name) LIKE shellstatic() AND type!='meta' AND sql
NOTNULL "
   "ORDER BY substr(type,2,1), name",
   callback, , );
 zShellStatic = 0;
@@ -2130,13 +2134,15 @@ static int do_meta_command(char *zLine,
 , , 0, 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; i++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_get_table(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() 

Re: [sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

[Yuriy Kaminskiy]

Jeremy Evans wrote:
> On Sat, Oct 15, 2011 at 11:46 PM, Yuriy Kaminskiy <yum...@mail.ru> wrote:
> 7> Whoops, patch eaten by hungry ewoks. Hopefully, inlining will work better:
>> Subject: fix false "ambiguous column" detection in multiple JOIN USING
>>
>> Instead of skipping only *next* table, we ignore matches when we have exactly
>> one match before and we joined to *previous* table with JOIN USING/NATURAL 
>> JOIN.
>> So,
>> CREATE TABLE a(i, j);
>> CREATE TABLE b(j);
>> CREATE TABLE c(i);
>> CREATE TABLE d(j);
>> SELECT * FROM a JOIN b USING(j) JOIN c USING(i) JOIN d USING(j)
>> should work properly.
>>
>> The author or authors of this code dedicate any and all copyright interest
>> in this code to the public domain. We make this dedication for the benefit
>> of the public at large and to the detriment of our heirs and successors.
>> We intend this dedication to be an overt act of relinquishment in perpetuity
>> of all present and future rights to this code under copyright law.
>>
>> Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>
>>
>> Index: sqlite3-3.7.8/src/resolve.c
>> ===
>> --- sqlite3-3.7.8.orig/src/resolve.c2011-10-16 09:18:20.0 +0400
>> +++ sqlite3-3.7.8/src/resolve.c 2011-10-16 09:39:53.0 +0400
>> @@ -190,33 +190,34 @@ static int lookupName(
>> for(j=0, pCol=pTab->aCol; jnCol; j++, pCol++){
>>   if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
>> IdList *pUsing;
>> -cnt++;
>> -pExpr->iTable = pItem->iCursor;
>> -pExpr->pTab = pTab;
>> -pMatch = pItem;
>> -pSchema = pTab->pSchema;
>> -/* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY 
>> */
>> -pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
>> -if( inSrc-1 ){
>> -  if( pItem[1].jointype & JT_NATURAL ){
>> +if( cnt == 1 ){

out of paranoia:
   +  assert( i>0 );
   +  assert( pItem->jointype!=0 );
(but both should be completely impossible)

>> +  /* We already met this name once in some previous table(s),
>> +  ** but... */
>> +  if( pItem->jointype & JT_NATURAL ){
>> /* If this match occurred in the left table of a natural 
>> join,
>> ** then skip the right table to avoid a duplicate match */
>> -pItem++;
>> -i++;
>> -  }else if( (pUsing = pItem[1].pUsing)!=0 ){
>> +continue;
>> +  }else if( (pUsing = pItem->pUsing)!=0 ){
>> /* If this match occurs on a column that is in the USING 
>> clause
>> ** of a join, skip the search of the right table of the join
>> ** to avoid a duplicate match there. */
>> int k;
>> for(k=0; knId; k++){
>>   if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){
>> -pItem++;
>> -i++;
>> break;
>>   }
>> }
>> +if( k!=pUsing->nId )
>> +  continue;
fwiw, I think both "continue;" can be replaced with "break;"
>>   }
>> }
>> +cnt++;
>> +pExpr->iTable = pItem->iCursor;
>> +pExpr->pTab = pTab;
>> +pMatch = pItem;
>> +pSchema = pTab->pSchema;
>> +/* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY 
>> */
>> +pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
>> break;
>>   }
>> }
> 
> I tried this patch and it does appear to fix the issue, but I'm also
> getting occasional segfaults in lookupName after applying it.

Thanks for testing.
Have no idea what can trigger sigsegv here.
Patch applied with any rejects/offsets/fuzziness?

`make test` shown no problem. `make fulltest` got some problems:

prepare.analyze3-1.1.8...
Expected: [999 999 499500]
 Got: [2000 0 499500]
prepare.analyze3-1.1.9...
Expected: [999 999 499500]
 Got: [2000 0 499500]
...
11 errors out of 2630788 tests
Failures on these tests: prepare.analyze3-1.1.8 prepare.analyze3-1.1.9
prepare.analyze3-1.2.8 prepare.analyze3-1.2.9 prepare.analyze3-1.3.8
prepare.analyze3-1.3.9 prepare.analyze3-2.4 prepare.analyze3-2.6
prepare.analyze3-2.7 prepare.analyze3-2.8 prepare.analyze3-2.9

... but they fails for me even with patch reverted.

And no segfaults.

Running few simple tests under valgrind have not produced anything suspicious.

> Haven't built a debug version of SQLite yet to determine exactly where.

I think SQL statement triggering segv would be enough.

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

[sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> Jeremy Evans wrote:
>>> After being open for more than 2 years, this ticket
>>> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1)
>>> was closed by Dr. Hipp with the comment:
>>>
>>> "The column name is ambiguous. Does it mean a.a or b.a? The result is
>>> the same either way, but I don't expect the parser to know this."
>>>
>>> Here's the SQL from the ticket:
>>>
>>> 1) CREATE TABLE a (a INTEGER);
>>> 2) CREATE TABLE b (a INTEGER);
>>> 3) CREATE TABLE c (a INTEGER);
>>> 4) SELECT * FROM a JOIN b USING (a);
>>> 5) SELECT * FROM a JOIN b USING (a) JOIN c USING (a);
>>> 6) SELECT * FROM a JOIN b USING (a) WHERE a = 1;
>>> 7) SELECT * FROM a JOIN b USING (a) JOIN c USING (a) WHERE a = 1;
>>>
>>> Note that SQLite only considers statement 7 invalid.  If column a was
>>> really ambiguous, statement 6 should also be invalid, but it is not.
>>> If Dr. Hipp's statement was correct, SQLite should consider statement
>>> 6 invalid as well.  The error is obviously in the predicate in
>>> statement 7 and not in the USING clause, since statement 5 is
>>> considered valid.
>>>
>>> I don't think column a in the predicate should be ambiguous in either
>>> statement 6 or 7, and it isn't on any other database I've tried that
>>> supports JOIN USING.  The columns in the USING clause (>> list> in the SQL 2003 standard) are supposed to be treated as if they
>>> were a single column shared by both tables.  The SQL 2003 standard
>>> covers this in ISO/IEC 9075-2:2003 (E) Section 7.7 ,
>>> where it explains that statement 6 should be treated as:
>>>
>>> SELECT coalesce(a.a, b.a) AS a FROM a, b WHERE a.a = b.a AND a = 1
>>>
>>> It doesn't explicitly give an example of a three table JOIN USING, but
>>> intuitively the columns in the USING clause shouldn't be ambiguous in
>>> the three table case if they aren't in the two table case.
>>>
>>> This combining of columns from multiple tables and treating them as a
>>> single column is reflected in the output of Statements 4-6, which only
>>> include a single column and not a column from each table.
>>>
>>> I would reopen the ticket to ask for an explanation, but that does not
>>> appear to be possible, which is why I'm asking here.  Can someone
>>> explain whether they think SQLite's current behavior for this case is
>>> correct, and why?
>> ... and according to sqlite documentation:
>> === begin quote lang_select.html ===
>> For each pair of columns identified by a USING clause, the column from 
>> the
>> right-hand dataset is omitted from the joined dataset. This is the only
>> difference between a USING clause and its equivalent ON constraint.
>> === end quote ===
>> So, I agree - "a" IMO, *should not* be ambiguous - with USING/NATURAL JOIN 
>> "a"
>> without qualifiers should expand to (only) LHS a, that is - a.a.
>>
>> BTW, I've seen same effect with ORDER BY, but only in specific conditions
>> (I renamed here "a" field to "i" to reduce possible confusion with table 
>> name):
>>
>> 8) SELECT * FROM a JOIN b USING (i) ORDER BY i; -- works
>> 9) SELECT 1 FROM a JOIN b USING (i) ORDER BY i; -- works
>> 10) SELECT * FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- works
>> 11) SELECT 1 FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
>> Error: ambiguous column name: i
>> 12) SELECT a.i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
>> Error: ambiguous column name: i
>> 12) SELECT a.i AS i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; 
>> --works
>>
>> Exactly same effect with GROUP BY.
>>
>> This is certainly bug. Either it should fail every time (in 6--11), or it 
>> should
>> work in all cases.
> 
> Looked at code, I think I found this bug origin.
> 
> resolve.c:
> static int lookupName(
>   Parse *pParse,   /* The parsing context */
>   const char *zDb, /* Name of the database containing table, or NULL */
>   const char *zTab,/* Name of table containing column, or NULL */
>   const char *zCol,/* Name of the column. */
>   NameContext *pNC,/* The name context used to resolve the name */
>   Expr *pExpr  /* Make this EXPR node point to the selected column */
> ){
> [...]
> if( inSrc-1 ){
>   if( pItem[1].joint

[sqlite] [patch] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Jeremy Evans wrote:
>> After being open for more than 2 years, this ticket
>> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1)
>> was closed by Dr. Hipp with the comment:
>>
>> "The column name is ambiguous. Does it mean a.a or b.a? The result is
>> the same either way, but I don't expect the parser to know this."
>>
>> Here's the SQL from the ticket:
>>
>> 1) CREATE TABLE a (a INTEGER);
>> 2) CREATE TABLE b (a INTEGER);
>> 3) CREATE TABLE c (a INTEGER);
>> 4) SELECT * FROM a JOIN b USING (a);
>> 5) SELECT * FROM a JOIN b USING (a) JOIN c USING (a);
>> 6) SELECT * FROM a JOIN b USING (a) WHERE a = 1;
>> 7) SELECT * FROM a JOIN b USING (a) JOIN c USING (a) WHERE a = 1;
>>
>> Note that SQLite only considers statement 7 invalid.  If column a was
>> really ambiguous, statement 6 should also be invalid, but it is not.
>> If Dr. Hipp's statement was correct, SQLite should consider statement
>> 6 invalid as well.  The error is obviously in the predicate in
>> statement 7 and not in the USING clause, since statement 5 is
>> considered valid.
>>
>> I don't think column a in the predicate should be ambiguous in either
>> statement 6 or 7, and it isn't on any other database I've tried that
>> supports JOIN USING.  The columns in the USING clause (> list> in the SQL 2003 standard) are supposed to be treated as if they
>> were a single column shared by both tables.  The SQL 2003 standard
>> covers this in ISO/IEC 9075-2:2003 (E) Section 7.7 ,
>> where it explains that statement 6 should be treated as:
>>
>> SELECT coalesce(a.a, b.a) AS a FROM a, b WHERE a.a = b.a AND a = 1
>>
>> It doesn't explicitly give an example of a three table JOIN USING, but
>> intuitively the columns in the USING clause shouldn't be ambiguous in
>> the three table case if they aren't in the two table case.
>>
>> This combining of columns from multiple tables and treating them as a
>> single column is reflected in the output of Statements 4-6, which only
>> include a single column and not a column from each table.
>>
>> I would reopen the ticket to ask for an explanation, but that does not
>> appear to be possible, which is why I'm asking here.  Can someone
>> explain whether they think SQLite's current behavior for this case is
>> correct, and why?
> 
> ... and according to sqlite documentation:
> === begin quote lang_select.html ===
> For each pair of columns identified by a USING clause, the column from the
> right-hand dataset is omitted from the joined dataset. This is the only
> difference between a USING clause and its equivalent ON constraint.
> === end quote ===
> So, I agree - "a" IMO, *should not* be ambiguous - with USING/NATURAL JOIN "a"
> without qualifiers should expand to (only) LHS a, that is - a.a.
> 
> BTW, I've seen same effect with ORDER BY, but only in specific conditions
> (I renamed here "a" field to "i" to reduce possible confusion with table 
> name):
> 
> 8) SELECT * FROM a JOIN b USING (i) ORDER BY i; -- works
> 9) SELECT 1 FROM a JOIN b USING (i) ORDER BY i; -- works
> 10) SELECT * FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- works
> 11) SELECT 1 FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
> Error: ambiguous column name: i
> 12) SELECT a.i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
> Error: ambiguous column name: i
> 12) SELECT a.i AS i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; 
> --works
> 
> Exactly same effect with GROUP BY.
> 
> This is certainly bug. Either it should fail every time (in 6--11), or it 
> should
> work in all cases.

Looked at code, I think I found this bug origin.

resolve.c:
static int lookupName(
  Parse *pParse,   /* The parsing context */
  const char *zDb, /* Name of the database containing table, or NULL */
  const char *zTab,/* Name of table containing column, or NULL */
  const char *zCol,/* Name of the column. */
  NameContext *pNC,/* The name context used to resolve the name */
  Expr *pExpr  /* Make this EXPR node point to the selected column */
){
[...]
if( inSrc-1 ){
  if( pItem[1].jointype & JT_NATURAL ){
/* If this match occurred in the left table of a natural join,
** then skip the right table to avoid a duplicate match */
pItem++;
i++;
  }else if( (pUsing = pItem[1].pUsing)!=0 ){
/* If this match occurs on a column that is in the USING clause
** of a join, skip the search of the right

Re: [sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

[Yuriy Kaminskiy]

Jeremy Evans wrote:
> After being open for more than 2 years, this ticket
> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1)
> was closed by Dr. Hipp with the comment:
> 
> "The column name is ambiguous. Does it mean a.a or b.a? The result is
> the same either way, but I don't expect the parser to know this."
> 
> Here's the SQL from the ticket:
> 
> 1) CREATE TABLE a (a INTEGER);
> 2) CREATE TABLE b (a INTEGER);
> 3) CREATE TABLE c (a INTEGER);
> 4) SELECT * FROM a JOIN b USING (a);
> 5) SELECT * FROM a JOIN b USING (a) JOIN c USING (a);
> 6) SELECT * FROM a JOIN b USING (a) WHERE a = 1;
> 7) SELECT * FROM a JOIN b USING (a) JOIN c USING (a) WHERE a = 1;
> 
> Note that SQLite only considers statement 7 invalid.  If column a was
> really ambiguous, statement 6 should also be invalid, but it is not.
> If Dr. Hipp's statement was correct, SQLite should consider statement
> 6 invalid as well.  The error is obviously in the predicate in
> statement 7 and not in the USING clause, since statement 5 is
> considered valid.
>
> I don't think column a in the predicate should be ambiguous in either
> statement 6 or 7, and it isn't on any other database I've tried that
> supports JOIN USING.  The columns in the USING clause ( list> in the SQL 2003 standard) are supposed to be treated as if they
> were a single column shared by both tables.  The SQL 2003 standard
> covers this in ISO/IEC 9075-2:2003 (E) Section 7.7 ,
> where it explains that statement 6 should be treated as:
> 
> SELECT coalesce(a.a, b.a) AS a FROM a, b WHERE a.a = b.a AND a = 1
> 
> It doesn't explicitly give an example of a three table JOIN USING, but
> intuitively the columns in the USING clause shouldn't be ambiguous in
> the three table case if they aren't in the two table case.
> 
> This combining of columns from multiple tables and treating them as a
> single column is reflected in the output of Statements 4-6, which only
> include a single column and not a column from each table.
> 
> I would reopen the ticket to ask for an explanation, but that does not
> appear to be possible, which is why I'm asking here.  Can someone
> explain whether they think SQLite's current behavior for this case is
> correct, and why?

... and according to sqlite documentation:
=== begin quote lang_select.html ===
For each pair of columns identified by a USING clause, the column from the
right-hand dataset is omitted from the joined dataset. This is the only
difference between a USING clause and its equivalent ON constraint.
=== end quote ===
So, I agree - "a" IMO, *should not* be ambiguous - with USING/NATURAL JOIN "a"
without qualifiers should expand to (only) LHS a, that is - a.a.

BTW, I've seen same effect with ORDER BY, but only in specific conditions
(I renamed here "a" field to "i" to reduce possible confusion with table name):

8) SELECT * FROM a JOIN b USING (i) ORDER BY i; -- works
9) SELECT 1 FROM a JOIN b USING (i) ORDER BY i; -- works
10) SELECT * FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- works
11) SELECT 1 FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
Error: ambiguous column name: i
12) SELECT a.i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS!
Error: ambiguous column name: i
12) SELECT a.i AS i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; --works

Exactly same effect with GROUP BY.

This is certainly bug. Either it should fail every time (in 6--11), or it should
work in all cases.

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