Darren Duncan <[EMAIL PROTECTED]> schrieb am 04.08.2004, 10:16:55:
> At 8:48 AM +0200 8/4/04, wrote:
> >When trying the first of your suggestions, the result I got was this:
> >+-----------+-------------+-------+-------+
> >| object_id | value_table | value | value |
> >+-----------+-------------+-------+-------+
> >| 1 | 1 | Hallo | 123 |
> >| 1 | 2 | Hallo | 123 |
> >+-----------+-------------+-------+-------+
> >
> >The problem here is that in both rows of the result, both the
> >string_value and the int_value are present.
> >I get this result both in SQLite and in MySQL.
> >
> >Any more ideas on how to get the result I want from SQLite?
> >Or is this something that can't be done?
>
> You never said what happened when you tried my *second* suggestion,
> and ignored value_table entirely. That's what I *really* wanted you
> to do ...
>
> Actually, just try this instead:
>
> SELECT
> properties.object_id,
> string_values.value,
> int_values.value
> FROM properties
> LEFT OUTER JOIN string_values ON properties.property_id =
> string_values.property_id
> LEFT OUTER JOIN int_values ON properties.property_id =
> int_values.property_id
> ;
>
> This suggestion cuts to the chase and shows what you really should be doing.
>
> FYI, this may mean the same thing, but proper expression grammar has
> dependent and independent values in an equivalence test always on a
> certain side of the '='. So you should write like this:
>
> SELECT
> properties.object_id,
> string_values.value,
> int_values.value
> FROM properties
> LEFT OUTER JOIN string_values ON string_values.property_id =
> properties.property_id
> LEFT OUTER JOIN int_values ON int_values.property_id =
> properties.property_id
> ;
>
> You might want to see if that last one gives different results.
Hello Darren,
I have tried both of your latest suggestions, and both result in the
following:
properties.object_id string_values.value int_values.value
-------------------- ------------------- ----------------
1 Hallo 123
1 Hallo 123
which is what I would have expected using this query.
However, this is not the result I need.
I've done lots of playing around today, and I'm more and more convinced
that it's a bug in the query optimizer.
Let me explain why I think so:
When I add a column 'value_table' to both 'string_values' and
'int_values', and set its value to 1 in 'string_values' and to 2 in
'int_values', I can do the following query:
SELECT
object.object_id,
properties.value_table,
string_values.value AS string_value,
int_values.value AS int_value
FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON
(properties.property_id = string_values.property_id) AND
(properties.value_table = string_values.value_table)
LEFT OUTER JOIN int_values ON
(properties.property_id = int_values.property_id) AND
(properties.value_table = int_values.value_table)
;
This query returns the result that I expect:
object.object_id properties.value_table string_value int_value
---------------- ---------------------- ------------ ----------
1 1 Hallo
1 2 123
For the query optimizer it seems to make a huge difference whether is
use (properties.value_table = string_values.value_table) or
(properties.value_table = 1) in the join condition. For me it seems to
be an overly aggressive optimization taking place.
Of course I could keep this (redundant) field in the string_values and
int_values tables as a workaround; however, I would prefer not having
to waste disk space.
I've done some searching for LEFT JOINs and SQLite and stumbled across
the bug ticket #350. The bug described in that ticket seems to be
similar to my problem.
I have also tried to find a SELECT that behaves exactly like my original
one, but doing the filtering in the WHERE clause.
However, I failed. I can't find a WHERE clause that produces the same
semantics like my JOIN ON clause.
Surprisingly, I've read somewhere that SQLite converts JOINs into WHERE
clauses internally. Is there more information on this?
Would be great if someone could confirm that this is a bug, or explain
me why it's not.
Thanks
Rolf Schäuble
P.S.: I have put Mr. Hipp on CC because after many hours of testing and
reading up on SQL (that's basically all I did today), I'm quite
convinced that there's a bug.
>
> -- Darren Duncan
