Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

At 06:29 05/12/2016, you wrote:

My app supports sqlite3, SQL Server, and PostgreSQL.

SQL Server has a ‘bit’ data type, which accepts 1/0 and 
‘1’/’0’ as valid values.


PostgreSQL has a ‘bool’ data type, which supports a variety of 
values ­ TRUE, ‘t’, ‘true’, ‘yy’, ‘yes’, ‘on’, 
‘1’ for true, and the opposites for false, but does not allow 1/0.


All [three] engines should support (1=1) and (1=0) for true and false, 
respectively, as well as bare columnname as a boolean assertion, like 
Simon said: select ... where columnC and not columnF ...


The choice of literals representing true and false is merely cosmetic.

JcD 


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

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Don V Nielsen 
Sent: Sunday, December 04, 2016 5:15 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

> Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.


Sorry about that. Maybe I over-simplified my example. In practice the case 
statement will never follow the ‘else’ clause, but it will select from a number 
of similar ‘then’ clauses.

Simon has given me enough info to come up with a solution. However, I am still 
a bit surprised at the result.

1) SELECT * FROM TEST WHERE posted = 1;
[(‘inv’, 100, 1)]

2) SELECT * FROM TEST WHERE posted = ‘1’;
[(‘inv’, 100, 1)]

3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
[(‘inv’, 100, 1)]

4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
[]

In 3) and 4), the WHEN clause evaluates to true, so I expected it to treat 1 
and ‘1’ identically, the same way as it does in 1) and 2).

Not important, just curious.

Frank

P.S. Here is the reason for assigning ‘1’ instead of 1.

My app supports sqlite3, SQL Server, and PostgreSQL.

SQL Server has a ‘bit’ data type, which accepts 1/0 and ‘1’/’0’ as valid values.

PostgreSQL has a ‘bool’ data type, which supports a variety of values – TRUE, 
‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’ for true, and the opposites for false, but 
does not allow 1/0.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scope of sqlite3_update_hook?

[Roger Binns]

On 01/12/16 16:51, Jens Alfke wrote:
> If so, then does that include connections in other OS processes? (I'm looking 
> for a way to detect this.)

You can't get a callback when other processes change the database for
many reasons.  However it is possible to detect if the database has changed:

  https://www.sqlite.org/pragma.html#pragma_data_version
  https://www.sqlite.org/fileformat2.html#file_change_counter

In theory some combination of file change notification from the OS (eg
inotify) and inspecting the database should come close to meeting your
needs.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I keep getting seg faults building my database using python sqlite3

[Keith Medcalf]

If pressing the CAPS LOCK or NUM LOCK keys on the keyboard does not toggle the 
light on the keyboard then you have lost the all interrupt processing since 
those keypresses have to be processed by the kernel mode keyboard driver 
toggling the internal state of the keyboard driver, and then the kernel driver 
sends output to the keyboard to change the status LEDs.  Typically (all 
Operating Systems) this means you have suffered a complete kernel crash (or 
halt) and the system is not running.  

Since the system must be running in order to output indicator status, all 
indicators will stay "stuck" in their last known position (hold output).

Only a power-cycle (or hardware reset -- assuming the RESET is a hardware reset 
and not just a request to reset which will be ignored) triggering a reboot will 
restart the system.

The most frequent cause is a Parity Check.  Or in these latter days of not 
having ECC or even Parity checked memory, just an undetected memory fault which 
will cause random AHTBL.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Kevin O'Gorman
> Sent: Sunday, 4 December, 2016 09:21
> To: SQLite mailing list
> Subject: Re: [sqlite] I keep getting seg faults building my database using
> python sqlite3
> 
> Well, the i7 system failed again, but this time it was quite different.
> And peculiar.
> The system wasn't doing anything, but it should have been.  So I tried
> something. It didn't matter what, because I could not get the mouse or
> keyboard to work -- it was like they weren't plugged in.  Really like it,
> because the caps lock light wasn't on, nor was the laser light visible in
> the mouse.  Even when I changed mouse, keyboard and USB slot.  I couldn't
> get in with SSH from elsewhere either.  But the computer's "I'm running"
> light was on.
> So I'm suspecting a partial power failure.  I don't know enough about
> mobos
> and USB to diagnose whether the problem was on the mobo or the power
> supply.
> 
> Creepty.  I had to do a hard reset  to get thing going again, and it's
> been
> running fine for a day now.
> 
> On Mon, Nov 21, 2016 at 9:51 AM, Kevin O'Gorman 
> wrote:
> 
> > On Mon, Nov 21, 2016 at 9:41 AM, Roger Binns 
> > wrote:
> >
> >> On 19/11/16 08:08, Kevin O'Gorman wrote:
> >> > System with problems: Running Xubuntu Linux 16.04.1, Python 3.5.2.
> >> [...]
> >> > System without this problem: Running Ubuntu Linux 14.04.5, Python
> 3.4.3.
> >>
> >> You are good on Python versions then.  My remaining recommendation is
> to
> >> make the process that does SQLite be a child process (ie no making its
> >> own children).  That will eliminate an entire class of potential
> >> problems, although it appears unlikely you are experiencing any of
> them.
> >>
> >> The final option is to run the process under valgrind.  That will
> >> definitively show the cause.  Do note however that you may want to
> >> change some of the default options since you have nice big systems.
> For
> >> example I like to set --freelist-vol and related to very big numbers
> >> (several gigabytes) which ensures that freed memory is not reused for a
> >> long time.  You could also set the valgrind option so that only one
> >> thread is allowed - it will catch inadvertent threading you may note be
> >> aware of.
> >>
> >> Roger
> >>
> >
> > Thanks for that.  I may do the valgrind thing -- it sounds useful.  But
> > just to add
> > to my annoyance about this whole things, I've been having both systems
> > running
> > for a couple of days now with no problems or interruptions.  Remember,
> the
> > i7 system was failing after 2 hours at most.  I did tweak the code a
> > little, but
> > the only thing that seems likely to have stopped the problem is that I
> put
> > in
> > code to do a commit after every 10,000 INSERT statements.  The two
> systems
> > are running identical Python code on the same inputs.  I had intended
> this
> > to
> > verify that one fails and the other does not.  What I got is something
> > different,
> > but on balance I like it best when my processes do not fail out.  Maybe
> > this
> > time the code will finish (at this rate it will be at least a week,
> maybe
> > three.
> >
> > --
> > #define QUESTION ((bb) || (!bb)) /* Shakespeare */
> >
> 
> 
> 
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] I keep getting seg faults building my database using python sqlite3

[Kevin O'Gorman]

Well, the i7 system failed again, but this time it was quite different.
And peculiar.
The system wasn't doing anything, but it should have been.  So I tried
something. It didn't matter what, because I could not get the mouse or
keyboard to work -- it was like they weren't plugged in.  Really like it,
because the caps lock light wasn't on, nor was the laser light visible in
the mouse.  Even when I changed mouse, keyboard and USB slot.  I couldn't
get in with SSH from elsewhere either.  But the computer's "I'm running"
light was on.
So I'm suspecting a partial power failure.  I don't know enough about mobos
and USB to diagnose whether the problem was on the mobo or the power supply.

Creepty.  I had to do a hard reset  to get thing going again, and it's been
running fine for a day now.

On Mon, Nov 21, 2016 at 9:51 AM, Kevin O'Gorman 
wrote:

> On Mon, Nov 21, 2016 at 9:41 AM, Roger Binns 
> wrote:
>
>> On 19/11/16 08:08, Kevin O'Gorman wrote:
>> > System with problems: Running Xubuntu Linux 16.04.1, Python 3.5.2.
>> [...]
>> > System without this problem: Running Ubuntu Linux 14.04.5, Python 3.4.3.
>>
>> You are good on Python versions then.  My remaining recommendation is to
>> make the process that does SQLite be a child process (ie no making its
>> own children).  That will eliminate an entire class of potential
>> problems, although it appears unlikely you are experiencing any of them.
>>
>> The final option is to run the process under valgrind.  That will
>> definitively show the cause.  Do note however that you may want to
>> change some of the default options since you have nice big systems.  For
>> example I like to set --freelist-vol and related to very big numbers
>> (several gigabytes) which ensures that freed memory is not reused for a
>> long time.  You could also set the valgrind option so that only one
>> thread is allowed - it will catch inadvertent threading you may note be
>> aware of.
>>
>> Roger
>>
>
> Thanks for that.  I may do the valgrind thing -- it sounds useful.  But
> just to add
> to my annoyance about this whole things, I've been having both systems
> running
> for a couple of days now with no problems or interruptions.  Remember, the
> i7 system was failing after 2 hours at most.  I did tweak the code a
> little, but
> the only thing that seems likely to have stopped the problem is that I put
> in
> code to do a commit after every 10,000 INSERT statements.  The two systems
> are running identical Python code on the same inputs.  I had intended this
> to
> verify that one fails and the other does not.  What I got is something
> different,
> but on balance I like it best when my processes do not fail out.  Maybe
> this
> time the code will finish (at this rate it will be at least a week, maybe
> three.
>
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
>



-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Don V Nielsen]

Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.

My recommendation is this. Make [posted] a varchar(1) with only two valid
values: 'y' and 'n'. Then rewrite your logic to work with 'y' and 'n' and
see if that works across every database. It is much more explicit, cleaner,
and does not rely on any underlying interpretations.

Just my opinion. Merry Christmas.
dvn

On Sun, Dec 4, 2016 at 2:46 AM, Frank Millman  wrote:

>
> From: Simon Slavin
> Sent: Sunday, December 04, 2016 10:26 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Problem with CASE in WHERE clause
>
>
> > On 4 Dec 2016, at 6:55am, Frank Millman  wrote:
> >
> > > If a column contains a ‘1’, I would expect sqlite3 to return true when
> testing for ‘1’, but in my example it returns false.
> >
> > I think I’ve found the problem ...
> >
>
> Thank you very much for your explanation, Simon.
>
> My live situation is a bit more complex than my example, so I will have to
> experiment to find the ideal solution.
>
> But you have given me the information I need to move forward – much
> appreciated.
>
> Frank
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Simon Slavin 
Sent: Sunday, December 04, 2016 10:26 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause


> On 4 Dec 2016, at 6:55am, Frank Millman  wrote:
> 
> > If a column contains a ‘1’, I would expect sqlite3 to return true when 
> > testing for ‘1’, but in my example it returns false.
> 
> I think I’ve found the problem ...
> 

Thank you very much for your explanation, Simon.

My live situation is a bit more complex than my example, so I will have to 
experiment to find the ideal solution.

But you have given me the information I need to move forward – much appreciated.

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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 4 Dec 2016, at 6:55am, Frank Millman  wrote:

> If a column contains a ‘1’, I would expect sqlite3 to return true when 
> testing for ‘1’, but in my example it returns false.

I think I’ve found the problem.  Here is your syntax:

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = '1'

SQLite does not have a BOOL type.  Instead it uses the integers 0 and 1 to 
indicate boolean values.  So it interprets your "posted" column as wanting to 
store integers. So this command returns …

SELECT posted,typeof(posted),posted='1' FROM test_1; <— [1, integer, 1]

So the result of your CASE expression can be 1, but not '1'.  And in SQLite …

SELECT 1='1'; <— [0]

You might like to use the following syntax instead:

SELECT * FROM test_1 WHERE tran_type = 'inv' AND posted;

This should work in both SQLite and PostgreSQL, and be extremely fast if you 
have an index on (tran_type,posted).

If you absolutely must let the SELECT stay as it is, declare the "posted" 
column as TEXT, not BOOL.  Then your original SELECT statement should work as 
intended.

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