On 06/03/2013 4:50 AM, Tom Matrix wrote:
Richard Hipp <drh@...> writes:
On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix <ratomatrix@...> wrote:
I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.
A simple, reproducible test case for (what we think is) your problem can be
seen in this ticket:
www.sqlite.org/src/tktview/fc7bd6358f59b
This bug has been latent in SQLite for almost four years and you are the
first to hit it. Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression. You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text. This
does not excuse SQLite: It is still getting the wrong answer and needs to
be fixed. We are working on a fix now. But a simple change to your schema
will work around the problem and get you going even before that fix is
available.
Very good example, thank you! It really solved my problem. Nevertheless, I think
I don't have to mention that entries.measurementid must have been an integer,
and this bug must be fixed in our schema, too.
My only remaining concern is, however: Should not SQLite give an error (or at
least a warning) in cases where a foreign key constraint refers to a different
data type?
My understanding is that it shouldn't have mattered, because the text
column should have been converted to int before performing the
comparison. This works for simpler queries where the non-int column is
only compared with an int column. Simplifying the test case from the
ticket above:
sqlite3> select * from t,i where textid=intid;
12|12
34|34
However, when comparing the non-int column with both an int column and a
non-int one, the bug is that the promotion no longer happens and the
join fails:
sqlite3> select * from t t1,t t2, i where t1.textid=i.intid and
t1.textid=t2.textid;
(nothing)
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
