Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

>
>
>   Then why do you keep hammering on the idea that SQLite is somehow
>   incorrect or wrong?
>
>   You've explained what you're trying to do.  We've explained there is
>   a better way to do that, that also happens to provide the correct
>   answer on all platforms, AND likely runs faster-- especially if any
>   of those columns has an index on them.
>

I gave a simple example. I work with generic cases. My application doesn't
have all static SQL. A lot is from the user or built dynamically.

>
> > What if the SET and WHERE contain many columns?
>
>   Then you're asking for a more complex operation.  Your SQL gets a bit
>   more complex as well.
>
> > Now I have to add a "WHERE column<>mynewval" for every column in SET
> > to get the actual changes, something like UPDATE testtable SET col1=?1,
> > col2=?2, col3=? WHERE  complex where clause
> > AND col1<>?1 AND col2<>?2 AND col3<>?3.
>
> > (passing a null parameter to the above won't even work!)
>
>   Well, no, it won't, because you're using the wrong operator.
>
>   Use "WHERE col1 IS NOT ?1 AND..." and it all works fine.
>
> OK thanks, so I should always use IS NOT where I always used <>. Oh well
(talk about yuck!)


> > No surprises there. Oracle has never managed to impress me.
>
>   I know what you mean.  That MySQL database they make is difficult to
>   take seriously.
>
> Very funny. They didn't make it, they own it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Jay A. Kreibich]

On Tue, Jul 03, 2012 at 03:21:57PM +0200, Paul van Helden scratched on the wall:
> On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS)
> wrote:
> 
> > And Oracle says the opposite:
> >
> > Yet they all give the same answer when done with "update testtable set
> > testrow=null where testrow not null;
>
> You keep hammering this one, it is obvious, I understand, THANKS!

  Then why do you keep hammering on the idea that SQLite is somehow
  incorrect or wrong?

  You've explained what you're trying to do.  We've explained there is
  a better way to do that, that also happens to provide the correct
  answer on all platforms, AND likely runs faster-- especially if any
  of those columns has an index on them.

> What if the SET and WHERE contain many columns?

  Then you're asking for a more complex operation.  Your SQL gets a bit
  more complex as well.

> Now I have to add a "WHERE column<>mynewval" for every column in SET
> to get the actual changes, something like UPDATE testtable SET col1=?1,
> col2=?2, col3=? WHERE  complex where clause
> AND col1<>?1 AND col2<>?2 AND col3<>?3.

> (passing a null parameter to the above won't even work!)

  Well, no, it won't, because you're using the wrong operator.

  Use "WHERE col1 IS NOT ?1 AND..." and it all works fine.

> No surprises there. Oracle has never managed to impress me.

  I know what you mean.  That MySQL database they make is difficult to
  take seriously. 

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Pavel Ivanov]

On Tue, Jul 3, 2012 at 10:55 AM, Jay A. Kreibich  wrote:
> On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the 
> wall:
>
>> >   The statement "UPDATE table SET column=NULL" updates every row in the
>> >   table.  The fact that some rows may already have a NULL in that
>> >   column is not important.
>> >
>> > Well, it is important to me, the word "change" means before != after :-)
>
>   You can argue about the naming of the _change() function all you
>   want.  It is a non-standard extension and the function operates as
>   documented.  If you want to call it poorly named, go ahead.  That
>   doesn't change what it does.
>
>   There is, however, little argument that the trigger is doing exactly
>   what one would expect.  You are applying an update operation to every
>   row, and the trigger is firing for every row.

BTW, I think you can add to trigger "WHEN NEW.column IS NOT
OLD.column" and it will fire only for rows where column value has
really changed (beware "IS NOT" with arbitrary right side works only
on SQLite 3.6.19 and above).

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Jay A. Kreibich]

On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the wall:

> >   The statement "UPDATE table SET column=NULL" updates every row in the
> >   table.  The fact that some rows may already have a NULL in that
> >   column is not important.
> >
> > Well, it is important to me, the word "change" means before != after :-)

  You can argue about the naming of the _change() function all you
  want.  It is a non-standard extension and the function operates as
  documented.  If you want to call it poorly named, go ahead.  That
  doesn't change what it does.

  There is, however, little argument that the trigger is doing exactly
  what one would expect.  You are applying an update operation to every
  row, and the trigger is firing for every row.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Pavel Ivanov]

On Tue, Jul 3, 2012 at 9:21 AM, Paul van Helden  wrote:
>> So rather than holding your breath for Oracle to change I'd recommend you
>> do it the portable way.
>>
> I'm not waiting for anything. My last question was simple: which is
> better? Since MySQL does it the "correct way" perhaps we can just think
> about this for sqlite4?

That's definitely not a correct way. It could be "intuitive" for those
who doesn't know SQL well. But for anybody else it's counter-intuitive
and I would be really disappointed if SQLite will implement that.

So the answer to your last question (as Michael already said): better
to write in SQL what you really want to do and not expect for SQL
engine to guess it for you. If you find writing complex WHERE clause
too complicated then don't use sqlite3_changes() function.


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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS)
wrote:

> And Oracle says the opposite:
>
> Yet they all give the same answer when done with "update testtable set
> testrow=null where testrow not null;
>
> You keep hammering this one, it is obvious, I understand, THANKS!  What if
the SET and WHERE contain many columns? Now I have to add a "WHERE
column<>mynewval" for every column in SET to get the actual changes,
something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE  AND col1<>?1 AND col2<>?2 AND col3<>?3.
(passing a null parameter to the above won't even work!)

>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
> With the Partitioning, Oracle Label Security, OLAP, Data Mining,
> Oracle Database Vault and Real Application Testing options
> SQL> create table testtable(testrow number);
> Table created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> update testtable set testrow=null;
> 3 rows updated.
> SQL> update testtable set testrow=null;
> 3 rows updated.
>

No surprises there. Oracle has never managed to impress me.


> SQL> update testtable set testrow=null where testrow is not null;
>
> 0 rows updated.
>
> So rather than holding your breath for Oracle to change I'd recommend you
> do it the portable way.
>
>  I'm not waiting for anything. My last question was simple: which is
better? Since MySQL does it the "correct way" perhaps we can just think
about this for sqlite4?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Black, Michael (IS)]

And Oracle says the opposite:

Yet they all give the same answer when done with "update testtable set 
testrow=null where testrow not null;


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table testtable(testrow number);
Table created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> update testtable set testrow=null;
3 rows updated.
SQL> update testtable set testrow=null;
3 rows updated.
SQL> update testtable set testrow=null where testrow is not null;

0 rows updated.

So rather than holding your breath for Oracle to change I'd recommend you do it 
the portable way.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Paul van Helden [p...@planetgis.co.za]
Sent: Tuesday, July 03, 2012 7:47 AM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Update trigger fires for all rows even if 
sqlite3_changes returns 0

On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden <p...@planetgis.co.za>wrote:

>
>>   The statement "UPDATE table SET column=NULL" updates every row in the
>>   table.  The fact that some rows may already have a NULL in that
>>   column is not important.
>>
>> Well, it is important to me, the word "change" means before != after :-)
>

Just checked MySQL:

UPDATE testtable SET testrow=NULL;
Affected rows: 40
UPDATE testtable SET testrow=NULL;
Affected rows: 0

That is what I'm familiar with.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS)
wrote:

> What's better is that it tells you what you asked for...not what you think
> you asked for...which it does.
>
I asked for changes :-)

>
> You've already been shown the correct solution...a WHERE clause...
>
> I've done that even before posting here, just thought it odd.

> You want sqlite to do a complete record compare, including following
> update triggers, on EVERY record it looks at to see if something happened
> to change???
>
Just the fields in the SET clause.

>
> Yuck...
>
> As compare to the WHERE clause which does exactly what you want and runs
> oodles faster (in all likelihood).
>
> I always keep an eye on the "affected rows" to see what my statements have
done (whether I used a WHERE or not). Even with a WHERE, I would prefer
seeing actual changes :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote:

>
>>   The statement "UPDATE table SET column=NULL" updates every row in the
>>   table.  The fact that some rows may already have a NULL in that
>>   column is not important.
>>
>> Well, it is important to me, the word "change" means before != after :-)
>

Just checked MySQL:

UPDATE testtable SET testrow=NULL;
Affected rows: 40
UPDATE testtable SET testrow=NULL;
Affected rows: 0

That is what I'm familiar with.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Black, Michael (IS)]

What's better is that it tells you what you asked for...not what you think you 
asked for...which it does.

You've already been shown the correct solution...a WHERE clause...

You want sqlite to do a complete record compare, including following update 
triggers, on EVERY record it looks at to see if something happened to change???

Yuck...

As compare to the WHERE clause which does exactly what you want and runs oodles 
faster (in all likelihood).



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Paul van Helden [p...@planetgis.co.za]
Sent: Tuesday, July 03, 2012 7:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Update trigger fires for all rows even if 
sqlite3_changes returns 0

You are right, sorry, just checked. sqlite3_changes returns number of
records hit, not changed. Have been using sqlite for 2 years now and was
always under the impression this was for actual changes.

But which is better behaviour, reporting "row hits" versus real changes?
Especially when it comes to triggers?

On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:

> Paul van Helden wrote:
> > Is this correct? Should update triggers not only fire for actual
> changes? I
> > have a large table with a column which contains all NULL values except
> for
> > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> > except it fires for every row.
>
> I'm pretty sure that sqlite3_changes() in this case also returns *all*
> rows, not
> only 4 "really" changed. If you want triggers to only fire for really
> changed
> rows (and sqlite3_changes() to return only those 4 rows), you should add
> WHERE
> clause:
> UPDATE table SET column=NULL WHERE column IS NOT NULL;
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

>
>
>   The statement "UPDATE table SET column=NULL" updates every row in the
>   table.  The fact that some rows may already have a NULL in that
>   column is not important.
>
> Well, it is important to me, the word "change" means before != after :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Paul van Helden]

You are right, sorry, just checked. sqlite3_changes returns number of
records hit, not changed. Have been using sqlite for 2 years now and was
always under the impression this was for actual changes.

But which is better behaviour, reporting "row hits" versus real changes?
Especially when it comes to triggers?

On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy  wrote:

> Paul van Helden wrote:
> > Is this correct? Should update triggers not only fire for actual
> changes? I
> > have a large table with a column which contains all NULL values except
> for
> > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> > except it fires for every row.
>
> I'm pretty sure that sqlite3_changes() in this case also returns *all*
> rows, not
> only 4 "really" changed. If you want triggers to only fire for really
> changed
> rows (and sqlite3_changes() to return only those 4 rows), you should add
> WHERE
> clause:
> UPDATE table SET column=NULL WHERE column IS NOT NULL;
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Jay A. Kreibich]

On Tue, Jul 03, 2012 at 01:32:14PM +0200, Paul van Helden scratched on the wall:
> Hi,
> 
> Is this correct? Should update triggers not only fire for actual changes? I
> have a large table with a column which contains all NULL values except for
> 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> except it fires for every row.

  The statement "UPDATE table SET column=NULL" updates every row in the
  table.  The fact that some rows may already have a NULL in that
  column is not important.

  If you only want the trigger to fire for non-NULL rows, you need to
  update only the non-NULL rows:

UPDATE table SET column=NULL WHERE column IS NOT NULL;

  As for sqlite3_changes() returning 0, that doesn't sound right unless
  you're checking inside the trigger.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

[Yuriy Kaminskiy]

Paul van Helden wrote:
> Is this correct? Should update triggers not only fire for actual changes? I
> have a large table with a column which contains all NULL values except for
> 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> except it fires for every row.

I'm pretty sure that sqlite3_changes() in this case also returns *all* rows, not
only 4 "really" changed. If you want triggers to only fire for really changed
rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE
clause:
UPDATE table SET column=NULL WHERE column IS NOT NULL;

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