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

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. Thanks, Paul.

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

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

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

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

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

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

> > > 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 :-)

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

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

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 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

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

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... > >

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

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,

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

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,

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

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

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

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,

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

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

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

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 > >

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

> > > 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--

Re: [sqlite] max size of a TEXT field

http://www.sqlite.org/limits.html -- View this message in context: http://sqlite.1065341.n5.nabble.com/max-size-of-a-TEXT-field-tp63069p63070.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list

[sqlite] Bug in sqlite3_step

Hello. It looks like I found a bug in sqlite3_step. Here is the sample code. #include #include #include #include "sqlite3.h" #ifdef _DEBUG #define verify(f) assert(f) #else #define verify(f) ((void)(f)) #endif char const * const fname = "bug.db3"; int cb(void*, int,

[sqlite] Bug: outer visibility of table names inside nested joins is position dependent

This query works as expected: SELECT aa.*,bb.* FROM ((SELECT 1 AS a) AS aa JOIN (SELECT 3 AS b) AS bb) JOIN ((SELECT 2 AS c) AS cc JOIN (SELECT 3 AS d) AS dd); But If we choose a table from the second nest, SQLite throws "no such table" error: SELECT cc.*,dd.* FROM ((SELECT 1 AS a) AS aa JOIN

Re: [sqlite] Bug in sqlite3_step

On 7/3/2012 10:05 AM, Unsupported wrote: // case 1: exception //verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" // " begin" // " update mails set kav=case old.result when 'infected' then ? else 0 end where

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. I don't understand

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. I don't understand

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. The first paragraph

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On 7/3/2012 3:50 PM, Steven E. Harris wrote: The first paragraph mentions that the encoding allows comparison of keys with memcmp(), which makes it sound like an entire key -- meaning the concatenation of several values -- can be be compared in one operation against another key. The second

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On Tue, Jul 3, 2012 at 3:59 PM, Igor Tandetnik wrote: > On 7/3/2012 3:50 PM, Steven E. Harris wrote: > >> The first paragraph mentions that the encoding allows comparison of keys >> with memcmp(), which makes it sound like an entire key -- meaning the >> concatenation of

Re: [sqlite] Bug in sqlite3_step

Igor Tandetnik wrote: > On 7/3/2012 10:05 AM, Unsupported wrote: >> // case 1: exception >> //verify(sqlite3_prepare_v2(db, "create trigger updater >> update of result on plugins" >> // " begin" >> // " update mails set kav=case old.result when >>

Re: [sqlite] Bug in sqlite3_step

On Tue, Jul 3, 2012 at 4:18 PM, Yuriy Kaminskiy wrote: > Igor Tandetnik wrote: > > On 7/3/2012 10:05 AM, Unsupported wrote: > >> // case 1: exception > >> //verify(sqlite3_prepare_v2(db, "create trigger updater > >> update of result on plugins" > >>

Re: [sqlite] Bug in sqlite3_step

On Tue, Jul 03, 2012 at 04:26:42PM -0400, Richard Hipp scratched on the wall: > Actually, you can bind on a DDL statement, but bindings are only valid for > the lifetime of the statement itself, not for the whole lifetime of the > object created by the CREATE statement. Is that a side-effect

[sqlite] Can I rely on this being ordered?

Have the following table: CREATE TABLE READCODE( [SUBJECT_TYPE] TEXT, [READ_CODE] TEXT, [TERM30] TEXT, [TERM60] TEXT, [ENTRY_COUNT] INTEGER) Records are ordered ascending on READ_CODE as the records are obtained from an ordered array and inserted sequentially. There is a non-unique index on Read

Re: [sqlite] Can I rely on this being ordered?

In SQL you cannot rely on the result set being in any order unless you use ORDER BY. This is true in SQLite3 as well. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I rely on this being ordered?

OK, thanks, that confirms my suspicion then. RBS On Tue, Jul 3, 2012 at 10:00 PM, Igor Tandetnik wrote: > On 7/3/2012 4:53 PM, Bart Smissaert wrote: >> >> However if I do this: >> >> SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT >> FROM >> READCODE >> WHERE >> TERM30 LIKE

Re: [sqlite] Can I rely on this being ordered?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/07/12 14:03, Bart Smissaert wrote: > OK, thanks, that confirms my suspicion then. SQLite can also help you. Run your test suite normally, and then run again with this pragma which gives a different order to unordered selects. Your test suite

Re: [sqlite] Can I rely on this being ordered?

On Jul 3, 2012, at 11:03 PM, Bart Smissaert wrote: > OK, thanks, that confirms my suspicion then. PRAGMA reverse_unordered_selects = boolean; When enabled, this PRAGMA causes SELECT statements without an ORDER BY clause to emit their results in the reverse order of what they normally would.

Re: [sqlite] Can I rely on this being ordered?

Thanks for that tip, useful to know that one. RBS On Tue, Jul 3, 2012 at 11:37 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 03/07/12 14:03, Bart Smissaert wrote: >> OK, thanks, that confirms my suspicion then. > > SQLite can also help

Re: [sqlite] Bug in sqlite3_step

On 7/3/2012 3:26 PM, Richard Hipp wrote: Igor is correct. Actually, you can bind on a DDL statement, but bindings are only valid for the lifetime of the statement itself, not for the whole lifetime of the object created by the CREATE statement. So doing such bindings are pointless. And you

Re: [sqlite] Bug in sqlite3_step

Robert Myers wrote: > DROP TABLE ? would've been useful for me. Parameters can only appear where literals would be allowed by the syntax. A table name is not a literal. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] Bug in sqlite3_step

On 07/04/2012 03:30 AM, Jay A. Kreibich wrote: On Tue, Jul 03, 2012 at 04:26:42PM -0400, Richard Hipp scratched on the wall: Actually, you can bind on a DDL statement, but bindings are only valid for the lifetime of the statement itself, not for the whole lifetime of the object created by the