Re: [Modeling-users] [patch] Switch to generic nested join for recent versions of MySQL

[John G. <ig...@do...>]

Unpatched and patched both succeed. However if I switch the last test
from:
'(age<100) AND pygmalion.books.title like "G*"'
To:
'(age<100) AND ((books.title like "G*") OR (pygmalion.books.title like
"G*"))'

Then the unpatched version fails, while the patched succeeds. I think,
this is related to two joins (Writer.books & Writer.pygmalion) going to
different directions. Below is the generated SQL in both cases

SELECT DISTINCT t0.ID, t0.LAST_NAME, t0.FIRST_NAME, t0.AGE,
t0.FK_WRITER_ID, t0.BIRTHDAY FROM BOOK t1 INNER JOIN WRITER t0 ON (
t0.ID=3Dt1.FK_WRITER_ID )  ( BOOK t3 INNER JOIN WRITER t2 ON (
t2.ID=3Dt3.FK_WRITER_ID )  )  INNER JOIN WRITER t0 ON (
t0.FK_WRITER_ID=3Dt2.ID )  WHERE (t0.AGE < 100 AND (t1.title LIKE BINARY
'G%' OR t3.title LIKE BINARY 'G%'))
You have an error in your SQL syntax.  Check the manual that corresponds
to your MySQL server version for the right syntax to use near '( BOOK t3
INNER JOIN WRITER t2 ON ( t2.ID=3Dt3.FK_WRITER_ID )  )

SELECT DISTINCT t0.ID, t0.LAST_NAME, t0.FIRST_NAME, t0.AGE,
t0.FK_WRITER_ID, t0.BIRTHDAY FROM WRITER t0 INNER JOIN BOOK t1 ON
t0.ID=3Dt1.FK_WRITER_ID INNER JOIN  ( WRITER t2 INNER JOIN BOOK t3 ON
t2.ID=3Dt3.FK_WRITER_ID )  ON t0.FK_WRITER_ID=3Dt2.ID WHERE (t0.AGE < 100
AND (t1.title LIKE BINARY 'G%' OR t3.title LIKE BINARY 'G%'))



On Mon, 8 Dec 2003, Sebastien Bigaret wrote:

>
>        Hi,
>
>  Thanks for the report. I've searched the mysql-site for about half an
>  hour but couldn't find any hints on when the change happened. Do you
>  (or anyone here) have any clue?
>
>  Second, could you possibly confirm that the test
>test_11_allQualifierOperators() in test_EditingContext_Global.py fails
>when you patch is /not/ applied, and succeeds when it is applied? If it
>does not, then I'll need to write a new one revealing that bug.
>
>  Given that this particular test behaves as expected, your patch will
>  be applied as-is and integrated to the main trunk. Thanks again for
>  reporting.
>
>-- S=E9bastien.
>
>
>John Georgiadis <ig...@do...> wrote:
>> Hi Sebastian,
>>
>> I get a syntax error from MySQL-4.0.11 when using nested JOINS created b=
y
>> MySQLAdaptorLayer.MySQLExpression._addTableJoinsForAlias(). If I switch =
to
>> Modeling.SQLExpression._addTableJoinsForAlias(), the problem is gone.
>>
>> The following patch does this for mysql version >=3D 4.0.11. If anyone c=
an
>> confirm this for an older version of MySQL, then feel free to lower the
>> version numbers in the tuple.
>
>