Bug #46367 Problem When using Aliases in a select Statement

• Submitted: 24 Jul 2009 13:10
• Modified: 24 Jul 2009 15:02
• Reporter: Nabil Barakat
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.37
• OS: Any
• CPU Architecture: Any

[24 Jul 2009 13:10] Nabil Barakat

Description:
When using a Select Staement like:

SELECT Sum(if(SlpBalBC>=0,SlpBalBC,0)) as `1`, Sum(if(SlpBalBC<0,SlpBalBC,0)) as `2`, `1`+`2` as Balance  FROM tbl_slips;

SQL server returns an error: Unknown column '1' in 'field list'

How to repeat:
Step 1: Create Table

CREATE TABLE `tbl_slips` (
  `SlpBalBC` FLOAT(30,4)) NOT NULL,
  `aSerial` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT
)

Step 2: Execute the SQL Statement

SELECT Sum(if(SlpBalBC>=0,SlpBalBC,0)) as `1`, Sum(if(SlpBalBC<0,SlpBalBC,0)) as `2`, `1`+`2` as Balance  FROM tbl_slips;

Suggested fix:
Work around:

SELECT Sum(if(SlpBalBC>=0,SlpBalBC,0)) as `1`, Sum(if(SlpBalBC<0,SlpBalBC,0)) as `2`, Sum(if(SlpBalBC>=0,SlpBalBC,0))+Sum(if(SlpBalBC<0,SlpBalBC,0)) as Balance  FROM tbl_slips;

The above works fine but complicates the code a lot.

[24 Jul 2009 13:33] Nabil Barakat

I Just noticed when using single quotes for the Alais it works fine like:

SELECT Sum(if(SlpBalBC>=0,SlpBalBC,0)) as `1`, Sum(if(SlpBalBC<0,SlpBalBC,0)) as `2`, '1'+'2' as Balance  FROM tbl_slips;

Have no idea why??? Any help would be appreciated.

[24 Jul 2009 13:37] Nabil Barakat

oops My mistake the last comment doesn't work (it just gives the Value 3 for the Balance (1+2=3)) ...

[24 Jul 2009 13:58] Valeriy Kravchuk

Sorry, but this is NOT a bug. Our manual, http://dev.mysql.com/doc/refman/5.0/en/select.html, clearly says:

"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."

So, alias can NOT be used the way you try to use it.

[24 Jul 2009 15:02] Nabil Barakat

Hi Valeriy,

Thanks for the clarification. I did read that statement in the manual but I guess it wasn't as clear as you put it ... I just assumed it is equivelant to a column name so you can use in functions as well ...

I think this would be a nice feature to add especially for people migrating from other DBMS.

Thanks again.

[24 Jul 2009 16:37] Valeriy Kravchuk

Because '1' is a string, not identifier. Then string is converted to number because of +. Look:

mysql> select 1 as a, 2 as b, a + b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
mysql> select 1 as '1', 3 as '2', '1' + '2';
+---+---+-----------+
| 1 | 2 | '1' + '2' |
+---+---+-----------+
| 1 | 3 |         3 | 
+---+---+-----------+
1 row in set (0.00 sec)

Check if you really get the results you expect...