From Martin Dougiamas (martin at moodle.com) Monday, 20 June 2005, 04:24 AM:
From (penny at catalyst.net.nz) Monday, 20 June 2005, 12:33 PM:
I've put a fix in cvs that uses one query for postgres and one for mysql. There's probably some clever way to do it that will work on both but I'm too flat out right now to find it, hopefully this will do for the now.
From Jon Papaioannou (pj at moodle.org) Monday, 20 June 2005, 10:15 PM:
From the MySQL manual found at http://dev.mysql.com/doc/mysql/en/join.html:
> Note that INNER JOIN syntax allows a join_condition only from
> MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only
> as of MySQL 4.0.11.
The query in question says just JOIN, which I believe means CROSS JOIN, and thus cannot be run in MySQL < 4.0.11.
What about simply stating what kind of JOIN (LEFT/INNER/CROSS) we want? That looks like it should fix it.
From Jon Papaioannou (pj at moodle.org) Monday, 20 June 2005, 10:16 PM:
Heh... I didn't even read it as carefully as I should. It specifically mentions a bare JOIN. So it's sure to be fixed by changing that to a LEFT or INNER JOIN.
From (penny at catalyst.net.nz) Tuesday, 21 June 2005, 04:29 AM:
Pim Koeman has also posted a potential fix, which involves swapping the order of the tables leading up to the left join. This may be a better solution, but I'm flat out right now.. what we have works & I'll look at this soon.
Re mysql and joins - I think bareword join implies inner join. But we have bareword JOINs all through moodle .. maybe it's the combination of JOIN and LEFT JOIN in this case that's breaking?
From Jon Papaioannou (pj at moodle.org) Tuesday, 21 June 2005, 04:48 AM:
AFAIK, changing the order of the tables can optimize a query but it can't make an illegal query legal.
MySQL docs imply that JOIN === CROSS JOIN. At least that's how I read it. And the snippet I pasted seems to affirm this, since it says that MySQL treats bare JOIN and CROSS JOIN the same.
As for bareword JOINs, I 've never written one. Martin also wasn't writing joins in the beginning, so most legacy code doesn't use joins at all. Can't remember seeing many of them either. I don't know how many there really are floating around.
From Martin Langhoff (martin at catalyst.net.nz) Tuesday, 21 June 2005, 04:56 AM:
JOIN, or the comma tablea,tableb all mean LEFT INNER JOIN as long as you add a clause (using ON or in the WHERE). If there's no clause, yes, they are CROSS JOINs.
A CROSS JOIN is all rows from A x all rows from B which is most certainly not what we want. Does that help?
From (penny at catalyst.net.nz) Saturday, 16 July 2005, 04:29 PM:
can we close this bug? Or do we want to keep trying to find a query that will work in both pg & mysql small number?
From (penny at catalyst.net.nz) Friday, 26 August 2005, 01:03 PM:
From Morgan Benton (morgan.benton at njit.edu) Thursday, 19 January 2006, 04:37 AM:
More information can be seen in this post:
In my comment from 18 Jan 2006.
From Martin Langhoff (martin at catalyst.net.nz) Thursday, 19 January 2006, 04:46 AM:
Morgan – you've hit a new bug... open a new bug entry for it.