Re: [sqlite] Problem with CASE in WHERE clause
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
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?
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
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
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
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
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
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
