Are result values always the same?
(1) By doug (doug9forester) on 2020-06-17 17:48:32 [link]
Given the following statement which sets the checkbox 'on' for a given pl_id and sets it 'off' for all others: ``` UPDATE pltable SET checkbox = (pl_id=?); ``` where the bind value is the id of a row in the table. I tried it with sqlite3 and found that the value of checkbox is always 0 or 1 depending on the match or not. Are the result values for this operation always 0 or 1? Does the SQL standard say so or is it implementation dependent? I mean, the result could be 0 and 99, right? Also, should I define an index for this kind of query? If so, what should it be?
(2) By Larry Brasfield (LarryBrasfield) on 2020-06-17 18:15:15 in reply to 1
SQLite's expression evaluation, at least in the VM code generated from the parse tree, is quite regular and traditional. The result of the boolean equality operator is in fact TRUE or FALSE as those literals have been [recently recognized and documented](https://sqlite.org/lang_expr.html#booleanexpr). This is not going to depend on where in the parse tree such an expression appears. So I think the answer to the "what happens" part of your question is "yes". You ask about the SQL standard, as if it might be controlling. I doubt that it is, except insofar as it has affected common expectation and the behavior of PostgreSQL which is frequently used as guidance by Dr. Hipp. Looking at the docs, I notice that this "actual expression result" point is not actually documented, at least not that I could find in the expectable places. So, the hardline position would be that the behavior cannot relied upon. But that would be to ignore the great weight and value given to backward compatibility by the SQLite designer(s). Since SQLite boolean expressions already generate 0 or 1 (aka FALSE or TRUE), I maintain that it can be relied upon because there may already be many applications relying upon it now.
(3) By Warren Young (wyoung) on 2020-06-17 18:24:40 in reply to 1 [link]
> UPDATE pltable SET checkbox = (pl_id=?); I think you mean UPDATE pltable SET checkbox = 1 WHERE pl_id = ?; > Are the result values for this operation always 0 or 1? I'm not quite sure what you mean by "result." If you're asking what an `UPDATE` query returns, it is a SQLite result code, which is either `SQLITE_OK` or some error code. If you're asking what value gets put into `checkbox`, then it's the integer 1 with the query above. > is it implementation dependent? SQLite doesn't have a distinct Boolean data type, so if you put 99 into the column, SQLite will store that, and truth tests on that value will then [proceed as documented]. > should I define an index for this kind of query? Assuming `pl_id` is the first column of the table and it's an integer, it becomes the [autoincrement ID column], so it *might* auto-index itself, but for a case like this, I see no harm in creating the index explicitly: CREATE UNIQUE INDEX idx_pl_id on pltable(pl_id); : https://sqlite.org/autoinc.html : https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison
(4) By Keith Medcalf (kmedcalf) on 2020-06-17 18:51:34 in reply to 1 [link]
> Are the result values for this operation always 0 or 1? A value of NULL means "unknown" (as in one of the arguments to the equality test was NULL), 0 means "false" and a non-zero value means "true". > Does the SQL standard say so or is it implementation dependent? I cannot answer what the SQL Standard says (if anything) about the result of comparison operations. The actual value obtained for "true" would depend on the underlying hardware, the language in which the "SQL engine" were implemented, and perhaps a myriad of other factors. However, in the case of SQLite3 the evaluation of a comparison operation returns either 0 for false, 1 for true, or null if either argument is null and the comparison operation is not "is" or "is not". > I mean, the result could be 0 and 99, right? Generally unlikely but yes. The value 99 is true because it is not 0. It is more likely that the value would be -1 (all bits on) however. If you wanted to "assure" that the result were always 1 or 0 then you could use something like: ``` UPDATE pltable SET checkbox = (CASE WHEN pl_id == ? THEN 1 ELSE 0 END); ``` which will always result in checkbox being 1 or 0 and does not have any dependencies on any possible implementation detail -- that is, it is 100% absolutely unambiguous (explicit) and is not subject to "interpretation", and if you pass "NULL" as the parameter, all results will be 0. That certainty, however, does have a cost. The more explicit version requires that the machine execute more instructions to obtain those more explicit results. So if you table had 1 billion rows this might be significant. If your table only has ten rows, then likely not. > Also, should I define an index for this kind of query? If so, what should it be? There is no point. You are updating the value of every row in the table and computing the result separately for each row. Indexes will not help for this particular operation.
(5) By doug (doug9forester) on 2020-06-17 20:21:09 in reply to 3 [link]
Warren, No, I meant it like I wrote it. Every row gets updated with a value of 0 or 1 (or whatever value Sqlite decides to put in for the result of the logical expression). Only the one whose id is passed to the query gets a 1; all others get zero.
(6) By Wout Mertens (wmertens) on 2020-06-18 05:26:42 in reply to 5 [link]
You might get a speed-up if you index the checkbox (sparsely, `WHERE checkbox=1`) and make the update condition `WHERE pl_id=? OR checkbox=1`
(7) By Keith Medcalf (kmedcalf) on 2020-06-18 05:53:55 in reply to 4 [link]
As Wout pointed out, if you had a unique index on pl_id and also a conditional index where checkbox == 1, then you could limit thee update to only the two rows needing update which would cause the update to take constant time no matter how many rows were in the table. ie: create unique index plindex1 on pltable (pl_id); -- unless it is already constrained unique or is a primary key create index plindex2 on pltable (pl_id) where checkbox == 1; Then do your update as update pltable set checkbox = (case when pl_id == ?1 then 1 else 0 end) where pl_id == ?1 or checkbox == 1; will update only the two rows.
(8) By doug (doug9forester) on 2020-06-18 17:02:07 in reply to 7 [link]
Keith and Wout, thanks for the suggestions. The field pl_id is a primary key (and thus unique). However, the table is tiny. There will never be more than a handful of rows in it and most times just one or two. (It's the list of folders in which there are playlists that the user might want to play.) So, given that it's tiny, that means that every update will cause the whole table to be written for every update. If the index you suggest exists, is it in the same physical record as the table? If it's in a separate record, then there is more I/O. Just for interest, knowing that I/O is going to happen to ensure commit integrity, and that the table will fit in one record, what is the sequence of I/O operations to accomplish these queries, and is there any way to minimize the number of them? ``` update pltable set checkbox = (case when pl_id == ?1 then 1 else 0 end) where pl_id == ?1 or checkbox == 1; ``` and this one: ``` update pltable set checkbox = (case when pl_id == ?1 then 1 else 0 end); ```
(9) By Keith Medcalf (kmedcalf) on 2020-06-18 18:22:30 in reply to 8 [link]
If we assume that the table looks like this: ``` create table pltable ( pl_id integer primary key, checkbox integer not null, description text collate nocase ); ``` or something of that nature, then you can easily determine how many pages it will take based on the number of bytes. If the number of bytes is less than the page size (usually 4096) then it will all be stored on one page. This is probable I should think. If you create an additional index, then that index will also be stored on a separate page. So minimum I/O would occur without an extra index and using the latter (non-indexed) update. The overhead of having, maintaining, and updating the extra index would likely cost much more (in both CPU and I/O) compared to not having it.