LEFT JOIN breaks and we don't like that

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

LEFT JOIN breaks and we don't like that

Post by patrikG »

Ladies & Gents,

my MySQL-knowledge is somewhat out-of-date, but LEFT JOIN has been part of my knowledge. For some reason, however, the following won't work anymore (SQL-statement simplified, but breaks all just the same)

Code: Select all

SELECT walks.id, walks.station, walks.station_address, walks.type, walks.county, walks.description, walks.walk_name, walks.duration, walks.national_park, walks.station_address_type, walks.url_title, walks.country, walks.os_map FROM walks, waypoints LEFT JOIN rating ON walks.id = rating.walk_id WHERE walks.id = waypoints.walk GROUP BY rating.walk_id
Error is: "#1054 - Unknown column 'walks.id' in 'on clause' "
MySQL version is 5.1.30

Strange thing is, the statement worked until recently the only possible thing that could have changed (and I haven't followed up, yet) is that the hosting company may have updated to MySQL 5.1.30

Any help is appreciated
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: LEFT JOIN breaks and we don't like that

Post by John Cartwright »

I recently experienced the exact same issue. :banghead:

We recently copied some software that we developed onto another server running mysql 5.0.67. I'm not sure what mysql version the original server was running, but the troublesome query certainly was working previously.

//following thread
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: LEFT JOIN breaks and we don't like that

Post by Benjamin »

I believe the LEFT JOIN needs to be right after the table you are joining on. Try this. I believe I put the comma in the correct place in the FROM clause.

Code: Select all

 
SELECT
  walks.id,
  walks.station,
  walks.station_address,
  walks.type,
  walks.county,
  walks.description,
  walks.walk_name,
  walks.duration,
  walks.national_park,
  walks.station_address_type,
  walks.url_title,
  walks.country,
  walks.os_map
FROM
  walks
  LEFT JOIN rating ON walks.id = rating.walk_id,
  waypoints
WHERE
  walks.id = waypoints.walk
GROUP BY rating.walk_id
 
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

MySQL manual wrote: INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.
Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
Simple solution - parenthesize your comma joins.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Re: LEFT JOIN breaks and we don't like that

Post by patrikG »

Thanks, astions, but that wasn't it. Weirdan nailed it - thanks, mate :)
The full statement should be

Code: Select all

SELECT walks.id, walks.station, walks.station_address, walks.type, walks.county, walks.description, walks.walk_name, walks.duration, walks.national_park, walks.station_address_type, walks.url_title, walks.country, walks.os_map FROM (walks, waypoints) LEFT JOIN rating ON walks.id = rating.walk_id WHERE walks.id = waypoints.walk GROUP BY rating.walk_id
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: LEFT JOIN breaks and we don't like that

Post by VladSun »

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).
So, in MySQL CROSS JOIN == INNER JOIN == , but only in the absence of ON condition in case INNER JOIN is used. Personally, I would never use INNER JOIN when I mean CROSS JOIN. Nor I would ever use a comma, either for CROSS or INNER JOIN.

In my queries, when I put INNER JOIN I really mean INNER JOIN, so ON condition always presents. And when I put CROSS JOIN I really mean CROSS JOIN, so there is no ON condition.
IMHO this query:
[sql]SELECT    *FROM    t1, t2WHERE     t1.id = t2.FK_t1_id[/sql]
which can be written as:
[sql]SELECT    *FROM    t1CROSS JOIN    t2WHERE     t1.id = t2.FK_t1_id[/sql]
and which in fact is:
[sql]SELECT    *FROM    t1INNER JOIN    t2 ON t1.id = t2.FK_t1_id[/sql]
So, I think that using the comma "operator" (or "condition-less" INNER JOIN, or CROSS JOIN) together with a related WHERE condition, in order to "implement" an INNER JOIN with ON condition is not right.

That's why I think your query is more readable when it's put like this:
[sql]SELECT     walks.id,     walks.station,     walks.station_address,     walks.type,     walks.county,     walks.description,     walks.walk_name,     walks.duration,     walks.national_park,     walks.station_address_type,     walks.url_title,     walks.country,     walks.os_map FROM     walksINNER JOIN    waypoints ON walks.id = waypoints.walk LEFT JOIN     rating ON walks.id = rating.walk_id GROUP BY     rating.walk_id[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

patrikG wrote:Thanks, astions, but that wasn't it. Weirdan nailed it - thanks, mate :)
Actually following astions' advice would have helped you just as well. Our advices are semantically equivalent in this case
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

VladSun wrote:That's why I think your query is more readable when it's put like this:...
Only if precedence of INNER JOIN is the same as or higher than precedence of LEFT JOIN. Myself, I don't like to rely on operator precedence, especially since I learned it could change between MySQL versions.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: LEFT JOIN breaks and we don't like that

Post by VladSun »

Weirdan wrote:Only if precedence of INNER JOIN is the same as or higher than precedence of LEFT JOIN. Myself, I don't like to rely on operator precedence, especially since I learned it could change between MySQL versions.
Indeed, I focused on the usage of coma operator, its ambiguity, the ambiguity of the INNER JOIN and using WHERE clause instead of ON condition. I really can't understand why MySQL provides so much of syntax freedom to the developer - it's confusing, it's not strict. It's like HTML vs. XHTML.

Also, I don't see how any of JOIN types could have precedence over the others. I think it's the order of JOINS which defines the result.

In this case, the lower precedence of the coma operator raises a syntax (parsing) error because as far as I understand it, it is a syntax parsing precedence, not a logical (run-time?) one.

Did I follow your arguments?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

VladSun wrote:Also, I don't see how any of JOIN types could have precedence over the others. I think it's the order of JOINS which defines the result.
I view comma in FROM clause as an alternative syntax for CROSS JOIN. Following this logic, and taking into account that its precedence is lower than other join types, I have no confidence that other join types are equal in this regard. Even if I learned join precedence table by heart I would forget it next week/month/year. This is the reason I prefer to use explicit parenthesis.
VladSun wrote:In this case, the lower precedence of the coma operator raises a syntax (parsing) error because as far as I understand it, it is a syntax parsing precedence, not a logical (run-time?) one.
Hmm.... no. Syntactically expression (a, b JOIN c [on a.a_primary=c.foreign_a]) is valid, it's type checking (query planning) stage when it becomes apparent that join operation is being requested on incompatible types (b doesn't have a_primary field).
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: LEFT JOIN breaks and we don't like that

Post by VladSun »

Weirdan wrote:
VladSun wrote:Also, I don't see how any of JOIN types could have precedence over the others. I think it's the order of JOINS which defines the result.
I view comma in FROM clause as an alternative syntax for CROSS JOIN. Following this logic, and taking into account that its precedence is lower than other join types, I have no confidence that other join types are equal in this regard. Even if I learned join precedence table by heart I would forget it next week/month/year. This is the reason I prefer to use explicit parenthesis.
I am referring to the "normal" syntax of JOINS - no comas allowed :) I don't think there are precedence issues with this syntax of JOINs.
Weirdan wrote:
VladSun wrote:In this case, the lower precedence of the coma operator raises a syntax (parsing) error because as far as I understand it, it is a syntax parsing precedence, not a logical (run-time?) one.
Hmm.... no. Syntactically expression (a, b JOIN c [on a.a_primary=c.foreign_a]) is valid, it's type checking (query planning) stage when it becomes apparent that join operation is being requested on incompatible types (b doesn't have a_primary field).
I didn't make myself clear enough:
by "syntax parsing precedence" I meant that the query woudln't be executed and it would raise an error;
by "a logical (run-time?) precedence" I meant something like having this

Code: Select all

X = 2 + 2 * 2
one to expect 8 as a result value for X :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

VladSun wrote:
Weirdan wrote: Syntactically expression (a, b JOIN c [on a.a_primary=c.foreign_a]) is valid, it's type checking (query planning) stage when it becomes apparent that join operation is being requested on incompatible types (b doesn't have a_primary field).
I didn't make myself clear enough:
by "syntax parsing precedence" I meant that the query woudln't be executed and it would raise an error;
by "a logical (run-time?) precedence" I meant something like having this

Code: Select all

X = 2 + 2 * 2
one to expect 8 as a result value for X :)
Those are same precedences. Consider the following expression:

Code: Select all

 
$x = $a + $b * $c;
 
Syntactically this expression is valid, regardless of types of variables involved, and translates to the following AST tree:

Code: Select all

 
     [=]
    /   \
[$x]     [+]
        /   \
    [$a]     [*]
            /   \
        [$b]     [$c]
 
Precedence rules affect the shape of this tree, the tree above represents the case when precedence(*) > precedence(+). AST tree for the case when precedence(*) < precedence(+) would look like this:

Code: Select all

 
     [=]
    /   \
[$x]     [*]
        /   \
      [+]    [$c]
     /   \
 [$a]     [$b]
 
Do you see? So far types were not considered at all.

Depending on the types of variables and operator characteristics (what operand types they are defined over and what are their resulting types) this expression may or may not be possible to execute. But if it's possible, the order of its execution is already defined by the shape of its AST tree. Thus there can't be two conceptually different operator precedences ('syntax parsing' and 'logical (run-time)')
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: LEFT JOIN breaks and we don't like that

Post by VladSun »

These:

Code: Select all

X = 4 - 2 * 2
(executes and gives a "false" result)

Code: Select all

X = 1 / (4 - 2 * 2)
(executes and throws an exception)

are closer to what I meant :)

PS: Never mind :)
I've just wanted to advice "Don't use coma operator instead of JOIN keyword, and don't use WHERE conditions instead of ON conditions" :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: LEFT JOIN breaks and we don't like that

Post by Weirdan »

VladSun wrote:

Code: Select all

X = 1 / (4 - 2 * 2)
(executes and throws an exception)

are closer to what I meant :)
That is completely different and is actually runtime error that cannot be caught at compile stage. Semantically invalid SQL statement like what was patrikG's problem is different in that the database server doesn't even try to execute it, reporting an error when statement is being prepared for execution (due to invalid operand types).
Post Reply