Re: [sqlite] Commit fails due to "database is locked" in active transaction

[Lior Okman]

Trevor Talbot wrote:


On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote:

  

I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable),
and I have a scenario using transactions in SQLite3 that is a bit
counter-intuitive.



You'll want to look at http://sqlite.org/lockingv3.html for this.

  

I'll go over this document.

I open an SQLite3 database from two terminals. In the first terminal I
run the following SQLs:



  

 > sqlite> begin;
 > sqlite> insert into a values (null);
 > sqlite> insert into a values (null);



This transaction has acquired a RESERVED (intent to write) lock, at
the first INSERT statement. Others may read, but no others may
announce an intent to write.

  

In the second terminal, I run the following SQLS:



  

 > sqlite> begin;
 > sqlite> insert into a values (null);
 > SQL error: database is locked



This transaction has acquired a SHARED (reading) lock at the first
access to the database. At the INSERT statement, it tries to acquire
RESERVED, but fails because another has already announced its intent
to write. This transaction remains SHARED.

  

I go back to the first terminal at this stage and I try to end the
transaction using commit:



  

 > sqlite> commit;
 > SQL error: database is locked



The second connection still has a SHARED (reading) lock, so this
transaction cannot make any physical changes to the file yet. The
INSERT statements you already executed are buffered internally, so it
did not need to make physical changes before.

  

At this point, I can't commit the transaction in the first terminal,
until I run a commit in the second terminal, even though the first
terminal is the one with the active transaction, and the second terminal
shouldn't have any effect on the active transaction.



The second connection has an active transaction too, just in read-only
state. It must end before the first can proceed with physical changes
to the file.

  

This behaviour varies, depending on the filesystem type on which the
sqlite database file is created in. If I use reiserfs, it sometimes
takes a long while until I can commit from any of the terminals. In
ext3, this is usually resolved after retrying the commit a few times in
both terminals. In tmpfs, there is never any issue, the first terminal
can always commit.

What am I missing here? Is this behaviour the expected one?



Requiring the second transaction to complete first is expected in
terms of SQLIte's concurrency system.
So in terms of using SQLite, I need to close the entire transaction and 
restart it when I get a "database locked" return code in a writer 
thread? It's not enough to just retry the commit in a little while?


Wouldn't it be more intuitive to allow the single handle holding the 
RESERVED lock to finish? Right now, the SQLite behaviour allows only the 
serialized isolation level. Making this change would make the isolation 
level be more like "read committed".



 The fact that you are seeing
changes in behavior depending on the filesystem is disturbing though.
They should all behave the same if they are implementing locking
correctly. I will let others speak to this point; if you can post more
detail on the steps (e.g. if I do "commit" here and "commit" here
nothing happens for N minutes), it will probably help them.
  
If I'm working in a tmpfs partition, the second transaction doesn't hold 
a SHARED lock at all, even though I got the "database is locked" 
message. I am able to commit the first transaction (holding the RESERVED 
lock) without ending the second transaction.


If I'm working in an ext3 or reiserfs partition, the second transaction 
does hold the SHARED lock, and I am not able to commit the first 
transaction without ending the second one first.


Currently my program runs two threads, both attempting to open a 
transaction, insert a single row into a table and commit the 
transaction. This is done using separate connection handles to the 
database - one for each thread, and when I get the SQLITE_BUSY return 
code, I sleep for around 10ms and retry the failed operation. When I'm 
running on a reiserfs based database, the SQLITE_BUSY return code never 
changes, and essentially my program is deadlocked once I get the 
SQLITE_BUSY return code in one of the threads. When I'm running on an 
ext3 based database, the issue resolves itself within a few retries. 
I'll try (maybe later today) to create a tester program that I can post 
to this mailing list that recreates this issue - I can't post my current 
program.


I'll also try to change the "begin" statement to "begin immediate" - 
like Ken suggested in a separate message.

Just to be clear, these are local filesystems, correct? Network mounts
like NFS are not expected to work.
  

These are not network mounts.

Tmpfs is a memory based filesystem. The ext3 and the reiserfs 
filesystems are both local to where I'm running the test.

[sqlite] SQLITE_CORRUPT error

[Doug]

I have a customer that has a database that has somehow become corrupted.
I'm fairly certain he was on v3.4.1 but I'll double check. The database
isn't completely bad, I can look at the master table and one of the two
tables in the database. But if the second table is touched SQLITE_CORRUPT
is returned. Running sqlite3_analyzer returns the following (StatData is
the second table)

Analyzing table StatData...

ERROR: SQLITE_CORRUPT

SQLITE_CORRUPT

while executing

"btree_next $csr"

("foreach" body line 32)

invoked from within

"foreach {name rootpage} [db eval $sql] {

puts stderr "Analyzing table $name..."

# Code below traverses the table being analyzed (table name $name..."

He is running with pragma synchronous off, but I thought that only opened up
the possibility for corruption if there was a power, OS, or hardware
failure, which he doesn't think occurred (it is Windows, and the server was
rebooted at some point.)

I suspect the answer is no, but is there any way to salvage any of the data?

 

Thanks

Doug

 

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

On 2008 Jan, 02, at 15:52, Jay Sprenkle wrote:


Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..


Thank you, Jay.  I kept getting syntax errors when I tried that;  
apparently I don't know how to query the sqlite_master.  But while  
thinking about it I realized that pragma SQL can be executed by the C  
API.  So I did this, and it worked.


Jerry

(It's Objective-C code but anyone who's interested should be able to  
get the idea.)


- (NSArray*)allColumnNamesInTableNamed:(NSString*)tableName {
// Will return nil if fails, empty array if no columns
void* db = [self db] ;  // database, a class instance variable
char* errMsg = NULL ;
int result ;

NSString* statement ;
statement = [[NSString alloc] initWithFormat:@"pragma  
table_info(%@)", tableName] ;

char** results ;
int nRows ;
int nColumns ;
result = sqlite3_get_table(
   db,/* An open database */
   [statement UTF8String], /* SQL to be  
executed */
   ,  /* Result is in char *[]  
that this points to */
   ,/* Number of result rows  
written here */
   , /* Number of result columns  
written here */

   /* Error msg written here */
) ;

[statement release] ;

NSMutableArray* columnNames = nil ;
if (!(result == SQLITE_OK)) {
// Invoke the error handler for this class
[self showError:errMsg from:16 code:result] ;
sqlite3_free(errMsg) ;
}
else {
int j ;
for (j=0; j

[sqlite] Re: sqlite3_get_table(); How to get all column names in C?

[Igor Tandetnik]

Jerry Krinock <[EMAIL PROTECTED]> wrote:

Is there any way to get a list of all column names in a table via the
C API, which works even when there are no data rows in the table?


PRAGMA table_info(tableName);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Date Problems

[John Stanton]

Rich Shepard wrote:

On Thu, 3 Jan 2008, [EMAIL PROTECTED] wrote:

The bottom line is that "one month ago" or "one month from now" is a 
fuzzy

concept since months vary in length.


  This is a very interesting thread since so many business applications are
highly dependent on dates, yet most SQL implementations have comparatively
minimal support. It would be great if SQL itself defined standards and
provided guidelines for complete implementations.

Rich


Do you have ideas?  What functions do you envisage?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: EXISTS and NULLs

[Aristotle Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-01-02 17:50]:
> If you wanted to know if there were non-null entries you would
> say:
> 
>SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

In fact I usually say

EXISTS ( SELECT NULL FROM ... )

in order to emphasize that the row data is of no interest in the
subquery in question.

> Can somebody please confirm that this is the correct behavior
> and that EXISTS does not do any special treatment of NULL
> values?

I have seen the above EXISTS SELECT NULL in several books, with
the collective implication that this construct must work in
MySQL, Postgres, Oracle, DB2, SQL Server and Sybase.

It’s a safe bet that SQLite works as expected.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Date Problems

[Rich Shepard]

On Thu, 3 Jan 2008, [EMAIL PROTECTED] wrote:


The bottom line is that "one month ago" or "one month from now" is a fuzzy
concept since months vary in length.


  This is a very interesting thread since so many business applications are
highly dependent on dates, yet most SQL implementations have comparatively
minimal support. It would be great if SQL itself defined standards and
provided guidelines for complete implementations.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Date Problems

[drh]

<[EMAIL PROTECTED]> wrote:
> Hi
> 
> Can somebody give any explain to this please.
> 
> sqlite> select date("2006-03-31");
> 2006-03-31
> --> correct
> 
> sqlite> select date("2006-03-31", "-1 month");
> 2006-03-03
> --> not correct
> 

"2006-03-31", "-1 month" is "2006-02-31".  Except
"2006-02-31" is really "2006-03-03".

What were you expecting the results to be?  How do
you compute one month before 2006-03-31?  If you
were expecting 2006-02-28, then wouldn't 

2006-03-31 -1 month
2006-03-30 -1 month
2006-03-29 -1 month
2006-03-28 -1 month 

All give the same answer?  That seems wrong.  Or is it?

The bottom line is that "one month ago" or "one month from now"
is a fuzzy concept since months vary in length.

What do you expect to get from this:

2008-02-29 +1 year

Should that be 2009-02-28 or 2009-03-01?  SQLite picks
the latter.  But you could argue either one, I suppose.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Date Problems

[Kees Nuyt]

Hi Craig,

On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:

>Hi
>
>Can somebody give any explain to this please.
>
>sqlite> select date("2006-03-31");
>2006-03-31
>--> correct
>
>sqlite> select date("2006-03-31", "-1 month");
>2006-03-03
>--> not correct
>
>
>Can anyone confirm? Any suggestions / workarounds greatfully received!

Confirmed.

Better:
select date('2006-03-31', 'start of month','-1 month');

>Many thanks

HTH

>Craig
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] join metadata in query results

[Kees Nuyt]

On Wed, 2 Jan 2008 17:49:36 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:

>On Jan 2, 2008 5:31 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
>I found a solution that seems workable. I ended up rewriting my query
>class so it assumes a bunch of things which are valid for my application
>(but not for general usage). The columns in the result set can always be
>correctly identified given a single key, that key is the first column of
>every query, and the code does not generate it's own SQL to do updates
>or deletes.
>I have to craft the sql for each query/update/delete. It's disappointing
>since it could have been much more elegant and generic (and a lot less
>work for me!).
>
>>
>> I know what the answer will be: "This is not a planned feature.
>> Adding this would slow down the code for vast majority of people
>> who do not need it."
>
>I noticed the column metadata routines are not included by the default
>compile settings
>so Dr. Hipp isn't averse to putting in things that aren't commonly used.

Ok, interesting.
Thanks for your feedback!
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Jay Sprenkle]

Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..

On Jan 2, 2008 4:52 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote:
> Is there any way to get a list of all column names in a table via the
> C API, which works even when there are no data rows in the table?
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Date Problems

[Craig.Street]

Hi

Can somebody give any explain to this please.

sqlite> select date("2006-03-31");
2006-03-31
--> correct

sqlite> select date("2006-03-31", "-1 month");
2006-03-03
--> not correct


Can anyone confirm? Any suggestions / workarounds greatfully received!

Many thanks
Craig
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named.  If you are not the named addressee you
should not disseminate, distribute or copy this e-mail.  Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses.  The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version.  This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] join metadata in query results

[Jay Sprenkle]

On Jan 2, 2008 5:31 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> >If the engine within sqlite recorded the row id as it
> >fetched each column it would be trivial and would
> >prevent me from having to query the database again.
> >If it's not present in the current code

I found a solution that seems workable. I ended up rewriting my query
class so it assumes a bunch of things which are valid for my application
(but not for general usage). The columns in the result set can always be
correctly identified given a single key, that key is the first column of
every query, and the code does not generate it's own SQL to do updates
or deletes.
I have to craft the sql for each query/update/delete. It's disappointing
since it could have been much more elegant and generic (and a lot less
work for me!).

>
> I know what the answer will be: "This is not a planned feature.
> Adding this would slow down the code for vast majority of people
> who do not need it."

I noticed the column metadata routines are not included by the default
compile settings
so Dr. Hipp isn't averse to putting in things that aren't commonly used.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] EXISTS and NULLs

[Nemanja Čorlija]

On Jan 2, 2008 5:44 PM,  <[EMAIL PROTECTED]> wrote:
> The current behavior of SQLite is to not do anything special
> with NULLs in an EXISTS operator.  For example:
>
>CREATE TABLE t1(x);
>INSERT INTO t1 VALUES(NULL);
>SELECT EXISTS(SELECT x FROM t1);
>
> The final SELECT above returns 1 (true) because an entry exists
> in t1, even though that entry is NULL.  This makes logical sense
> because if you wanted to know if there were non-null entries
> you would say:
>
>SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);
>
> But I have long ago learned that NULL values in SQL rarely
> make logical sense, so I figure I better check.
>
> Can somebody please confirm that this is the correct behavior
> and that EXISTS does not do any special treatment of NULL
> values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
> and Firebird do in this case?
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>

Firebird 2.0:

SQL> CREATE TABLE t1(x INTEGER);
SQL> INSERT INTO t1 VALUES(NULL);
SQL> SELECT EXISTS(SELECT x FROM t1);
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 8
-EXISTS
SQL> select count(*) from t1 where exists (select x from t1);

   COUNT

   1


-- 
Nemanja Čorlija <[EMAIL PROTECTED]>

[sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

Is there any way to get a list of all column names in a table via the  
C API, which works even when there are no data rows in the table?


The example given for sqlite3_get_table() indicates that the column  
names are returned as the "zeroth" row.  Indeed, it states:


"In general, the number of values inserted into azResult will be  
((*nrow) + 1)*(*ncolumn)."


However, I when the table has no data rows, the returned ncolumn=0, so  
the expression evaluates to 0 and indeed I get 0 values.


Thanks,

Jerry Krinock

(Using sqlite 3.4.0, as shipped in Mac OS X version 10.5)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Commit fails due to "database is locked" in active transaction

[Trevor Talbot]

On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote:

> I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable),
> and I have a scenario using transactions in SQLite3 that is a bit
> counter-intuitive.

You'll want to look at http://sqlite.org/lockingv3.html for this.

> I open an SQLite3 database from two terminals. In the first terminal I
> run the following SQLs:

>  > sqlite> begin;
>  > sqlite> insert into a values (null);
>  > sqlite> insert into a values (null);

This transaction has acquired a RESERVED (intent to write) lock, at
the first INSERT statement. Others may read, but no others may
announce an intent to write.

> In the second terminal, I run the following SQLS:

>  > sqlite> begin;
>  > sqlite> insert into a values (null);
>  > SQL error: database is locked

This transaction has acquired a SHARED (reading) lock at the first
access to the database. At the INSERT statement, it tries to acquire
RESERVED, but fails because another has already announced its intent
to write. This transaction remains SHARED.

> I go back to the first terminal at this stage and I try to end the
> transaction using commit:

>  > sqlite> commit;
>  > SQL error: database is locked

The second connection still has a SHARED (reading) lock, so this
transaction cannot make any physical changes to the file yet. The
INSERT statements you already executed are buffered internally, so it
did not need to make physical changes before.

> At this point, I can't commit the transaction in the first terminal,
> until I run a commit in the second terminal, even though the first
> terminal is the one with the active transaction, and the second terminal
> shouldn't have any effect on the active transaction.

The second connection has an active transaction too, just in read-only
state. It must end before the first can proceed with physical changes
to the file.

> This behaviour varies, depending on the filesystem type on which the
> sqlite database file is created in. If I use reiserfs, it sometimes
> takes a long while until I can commit from any of the terminals. In
> ext3, this is usually resolved after retrying the commit a few times in
> both terminals. In tmpfs, there is never any issue, the first terminal
> can always commit.
>
> What am I missing here? Is this behaviour the expected one?

Requiring the second transaction to complete first is expected in
terms of SQLIte's concurrency system. The fact that you are seeing
changes in behavior depending on the filesystem is disturbing though.
They should all behave the same if they are implementing locking
correctly. I will let others speak to this point; if you can post more
detail on the steps (e.g. if I do "commit" here and "commit" here
nothing happens for N minutes), it will probably help them.

Just to be clear, these are local filesystems, correct? Network mounts
like NFS are not expected to work.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Commit fails due to "database is locked" in active transaction

[Ken]

Try your test using a "begin Immediate" instead of begin.

A write lock is not taken out until the very last moment (ie  a spill to disk 
or commit).


Lior Okman <[EMAIL PROTECTED]> wrote: 

Hi,



I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable), 
and I have a scenario using transactions in SQLite3 that is a bit 
counter-intuitive.


I open an SQLite3 database from two terminals. In the first terminal I 
run the following SQLs:


 > $ sqlite3  test.db
 > SQLite version 3.4.2
 > Enter ".help" for instructions
 > sqlite> create table a (id integer not null primary key);
 > sqlite> begin;
 > sqlite> insert into a values (null);
 > sqlite> insert into a values (null);


In the second terminal, I run the following SQLS:


 > $ sqlite3 test.db
 > SQLite version 3.4.2
 > Enter ".help" for instructions
 > sqlite> begin;
 > sqlite> insert into a values (null);
 > SQL error: database is locked

I go back to the first terminal at this stage and I try to end the 
transaction using commit:

 > sqlite> commit;
 > SQL error: database is locked

At this point, I can't commit the transaction in the first terminal, 
until I run a commit in the second terminal, even though the first 
terminal is the one with the active transaction, and the second terminal 
shouldn't have any effect on the active transaction.

This behaviour varies, depending on the filesystem type on which the 
sqlite database file is created in. If I use reiserfs, it sometimes 
takes a long while until I can commit from any of the terminals. In 
ext3, this is usually resolved after retrying the commit a few times in 
both terminals. In tmpfs, there is never any issue, the first terminal 
can always commit.

What am I missing here? Is this behaviour the expected one?


Thanks,
Lior

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Commit fails due to "database is locked" in active transaction

[Lior Okman]

Hi,



I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable), 
and I have a scenario using transactions in SQLite3 that is a bit 
counter-intuitive.



I open an SQLite3 database from two terminals. In the first terminal I 
run the following SQLs:



> $ sqlite3  test.db
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> create table a (id integer not null primary key);
> sqlite> begin;
> sqlite> insert into a values (null);
> sqlite> insert into a values (null);


In the second terminal, I run the following SQLS:


> $ sqlite3 test.db
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> begin;
> sqlite> insert into a values (null);
> SQL error: database is locked

I go back to the first terminal at this stage and I try to end the 
transaction using commit:


> sqlite> commit;
> SQL error: database is locked

At this point, I can't commit the transaction in the first terminal, 
until I run a commit in the second terminal, even though the first 
terminal is the one with the active transaction, and the second terminal 
shouldn't have any effect on the active transaction.


This behaviour varies, depending on the filesystem type on which the 
sqlite database file is created in. If I use reiserfs, it sometimes 
takes a long while until I can commit from any of the terminals. In 
ext3, this is usually resolved after retrying the commit a few times in 
both terminals. In tmpfs, there is never any issue, the first terminal 
can always commit.


What am I missing here? Is this behaviour the expected one?


Thanks,
Lior

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] EXISTS and NULLs

[Ken]

Oracle behavoir:

SQL> create table t1 (x number);

Table created.

SQL> insert into t1 values (NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);
SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL)
   *
ERROR at line 1:
ORA-00936: missing expression


--- Revised Syntax ---
The following returns -1 for the row when x is NULL 
SQL>  select nvl(x,-1) from t1 where exists (select x from t1 where x is null);

 NVL(X,-1)
--
-1


The is more equivalent meaning.

SQL> select count(*) from t1 where exists (select x from t1 where x is not 
null);

  COUNT(*)
--
 0


Hope that helps.
Ken


[EMAIL PROTECTED] wrote: The current behavior of SQLite is to not do anything 
special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




[EMAIL PROTECTED] wrote: The current behavior of SQLite is to not do anything 
special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[EMAIL PROTECTED] wrote: The current behavior of SQLite is to not do anything 
special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] EXISTS and NULLs

[Eugene Wee]

Hi,

MySQL 5.0.41 and Postgresql 8.2.5 work as you described in their 
treatment of NULL. There were some minor syntax tweaks for CREATE TABLE 
and the second SELECT EXISTS, but other than that it was true for the 
first SELECT EXISTS and false for the second SELECT EXISTS.


Regards,
Eugene Wee

[EMAIL PROTECTED] wrote:

The current behavior of SQLite is to not do anything special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp <[EMAIL PROTECTED]>





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] EXISTS and NULLs

[Samuel R. Neff]

This behavior is consistent with MSSQL.  EXISTS returns true for NULL fields
in MSSQL 2005.

Sam

---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 02, 2008 11:44 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] EXISTS and NULLs

The current behavior of SQLite is to not do anything special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] EXISTS and NULLs

[Kees Nuyt]

On Wed, 02 Jan 2008 16:44:12 +, [EMAIL PROTECTED] wrote:

>The current behavior of SQLite is to not do anything special
>with NULLs in an EXISTS operator.  For example:
>
>   CREATE TABLE t1(x);
>   INSERT INTO t1 VALUES(NULL);
>   SELECT EXISTS(SELECT x FROM t1);
>
>Can somebody tell me what MySQL, PostgreSQL, Oracle,
>and Firebird do in this case?

Server version: 5.0.41-community-nt-log MySQL Community Edition
(GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1(x);
ERROR 1064 (42000): You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the
right syntax to use near ')' at line 1

mysql> CREATE TABLE t1(x INTEGER);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT EXISTS(SELECT x FROM t1);
+--+
| EXISTS(SELECT x FROM t1) |
+--+
|1 |
+--+
1 row in set (0.02 sec)
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] EXISTS and NULLs

[drh]

The current behavior of SQLite is to not do anything special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] join metadata in query results

[Kees Nuyt]

On Tue, 1 Jan 2008 18:56:25 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:

>> I fail to see why you can't simply include the PRIMARY KEY
>> (portable) or ROWID (not portable) in your SELECT statement if
>> you apparently need them later.
>
>I could, but that would involve changing a lot of existing code.
>If there was a way to get the row id of each column in the result set
>then it would be simple.
>I could extract the database,table,column, and row id of the changed
>column and do the update
>with a single line of sql.

I think I understand your original problem now.
SQLite (and every other engine I know of) assumes the
application knows the schema and the query, so it can act
accordingly.

Your solution would only work for non-aggregate queries.

>If I include the key of the row in my select(s) how does 
>the program determine which column was the key?
>I could always make it the first column, but that breaks down for joins.

Yes, it would also break for multi-column primary keys.
ROWID is more feasable in that respect.

>I would have to iterate through the columns in the result set and find
>what table each came from.

Yes.

>Then run a pragma or a select from sqlite_master table 
>to find the index column. That's going to be very slow.
>It's going to add an extra database operation for every 
>table in the query. This would more than double the 
>time required for each query.

PRAGMA table_info(); is not very expensive.
You only have to execute it after open and schema changes.

>If the engine within sqlite recorded the row id as it 
>fetched each column it would be trivial and would 
>prevent me from having to query the database again. 
>If it's not present in the current code 

I know what the answer will be: "This is not a planned feature.
Adding this would slow down the code for vast majority of people
who do not need it." 

Ref: 
Date: Sat, 14 Apr 2007 12:24:50 +
From: [EMAIL PROTECTED]
Message-ID:
<[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
Subject: Re: [sqlite] sqlite3_rowid

>I will probably just do it myself. I try not to reinvent 
>the wheel where possible. ;)

Good luck then ;)
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-