Unknown column

[crimaniak]

This is my query:

SELECT countries.*, lnk0.Value AS fraudIndex, lnk1.Value AS enabled
FROM o as countries LEFT JOIN l_TINYINT AS lnk0 ON
lnk0.FID=countries.ID AND lnk0.TID=55 LEFT JOIN l_TINYINT AS lnk1 ON
lnk1.FID=countries.ID AND lnk1.TID=57 WHERE ((enabled=1) AND
countries.ParentID=46) ORDER BY Name

And this is result:

Unknown column 'enabled' in 'where clause'

Why? 🙁

'enabled' is not keyword.
Columns aliases seems to be ignored here.

MySQL .49max for Windows, table type InnoDB.

RS. Query so strange because generated. Really it's
SELECT countries.*, countries->\shop\parms\countries\fraudIndex as
fraudIndex, countries->\shop\parms\countries\enabled as enabled FROM
\shop\parts\countries\% WHERE enabled=1 ORDER BY Name

[sarahk]

There is a difference between WHERE and HAVING. Having would allow you to filter on "enabled" but Where can't see it yet.

Just change the clause to use lnk1.Value and you'll be fine.

Sarah

[crimaniak]

This is strange because here http://www.mysql.com/doc/S/E/SELECT.html is written (underlined by me):

• Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions begin with 1:

mysql> SELECT college, region, seed FROM tournament
-> ORDER BY region, seed;
mysql> SELECT college, region AS r, seed AS s FROM tournament
-> ORDER BY r, s;
mysql> SELECT college, region, seed FROM tournament
-> ORDER BY 2, 3;

I know simple solution to avoid aliases using, but I try understand there is bug.