[sqlite] Use of same aliases in single query
Hi list, Accidentally I discovered that I could use the same table aliases for 2 tables in a single sql statement. As long as I do not use a column name that exists in both tables Sqlite accepts this. It looks strange to me that I can use the same aliases multiple times, and that they co-exist. As a programmer I'm not used to this, but the Sqlite docs do not seem to forbid this. Is this a bug or a feature? I guess because of backward compatibility this behavior will not be changed. Something to document in https://www.sqlite.org/quirks.html ? Similar behavior for result row aliases, but in that case the ambiguous aliases can even be used. Examples duplicate table aliases: CREATE TABLE aaa (a INT, z INT); CREATE TABLE bbb (b INT, z INT); SELECT 1 FROM aaa x, bbb x ON x.a = x.b; -- Same alias "x" for 2 tables, but no complaints (all column names in the query can be resolved) SELECT 1 FROM aaa x, bbb x ON x.z = x.z; -- "Error: ambiguous column name: x.z" SELECT * FROM aaa x, bbb x ON x.a = x.b; -- "Error: ambiguous column name: x.z" (during expansion of *) Example duplicate result rows aliases: SELECT x.a as y, x.z as y -- Same alias, but no complaints FROM aaa x; And the result row aliases can even be used in the query. INSERT INTO "aaa" VALUES(1,2); INSERT INTO "aaa" VALUES(1,3); SELECT count(), x.a as y, x.z as y FROM aaa x GROUP BY y; -- No complaints, even though "y" is ambiguous here count()|y|y 2|1|3 -- Looks like the first alias "y" is used. Tested with versions 3.27.2 and 3.15.2. Regards, Rob Golsteijn Met Vriendelijke Groet, Kind Regards, 谨致问候, Rob --- Rob Golsteijn Software Engineer Mapscape Luchthavenweg 34 | 5657 EB Eindhoven | The Netherlands Phone +31 (0)40 7113583 | Fax: +31 (0)40 711 3599 www.mapscape.eu <http://www.mapscape.eu/> Mapscape B.V. is ISO9001:2008 certified.This e-mail and any attachment may contain corporate proprietary information and may only be read, copied and used by the intended recipient. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system. We believe but do not warrant that this message and any attachments are virus free. Mapscape B.V. is registered at the Kamer van Koophandel Oost-Brabant located in Eindhoven, The Netherlands number 17210210 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation issues for collating sequences
I encountered a documentation issue at the page that describes how to define new collating sequences ( https://www.sqlite.org/c3ref/create_collation.html ) For the 3 variants of sqlite3_create_collation the 5th argument is a function called "xCompare", but the text refers to "xCallback" instead of "xCompare": "The fifth argument, xCallback, is a pointer to the collating function. [...]" Please correct this. Furthermore the text states: "The collating function callback is invoked with a copy of the pArg application data pointer and with two strings in the encoding specified by the eTextRep argument" But function xCompare is defined as "int(*xCompare)(void*,int,const void*,int,const void*)", so having 2 additional integer parameters. These will contain the lengths of the two strings but this is not documented. At first I guessed they would contain one of the type constants (see https://www.sqlite.org/c3ref/c_blob.html) to allow collation implementations like the built-in one where integer < text < blob. But now I see that lengths are necessary, especially with blobs that can contain embedded NUL characters. I guess this also means that I cannot assume that the strings are nul-terminated. Please document that the integers arguments contain the lengths of the strings. Btw. I expected "xCompare" to have signature int(*xCompare)(void*, const sqlite3_value * const, const sqlite3_value * const). Can anyone explain why the values are passed as strings. E.g. were collating sequences only foreseen for text values? Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query planner improvements in case of AUTOMATIC INDEX
Hi List, When investigating performance of one of our queries I found an interesting situation that might be an opportunity for performance improvement. Tested with Sqlite version 3.15.2 (November 2016). Consider the following table and query CREATE TABLE Node ( Id INTEGER PRIMARY KEY AUTOINCREMENT, x NUMBER(10), y NUMBER(10), HeightLevel NUMBER(2), GeomWGS84 BLOB, Perm_id TEXT /* some irrelevant fields removed */ ); /* find duplicates */ SELECT NOD1.PERM_ID, NOD1.X, NOD1.Y, NOD1.HeightLevel, NOD1.GeomWGS84 FROM Node NOD1 INNER JOIN Node NOD2 ON NOD1.X = NOD2.X AND NOD1.Y = NOD2.Y AND NOD1.HeightLevel = NOD2.HeightLevel AND NOD1.GeomWGS84 = NOD2.GeomWGS84 AND NOD1.ID <> NOD2.ID ORDER BY NOD1.GeomWGS84; The query plan of this query is selectid|order|from|detail 0|0|0|SCAN TABLE Node AS NOD1 0|1|1|SEARCH TABLE Node AS NOD2 USING AUTOMATIC COVERING INDEX (GeomWGS84=? AND HeightLevel=? AND y=? AND x=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY It takes 636 seconds wall clock time to execute the query. I would expect that this is the execution time of creating the index + the execution time of the query when the index is already present. So if I create the "AUTOMATIC" index explicitly the expected total execution time would also be 636 seconds, but CREATE INDEX idx_node on node (GeomWGS84, HeightLevel, y, x); Takes 40 seconds and subsequent query execution takes 8 seconds. So 48 seconds in total compared to 636 second with the AUTOMATIC query. The explanation can be found when looking at the query plan of the query (in the new schema with index present): selectid|order|from|detail 0|0|0|SCAN TABLE DH_NOD AS NOD1 USING INDEX idx_node 0|1|1|SEARCH TABLE DH_NOD AS NOD2 USING COVERING INDEX idx_node (GeomWGS84=? AND HeightLevel=? AND y=? AND x=?) So the explicit index is now also used for ORDER BY optimization. I guess in general it could be used for other optimizations as well . The optimization possibility is to re-evaluate the query plan, taking also the AUTOMATIC indexes into account, once Sqlite decided that AUTOMATIC indexes are useful. To avoid extra planning time, maybe this should only be done when AUTOMATICALLY INDEXED table(s) are used multiple times in the query (otherwise they will not change the query plan anyway)? Since query planning is typically fast compared to query execution, the extra iteration of the query planner may be acceptable for the cases the query plan cannot be improved. For our company it would be acceptable but in general I cannot judge. Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: ambiguous column name
Hi List, Given a table created as: create table aaa(a); Sqlite reports an error "ambiguous column name: main.aaa.a" for the following query. select * from aaa, aaa; Error: ambiguous column name: main.aaa.a And also for similar queries select * from aaa INNER JOIN aaa; select * from aaa CROSS JOIN aaa; select * from aaa JOIN aaa; Tested with sqlite version 3.21.0 and an old version 3.8.4.3. I think the query is valid and should not result in an error. Typically Sqlite would name the resulting columns "a" and "a:1" in this case. Workaround: add an alias for one of the tables in the join (both columns will be called "a"). Met Vriendelijke Groet, Kind Regards, 谨致问候, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] documentation flaws
Hi List, I found 2 flaws in the online documentation: Documentation conflict: Section "1.3. Limitations" on http://sqlite.org/sessionintro.html <http://sqlite.org/sessionintro.html> states: "• Prior to SQLite version 3.17.0, the session extension only worked with rowid tables, not WITHOUT ROWID tables. As of 3.17.0, both rowid and WITHOUT ROWID tables are supported." This contradicts with the statement in item 7 of Section "2. Differences From Ordinary Rowid Tables" on page http://sqlite.org/withoutrowid.html <http://sqlite.org/withoutrowid.html> "Note that since the session extension uses the update hook, that means that the session extension will not work correctly on a database that includes WITHOUT ROWID tables." The latter remark is probably outdated, or needs some refinement. Secondly, a typo: "changset" should be "changeset" in first line of Section "2.2. Conflicts" on page http://sqlite.org/sessionintro.html <http://sqlite.org/sessionintro.html> Met Vriendelijke Groet, Kind Regards, 谨致问候, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error using multiline command line argument with dot-command
Hi List, I want to report a minor issue for the Sqlite shell. It does not handle multiline command line arguments in which the second line contains a dot-command correctly. If the same statements are passed via stdin they are handled fine. Tested with Sqlite 3.15.2 on Ubuntu 14.04 using Bash. Example: Passing statements via stdin works fine: echo "SELECT 1; .mode csv SELECT 1;" | sqlite3 mydb.sq3 (no error) Passing the statements via a command line argument gives an error: sqlite3 mydb.sq3 "SELECT 1; .mode csv SELECT 1;" Error: near ".": syntax error Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no such column error
Hi RBS, Re-introducing mytable in the sub-select is a workaround (and to get the same semantics in the general case I have to use the same row from the inner mytable and outer mytable). As indicated in my original message I already have a workaround for the issue. The intention of my post was to report that sqlite incorrectly reports that mytable does not exists. Table mytable exists --- it is the target table of the UPDATE statement and still in scope. I see no reason why it is not allowed to use it in that EXISTS expression. So my question: is there a valid reason why sqlite does not know mytable.myfield2 in my simplified query? Or is this a bug? Regards, Rob Golsteijn UPDATE mytable SET myfield1 = (SELECT 1 from mytable ORDER BY EXISTS (SELECT 1 WHERE mytable.myfield2 = 1 ) ) RBS On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn wrote: > Hi List, > > I encountered a situation in which sqlite does not understand to which > field I try to refer. I simplified the original query to show the problem. > The simplified query itself is now completely meaningless (and for my > specific situation I could rewrite the query to work around the problem). > > In the example below I expected that mytable.myfield2 in the EXISTS > expression would refer to myfield2 of table mytable from the top level > UPDATE statement. It looks like the combination of an ORDER BY and an > EXISTS that refers to the table of an UPDATE statement causes sqlite to > report that myfield2 is unknown. Sqlite does not complain when I use > mytable.myfield2 in other places in the query. > > CREATE TABLE mytable > ( >myfield1 INTEGER, >myfield2 INTEGER > ); > > UPDATE mytable >SET myfield1 = (SELECT 1 > ORDER BY EXISTS (SELECT 1 >WHERE mytable.myfield2 =1 > ) > ); > > Error: no such column: mytable.myfield2 > > Tested with sqlite versions 3.8.4.3, 3.8.8.2, 3.11.1 and 3.15.0 > (latest). > > Met Vriendelijke Groet, Kind Regards, > > Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] no such column error
Hi List, I encountered a situation in which sqlite does not understand to which field I try to refer. I simplified the original query to show the problem. The simplified query itself is now completely meaningless (and for my specific situation I could rewrite the query to work around the problem). In the example below I expected that mytable.myfield2 in the EXISTS expression would refer to myfield2 of table mytable from the top level UPDATE statement. It looks like the combination of an ORDER BY and an EXISTS that refers to the table of an UPDATE statement causes sqlite to report that myfield2 is unknown. Sqlite does not complain when I use mytable.myfield2 in other places in the query. CREATE TABLE mytable ( myfield1 INTEGER, myfield2 INTEGER ); UPDATE mytable SET myfield1 = (SELECT 1 ORDER BY EXISTS (SELECT 1 WHERE mytable.myfield2 =1 ) ); Error: no such column: mytable.myfield2 Tested with sqlite versions 3.8.4.3, 3.8.8.2, 3.11.1 and 3.15.0 (latest). Met Vriendelijke Groet, Kind Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] builtin functions and strings with embedded nul characters
@Clemens, It is indeed documented that the behaviour is undefined when using a bind_text variant. I missed that part of documentation. On the other, as shown in my test queries, it is possible to construct strings with embedded nuls in sql itself, not using the C api (so technically, not allowing nuls could be a limitation of the C API). And as also remarked before by Simon an (embedded) nul is special in C-like languages. That made me wonder if sqlite would see an embedded nul as a "normal" character. Therefor my tests to see how sqlite functions operate on strings containing such nuls. From my experiments I could not include whether nul was "normal" or "special". The way I constructed TEXT values with embedded nuls is just a minor variation on how we add (unix) newlines in sql: 'line1' || x'0a' || 'line2". So, if nuls were a normal character this way to construct such a string with embedded nuls would not be very strange. I understand that changing the implementation would be a risk for backward compatibility and that most developers (including my company) do not want to used embedded nuls. My post was mainly intended to SHOW that string functions behave inconsistent when they contain embedded nuls, and WARN developers for this. Therefor, I asked to document if embedded nuls in string are allowed, e.g. on the www.sqlite.org/lang_corefunc.html. @Simon, Our original bug was caused by specifying an incorrect length for our C string using the C API. After we found our bug I did my tests, shown in my original post, using the SQlite command line tool. Rob Golsteijn wrote: > Due to a bug in our own code we inserted a string with embedded nul > character in the database. <http://www.sqlite.org/c3ref/bind_blob.html> says: | The result of expressions involving strings with embedded NULs is | undefined. > I investigated how the builtin functions handle strings with embedded > nul characters. Everything you found falls under the label "undefined". -- > Below my test queries (sqlite version 3.11.1), executed on TEXT data and BLOB > data. Nice set of tests. Did you execute them in the SQLite command-line tool or your own program ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] builtin functions and strings with embedded nul characters
Hi List,
Due to a bug in our own code we inserted a string with embedded nul character
in the database. This caused strange behavior when we used the built-in
function instr() on this data. As part of the analysis of this issue I
investigated how the builtin functions handle strings with embedded nul
characters. I want to share my findings.
Although I don't find in the sqlite documentation if a nul character is
special, and ends a string, the specification and implementation of some string
related functions seem to suggest this. However, the behaviour of string
functions is not always consistent.
For the tests below I assumed that the intention is that a string ends at the
first embedded nul character.
Summary:
- The instr() function returns incorrect results.
- The rtrim() and replace() functions return incorrect results, but this is
usually not noticeable.
- builtin functions not consistent in truncation after embedded nul character
Please document that functions that operate on string arguments process the
data till the first embedded nul character, if any, and (may) truncate excess
data (or otherwise that string data may/should not contain embedded nul
character).
Please adapt the implementation of instr() with string arguments to stop
searching after an embedded nul character. No strong opinion about desired
behaviour of rtrim() and replace().
Details:
Below my test queries (sqlite version 3.11.1), executed on TEXT data and BLOB
data. Most queries produce also HEX-output to show the exact output. Annotated
output added as comments.
.mode lines
CREATE TABLE test(t TEXT, b BLOB);
INSERT INTO test VALUES ( 'A' || x'00' || 'B ',
CAST('A' || x'00' || 'B ' AS BLOB));
-- show actual data value and type of data
SELECT t, hex(t), typeof(t),
b, hex(b), typeof(b)
FROM test;
-- t = A
-- hex(t) = 41004220 -- OK. contains embedded nul character
-- typeof(t) = text
-- b = A
-- hex(b) = 41004220 -- OK. contains embedded nul character
-- typeof(b) = blob
SELECT length(t),
length(b)
FROM test;
-- length(t) = 1 -- OK. till first embedded nul character
-- length(b) = 4 -- OK. size of entire blob
SELECT lower(t),
hex(lower(t)),
lower(b),
hex(lower(b))
FROM test;
-- lower(t) = a
-- hex(lower(t)) = 61 -- OK. String is assumed to end at embedded nul
character.
-- lower(b) = a
-- hex(lower(b)) = 61 -- OK. Blob is interpreted as string (suggested in spec),
so processed till embedded nul character.
SELECT rtrim(t),
hex(rtrim(t)),
rtrim(b),
hex(rtrim(b))
FROM test;
-- rtrim(t) = A
-- hex(rtrim(t)) = 410042 -- NOT OK. Data after embedded nul character is
considered string content. Changed part is usually
-- -- not noticed if resulting data is
processed as string (so upto embedded nul char)
-- -- Note that output is not truncted at nul
character like lower() does.
-- rtrim(b) = A
-- hex(rtrim(b)) = 410042 -- Blob is interpreted as string. Same behaviour as
string.
SELECT quote(t),
hex(quote(t)),
quote(b),
hex(quote(b))
FROM test;
-- quote(t) = 'A'
-- hex(quote(t)) = 274127 -- OK. As specified. String till
first embedded nul character quoted
-- quote(b) = X'41004220' -- OK. As specified. Hex
representation of entire string
-- hex(quote(b)) = 5827343130303432323027
SELECT replace(t, 'B', 'C'),
hex(replace(t, 'B', 'C')),
replace(b, 'B', 'C'),
hex(replace(b, 'B', 'C'))
FROM test;
-- replace(t, 'B', 'C') = A
-- hex(replace(t, 'B', 'C')) = 41004320 -- NOT OK. Replaces also characters
after embedded nul. Chnaged part is usually not noticed if
-- -- resulting data is processed
as string (so upto embedded nul char)
-- -- Note that output is not
truncated at embedded nul character like lower() does.
-- replace(b, 'B', 'C') = A
-- hex(replace(b, 'B', 'C')) = 41004320 -- OK. Replaces in entire data
SELECT substr(t, 1, 10),
hex(substr(t, 1, 10)),
substr(b, 1, 10),
hex(substr(b, 1, 10)),
substr(t, 3, 10),
hex(substr(t, 3, 10)),
substr(b, 3, 10),
hex(substr(b, 3, 10))
FROM test;
-- substr(t, 1, 10) = A
-- hex(substr(t, 1, 10)) = 41 -- OK. Till embedded nul character
-- substr(b, 1, 10) = A
-- hex(substr(b, 1, 10)) = 41004220 -- OK. Entire data
-- substr(t, 3, 10) =
-- hex(substr(t, 3, 10)) = -- OK. Pos 3 is after emdedded nul
character, so not part of string
-- substr(b, 3, 10) = B
-- hex(substr(b, 3, 10)) = 4220 -- Ok substitute in all data
SELECT instr(t, 'B'),
instr(b, 'B')
FROM test;
-- instr(t, 'B') = 3 -- NOT OK. String ends at embedded nul
character. This violates
[sqlite] Differences for DELETE with EXISTS
>> I observe a difference in results of a DELETE query using the EXISTS >> operator between Sqlite version 3.8.11.1 and 3.9.0. > > Thanks for the bug report. > > Ticket: https://www.sqlite.org/src/info/dc6ebeda9396087 > Candidate fix: https://www.sqlite.org/src/info/3f221f592a9a1900 > -- > Richard Hipp > drh at sqlite.org Thanx for solving this issue so quickly. We integrated the fix and we get the expected results. I guess that the fact that the behavior of sqlite was updated to the old behavior also answers my question how my query should be interpreted according to the SQL standard: EXISTS and subqueries should operate on the original (unmodified) tables, not on the (partly) updated table. Met Vriendelijke Groet, Kind Regards, Rob Golsteijn
[sqlite] Differences for DELETE with EXISTS
Hi List, I observe a difference in results of a DELETE query using the EXISTS operator between Sqlite version 3.8.11.1 and 3.9.0. After executing DELETE the number of remaining rows in the table differs. I can't figure out if this is regression, an improvement, or that the behavior is just undefined (i.e. both results is correct).
Re: [sqlite] Non-optimal query plan
>> Hi List, >> >> I was looking at the query plan of a rather simple query, but I don't >> understand why sqlite would choose this query plan. >> >> ...I was surprised that sqlite came up with the inferior query plan... >> >> Note: After an "analyze aaa" (on a decently populated table) sqlite chooses >> the full table scan instead of creating an automatic index (but our >> application never uses 'analyze' to avoid that other (bad performing) query >> plans are used during operation than during testing) >> Note 2: Adding "NOT INDEXED" to aa1 gives the desired query plan, but of >> course I prefer that sqlite choses the right query plan. >I think this issue is fixed since it does not do it in my version, but... even >if it isn't, let me just note that basically SQLite >chooses a Query Plan that might in many circumstances work perfectly since it >has no information available about the data. I do realize that it is impossible to find a QP that works correct in all cases. Certainly if sqlite has no knowledge about the tables's contents. We encounter query plans that do not work out for our data occasionally. And if needed, we "fix" them by pushing the query planner in the desired direction by using INDEXED BY, NOT INDEXED, CROSS JOINs, and adding +es, or rewriting the queries. The reason I reported this QP problem, is that the choice of this query plan is not optimal no matter what the table contents is, and I expected it to find this without data analysis. Appearently, the further optimizations/tuning in the query planner solved this issue. >Further >to this SQLite provides not one, but two explicit methods for you to improve >the query planning should the default not fit the best >for your specific query - yet you choose to ignore BOTH of them and expects >SQLite to pick a good plan without any knowledge of the >data, by default. [and ironically you are very happy to circumvent a whole >part of SQLite Query planning prowess to hide "other" QP >issues in stead of fixing it or requesting a fix, but won't add anything legal >and valid that would actually improve it. That is >just bizarre.] We use databases in a data format conversion processes. Each conversion uses another set of (fresh) databases, on which a few thousand different queries are executed once. The type and amount of data available per conversion differs significantly. Hence query plans when using ANALYZE on these database will differ very much. In the past we used ANALYZE, and we occasionally had problems with "never ending queries" for queries that usually only run minutes to a few hours. This was due to an unfortunate query plan. Then my company decided not to use "ANALYZE" anymore, and only rely on fixed QP. (In the future we might introduce the ANALYZE results of a "standard database" for all our databases to have the best of both worlds: predictable QP and sqlite having a bit of knowledge about our database contents, even if this might differ from actual content in many cases). Generally sqlite picks good QPs even if ANALYZE results are absent. There are some queries that need manual fine tuning for reasonable performance, and we do this. But of couse we want to avoid this tuning as much as possible. I don't just complain about any QP that doesn't work for me. We are regularly using the methods to finetune queries. But I reported this specific query plan because I think even without knowledge of the table contents the QP should have come up with a better plan: I expected that it should have found that the a single full table scan is always cheaper than creating an index on that same table and using this index for searching. > If I was the asker of this question I would concentrate on what you mention > in passing in Note1 with regards to avoiding "other" bad > plans. > If you do use Analyze and then at any point find the QP comes up with a bad > plan WITH access to analyze data - now THAT would > be a reason to complain and I have seen requests such as that cause a QP > overhaul many times here, you'd actually improve it for > everyone should you find a use-case with real QP problems based on proper > knowledge of data shape. As explained our main problem with ANALYZE is predictability: for some specific database contents and query combination we might suddenly get a bad query plan, that we never encountered during our application testing. Aborting our conversion process for this reason means that a lot of time is lost, and reconversions are needed. We want to prevent this. I guess there are more users that do not use ANALYZE for this reason. Regards, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Non-optimal query plan
Hi List,
I was looking at the query plan of a rather simple query, but I don't
understand why sqlite would choose this query plan.
For the following example:
create table aaa(id INTEGER, name_id INTEGER, type CHAR);
create table bbb(name_id INTEGER, name CHAR);
create index ix_aaa ON aaa(id);
create index ix_bbb ON bbb(name_id);
.explain ON
explain query plan
SELECT aaa1.name_id,
bbb1.name
FROM aaa aaa1,
aaa aaa2 ON aaa1.id = aaa2.id,
bbb bbb1 ON bbb1.name_id = aaa1.name_id
WHERE aaa1.type = 'A'
AND aaa2.type <> 'A';
=== output ==
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = aaa (AS aaa1)
{1,*} = aaa (AS aaa2)
{2,*} = bbb (AS bbb1)
WHERE AND(AND(AND(EQ({0:2},'A'),NE({1:2},'A')),EQ({0:0},{1:0})),EQ({2:0},{0:1}))
END
sele order from deta
-
0 0 0 SEARCH TABLE aaa AS aaa1 USING AUTOMATIC COVERING
INDEX (type=?)
0 1 1 SEARCH TABLE aaa AS aaa2 USING INDEX ix_aaa (id=?)
0 2 2 SEARCH TABLE bbb AS bbb1 USING INDEX ix_bbb
(name_id=?)
Sqlite decides to create an AUTOMATIC INDEX (time complexity O(n log n)) which
it then uses to iterate table aaa1. This index is not re-used for anything else
(it can't be re-used since 'type' is not used anywhere else) so only the
traversal of table aaa1 benefits from this index. However, I think, a full
table scan of aaa1 (time complexity O(n)) would always be faster, since for
creating the index it has to read that entire table anyway.
I was surprised that sqlite came up with the inferior query plan.
What makes sqlite think that creating + using an automatic index (for the outer
loop) makes the query faster the a full scan + filtering records? Is the
estimation of the costs for some action very bad for this query? Can I somehow
show the costs of a query plans (or of the rejected query plans)?
I'm using sqlite version 3.8.4.3
Note: After an "analyze aaa" (on a decently populated table) sqlite chooses the
full table scan instead of creating an automatic index (but our application
never uses 'analyze' to avoid that other (bad performing) query plans are used
during operation than during testing).
Note 2: Adding "NOT INDEXED" to aa1 gives the desired query plan, but of course
I prefer that sqlite choses the right query plan.
Regards,
Rob Golsteijn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sequential numbers
Hi Dave, You can of course also calculate a new sequence number based on the row ids. Just count the number of records with a smaller or equal rowid. This way it doesn't matter if rowid starts at 1 or if there are any gaps in the range. Example: CREATE TABLE aaa (i, seqnr); INSERT INTO "aaa" VALUES(10,NULL); INSERT INTO "aaa" VALUES(20,NULL); INSERT INTO "aaa" VALUES(50,NULL); INSERT INTO "aaa" VALUES(30,NULL); INSERT INTO "aaa" VALUES(20,NULL); UPDATE aaa SET seqnr=(SELECT count() FROM aaa smaller where smaller.rowid <= aaa.rowid); select * from aaa; i|seqnr 10|1 20|2 50|3 30|4 20|5 Regards Rob Golsteijn > Hi all, > >I have some rows in a table (not very many, typically less than 20) and I >want to generate a unique, sequential number for each row. In another dbms >I've used a row_number function (amongst others) to achieve this but I can't >see anything with equivalent functionality in sqlite3. My apologies if I've >missed something. > > > >I thought about using the 'rowid' and in some simple testing that seems to >give me what I want. But I need to check a couple of things. > > > >1) Is there a function that will give me unique, sequential numbers? > > > >2) Assuming that my processing follows this pattern: empty table T1 >completely, insert a number of rows, insert/select from T1 into T2. On the >'select' processing will the 'rowid' >** always ** start at 1? > > > >3) If I repeat the processing pattern shown in #2 above, will >subsequent selects always have rowid that starts from 1? > > > >Yes, I know that I could select the rows back to my application, generate >the numbers and then insert rows back into the table but I'm trying to do >this within the dbms. > > > >All help or ideas gratefully received. > > > >Cheers, > >Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shell core dumps on incomplete init file
>Dan Kennedy Wed, 04 Jun 2014 03:56:33 -0700 >On 06/04/2014 05:06 PM, Rob Golsteijn wrote: >> Hi List, >> I noticed that the sqlite shell core dumps when it is started with an init >> file that ends with an incomplete statement. >> >> Example: >> Init file called "my_init.sql" with the following contents: >> >> -- note that the line below is NOT a valid sqlite comment line, and >> hence an incomplete sqlite statement >> #.headers on >> >> >> sqlite3 -init my_init.sql my_db.sq3 > Cannot reproduce here. Any special options when you built SQLite? > > Do you have "valgrind" installed? If so, can you run this under it and post the output? Thanks. > Dan. We found and fixed the problem. It occured in our own changes/extensions of the shell. When reporting the issue I didn't realise that we used a modified shell. Thanks for pointing to valgrind, sorry for reporting the problem. Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] shell core dumps on incomplete init file
Hi List, I noticed that the sqlite shell core dumps when it is started with an init file that ends with an incomplete statement. Example: Init file called "my_init.sql" with the following contents: -- note that the line below is NOT a valid sqlite comment line, and hence an incomplete sqlite statement #.headers on sqlite3 -init my_init.sql my_db.sq3 Result: -- Loading resources from my_init.sql Error: incomplete SQL: #.headers on *** glibc detected *** ./bin/sqlite3: double free or corruption (fasttop): 0x035ecf80 *** Same thing happens when typing an incomplete sql statement in the shell and then press CTRL-D. Sqlite version is 3.8.4.3. Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mutally dependent JOIN clauses
Guys, Just a clarification to my previous post: I'm not looking for a solution to my example problem (but thanx anyway for the suggestions). What I'm really trying to understand is if the behaviour of the LEFT JOIN operator is correct. I think SQlLite produces the wrong result, but please correct me if I'm wrong. My statement: SELECT * FROM C LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; Should be interpreted as SELECT * FROM (C LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c) LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; The "C LEFT JOIN A" part is to be evaluated first; produces output for all value in C (only value 5). Each of these rows of C is completed either with all suitable value of A, or NULL if such value does not exist. Sqlite should find 2 candidates from A (values 3 and 4). The a values 1, 2, and 5 are not suitable, since they can never satisfy the ON clause. (Note that for finding these suitable values in A SqLite also needs to look into table B, but that is, I think, an implementtion detail) So the result of "C LEFT JOIN A" is c a - - 5 3 5 4 This intermediate table is LEFT JOINed with B. Again, this means that if a suitable b can be found it will complete the row with this b, and otherwise with NULL. Since both rows can be completed with suitable values of B the end result would be: c a b - - - 5 3 4 5 4 3 But SqLite produces additional 3 rows c a b - - - 5 1 -- additional row 5 2 -- additional row 5 3 5 4 5 5 -- additional row It looks like SqLite interprets the query as (warning pseudo sql) SELECT * FROM C LEFT JOIN ( A ON A.a*A.a + B.b*B.b = C.c*c.c LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c); So for each value in A it tries to find a suitable value in B resulting in a b - - 1 2 3 4 4 3 5 and then JOINs C with this intermediate table. leading to: c a b - - - 5 1 5 2 5 3 4 5 4 3 5 5 So my question if Sqlite produces the correct result remains. I think it doesn't. Regards, Rob The join is valid and the results are perfectly ok. You are using LEFT JOIN, which produces a row even if there is NO MATCH on the RHS, returning NULL for fields selected from there. SELECT * FROM C JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c a b -- -- -- 5 3 4 5 4 3 -----Urspr?ngliche Nachricht- Von: Rob Golsteijn [mailto:rob.golste...@mapscape.eu] Gesendet: Donnerstag, 16. J?nner 2014 11:21 An: sqlite-users@sqlite.org Betreff: [sqlite] Mutally dependent JOIN clauses Dear List, I came across a query with 2 LEFT JOINs of which the join clauses were mutually dependent. They did not produce the result I expected, but now I wonder if this is legal SQL in the first place. I created a small example which illustrates the problem. The example tries to find Pythagorean Triples (i.e. integers a, b, and c for which holds a^2 + b^2 = c^2) for given set of possible values for a, b, and c. Note: that in my query the JOIN-clause of A refers to table B that is LEFT JOINed later, and the JOIN clause of B refers back to table A. .headers on .null CREATE TABLE A (a INTEGER); CREATE TABLE B (b INTEGER); CREATE TABLE C (c INTEGER); INSERT INTO C VALUES(5); INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO A VALUES(3); INSERT INTO A VALUES(4); INSERT INTO A VALUES(5); INSERT INTO B VALUES(1); INSERT INTO B VALUES(2); INSERT INTO B VALUES(3); INSERT INTO B VALUES(4); INSERT INTO B VALUES(5); SELECT * FROM C LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c|a|b 5|1| 5|2| 5|3|4 5|4|3 5|5| When I look at the first result row and substitute that in the JOIN clause of A I get 1*1 + NULL*NULL= 5*5 which is definitely not true since the lhs of the expression is NULL and the rhs 25. This made me wonder which value Sqlite uses for B.b when it is LEFT JOINing table A. It is appearantly not the value that is actually used when LEFT JOINing table B. I don't know exactly what to expect from Sqlite. Either (1) an error indicating that it is illegal to refer in the JOIN clause of a LEFT JOIN to a table that is LEFT JOINed later; or (2) only the result rows c|a|b 5|3|4 5|4|3 but neither of these options is the case. Is this valid SQL that Sqlite cannot handle or is this just invalid SQL? In the latter case it would be nice if SqLite complained about it). Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mutally dependent JOIN clauses
Dear List, I came across a query with 2 LEFT JOINs of which the join clauses were mutually dependent. They did not produce the result I expected, but now I wonder if this is legal SQL in the first place. I created a small example which illustrates the problem. The example tries to find Pythagorean Triples (i.e. integers a, b, and c for which holds a^2 + b^2 = c^2) for given set of possible values for a, b, and c. Note: that in my query the JOIN-clause of A refers to table B that is LEFT JOINed later, and the JOIN clause of B refers back to table A. .headers on .null CREATE TABLE A (a INTEGER); CREATE TABLE B (b INTEGER); CREATE TABLE C (c INTEGER); INSERT INTO C VALUES(5); INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO A VALUES(3); INSERT INTO A VALUES(4); INSERT INTO A VALUES(5); INSERT INTO B VALUES(1); INSERT INTO B VALUES(2); INSERT INTO B VALUES(3); INSERT INTO B VALUES(4); INSERT INTO B VALUES(5); SELECT * FROM C LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c|a|b 5|1| 5|2| 5|3|4 5|4|3 5|5| When I look at the first result row and substitute that in the JOIN clause of A I get 1*1 + NULL*NULL= 5*5 which is definitely not true since the lhs of the expression is NULL and the rhs 25. This made me wonder which value Sqlite uses for B.b when it is LEFT JOINing table A. It is appearantly not the value that is actually used when LEFT JOINing table B. I don't know exactly what to expect from Sqlite. Either (1) an error indicating that it is illegal to refer in the JOIN clause of a LEFT JOIN to a table that is LEFT JOINed later; or (2) only the result rows c|a|b 5|3|4 5|4|3 but neither of these options is the case. Is this valid SQL that Sqlite cannot handle or is this just invalid SQL? In the latter case it would be nice if SqLite complained about it). Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] duplicate table aliases
Hi List, By coincidence we discovered that SQLite allows to use the same tables aliases multiple times in one query at the same level. As long as all referred columns are unambiguously named SQLite does not complain about duplicate table aliases. Although I cannot find any documentation that duplicate table aliases are not allowed, it is not what the average user would expect. Is this behaviour by design or should SQLite report an error? On the internet I found that (at least some) other database engines do not allow duplicate table aliases. Examples: CREATE TABLE aaa(a INTEGER, b INTEGER); CREATE TABLE bbb(a INTEGER, c INTEGER); INSERT INTO aaa VALUES(1,2); INSERT INTO bbb VALUES(2,3); -- Example 1: -- No error is reported since duplicate.c is not ambiguous -- Expected an error reporting duplicate table aliases SELECT duplicate.c FROM aaa duplicate, bbb duplicate; -- output: -- 3 -- Example 2: -- Error is reported since duplicate.a is ambiguous -- Expected an error reporting duplicate table aliases SELECT duplicate.a FROM aaa duplicate, bbb duplicate; -- output: -- Error: ambiguous column name: duplicate.a -- Example 3: -- Error is reported since the implicit duplicate.a is ambiguous -- Expected an error reporting duplicate table aliases SELECT * FROM aaa duplicate, bbb duplicate; -- output: -- Error: ambiguous column name: main.duplicate.a -- Example 4: -- No error, since local alias (for table bbb) shadows global alias (for table aaa), which is allowed. -- Behaviour as expected SELECT duplicate.a FROM aaa duplicate WHERE duplicate.b IN (SELECT duplicate.a FROM bbb duplicate); -- output: -- 1 Example 4 shows the expected behaviour (duplicate in the subquery refers to table bbb, in the main query to table aaa); For Examples 1-3 I would expect an error for duplicate table aliases. Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] name resolutionn in GROUP BY
Hi List,
The behaviour of Sqlite of w.r.t. name resolving in "group by" caluses seems to
have changed in the latest version.
This might lead to errors in previously working SQL code, or worse, undetected
changes in behaviour.
Example
create table test(name);
select min(name) from test group by lower(name); -- OK
select min(name) as name from test group by lower(name); -- Error: misuse of
aggregate: min() in version 3.7.17, OK in 3.7.15
In version version 3.7.15 the last query executed without problems, in 3.7.17
it reports an error (3.7.16 not tested).
In the last query Sqlite 3.7.15 used "test.name" for "name" in the group by
clause, Sqlite 3.7.15 seems to refer to the result column named "name".
This difference in name resolution is also illustrated by the following example:
.null
create table test(name);
insert into test values (NULL);
insert into test values ('abc');
select count(),
NULLIF(name,'abc') AS name
from test
group by lower(name);
In version 5.7.15 the output is:
1|
1|
(i.e. "group by" made 2 groups)
In version 5.7.17 the output is:
2|
(i.e. "group by" made 1 group)
I couldn't find a specifcation of which name should be used in the group by
clause.
I think it does not make sense to use the result column alias in the "group by"
clause since it is the result of a calculation based on a grouping, but then
again also used to produce the groups. Hence using the name of the result row
alias looks like a circular definition.
My question is whether the change is a bug or an intended change?
From the release history's text it looks like it could have been introduced by
the changes in Ticket 2500cdb9be05
Regards,
Rob Golsteijn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite accepts invalid column names
> On Thu, Jun 20, 2013 at 10:26 AM, Rob Golsteijn > wrote: > > > > > SELECT a.col2, > >b.col2, > >a.b.col2,-- invalid column name > >b.a.col2,-- invalid column name > >a.a.col2,-- invalid column name > >b.b.col2,-- invalid column name > >anything.a.col2 -- invalid column name > > FROM aaa a, > > bbb b ON a.col1 = b.col1; > > > > I get an error: "no such column: a.b.col2". What version of SQLite did > you say you were running? > > -- > D. Richard Hipp > d...@sqlite.org I found the issue in SQLite version 3.7.15. Great if it is solved in the latest version. Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite accepts invalid column names
Hi List, I made a copy-paste mistake, resulting in something that looked to me as invalid syntax for column names, but sqlite accepted it. The invalid column names are shown by the following example: .header on -- pragma is deprecated, but shows what happens pragma full_column_names=1; CREATE TABLE aaa(col1 INTEGER, col2 INTEGER); CREATE TABLE bbb(col1 INTEGER, col2 INTEGER); INSERT INTO aaa VALUES (1,2); INSERT INTO bbb VALUES (1,3); SELECT a.col2, b.col2, a.b.col2, -- invalid column name b.a.col2, -- invalid column name a.a.col2, -- invalid column name b.b.col2, -- invalid column name anything.a.col2 -- invalid column name FROM aaa a, bbb b ON a.col1 = b.col1; output: aaa.col2 bbb.col2 bbb.col2 aaa.col2 aaa.col2 bbb.col2 aaa.col2 -- -- -- -- -- -- -- 2 3 3 2 2 3 2 From the headers in the output it is clear that sqlite ignores the first part of the column name, which indeed is meaningless when using table aliases. According to the syntax diagrams these invalid colum names are expressions of the form .. and hence syntactically correct. But it is interpreted as ... It is confusing that part of the column name is ignored, but the column name is still accepted. Could sqlite be adapted to reject such invalid names and report an error, instead of silently ignore the first part of the column name? Or did I miss something? Regards, Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite shell cannot read its own dump file
Hi list, I observed that the Sqlite shell is not able to read it own dump files, in case a column contains the real value INF (infinity). I would expect that the shell would always be able to read any dump file it produced itself. Sqlite version is 3.7.15. Below a small sqlite script and its output to show te issue: CREATE TABLE test (r REAL); INSERT INTO test VALUES (1e999); -- stores +infinity INSERT INTO test VALUES (-1e999); -- stores -infinty .dump test -- write to file .output dump.sql .dump .output stdout -- read back from file DROP TABLE test; .read dump.sql Output: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test (r REAL); INSERT INTO "test" VALUES(Inf); INSERT INTO "test" VALUES(-Inf); COMMIT; Error: near line 4: no such column: Inf Error: near line 5: no such column: Inf --- Rob Golsteijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Garbage strings as query output
The following code is intended to change text values 'UND' into NULL values.
However, I see that string values different from 'UND' get corrupted. In the
example below the value 'ENG' is corrupted and the new value is a string of
three non-printable characters (ascii values 0x03 0x17 0x13) in the destination
table.
CREATE TABLE orig (name TEXT, l CHAR(3));
INSERT INTO "orig" VALUES('name1','ENG');
INSERT INTO "orig" VALUES('name2',NULL);
INSERT INTO "orig" VALUES('name3','UND');
CREATE TABLE dest (n text, l char(3));
-- The statement below messes up column l
INSERT INTO dest(n, l)
SELECT orig.name AS n,
CASE orig.l WHEN 'UND' THEN NULL
ELSE orig.l END AS l
FROM orig;
When inspecting table dest we see that the first record has a garbage value in
column l:
.dump dest
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE dest (n text, l char(3));
INSERT INTO "dest" VALUES('name1','???'); --- 'ENG'
expected, but has 3 non-printable chars 0x03 0x17 0x13
INSERT INTO "dest" VALUES('name2',NULL);
INSERT INTO "dest" VALUES('name3',NULL);
COMMIT;
Tested with sqlite versions 3.4.2 and 3.7.2. Occurs in both versions.
Do I miss something in my code or is this an sqlite bug?
Regards
Rob
PS: In the mean while I changed my code to use the nullif(l,'UND') function
instead of the above CASE..END construct. Nullif(l.'UND') works fine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
