Re: [sqlite] SQL language question

[Nicolas Williams]

On Wed, Sep 23, 2009 at 06:12:13PM +0100, Simon Slavin wrote:
> 
> On 23 Sep 2009, at 5:12pm, Nicolas Williams wrote:
> 
> > On Tue, Sep 22, 2009 at 04:45:31PM -0400, D. Richard Hipp wrote:
> >> UPDATE t1 SET x=x;   -- key line:  Is this considered an "update"
> >> of t1.x?
> >
> > Igor pointed to the standards text, which I think is quite reasonable:
> > an update is only an update if something changes.
> 
> On this basis, should the results of sqlite3_changes() and PRAGMA  
> count_changes not agree with those ?  Because they don't.

Yes, but pragmas are outside the language -- it might be useful to have
a pragma to count no-ops.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL language question

[Simon Slavin]

On 23 Sep 2009, at 5:12pm, Nicolas Williams wrote:

> On Tue, Sep 22, 2009 at 04:45:31PM -0400, D. Richard Hipp wrote:
>> UPDATE t1 SET x=x;   -- key line:  Is this considered an "update"
>> of t1.x?
>
> Igor pointed to the standards text, which I think is quite reasonable:
> an update is only an update if something changes.

On this basis, should the results of sqlite3_changes() and PRAGMA  
count_changes not agree with those ?  Because they don't.

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

Re: [sqlite] SQL language question

[Nicolas Williams]

On Tue, Sep 22, 2009 at 04:45:31PM -0400, D. Richard Hipp wrote:
>  UPDATE t1 SET x=x;   -- key line:  Is this considered an "update"  
> of t1.x?

Igor pointed to the standards text, which I think is quite reasonable:
an update is only an update if something changes.

The same should probably apply to TRIGGERs too!

> And a related question:  Does anybody really care about ON UPDATE SET  
> NULL?  Has anybody ever actually seen ON UPDATE SET NULL used in  
> practice?

ON UPDATE SET NULL has very few uses, if any.  Any use of ON UPDATE SET
NULL that I can imagine is contrived (e.g., select for NULL references
in t2 as a way to detect past updates to t1).

On the other hand, ON DELETE SET NULL is very useful.

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

Re: [sqlite] SQL language question

[Simon Slavin]

On 22 Sep 2009, at 9:45pm, D. Richard Hipp wrote:

> The question is this:  Should the no-op UPDATE statement (x=x) cause
> the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or
> not?

You would surely need agreement with the result of sqlite3_changes(),  
right ?

CREATE TABLE fred (a INT, b TEXT);
INSERT INTO "fred" VALUES(1,'fred');
INSERT INTO "fred" VALUES(2,'june');
INSERT INTO "fred" VALUES(3,'june');
INSERT INTO "fred" VALUES(4,'may');
PRAGMA count_changes = YES;
sqlite> UPDATE fred SET b='june' WHERE b='june';
2
sqlite> UPDATE fred SET a=a WHERE b='june';
2

Similarly, from your own code (just in case 'REFERENCES' did something  
strange):

sqlite> UPDATE t1 SET x=x;
1

In SQLite the change count corresponds to the number of rows which  
qualify for the WHERE clause, not the number of rows which have values  
which actually changed.  Is this somehow specified by an SQL standard,  
or was it a design-choice for SQLite ?

Either way, if you noted that two rows were changed, I would guess  
that anything that results from a change to a row should apply to  
those rows.  This is also more flexible because if you want the other  
behaviour you can do something like

UPDATE t1 SET x=x WHERE x!=x;

> And a related question:  Does anybody really care about ON UPDATE SET
> NULL?  Has anybody ever actually seen ON UPDATE SET NULL used in
> practice?

Not me, but I think I've only ever used ON UPDATE once.

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

Re: [sqlite] SQL language question

[Swithun Crowe]

Hello

DRH The question is this:  Should the no-op UPDATE statement (x=x) cause 
DRH the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or 
DRH not?

I think MySQL knows if a row gets actually updated. If the values in a row 
don't change, then it says that no rows were updated.

I was surprised at first, but I suppose it is quite handy to know if 
anything was really changed. To get round this, I found a suggestion that 
you have a dummy column in the table, and you make it default to 0 and 
then on each update, set it to 1 - dummy.

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

Re: [sqlite] SQL language question

[Igor Tandetnik]

D. Richard Hipp  wrote:
> Consider the following SQL:
>
> CREATE TABLE t1(x integer);
> INSERT INTO t1 VALUES(123);
> CREATE TABLE t2(y integer REFERENCES t1 ON UPDATE SET NULL);
> INSERT INTO t2 VALUES(123);
>
> UPDATE t1 SET x=x;   -- key line:  Is this considered an "update"
> of t1.x?
>
> SELECT * FROM t2;-- did t2.y get nulled by the previous
> statement?
>
> The question is this:  Should the no-op UPDATE statement (x=x) cause
> the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or
> not?

Apparently not. From SQL 92 
(http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt):

11.8p6 If an  is specified and a non-null value of a 
referenced column in the referenced table is updated to a value that is 
*distinct* from the current value of that column, then ... [Long 
descripton omitted. Emphasis mine.]
Note: Otherwise, the  is not performed.

Igor Tandetnik 



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

Re: [sqlite] SQL language question

[Rich Shepard]

On Tue, 22 Sep 2009, D. Richard Hipp wrote:

> The question is this:  Should the no-op UPDATE statement (x=x) cause the
> ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or not?
>
> PostgreSQL says "no" - the t2.y value is not nulled unless the t1.x
> value really does change values.

Richard,

   Given the choices, I would go with postgres. It is rational and logical
that if the t1.x value has not changed then the referential t2.x value
should not be changed either.

> And a related question: Does anybody really care about ON UPDATE SET
> NULL? Has anybody ever actually seen ON UPDATE SET NULL used in practice?

   Not I.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users