Bug #16098 Ability to reference 'prior' Column Alias in SELECT cols

• Submitted: 30 Dec 2005 14:15
• Modified: 30 Dec 2005 15:33
• Reporter: Azza Azza69
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• CPU Architecture: Any

[30 Dec 2005 14:15] Azza Azza69

Description:
Not being able to use column aliases in the 'SELECT column' part is a pain.

Consider this:
SELECT
  l.ProductCode,
  l.Quantity,
  s.CostPrice AS UnitPrice,
  l.Quantity*s.CostPrice AS TotalCost,
  ((100+v.VAT_Rate)/100) AS VATRate,
  (l.Quantity*s.CostPrice)*((100+v.VAT_Rate)/100) AS CostPlusVAT
LEFT JOIN stock s ON l.Product=s.Code
LEFT JOIN vatrates v ON v.Code=s.VATCode
FROM purchased l

it's a shame that (for CostPlusVAT) you can't enter:
TotalCost*VATRate AS CostPlusVAT even though TotalCost and VATRate have both already been pre-defined.

How to repeat:
n/a

[30 Dec 2005 15:33] Valeriy Kravchuk

Thank you for a feature request. Can you, please, name at least one populat RDBMS that supports such a feature?

According to SQL 2003 Standard:

<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
...

<select sublist> ::=
<derived column>
| <qualified asterisk>
<qualified asterisk> ::=
<asterisked identifier chain> <period> <asterisk>
| <all fields reference>
<asterisked identifier chain> ::=
<asterisked identifier> [ { <period> <asterisked identifier> }... ]
<asterisked identifier> ::= <identifier>
<derived column> ::= <value expression> [ <as clause> ]
...

<numeric value expression> ::=
<term>
| <numeric value expression> <plus sign> <term>
| <numeric value expression> <minus sign> <term>
<term> ::=
<factor>
| <term> <asterisk> <factor>
| <term> <solidus> <factor>
<factor> ::= [ <sign> ] <numeric primary>
<numeric primary> ::=
<value expression primary>
| <numeric value function>
...

<nonparenthesized value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <window function>
| <scalar subquery>
| <case expression>
| <cast specification>
| <field reference>
| <subtype treatment>
| <method invocation>
| <static method invocation>
| <new specification>
| <attribute or method reference>
| <reference resolution>
| <collection value constructor>
| <array element reference>
| <multiset element reference>
| <routine invocation>
| <next value expression>

you can not use column alias as element of expression. So, I do not think that this feature will be ever implemented.