Re: [sqlite] Date Search
On 2018/07/05 8:51 PM, dmp wrote:
Given:
SELECT STRFTIME('%s', '2018-01-01');
1514764800
SELECT STRFTIME('%s', '2017-01-01');
1483228800
CREATE TABLE date_types(
id INTEGER PRIMARY KEY,
date_type DATE
);
INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);
date_type: Affinity now Integer.
This does not seem to be possible, without some type of conversion to a
Integer(s)
value(s) then searching in a numeric range for the given TEXT date?
Why not add to the table both the converted Integer date[i.e:
strftime('%s', '2017-01-01')] AND the ISO8601 date [i.e: '2017-01-01
00:00:00']?
That way you can reference either, use any of them for calculations, and
filter/lookup by whichever one suits the occasion best.
CREATE TABLE date_types(
id INTEGER PRIMARY KEY,
INTDate INT,
ISODate DATETIME -- This will be NUMERIC affinity.
);
INSERT INTO "date_types" ("id", "INTDate", "ISODate") VALUES(1, 1514764800,
'2018-01-01 00:00:00');
INSERT INTO "date_types" ("id", "INTDate", "ISODate") VALUES(2, 1483228800,
'2017-01-01 00:00:00');
Then it becomes easy to do:
SELECT "id", "INTDate", "ISODate" FROM "date_types" WHERE "ISODate" LIKE
'2018-%';
and any other date-lookup you can imagine.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typo in window function docs
In section 2, the docs read: The default is: RANGE BETWEEN UNBOUNDED PRECEDING TO CURRENT ROW I believe it should read instead: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Search
You have to make everything the same type, either numeric or text. When
possible though you want to do your conversions on your input constant(s), and
not on the stored values. That way you can use an index on the stored value.
sqlite> create index date_type_index on date_types(date_type);
sqlite> explain query plan
...> select * from date_types
...> where strftime('%s', '2018-01-01') <= date_type
...> and date_type < strftime('s', '2018-01-01', '+1 year');
QUERY PLAN
`--SEARCH TABLE date_types USING COVERING INDEX date_type_index (date_type>?
AND date_type explain query plan SELECT "id", "date_type" FROM "date_types" WHERE
"date_type" LIKE '2018-%-%';
QUERY PLAN
`--SCAN TABLE date_types
sqlite> explain query plan SELECT id, date_type FROM date_types WHERE
date(date_type, 'unixepoch') LIKE '2018-%-%';
QUERY PLAN
`--SCAN TABLE date_types
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of dmp
Sent: Thursday, July 05, 2018 2:51 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Date Search
Given:
SELECT STRFTIME('%s', '2018-01-01');
1514764800
SELECT STRFTIME('%s', '2017-01-01');
1483228800
CREATE TABLE date_types(
id INTEGER PRIMARY KEY,
date_type DATE
);
INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);
date_type: Affinity now Integer.
This does not seem to be possible, without some type of conversion to a
Integer(s)
value(s) then searching in a numeric range for the given TEXT date?
SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%';
danap.
___
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] Time Precision
>SELECT CAST((SELECT (julianday('now', 'localtime') -
>julianday('1970-01-01'))*24*60*60*1000) AS INTEGER);
Are you sure you want to be mixing up timezones?
julianday('1970-01-01') returns the julianday timestamp for 1970-01-01 00:00:00
GMT
julianday('now', 'localtime') returns the julianday timestamp for 'now' in your
current timezone.
The stored result will be the offset in milliseconds from the "wall clock time"
at Greenwich England, to the "wall clock time" at your current location
(excluding travel time by car/train/steamer/airplane). It will have folds
based on the timezone (that is, it will not be monotonic).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of dmp
>Sent: Monday, 2 July, 2018 11:07
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Time Precision
>
>> Igor wrote:
>> select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
>
>> Keith wrote:
>> select (julianday() - 2440587.5) * 86400.0
>
>Both of these got me on my way, Igor's a little more clearer. I'll
>doing a little more checking to insure the solution below is correct,
>but seems good. Thanks.
>
>danap.
>
>Solution:
>
>SELECT CAST(
>(SELECT (julianday('now', 'localtime') -
>julianday('1970-01-01'))*24*60*60*1000)
>AS INTEGER);
>
>___
>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] Date Search
Correct.
You have stored integer Unix Epoch timestamps. You cannot do "string" searches
on integers (at least not ones like what you have asked for, which involves
conversion of an integer representing a Unix Epoch offset to an ISO-8601
string, not to an ordinary "string representation of the integer".
There is no "DATE" type in SQLite3 -- only INTEGER, REAL, TEXT, and BLOB -- and
no magical conversion of integers into ISO-8601 strings or v/v.
That is:
SELECT id, date_type FROM date_types WHERE date_type LIKE '148%';
will work.
If you want your date_type integer to be converted to a date string, you need
to use the function for converting integer unix epoch offsets into ISO-8601
date strings:
SELECT id, date_type FROM date_types WHERE date(date_type, 'unixepoch') LIKE
'2018-%-%';
The usual caveats apply for knowing what your timezone is and handling such
conversions appropriately for your platform (OS).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of dmp
>Sent: Thursday, 5 July, 2018 12:51
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Date Search
>
>Given:
>
>SELECT STRFTIME('%s', '2018-01-01');
>1514764800
>SELECT STRFTIME('%s', '2017-01-01');
>1483228800
>
>CREATE TABLE date_types(
> id INTEGER PRIMARY KEY,
> date_type DATE
>);
>
>INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
>INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);
>
>date_type: Affinity now Integer.
>
>This does not seem to be possible, without some type of conversion to
>a
>Integer(s)
>value(s) then searching in a numeric range for the given TEXT date?
>
>SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE
>'2018-%-%';
>
>danap.
>
>___
>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
[sqlite] Date Search
Given:
SELECT STRFTIME('%s', '2018-01-01');
1514764800
SELECT STRFTIME('%s', '2017-01-01');
1483228800
CREATE TABLE date_types(
id INTEGER PRIMARY KEY,
date_type DATE
);
INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);
date_type: Affinity now Integer.
This does not seem to be possible, without some type of conversion to a
Integer(s)
value(s) then searching in a numeric range for the given TEXT date?
SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%';
danap.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locked problem on Windows 7
Hello all, I am working on a project involving the Python SQLite3 API for SQLite (a compile cache system), and I have started running into "database locked" problems on the Windows 7 Pro machines some of the instances will run on; the Windows 10 instances works without any problems. The database is configured with WAL journaling, and when deployed will have up to 42 active connection at a time. There are three tables, one containing a blob with information about a source file and a time stamp (the timestamp is updated each time the entry is used, the blob may be updated), one with a blob of binary data, and the third have the time stamp for the corresponding entry in the second table (updated each time the blob entry is used). The Python SQLite3 connection is started with a timeout of 100 seconds. The lock problem does not appear when seeding the database, but that process involves compiling the source first, so database updates should be less frequent. The problem occurs when the data is (mostly) only being pulled out of the database for reuse, followed by an update of the time stamp the rows used (to be used when removing old entries). Using a locally built DLL with some printfs I found that it looks like the problem occurs when winLock/winLockFile is called from sqlite3WalClose to obtain an exclusive lock; it seems to fail in the Exclusive lock part of the function. The reported windows error code is 33. For reference, the folder where the database is stored, is excluded from AV scans, the disk drives are all local SSDs. The size of the DB is currently about 4GB. The SQLite DLL version from Python 3.6 (can't use 3.7) is 3.21 (no change with 3.24). I have found a few "workarounds", most of them not remotely satisfactory: - Increase busy_timeout to at least 40 seconds, wal_autocheckpoint to 3000+ seems to work, although I have had failures in this scenario, too. The downside is that this takes (at least) as long as seeding the database, and 5-6 times as long as the system it is supposed to replace, and 10(!) times as long as the corresponding tests on Windows 10 (and 7 times as long as on my own Win10 machine with half the cores). - Use defaults for busy_timeout, wal_autocheckpoint, and use a Windows mutex to lock access to the database during write operation. This *is* much faster than the previous example, but that is the best that can be said for it. It is still 10-20% slower than the old system, takes twice as long as the Win 10 test (and 20-40% longer than tests on my own machine, with half the cores). Trying to use multiple mutexes only took longer. - I tried resuming the operation after a lock, that was also slower than both the old system and the Win 10 system, and it also ran into trouble when a repeated insert operation broke the uniqeness constraint (the transaction had completed and been committed before the database locked error occurred). - Reducing number of parallel jobs to 20 on the 32 (logical) core machine worked (24 failed almost at the end), but the reuse case still took close to 4 times as long as the old system (and 2/3 of the time used for a normal seeding using). OTOH, using 20 jobs on a 40 core Win 10 machine took only twice as long as using all cores (effectively what the old system used). - I also tried to rebuild the database without WAL. Seeding the database took 3 times longer than it did with WAL, and the second stage (reuse) seems to take as long (stopped it when it had taken longer than a normal seed, when it was just 25% finished). (And yes, upgrading to Win 10 may be a possibility, although at least two of the machines was not able to use Win 10 last time I tried.) I don't know if this is a problem caused by a problem in SQLite, or if it as limitation in Windows 7. That halving the number of jobs takes 4 times as long on Win 7 as the old job (which indicates that a all cores would still require double the time of the old system), while the Win 10 version was running just doubling (as expected) the its original time, which was half of the old system's time, seems to indicate that there is a performance issue with SQLite on Windows 7 (and I don't see similar issues with other applications on these machines). Additionally, since the *mutex* option actually got the run time within shouting range of the old system for the data reuse case, my guess is that it is _not_ a file performance problem with Windows 7. OTOH there is still a possibility that there is a Win 7 file locking performance issue, but I doubt it. Does anyone have any suggestions for how to fix/work around this problem without losing too much (or any) efficiency? Thanks in advance. -- Sincerely, Yngve N. Pettersen Vivaldi Technologies AS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check if the new table has been created
Hi, Simon, On Thu, Jul 5, 2018 at 11:45 AM, Simon Slavin wrote: > On 5 Jul 2018, at 4:51pm, Igor Korot wrote: > >> Is there a way to get which command was executed? >> Or which table was added/changed/dropped? > > There is no reason for SQLite to record the information you want. If a > connection you have no control over changes your schema you can't do anything > about it. Well, I can. I just have to do another check thru sqlite_master. But I was hoping for an easier solution... > >> Or the only way is to query sqlite_master? But there is no guarantee >> that the last record in that table with the "'table' || 'view'" condition >> willbe that one that was just created/altered. > > Correct. It could be any row in that table. And they might have DROPped a > table just as easily as CREATing a new one. They might. But I guess it is the limitation of the embedded database - not everything can be done in a simple manner. ;-) > >> 2. During the application run, someone started sqlite3, connects to >> the database and creates a >> brand new table. >> 3. My application will need to pick up the newly created table and continue. > > Why are people creating new tables in a database someone else created ? > That's not a common thing to do. Normally people add records to existing > tables. There are many ways to monitor adding records to an existing table. Not necessary other people. I might as well open the shell and create/drop a table. Thank you. > > Simon. > ___ > 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] Check if the new table has been created
On 5 Jul 2018, at 4:51pm, Igor Korot wrote: > Is there a way to get which command was executed? > Or which table was added/changed/dropped? There is no reason for SQLite to record the information you want. If a connection you have no control over changes your schema you can't do anything about it. > Or the only way is to query sqlite_master? But there is no guarantee > that the last record in that table with the "'table' || 'view'" condition > willbe that one that was just created/altered. Correct. It could be any row in that table. And they might have DROPped a table just as easily as CREATing a new one. > 2. During the application run, someone started sqlite3, connects to > the database and creates a > brand new table. > 3. My application will need to pick up the newly created table and continue. Why are people creating new tables in a database someone else created ? That's not a common thing to do. Normally people add records to existing tables. There are many ways to monitor adding records to an existing table. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
Can't you just use IFNULL to assign a default value? CASE IFNULL( x, -999 ) WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN -999 THEN 55 ELSE 66 END On 5 July 2018 at 11:35, R Smith wrote: > On 2018/07/05 8:44 AM, Simon Slavin wrote: > >> On 5 Jul 2018, at 7:30am, Clemens Ladisch wrote: >> >> The expression "x = x" will fail for NULL, but succeed for everything >>> else. So you can use that to implement a "not-NULL ELSE" >>> >> Wow. That has to be the most counter-intuitive feature of SQLite. I >> understand why it works, but I still don't like it. Thanks for posting it. >> > > That's how it works everywhere, not just in SQLite. NULL has special > handling in that any expression or function that gets touched by a NULL > value immediately returns NULL (except for some aggregates that sometimes > have NULL values among their input populations, which they simply ignore). > > What the OP essentially wants is to test for NULL values, which is > possible using "IS" but not in an equality test (since the expression [ a = > x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL, as > they should), so it cannot use equality testing in the usual way a CASE > executes. > > My typical way to do this is: > > CASE > WHEN x IS NULL THEN ... > WHEN x < 1 THEN ... > WHEN x < 3 THEN ... > WHEN x < 5 THEN ... > ELSE ... > END; > > But I feel like the equality check option can easily be enhanced in SQLite > to have this work: > > CASE x > WHEN IS NULL THEN > WHEN 1 THEN ... > WHEN 3 THEN ... > WHEN 5 THEN ... > END; > > but then it's so little difference from the example above it that I have > never yearned for it - in fact, I never use this latter version due to its > shortcomings in testing anything that is not an equality check (but since > my preference is no measure of its utility, perhaps it's worth considering). > > > Cheers, > Ryan > > PS: Here is a version of the 1st example working: > > WITH C(x) AS ( > SELECT NULL > UNION ALL > SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL > ) > SELECT x, CASE > WHEN x IS NULL THEN 'None' > WHEN x < 1 THEN 'Zero' > WHEN x < 3 THEN 'Small' > WHEN x < 6 THEN 'Medium' > ELSE 'Large' > END AS size > FROM C > ; > > > -- x| size > -- | -- > -- NULL | None > -- 1| Small > -- 2| Small > -- 3| Medium > -- 4| Medium > -- 5| Medium > -- 6| Large > -- 7| Large > -- 8| Large > -- 9| Large > -- 10 | Large > > > > > ___ > 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] Check if the new table has been created
Hi, On Tue, Jun 19, 2018 at 1:56 PM, Richard Hipp wrote: > On 6/19/18, Igor Korot wrote: >> Hi, Wout, >> >> On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens >> wrote: >>> you can query the table with >>> https://www.sqlite.org/pragma.html#pragma_table_info >> >> Let me give you a scenario: >> >> 1. My application connects to the database and performs some >> operations (using C API). >> 2. During the application run, someone started sqlite3, connects to >> the database and creates a >> brand new table. >> 3. My application will need to pick up the newly created table and continue. >> >> Is it easily possible? >> >> There is a sqlite3_*_hook() family of functions, but it looks like >> they won't help with sqlite_master. >> >> Is there a different way? > > Poll the PRAGMA schema_version value and watch for changes. Is there a way to get which command was executed? Or which table was added/changed/dropped? Or the only way is to query sqlite_master? But there is no guarantee that the last record in that table with the "'table' || 'view'" condition will be that one that was just created/altered. Thank you. > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Automatic numbering
I believe this trigger below should work for you. If you insert with the sequence number null, then it'll make it one more than the current max, or 1 if the table's empty. If you explicitly give it a sequence number then the trigger doesn't fire and your specific sequence number either goes in or fails like normal. I put in the "select raise(ignore);" bit so that the original insert won't fail with the not null constraint failure. That might cause issues if there get to be more triggers on the table though. https://www.sqlite.org/lang_createtrigger.html "When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step." create table foo ( date text not null, sequence int not null, something1, something2, something3, primary key (date, sequence) ); create trigger foo_insert_trg before insert on foo for each row when new.date is not null and new.sequence is null begin insert into foo values ( new.date, coalesce((select max(sequence) from foo where date = new.date), 0) + 1, new.something1, new.something2, new.something3 ); select raise(ignore); end; sqlite> delete from foo; sqlite> insert into foo values (date(), null, 'A', 'A', 'A');--empty case sqlite> insert into foo values (date(), null, 'B', 'B', 'B');--with something in there sqlite> select * from foo; QUERY PLAN `--SCAN TABLE foo date|sequence|something1|something2|something3 2018-07-05|1|A|A|A 2018-07-05|2|B|B|B sqlite> insert into foo values (date(), 1, 'C', 'C', 'C');--giving it an existing sequence number Error: UNIQUE constraint failed: foo.date, foo.sequence sqlite> insert into foo values (date(), 7, 'D', 'D', 'D');--giving it a specific new sequence number sqlite> insert into foo values (date(), null, 'E', 'E', 'E');--should continue past the new high sequence number sqlite> select * from foo; QUERY PLAN `--SCAN TABLE foo date|sequence|something1|something2|something3 2018-07-05|1|A|A|A 2018-07-05|2|B|B|B 2018-07-05|7|D|D|D 2018-07-05|8|E|E|E -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof Sent: Thursday, July 05, 2018 12:00 AM To: SQLite mailing list Subject: Re: [sqlite] Automatic numbering 2018-07-05 5:37 GMT+02:00 Simon Slavin : > On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote: > > > I only want to store a date with a record. But it is possible that more > as > > one record will be inserted, so I want to use another field to use as an > > index. So that the first gets an one, the second a two, etc. > > Is this possible, or do I just have to check if there is already a date > and > > fetch the highest index and increase this with one? > > There's no magical shortcut. > > I would create an index on (theDate, dateEventNumber). Then do > > BEGIN > SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1 > [ in your code see whether you got NULL back, substitute 0 ] > INERT INTO MyTable ... ?1 + 1 > COMMIT > > You can combine the two commands into one more complicated thing, but I'd > do that only if I was sure nobody would ever have to figure out why my code > wasn't working. > OK, thank you. I am going to play with it. -- Cecil Westerhof ___ 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] CASE and NULL
On 2018/07/05 8:44 AM, Simon Slavin wrote: On 5 Jul 2018, at 7:30am, Clemens Ladisch wrote: The expression "x = x" will fail for NULL, but succeed for everything else. So you can use that to implement a "not-NULL ELSE" Wow. That has to be the most counter-intuitive feature of SQLite. I understand why it works, but I still don't like it. Thanks for posting it. That's how it works everywhere, not just in SQLite. NULL has special handling in that any expression or function that gets touched by a NULL value immediately returns NULL (except for some aggregates that sometimes have NULL values among their input populations, which they simply ignore). What the OP essentially wants is to test for NULL values, which is possible using "IS" but not in an equality test (since the expression [ a = x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL, as they should), so it cannot use equality testing in the usual way a CASE executes. My typical way to do this is: CASE WHEN x IS NULL THEN ... WHEN x < 1 THEN ... WHEN x < 3 THEN ... WHEN x < 5 THEN ... ELSE ... END; But I feel like the equality check option can easily be enhanced in SQLite to have this work: CASE x WHEN IS NULL THEN WHEN 1 THEN ... WHEN 3 THEN ... WHEN 5 THEN ... END; but then it's so little difference from the example above it that I have never yearned for it - in fact, I never use this latter version due to its shortcomings in testing anything that is not an equality check (but since my preference is no measure of its utility, perhaps it's worth considering). Cheers, Ryan PS: Here is a version of the 1st example working: WITH C(x) AS ( SELECT NULL UNION ALL SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL ) SELECT x, CASE WHEN x IS NULL THEN 'None' WHEN x < 1 THEN 'Zero' WHEN x < 3 THEN 'Small' WHEN x < 6 THEN 'Medium' ELSE 'Large' END AS size FROM C ; -- x | size -- | -- -- NULL | None -- 1 | Small -- 2 | Small -- 3 | Medium -- 4 | Medium -- 5 | Medium -- 6 | Large -- 7 | Large -- 8 | Large -- 9 | Large -- 10 | Large ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On Thursday, 5 July, 2018 00:57, Donald Shepherd : >On Thu, 5 Jul 2018 at 16:45, Simon Slavin >wrote: >> On 5 Jul 2018, at 7:30am, Clemens Ladisch >wrote: >>> The expression "x = x" will fail for NULL, but succeed for >>> everything else. So you can use that to implement a >>> "not-NULL ELSE" >> Wow. That has to be the most counter-intuitive feature of SQLite. >> I understand why it works, but I still don't like it. Thanks for >> posting it. >> Hmm. Yes, "x != x" works too. I forgot to check "x IS NOT x". > I've always thought of that as an SQL thing rather than an SQLite > thing, because SQL Server and PostgreSQL and MySQL all do the same > (or at least so I'm lead to believe in the last two cases). It is a mathematics thing. The NULL value works like that everywhere. Unless the coder/programmer made a boo-boo. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On Thu, 5 Jul 2018 at 16:45, Simon Slavin wrote: > On 5 Jul 2018, at 7:30am, Clemens Ladisch wrote: > > > The expression "x = x" will fail for NULL, but succeed for everything > > else. So you can use that to implement a "not-NULL ELSE" > > Wow. That has to be the most counter-intuitive feature of SQLite. I > understand why it works, but I still don't like it. Thanks for posting it. > > Hmm. Yes, "x != x" works too. I forgot to check "x IS NOT x". > I've always thought of that as an SQL thing rather than an SQLite thing, because SQL Server and PostgreSQL and MySQL all do the same (or at least so I'm lead to believe in the last two cases). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On 5 Jul 2018, at 7:30am, Clemens Ladisch wrote: > The expression "x = x" will fail for NULL, but succeed for everything > else. So you can use that to implement a "not-NULL ELSE" Wow. That has to be the most counter-intuitive feature of SQLite. I understand why it works, but I still don't like it. Thanks for posting it. Hmm. Yes, "x != x" works too. I forgot to check "x IS NOT x". Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
Andy Goth wrote: > The expression "x = NULL" is meaningless since it will always evaluate > to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will > never accomplish anything. > [...] > So I'm wondering: can we do better? The expression "x = x" will fail for NULL, but succeed for everything else. So you can use that to implement a "not-NULL ELSE": CASE x WHEN 1 THEN ... WHEN x THEN 'not NULL' ELSE'NULL' END > [...] > The next evolution in bloat is to also support AND, OR, NOT, and > parentheses, allowing the LHS operand of any operator in a complex > expression to be omitted So you want to have your beloved COBOL features in SQL? ;-) http://www.3kranger.com/HP3000/mpeix/doc3k/B3150090013.11820/65.htm http://www.csis.ul.ie/cobol/course/Selection.htm Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
