470,631 Members | 1,557 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

"unknown column image_location_country_alt" but this column must exist!

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
This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using
query
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

Sep 18 '06 #1
8 3114

ph**************@gmail.com wrote:
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

This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using
query

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
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,
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

Sep 18 '06 #2

strawberry wrote:
ph**************@gmail.com wrote:
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
This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using
query
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

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,
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
No it doesn't "unknown column "isnull""

Phil

Sep 18 '06 #3

ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
>
This query produces the following error:
>
Unknown column 'image_location_country_alt' in 'order clause' using
query
>
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
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,
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

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

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

Sep 18 '06 #4

strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using
query
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
>
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,
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
No it doesn't "unknown column "isnull""

Phil

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

Sep 18 '06 #5

ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
>
This query produces the following error:
>
Unknown column 'image_location_country_alt' in 'order clause' using
query
>
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

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,
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
>
No it doesn't "unknown column "isnull""
>
Phil
Can I just confirm that you're using mysql 4.1 or later?

Yes, 4.1.20

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)

Sep 18 '06 #6

strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using
query
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
>
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,
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

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

Phil
>
Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20


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)
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

Sep 18 '06 #7
ph**************@gmail.com wrote:
Unknown column 'image_location_country_alt' in 'order clause' using
query
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.
Sep 18 '06 #8
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.
Sep 18 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Marcus | last post: by
1 post views Thread by Sandro Daehler | last post: by
9 posts views Thread by Klaus Johannes Rusch | last post: by
6 posts views Thread by Sile | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.