List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:January 28 2013 3:56pm
Subject:Re: adding column breaks query
View as plain text  
Hello Larry,

On 1/28/2013 10:42 AM, Larry Martell wrote:
> I have a query that works fine:
>
>
> SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
> data_cstmeta.date_time as 'Last Run'
> FROM data_recipe, data_ppl, data_cstmeta
>     INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
>     FROM data_cstmeta
>     GROUP BY recipe_id) grouped
>     ON data_cstmeta.id = grouped.id
>     AND data_cstmeta.date_time = grouped.MaxDateTime
> WHERE data_cstmeta.ppl_id = data_ppl.id
> AND data_cstmeta.recipe_id = data_recipe.id
> ORDER BY data_cstmeta.date_time desc;
>
>
> Now I need to add one more column to it, data_tool.name, so I did this:
>
>
> SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
> data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
> FROM data_recipe, data_ppl, data_cstmeta, data_tool
>     INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
>     FROM data_cstmeta
>     GROUP BY recipe_id) grouped
>     ON data_cstmeta.id = grouped.id
>     AND data_cstmeta.date_time = grouped.MaxDateTime
> WHERE data_cstmeta.ppl_id = data_ppl.id
> AND data_cstmeta.recipe_id = data_recipe.id
> AND data_cstmeta.tool_id = data_tool.id
> ORDER BY data_cstmeta.date_time desc;
>
> And now it fails with:
>
> ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'
>
> I've messed around with this for a day and I can't figure out what
> stupid mistake I'm making.
>

You didn't add just one column, you added an entire table to the FROM 
clause.  Also, you used a comma-join instead of an ANSI JOIN clause.

Because of changes we made in 5.0.12 to make MySQL behave more like the 
SQL standard requires, the priority of the comma operator in join 
operations was demoted. This means that when the explicit JOIN 
declaration is being evaluated (which has a higher order of precedence) 
only the derived table (grouped) and the base table `data_tool` are 
visible to the ON clause. The `data_cstmeta` implicit join will be 
evaluated later as it is based on a comma-join.

I suggest you rewrite your queries to only use the explicit "JOIN ... ON 
..." syntax for all of your joins to avoid these problems in the future.
http://dev.mysql.com/doc/refman/5.5/en/join.html

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


Thread
adding column breaks queryLarry Martell28 Jan
  • Re: adding column breaks queryShawn Green28 Jan
    • Re: adding column breaks queryLarry Martell28 Jan
Re: adding column breaks queryLarry Martell28 Jan