[RESOLVED] MySQL Version Error?

[cap97]

I have the following query:

SELECT *, MIN(dates.dates_time) AS 'min' FROM events, dates, types, seasons
	LEFT JOIN shows ON events.events_shows_id = shows.shows_id
	WHERE events.events_types_id = types.types_id
	AND events.events_id = dates.dates_events_id
        AND events.events_seasons_id = seasons.seasons_id
	AND seasons.seasons_active = 1
        AND types.types_id = 1
	GROUP BY events.events_id
	ORDER BY min

My old host runs MySQL v. 4.1.22. The above query worked. I have since switched to a new host, who uses MySQL v. 5.1.46 and that query gives me the following error message:

#1054 - Unknown column 'events.events_shows_id' in 'on clause'

As far as I can tell, the tables are all the same after the host switch. Certainly there IS an "events.events_shows.id" column in the "events" table so why the error is saying there isn't one is baffling me. Are there new JOIN rules with this newer version of MySQL?

[mrbaseball34]

You have an ambiguous column reference * in your select.
You MUST prefix columns with the table they are coming from when doing this type of query.

[cap97]

Never mind. Specifically...

According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
<table factor>
| <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
...

Thus when you write

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1 columns
from expr.

Unfortunately, this change is not properly documented in the manual, it will be fixed.

So changed my query as such and it works like a charm:

SELECT *, MIN(dates.dates_time) AS 'min' FROM (events, dates, types, seasons)
	LEFT JOIN shows ON (events.events_shows_id = shows.shows_id)
	WHERE events.events_types_id = types.types_id
	AND events.events_id = dates.dates_events_id
        AND events.events_seasons_id = seasons.seasons_id
	AND seasons.seasons_active = 1
        AND types.types_id = 1
	GROUP BY events.events_id
	ORDER BY min

It's all about the parenthesis people!