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

[Sebastien B. <sbi...@us...>]

        Hi John and all,

  I wish I had more time for this now :/ BTW, I just wanted to say thank
you John for the tests and the example. They reveal that the SQL
generated for MySQL is *incorrect* for such qualifiers.

  I created bug ticket #857803 for that, where you'll also find the
  patch John offered and which solves the problem for mysql 4.0+

https://sf.net/tracker/index.php?func=3Ddetail&aid=3D857803&group_id=3D5893=
5&atid=3D489335

When a patch is available solving the problem for mysql 3.23 as well it
will be put there and announced here.


  I'm afraid there is no working solution for mysql 3.23 using the JOIN
keyword (if there is one, I couldnt find it) for such qualifiers, so
we'll need to forget about the JOIN keyword for mysql 3.23 and use
explicit joints, i.e. w/ no join kw. but w/ the join condition in the
where clause (as this is already done for oracle8i).

  mysql_server_version() will probably also check for an env. variable
  before checking MySQLdb.get_client_info(), since it seems that MySQLdb
  can be built w/ a 3.23 compatible library while speaking w/ a 4.0
  server (I have an example at home:)

-- S=E9bastien.


John Georgiadis <ig...@do...> writes:
> 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*"))'
>=20
> 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
>=20
> 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 )  )
>=20
> 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%'))