Re: [sqlite] xBestIndex/XFilter and virtual tables
>Estimated cost and row for each call to xBestIndex (sqlite3_index_info is >filled with the below values for each call) > >a) When there is one non-usable constraint > idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000 > >b) when there is one usable constraint >idxNum = 1 estimatedCost = 1.00 estimatedRows = 1 > >xFilter is called with idxNum = 0 and argc = 0 If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with idxNum = 1. If aConstraintUsage[0].omit = 0 then xFilter is called with idxNum = 0 and argc = 0 Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] xBestIndex/XFilter and virtual tables
>> I am expecting two usable entries in sqlite3_index_info with >> aConstraint[i].iColumn = 0 and aConstraint[i].op == >> SQLITE_INDEX_CONSTRAINT_EQ for each check > > What actually happens is that SQLite transforms the query into > >SELECT stamp, to_text(value) FROM store WHERE id IN (42,24). > > The xBestIndex method does not distinguish between == and IN, so only > a single == constraint will be mentioned in the xBestIndex call. > How to i extract multiple values in xFilter -- int argc, sqlite3_value **argv ? If i specify aConstraintUsage[].argvIndex = 1 then i should expect a list of values (42 and 24) in argv[0]. >> >> xBestIndex is called twice, >> >> a) once with only one non-usable entry with aConstraint[i].iColumn = 0 >> and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and > > Here it is checking the cost of a full table scan. > >> b) once with only one usable entry with aConstraint[i].iColumn = 0 and >> aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ. > > Here it is checking the codes of using the IN operator. What > estimatedCost values are you returning for these two calls. Because > when I run it, and the cost of the second version is less, it chooses > the second version. What version of SQLite are you using? Sqlite Version = 3.8.7.1 Estimated cost and row for each call to xBestIndex (sqlite3_index_info is filled with the below values for each call) a) When there is one non-usable constraint idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000 b) when there is one usable constraint idxNum = 1 estimatedCost = 1.00 estimatedRows = 1 xFilter is called with idxNum = 0 and argc = 0 Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] xBestIndex/XFilter and virtual tables
Hi, I have a virtual table. create table store (id int, stamp int, value blob); When i execute the following sql select stamp, to_text(value) from store where id = 42; I get as expected sqlite3_index_info to have one usable entry with aConstraint[i].iColumn = 0 aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ In xBestIndex I set the aConstraintUsage[i].argvIndex value to 1, to indicate that '42' should be the first argument in corresponding xFilter call. And in the corresponding xFilter I get a value of 42 as the first argument and I can initialize the cursor with just one element with id = 42. When i execute the following sql select stamp, to_text(value) from store where id = 42 or id = 24; I am expecting two usable entries in sqlite3_index_info with aConstraint[i].iColumn = 0 and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ for each check xBestIndex is called twice, a) once with only one non-usable entry with aConstraint[i].iColumn = 0 and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and b) once with only one usable entry with aConstraint[i].iColumn = 0 and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ. And the xFilter called corresponds to the first call to xBestIndex. Thus it does a table scan to find entries (42, 24) Am i missing something here ? -venkat murty Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
The visibility of table created in with clause is only in the SET part. The following is a valid sql statement in sqlite3. with ds as (select id, a , b, c from some_table where c = 43) update temp_table set id = (select ds.id from ds where ds.a = temp_table.a AND ds.b = temp_table.b), operation = 'UPDATE'; Have to take care using case statement when a match is not found. Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I update multiple rows in a single sql statement
How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table. with ds as (select id, a , b, c from some_table where c = 42) update temp_table set id = ds.id, operation = 'UPDATE' WHERE ds.a = temp_table.a AND ds.b = temp_table.b; I get the error " no such column: ds.id" Thanks, Venkat Murty Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
