Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

2014-01-09 Thread Dominique Devienne 
On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N  wrote:

> I am seeing a change in the results returned for a query in SQLIte 3.8.2
> version. The query used to return expected results with 3.7.7 and when I
> updated to 3.8.2, it changed the results.
>
> Here is a test case:
>
> CREATE TABLE test(name text, value text);
> insert into test values ('first', 't');
> insert into test values ('second', 'T');
>
> Now execute the following query in 3.7.7 version:
> sqlite> select lower(value) as value from test group by value;
> t
>

[DD] that's not what I'd expect. This looks like 'select lower(value) as
value from test group by lower(value)' to me. i.e. group first, then lower
the grouped values.


> Now execute the same query in 3.8.2 which returns
> t
> t
>

[DD] This is what I expect from your query, so it's more a bug fix IMHO.
Someone will soon point out exactly what commit/bug was fixed in SQLite I'm
sure.


> I can fix it by changing the query like to one the following:
> select lower(value) as value1 from test group by value1;
> select lower(value) as value from test group by lower(value);
>
> But I am not sure if this is expected. Is this a bug or should I change
> the query like the above?
>

[DD] Fix the query IMHO, but using the second one, as Oracle doesn't allow
using the select clause alias in the group by clause (see below), so this
might be a SQLite thing (or an Oracle thing, not sure. In any case, better
to pick the more portable alternative).


C:\Users\DDevienne>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 9 09:32:10 2014
... Connected to: ... Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - 64bit Production
SQL> create table test(name varchar2(64), value varchar2(64));
Table created.

SQL> insert into test values ('first', 't');
1 row created.

SQL> insert into test values ('second', 'T');
1 row created.

SQL> select lower(value) from test group by value;
LOWER(VALUE)

t
t

SQL> select lower(value) from test group by lower(value);
LOWER(VALUE)

t

SQL> select lower(value) as lvalue from test group by lvalue;
select lower(value) as lvalue from test group by lvalue
 *
ERROR at line 1:
ORA-00904: "LVALUE": invalid identifier
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

2014-01-09 Thread Dan Kennedy 

On 01/09/2014 03:38 PM, Dominique Devienne wrote:

On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N  wrote:


I am seeing a change in the results returned for a query in SQLIte 3.8.2
version. The query used to return expected results with 3.7.7 and when I
updated to 3.8.2, it changed the results.

Here is a test case:

 CREATE TABLE test(name text, value text);
 insert into test values ('first', 't');
 insert into test values ('second', 'T');

Now execute the following query in 3.7.7 version:
 sqlite> select lower(value) as value from test group by value;
 t


[DD] that's not what I'd expect. This looks like 'select lower(value) as
value from test group by lower(value)' to me. i.e. group first, then lower
the grouped values.



Now execute the same query in 3.8.2 which returns
 t
 t


[DD] This is what I expect from your query, so it's more a bug fix IMHO.
Someone will soon point out exactly what commit/bug was fixed in SQLite I'm
sure.


Probably this:

  http://www.sqlite.org/src/info/f2d175f975

Fix first appeared in 3.8.0.

Dan.




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

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

2014-01-09 Thread Dominique Devienne 
On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy  wrote:

> On 01/09/2014 03:38 PM, Dominique Devienne wrote:
>
>> Someone will soon point out exactly what commit/bug was fixed in SQLite
>> I'm sure.
>>
>
> Probably this:
>   http://www.sqlite.org/src/info/f2d175f975
>
> Fix first appeared in 3.8.0.


Thanks Dan. FWIW, the test cases added look more complex than the one from
this report. And the comments I read do seem to indicate that the ability
to use output column names ("as" aliases) in later clauses like group-by is
an SQLite extension over SQL indeed. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

2014-01-09 Thread Dominique Devienne 
On Thu, Jan 9, 2014 at 10:18 AM, Dominique Devienne wrote:

> On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy wrote:
>
>> On 01/09/2014 03:38 PM, Dominique Devienne wrote:
>>
>>> Someone will soon point out exactly what commit/bug was fixed in SQLite
>>> I'm sure.
>>>
>>
>> Probably this:
>>   http://www.sqlite.org/src/info/f2d175f975
>>
>> Fix first appeared in 3.8.0.
>>
>
> Thanks Dan. FWIW, the test cases added look more complex than the one from
> this report. And the comments I read do seem to indicate that the ability
> to use output column names ("as" aliases) in later clauses like group-by is
> an SQLite extension over SQL indeed. --DD
>

Why made me think. There's a page in the doc listing what SQL SQLite does
not support, but is there one about what "extensions" SQLite does support
in its SQL "dialect" which are not standard SQL? This is obviously one such
extension, and such a page (if it does not exist) would help write more
portable SQL in SQLite, if users could be made aware of these subtle
differences. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiler warning: Unused variable ‘pFd'

2014-01-09 Thread Matthias Schmitt 
Hello,

I am using SQLite 3.8.2 for my iOS projects in Xcode 5.0.2. After updating to 
version 3.8.2 I am getting the following compiler warning:

sqlite3.c:28141:13: Unused variable ‘pFd’

When looking into the code I see starting at line 28141:

static int unixUnfetch(sqlite3_file *fd, i64 iOff, void *p){
  unixFile *pFd = (unixFile *)fd;   /* The underlying database file */
  UNUSED_PARAMETER(iOff);

#if SQLITE_MAX_MMAP_SIZE>0
  … the code of the function
#endif
  return SQLITE_OK;
}

Obviously the compiler switch “#if SQLITE_MAX_MMAP_SIZE>0” instructs the 
compiler to ignore the code inside the switch on my platform. As a result the 
variable “pFd” is never used and results in an ugly warning message. Would it 
be better to include the variable declaration inside the compiler switch to 
protect from this warning? So the code would look like this:

static int unixUnfetch(sqlite3_file *fd, i64 iOff, void *p){
#if SQLITE_MAX_MMAP_SIZE>0
  unixFile *pFd = (unixFile *)fd;   /* The underlying database file */
  UNUSED_PARAMETER(iOff);
  … the code of the function
#endif
  return SQLITE_OK;
}

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75 - 0
http://www.mmp.lu




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

Re: [sqlite] Compiler warning: Unused variable 'pFd'

2014-01-09 Thread Hick Gunter 
This is my list of disabled compiler warnings (using GCC in "absolutely most 
pedantic" mode) required to compile sqlite (and even omitting FTS and some 
other stuff).

-Wno-aggregate-return
-Wno-bad-function-cast
-Wno-cast-qual
-Wno-empty-body
-Wno-format-extra-args
-Wno-format-nonliteral
-Wno-format-security
-Wno-old-style-definitions
-Wno-parentheses
-Wno-pointer-sign
-Wno-return-type
-Wno-shadow
-Wno-sign-compare
-Wno-strict-prototypes
-Wno-switch-default
-Wno-undef
-Wno-uninitialized
-Wno-unused-function
-Wno-write-strings

See gcc docs for explanation of which constructs lead to these warnings

-Ursprüngliche Nachricht-
Von: Matthias Schmitt [mailto:freak...@mmp.lu]
Gesendet: Donnerstag, 09. Jänner 2014 11:09
An: General Discussion of SQLite Database
Betreff: [sqlite] Compiler warning: Unused variable 'pFd'

Hello,

I am using SQLite 3.8.2 for my iOS projects in Xcode 5.0.2. After updating to 
version 3.8.2 I am getting the following compiler warning:

sqlite3.c:28141:13: Unused variable 'pFd'

When looking into the code I see starting at line 28141:

static int unixUnfetch(sqlite3_file *fd, i64 iOff, void *p){
  unixFile *pFd = (unixFile *)fd;   /* The underlying database file */
  UNUSED_PARAMETER(iOff);

#if SQLITE_MAX_MMAP_SIZE>0
  . the code of the function
#endif
  return SQLITE_OK;
}

Obviously the compiler switch "#if SQLITE_MAX_MMAP_SIZE>0" instructs the 
compiler to ignore the code inside the switch on my platform. As a result the 
variable "pFd" is never used and results in an ugly warning message. Would it 
be better to include the variable declaration inside the compiler switch to 
protect from this warning? So the code would look like this:

static int unixUnfetch(sqlite3_file *fd, i64 iOff, void *p){ #if 
SQLITE_MAX_MMAP_SIZE>0
  unixFile *pFd = (unixFile *)fd;   /* The underlying database file */
  UNUSED_PARAMETER(iOff);
  . the code of the function
#endif
  return SQLITE_OK;
}

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75 - 0
http://www.mmp.lu




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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] integrity check

2014-01-09 Thread dd 
Hi all,

  Executed integrity check for database before application starts.
Sometimes, it takes 1 minute. Other times, it finishes within 2
seconds. How integrity check works? can somebody explain why it takes
less time.

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

Re: [sqlite] integrity check

2014-01-09 Thread Richard Hipp 
On Thu, Jan 9, 2014 at 8:29 AM, dd  wrote:

> Hi all,
>
>   Executed integrity check for database before application starts.
> Sometimes, it takes 1 minute. Other times, it finishes within 2
> seconds. How integrity check works? can somebody explain why it takes
> less time.
>

PRAGMA integrity_check is suppose to do exactly the same thing every time
it is run.  I don't know why you are seeing a 30x timing difference.  Can
you provide us with a test case?


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

Re: [sqlite] integrity check

2014-01-09 Thread dd 
Applied encryption on top of sqlite. Now, I suspect on encryption.
Thanks for prompt response.

On Thu, Jan 9, 2014 at 5:38 PM, Richard Hipp  wrote:
> On Thu, Jan 9, 2014 at 8:29 AM, dd  wrote:
>
>> Hi all,
>>
>>   Executed integrity check for database before application starts.
>> Sometimes, it takes 1 minute. Other times, it finishes within 2
>> seconds. How integrity check works? can somebody explain why it takes
>> less time.
>>
>
> PRAGMA integrity_check is suppose to do exactly the same thing every time
> it is run.  I don't know why you are seeing a 30x timing difference.  Can
> you provide us with a test case?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

[sqlite] Saw something interesting in the debugger...

2014-01-09 Thread Ward Willats 
I've got a multi-threaded iOS app. Each thread has its own long-lived DB 
connection.

I was debugging a "stuttering" in the UI thread and broke into the debugger 
during one of the pauses.

I found the UI thread and a worker thread, both in the DB, both in the default 
busy handler, both taking a 1 second sleep.

I expected to see a third thread in the DB doing some work while the other two 
waited -- but no such thing.

Now, I could have missed it. A third thread could have gotten in and out by the 
time I broke in.

BUT, I was wondering if there are scenarios where only two threads can bounce 
each other into busy sleep like two bocci balls colliding?

(one thread wanted a read lock, the other a BEGIN EXCLUSIVE write lock)

-- Ward

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

Re: [sqlite] Saw something interesting in the debugger...

2014-01-09 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/01/14 08:53, Ward Willats wrote:
> I found the UI thread and a worker thread, both in the DB, both in the
> default busy handler, both taking a 1 second sleep.
> 
> I expected to see a third thread in the DB doing some work while the
> other two waited -- but no such thing.

The default busy handler (see sqliteDefaultBusyCallback in source) sleeps
for these amount of milliseconds:

  { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };

However on non-Windows if you do not have HAVE_USLEEP defined then it
sleeps with a granularity of one second.  The blocking thread could have
finished after 10ms, but you'll still be stuck in the busy handlers for
another 990ms.

Simply ensure HAVE_USLEEP is defined when building sqlite3.c.  Or add your
own busy handler that sleeps for sub-second amounts of time.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlLO5gYACgkQmOOfHg372QQffACferRzozDtbmZqn+R/fVwxMKtf
rkYAn0oQDoHSne95rciPUlfqPCeN3yCv
=fAg3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Saw something interesting in the debugger...

2014-01-09 Thread Ward Willats 

On Jan 9, 2014, at 10:10 AM, Roger Binns  wrote:
> The default busy handler (see sqliteDefaultBusyCallback in source) sleeps
> for these amount of milliseconds:
> 
>  { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };
> 
> However on non-Windows if you do not have HAVE_USLEEP defined then it
> sleeps with a granularity of one second.  The blocking thread could have
> finished after 10ms, but you'll still be stuck in the busy handlers for
> another 990ms.
> 
> Simply ensure HAVE_USLEEP is defined when building sqlite3.c.  Or add your
> own busy handler that sleeps for sub-second amounts of time.


I was more interested in the scenario than the solution -- but I probably would 
have missed this simple fix and done something elaborate and stupid, so thank 
you very much!

-- Ward

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

Re: [sqlite] Saw something interesting in the debugger...

2014-01-09 Thread E.Pasma 


Op 9 jan 2014, om 19:50 heeft Ward Willats het volgende geschreven:



BUT, I was wondering if there are scenarios where only two threads  
can bounce each other into busy sleep like two bocci balls colliding?


(one thread wanted a read lock, the other a BEGIN EXCLUSIVE write lock)

On Jan 9, 2014, at 10:10 AM, Roger Binns   
wrote:
The default busy handler (see sqliteDefaultBusyCallback in source)  
sleeps

for these amount of milliseconds:

{ 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };

However on non-Windows if you do not have HAVE_USLEEP defined then it
sleeps with a granularity of one second.  The blocking thread could  
have
finished after 10ms, but you'll still be stuck in the busy handlers  
for

another 990ms.

Simply ensure HAVE_USLEEP is defined when building sqlite3.c.  Or  
add your

own busy handler that sleeps for sub-second amounts of time.



I was more interested in the scenario than the solution -- but I  
probably would have missed this simple fix and done something  
elaborate and stupid, so thank you very much!


-- Ward
Indeed there is a scenario where two threads block each other. I think  
this occurs when more than one thread acquires a RESERVED lock. SQLite  
detects deadlock in such case and does not invoke the busy handler. So  
with two threads you can see at most one waiter. If you have two  
waiters the conclusion is clear ..


___
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] fstat warning with SQLite 3.8.1 on Android

2014-01-09 Thread Sascha Sertel 
Some analysis shows that the call to fstat fails because the filename being
passed into it is null.

This seems to be specific to Android as I tried the same code on iOS and it
did not produce the same warning message. I feel it is somehow related to
the temp file storage on Android, but that is only a hunch.

On a somewhat related note, is there a plan going forward for how to
properly set the temp file directory on Android? As previously said I'm
currently using the deprecated pragma, which means at some point with some
SQLite release this pragma could go away entirely...

// Sascha


On Wed, Jan 8, 2014 at 12:11 PM, Richard Hipp  wrote:

> On Tue, Jan 7, 2014 at 3:55 PM, Sascha Sertel  >wrote:
>
> > Hello,
> >
> >
> > I'm hoping someone has any ideas what to do about the error messages I'm
> > getting from SQLite 3.8.1 when run on Android:
> >
> >
> > Getting warning: "cannot fstat db file  "
> >
> > -- please take note that there is a blank inserted for the file name
> >
> >
> > This is on Android 4.3 KitKat with manually compiled (not built-in one)
> > SQLite 3.8.1 (from amalgamation) using PERSIST journaling when writing to
> > db from multiple threads (serialized with mutex) in explicit transaction
> > context.
> >
> >
> > The same transactions are working fine on SQLite 3.7.16 but I would like
> to
> > upgrade to 3.8.x. and solve these issues.
> >
>
> Probably it is "working fine" in 3.8.x too.  The warning has been added.
> But it is just a warning, not an error.
>
> The code to implement the warning is here:
>
> http://www.sqlite.org/src/artifact/abeb9d54036aaea6?ln=1351-1356
>
> Can you run your application in a debugger, or maybe add some "printf()s"
> to the code to try to figure out why the call to fstat() is failing?  At
> the very least, get us a call-stack for when the failure occurs?
>
>
>
> >
> >
> > Any ideas or suggestions are much appreciated!
> >
> >
> > // Sascha
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

[sqlite] Visual Studio 2013 RC vs. RTM issue

2014-01-09 Thread Sascha Sertel 
I don't usually like shooting off an email to the list before having
analyzed the entire problem sufficiently, but given that a whole day was
spent on this already I wanted to see if any of this sounds familiar to
anyone reading this:

In my project I am using SQLite 3.7.16.1 which on Windows gets compiled
into 4 static libraries for Debug and Release in 32 and 64 bit.

Today I realized that the last time I built the binaries was with the
Visual Studio 2013 Release Candidate (RC). Since the RTM has been out for a
while and I rebuilt all my other third party libs already I thought I'll
just quickly (famous last words, I know...) recompile SQLite as well so
everything is on the same compiler version.

To my very surprise some of my own SQLite unit tests started failing with
the RTM compiled binary, but only in 32 bit (in Debug, with all compiler
optimizations turned off). The 64 bit RTM compiled binary works fine, but
32 bit suddenly fails some test. I switched back to the RC binary and all
unit tests succeeded fine. Since I'm using the same project files all
compiler switches and precompiler flags are identical, it looks to me like
there is some change between VS2013 RC and RTM that is causing this.

My unit test does a lot more than run SQLite commands, so I'm working on
isolating a single SQL query that can be rerun by anyone to reproduce this
issue. At first (and second, third...) glance it looks like a date/datetime
timestamp related issue. My code uses a lot of system generated UTC
timestamps in Unix format which in the SQLite query are converted back to
localtime before doing some date operations on it.

I will reply with more details if I can boil it down to a query that
behaves differently between the two binaries.

In the meantime if any of this rings a bell to anyone please let me know,
it could save me some time, as I would prefer not to distribute RC compiled
binaries with my product.

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