[sqlite] Behavior When Comparing NULL Values
On 2/16/16, Denis Burke wrote:
>
> select case when null not in ('a') then 'not in list' else 'is in list'
> end;
>
The expression "null not in ('a')" evaluates to null. So your query
is really: "select case when null then 'not-in-list' else
'is-in-list' end;" Since the condition is not true (because null is
not true) then the else clause is taken. This is correct behavior.
PostgreSQL gets the same answer.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Behavior When Comparing NULL Values
I realize that SQLite will handle NULLs according to:
https://sqlite.org/nulls.html
As best as I can tell this is a case not listed on that page and gives an
answer different than I would expect:
select case when null not in ('a') then 'not in list' else 'is in list' end;
This gives "is in list". But since null is clearly not in the list of one
member ('a'), I would expect this to generate "not in the list".
Is this expected behavior, or a bug?
Thank you,
Denis Burke
[sqlite] Behavior When Comparing NULL Values
On Tuesday, 16 February, 2016 20:33. Denis Burke asked:
> I realize that SQLite will handle NULLs according to:
> https://sqlite.org/nulls.html
> As best as I can tell this is a case not listed on that page and gives an
> answer different than I would expect:
What answer do you expect? Why do you expect that answer?
> select case when null not in ('a') then 'not in list' else 'is in list'
> end;
> This gives "is in list". But since null is clearly not in the list of one
> member ('a'), I would expect this to generate "not in the list".
Faulty reasoning. "null in ('a')" is null, and "null not in ('a')" is null --
operations with null return null.
null is not a not-zero (true) value (it is not any value, it is null), hence
the "then" (true) path is not taken. That leaves the else.
> Is this expected behavior, or a bug?
It is what I would expect.
> Thank you,
> Denis Burke
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance comparison between SQLite and SQL Server?
The quality and helpfulness of this mailing list makes me wish for a SQHeavy...
[sqlite] Performance comparison between SQLite and SQL Server?
IME when SQLite is applicable (mostly the one writer limit), it usually runs circles around to server DBs, provided: - you have comparable CPU - you have enough cache memory, or free RAM for OS disk caching, to be in a comparable memory scenario - you do not have a virtualisation layer, especially compared against a server DB running on bare-metal, as that can make a huge difference in effective IOPS Also the ability of SQLite to smoothly work with temporary tables can make a world of difference when faced with complex queries. That is not something that will show in 1:1 benchmarks, but when a complex query can be broken down into several simpler queries + temporary tables, well, you can save lots on both execution and debugging times. On Tue, Feb 16, 2016 at 3:02 PM, olivier vidal wrote: > > obviously it will never be perfect . We already know the benefits of each > database , including those of SQLITE . This is not because there is a > benchmark that people are not going to look at all the other features. But > in equal hardware, it gives a small indication of current performance, as > sqlite.org had done a long time ago. > > Simon Slavin a ?crit : > >> On what hardware ? Should the SQLite test be on a laptop because SQLite >> will run on a laptop ? Or should you test both on identical hardware even >> though they're unlikely to run on identical hardware ? Should you include >> the time taken to install and configure PostgreSQL in the times quoted >> because it takes no time to install or configure SQLite ? >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Bug: writing to a database in shared cache mode doesn't work if same database was opened in readonly mode previously
Hello,
I connect to the same database file twice, first in readonly mode and
then in readwrite mode while having set sqlite3_enable_shared_cache(1).
Any attempt to change any data using the second database connection
results in an "attempt to write a readonly database" error.
The same code works fine with sqlite3_enable_shared_cache(0).
tested in sqlite version 3.11.0 and 3.10.2
example code:
#include
#include
#include
int main(int argc, char *argv[])
{
sqlite3* dbreadonly;
sqlite3* dbreadwrite1;
sqlite3* dbreadwrite2;
int rc;
const char *pzTail = NULL;
sqlite3_enable_shared_cache(1);// code works with
sqlite3_enable_shared_cache(0);
// create dummy table
rc = sqlite3_open_v2("test.db", &dbreadwrite1,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
const char* sqlreadwrite1 = "CREATE TABLE TEST(ID INTEGER);";
sqlite3_stmt *stmtreadwrite1;
rc = sqlite3_prepare_v2(dbreadwrite1, sqlreadwrite1,
strlen(sqlreadwrite1),&stmtreadwrite1, &pzTail);
rc = sqlite3_step(stmtreadwrite1);
sqlite3_finalize(stmtreadwrite1);
sqlite3_close(dbreadwrite1);
// open readonly database
rc = sqlite3_open_v2("test.db", &dbreadonly, SQLITE_OPEN_READONLY,
NULL);
if( rc ) printf("Can't open readonly database: %s\n",
sqlite3_errmsg(dbreadonly));
// open readwrite database and try to drop table
rc = sqlite3_open_v2("test.db", &dbreadwrite2,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if( rc ) printf("Can't open database: %s\n",
sqlite3_errmsg(dbreadwrite2));
const char* sqlreadwrite2 = "DROP TABLE IF EXISTS TEST";
sqlite3_stmt *stmtreadwrite2;
rc = sqlite3_prepare_v2(dbreadwrite2, sqlreadwrite2,
strlen(sqlreadwrite2),&stmtreadwrite2, &pzTail);
rc = sqlite3_step(stmtreadwrite2); // ** error occurs here
"attempt to write a readonly database"
if( rc != SQLITE_DONE) printf("Can't drop table: %s\n",
sqlite3_errmsg(dbreadwrite2));
sqlite3_finalize(stmtreadwrite2);
sqlite3_close(dbreadwrite2);
sqlite3_close(dbreadonly);
}
--
Regards,
Ludger Kr?mer
[sqlite] applyng schema changes to several databases
On 2/16/16, Roger Binns wrote:
>
> The way I (and many others) do it is to use the user_version. It is
> an integer that starts out as zero, and can be read or written with
> pragma user_version. Startup code then looks like this:
>
> if user_version()==0 {
> BEGIN;
> CREATE TABLE ;
> CREATE INDEX ;
> pragma user_version=1;
> COMMIT;
> }
Another approach is to use the sqlite3_table_column_metadata()
interface to check for the existence of various tables and columns,
and then run whatever CREATE TABLE and ALTER TABLE ADD COLUMN commands
are necessary based on those findings.
Whichever works best for you
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Performance comparison between SQLite and SQL Server?
obviously it will never be perfect . We already know the benefits of each database , including those of SQLITE . This is not because there is a benchmark that people are not going to look at all the other features. But in equal hardware, it gives a small indication of current performance, as sqlite.org had done a long time ago. Simon Slavin a ?crit : > On what hardware ? Should the SQLite test be on a laptop because SQLite will > run on a laptop ? Or should you test both on identical hardware even though > they're unlikely to run on identical hardware ? Should you include the time > taken to install and configure PostgreSQL in the times quoted because it > takes no time to install or configure SQLite ?
[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 14:19:12 -0700 Scott Robison wrote: > Each job will take some amount of time to process. The order doesn't > matter as long as all jobs are eventually processed and you have a > single process running the jobs. Limit 1 is a reasonable way to grab > a single job. Reasonable, perhaps, but not logical. The logical approach is to use a feature in the data to select the "single job". One obvious way in your example would be to use min(jobid) or somesuch. --jkl
[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 14:55:34 -0700 "Keith Medcalf" wrote: > Pretty sure you meant: > > select * from (select min(t) as t from T) as T; Yes, thanks. :-) --jkl
[sqlite] Why SQLite does not use a web-based forum?
On 16 Feb 2016, at 11:08am, Klaas Van B. wrote: > N.N. wrote Mon, 15 Feb 2016 09:19:11 +0800: > >> Why SQLite does not utilize a web-based forum for all users discuss >> problems? I think that will be more convenient and can help more people. > > LinkedIn (https://www.linkedin.com) has two: "SQLite" and "SQLite > Professional" and I'm pretty sure that on sites like StackOverflow, gitHub > etc. there are lots of colleagues willing to discuss SQLite issues. SQLite Professional is an old name for SQLPro. It's an application for editing SQLite databases. It's not SQLite. Simon.
[sqlite] Performance comparison between SQLite and SQL Server?
On 16 Feb 2016, at 6:30am, olivier vidal wrote: > It would be great if someone could redo these tests with the current versions > of the databases. On what hardware ? Should the SQLite test be on a laptop because SQLite will run on a laptop ? Or should you test both on identical hardware even though they're unlikely to run on identical hardware ? Should you include the time taken to install and configure PostgreSQL in the times quoted because it takes no time to install or configure SQLite ? Simon.
[sqlite] applyng schema changes to several databases
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 15/02/16 01:01, Luca Ferrari wrote:
> While I'm pretty sure a simple sheel script that will execute,
> file per file, the alter table (within a transaction) will do the
> job I'm wondering if there's a better approach or a more automated
> one.
The way I (and many others) do it is to use the user_version. It is
an integer that starts out as zero, and can be read or written with
pragma user_version. Startup code then looks like this:
if user_version()==0 {
BEGIN;
CREATE TABLE ;
CREATE INDEX ;
pragma user_version=1;
COMMIT;
}
if user_version()==1 {
BEGIN;
CREATE TABLE ;
ALTER TABLE .;
pragma user_version=2;
COMMIT;
}
if user_version()==2 {
BEGIN;
DELETE INDEX ;
CREATE TABLE ;
pragma user_version=3;
COMMIT;
}
This way the schema will always end up as desired, even if the program
terminated while updating a schema. Even a restored backup will
adjust nicely.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2
iEYEARECAAYFAlbDi78ACgkQmOOfHg372QSgPACgxIpKdSlzUVznymQThe9aqqJM
pGkAnj99zXJbzO1Tm6/uyuIgXCt5jq42
=Y/zw
-END PGP SIGNATURE-
[sqlite] Correlated subquery throwing an error
On Tue, Feb 16, 2016 at 11:44 AM, James K. Lowden wrote: > On Mon, 15 Feb 2016 14:19:12 -0700 > Scott Robison wrote: > > > Each job will take some amount of time to process. The order doesn't > > matter as long as all jobs are eventually processed and you have a > > single process running the jobs. Limit 1 is a reasonable way to grab > > a single job. > > Reasonable, perhaps, but not logical. The logical approach is to use a > feature in the data to select the "single job". One obvious way in > your example would be to use min(jobid) or somesuch. > I have a hard time seeing into the future and understanding the implications of every problem statement in the world to know what the best solution would be to each and every one of them, so you could be right. :) Certainly there are algorithms in this world that depend on randomness as a feature. That being said, my self diagnosed OCD (my particular form is spelled COD, because it should be as symmetrical as possible) would probably require that I pick a very specific deterministic row. Not necessarily because it was right, but so that I could sleep at night. -- Scott Robison
[sqlite] Documentation Typo
On https://www.sqlite.org/datatypes.html there is a typographical error. In the first paragraph of 1.0 Typelessness "A database is suppose to store and retrieve data and it should? the word should be ?supposed"
[sqlite] Why SQLite does not use a web-based forum?
N.N. wrote Mon, 15 Feb 2016 09:19:11 +0800: >Why SQLite does not utilize a web-based forum for all users discuss >problems? I think that will be more convenient and can help more people. LinkedIn (https://www.linkedin.com) has two: "SQLite" and "SQLite Professional" and I'm pretty sure that on sites like StackOverflow, gitHub etc. there are lots of colleagues willing to discuss SQLite issues. Kind regards/Vriendelijke groeten. Klaas `Z4us` Van B., CEO/CIO LI#437429414
[sqlite] Performance comparison between SQLite and SQL Server?
Good thread, which absolutely nails the point 'dev decisions for app cases' make a developers world go round. I personally couldn't think of a greater waste of time than a benchmark comparison between client server rdbms's and sqlite. Do what benefits your case most. The above from Jim pretty much encapsulates my thoughts: "SQLite is not directly comparable to client/server SQL database engines > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to > solve a different problem. Client/server SQL database engines strive to > implement a shared repository of enterprise data. ...SQLite strives to > provide local data storage for individual applications and devices." > I could bang on about my own preferences and decisions I've made but they'd only be reiterating the points made above. They were based on system requirement specs and where local storage was involved it was a blindingly obvious decision to go with sqlite. Rob above made another excellent point often overlooked (usually an afterthought for many dev's): 4. The support is top notch. I have brought and paid for govt scale > databases for governments and to be honest the support for SQLite is just > as good, and to be honest I would say better than Big Red or Big Blue (and > I used to work for Big Blue). > It is another unique property of a great product. Support is not just sqlite specific either (a cop out on many a tech forum) and particularly on this list the topics can be rather broad. There is plenty of good quality feedback and many a good general SQL solution which just adds to the sqlite package as a whole. On 16 February 2016 at 09:42, Jim Callahan wrote: > SQLite would be most comparable to *SQL Server Express LocalDB* edition > which is introduced in this July 2011 blog post > > https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/ > > More uptodate information about *SQL Server Express LocalDB* edition > is in this 2016 Microsoft Developer's Network (MSDN) article > https://msdn.microsoft.com/en-us/library/hh510202.aspx > > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH > "*Situations Where SQLite Works Well*" > > and > > "*Situations Where A Client/Server RDBMS May Work Better*" > http://sqlite.org/whentouse.html > > > Opening lines of whentouse.html: > > "SQLite is not directly comparable to client/server SQL database engines > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to > solve a different problem. Client/server SQL database engines strive to > implement a shared repository of enterprise data. ...SQLite strives to > provide local data storage for individual applications and devices." > > Even Microsoft has adopted SQLite for some limited tasks (such as storing > state) within every shipping copy of Windows 10. > "SQLite is a unique case: it is an open source, externally developed > software that is used by core system components, and our flagship apps like > Cortana and Skype. ...After shipping SQLite as a system component in July, > we wanted to include it in our SDK for November. With more than 20,000 > Windows Apps and more than half of our top apps using SQLite, it made sense > to just make expose the system SQLite to app developers." > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ > > > There is a historical and unfair (specially compiled version of SQLite > against default settings of PostgreSQL) benchmark > available on this page, but now that you understand the use cases, this > particular benchmark is not that useful in addition > to being out of date and unfair. > https://www.sqlite.org/speed.html > > Jim Callahan > Data Scientist > https://www.linkedin.com/in/jamesbcallahan > Orlando, FL > > On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin > wrote: > > > > > On 15 Feb 2016, at 9:41pm, James K. Lowden > > wrote: > > > > > SQL Server has none of those restrictions, and probably keeps pace with > > > SQLite even on its home turf. But the administration of SQL Server is > > > nontrivial. For that reason alone, I would never use it in situations > > > where SQLite would do. > > > > That's the fella. Major advantage of SQLite: zero admin. Not even a > > background task. > > > > Second advantage: you know exactly where you data is. Better still, it's > > simple: one database == one file, and the file has the same name as the > > database. I remember trying to reconstruct a MySQL database from a dead > > server. One folder with a confusing mass of files in. Your database is > > part of some of those files, but the files may be huge even if the one > > database you care about is tiny. That was not a fun time. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >
[sqlite] Performance comparison between SQLite and SQL Server?
It would be great if someone could redo these tests with the current versions of the databases. Jim Callahan a ?crit : > There is a historical and unfair (specially compiled version of SQLite > against default settings of PostgreSQL) benchmark > available on this page, but now that you understand the use cases, this > particular benchmark is not that useful in addition > to being out of date and unfair. > https://www.sqlite.org/speed.html
