"unknown column image_location_country_alt" but this column must exist!

[phillip....@gmail.com]

[quote]
SELECT
IF(image_location_country IS NULL OR image_location_country = '', 'x',
NULL) AS image_location_country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_name, 2)) DESC

LIMIT 1
[/quote]

This query produces the following error:

[quote]
Unknown column 'image_location_country_alt' in 'order clause' using
query
[/quote]

I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil

[strawberry]

Not quite sure what's going on here, but does this work:

SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull

FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY isnull ASC,

[phillip....@gmail.com]

No it doesn't "unknown column "isnull""

Phil

[strawberry]

Can I just confirm that you're using mysql 4.1 or later?

[phillip....@gmail.com]

Yes, 4.1.20

[strawberry]

So this query works fine:

SELECT image_path AS imagepath FROM image ORDER BY imagepath LIMIT 1;

Sorry, I'm clutching at straws now (as if you couldn't guess)

[phillip....@gmail.com]

I got it.. but the solution makes no sense. You apparently can't do
upper() on this alias because MySQL doesn't allow functions upon
aliases.

Phil

[Bill Karwin]

phillip....@gmail.com wrote:
> [quote]
> Unknown column 'image_location_country_alt' in 'order clause' using
> query
> [/quote]

I have confirmed the error you saw, using both MySQL 4.1.21 and 5.0.24a.

This looks identical to the problem in Bug #11694:
http://bugs.mysql.com/bug.php?id=11694

If you just use the plain column alias name, it works. If you include
it in an UPPER() expression, it breaks. This is consistent with the bug
described in Bug #11694.

That bug was supposed to have been fixed as of MySQL 4.1.16. But it
appears to have regressed (that is, re-broken).

Regards,
Bill K.

[Bill Karwin]

Bill Karwin wrote:
> This looks identical to the problem in Bug #11694:
> http://bugs.mysql.com/bug.php?id=11694

I did another test, and narrowed down the problem. It's not exactly a
regression; the test cases in Bug #11694 are still fixed correctly.
But it's a slightly different problem.

If the alias is for an expression instead of a plain column name in the
select-list, MySQL gives an error if you use the alias in an expression
in the ORDER BY clause.

I've logged a new bug: http://bugs.mysql.com/bug.php?id=22457
See that bug log for more detailed explanation, and steps to demonstrate
the bug.

Regards,
Bill K.