Using column aliases in derived selects or views breaks simple select queries

Question

I have a genuine use-case which requires referring to column aliases in a "where" clause. I'm trying to use the techniques outlined here, which I expect to work in Sybase and MySQL but don't seem to work in either H2 or HSQLDB: http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html

If you'd be kind enough to try and recreate my issue, here's how you can do it:

create table CUSTOMER (code varchar(255), description varchar(255), active bit, accountOpeningDate date, currentBalance numeric(20,6), currentBalanceDigits int)
insert into CUSTOMER (code, description, active, accountOpeningDate, currentBalance, currentBalanceDigits) values ('BMW', 'BMW Motors', 0, '2011-01-01', 345.66, 2)
insert into CUSTOMER (code, description, active, accountOpeningDate, currentBalance, currentBalanceDigits) values ('MERC', 'Mercedes Motors', 1, '2012-02-02', 14032, 0)

Then, this SQL query fails:

select nest.* from (
  select CODE "id", DESCRIPTION "description",
  ACTIVE "active",
  accountOpeningDate "accountOpeningDate",
  currentBalance "currentBalance"
  from customer
) as nest
where nest.id = 'BMW'

It's fine if you strip of the "where nest.id = 'BMW'". However, trying to use any of the aliases in either the where clause or the select clause (nest.id rather than next.*) then the query fails. Error code is Column "NEST.ID" not found; ... [42122-167] 42S22/42122

The same failure occurs if you try and create a view with aliased column names then try and select from the view. For example:

create view customer_view as 
select CODE "id", DESCRIPTION "description", 
ACTIVE "active", 
accountOpeningDate "accountOpeningDate", 
currentBalance "currentBalance" 
from customer

Then:

select id from customer_view

score 5 · Accepted Answer · answered Jun 22 '12 at 17:57

The problem is the mixed usage of unquoted and quoted identifiers. According to the SQL specification, unquoted identifiers (such as id) are case insensitive, and the database might convert them to uppercase or lowercase. Quotes identifiers (such as "id") are case sensitive, and the database engine must not convert the identifier.

H2 converts unquoted identifiers to uppercase (like other database engines such as Oracle). In your query, you have used both quoted and unquoted identifiers. Simplied test case (fails for H2 and other databases):

select * from (select 1 "id") where id = 1

To solve the problem, you need to use either quoted identifiers everywhere, or unquoted identifiers:

select * from (select 1 id) where id = 1

or

select * from (select 1 "id") where "id" = 1

Using column aliases in derived selects or views breaks simple select queries

1 Answers1