I am running SQLite 3.6.22 (debugging code enabled) with extensive use of
virtual tables that behave as if their structures were as follows (unused
fields have been omitted):
CREATE TABLE one (
a unsigned,
b unsigned,
c unsigned,
d unsigned,
s text);
CREATE INDEX one_pk ON one (a, b, c, d);
CREATE TABLE two (
one_rowid unsigned,
b unsigned,
e unsigned,
f unsigned,
g unsigned);
CREATE INDEX two_pk ON two (one_rowid);
CREATE TABLE three (
a unsigned,
b unsigned,
e unsigned,
h unsigned);
CREATE INDEX three_pk ON three (a, b, e, h);
When I attempt to perform a certain join (shown below), the disableTerm
function fails in the ALWAYS assertion, because the wtFlags field already has
the TERM_CODED bit set. As far as I can tell, it is looking at the first
constraint in the ON clause of the LEFT JOIN, possibly for the second time.
The problem goes away on any of the following conditions:
- native tables are used as opposed to virtual tables
- the first constraint of the WHERE clause (one.a = 3) is omitted
- the constant from the WHERE clause is repeated in the ON clause
(three.a = 3)
I suspect there is a subtle difference in parsing and/or code generation
between native and virtual tables the leads to this effect
SELECT
one.s,
two.b,
two.e,
two.f
FROM one
join two ON two.one_rowid = one.rowid
left join three ON three.a = one.a and
three.b = two.b and
three.e = two.e and
three.h = two.f
where one.a = 3 and
two.g = 1;
________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 - 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
