A SQL alias can be in ORDER BY clause, but not in WHERE clause?

Question

The query I use for a JDBCPagingItemReader in my Spring Batch project is:

 SELECT
  account_login_log_id log_id,
  created_at reference_time 
 FROM account_login_log 
 WHERE account_login_log_id > 1000 
 ORDER BY account_login_log_id

And a RowMapper catches items got from the reader and makes a Object named say, SomeGeneralLog.

    SomeGeneralLog log = new SomeGeneralLog();
    log.setLogId(rs.getInt("log_id");
    log.setReferenceTime(rs.getTimestamp("reference_time");

and, return this log to a writer, as some Batch processes do.

Here's the point. The program throws SQLException! java.sql.SQLException: Column 'account_login_log_id' not found.

What? I didn't even make it to find the column 'account_login_log_id' in RowMapper code. Actually, I could make it run anyway by replacing the column name in ORDER BY clause with 'log_id'. But why? What was the reason? Please make any notes as much as you could imagine. (I suspect the order of query processing by keywords in query. *ref: https://stackoverflow.com/a/31808872/3648833)

Thanks.

score 1 · Answer 1 · answered Oct 28 '15 at 10:20

1

SELECT account_login_log_id log_id

You gave the column an alias, so now it has a new name, and you have to use that new name.

Your exact mileage may vary between RDBMS.

answered Oct 28 '15 at 10:20

Thilo

257,207
101
511
656

But why is using the alias in where clause not allowed? – choiapril Oct 28 '15 at 10:22
The `WHERE` clause happens before the `SELECT` clause, so the alias does not exist yet (the linked question explains that). – Thilo Oct 28 '15 at 10:23
Accepted, but what about the ResultSet not capable of finding "log_id" column? – choiapril Oct 28 '15 at 10:25
Driver bug/limitation. They are supposed to respect column alias, but some sometimes don't. – Thilo Oct 28 '15 at 12:23

A SQL alias can be in ORDER BY clause, but not in WHERE clause?

1 Answers1