[Firebird-devel] [ firebird-Bugs-1034951 ] Field alias in subquery throws error -104

[SourceForge.net <no...@so...>]

Bugs item #1034951, was opened at 2004-09-26 07:04
Message generated for change (Settings changed) made by seanleyne
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1034951&group_id=9028

Category: DSQL
>Group: As Designed/Pitfall
Status: Deleted
>Resolution: Rejected
Priority: 5
Submitted By: Frans Bouma (otis_inf)
Assigned to: Nobody/Anonymous (nobody)
Summary: Field alias in subquery throws error -104

Initial Comment:
The query:
select * from employee
where emp_no in (select emp_no AS Foo from
employee_project)

gives:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, char 32.
AS.

On: Firebird 1.5.0.xxx and 1.5.1.xxx, dialect 3,
example database (and other databases)

The aliassing is sometimes required for order by
clauses on expression columns aliassed in the select
list. As I generate the query with generic code which
follows ansi standards, I need to use a lame workaround
for Firebird 1.5.x only. 

Please fix.

Frans Bouma, LLBLGen Pro developer

----------------------------------------------------------------------

Comment By: Frans Bouma (otis_inf)
Date: 2004-09-27 09:35

Message:
Logged In: YES 
user_id=36363

"It's not a bug.  It's invalid syntax to use the alias in this 
context. "
A lot of  databases support it (even access), firebird
doesn't, perhaps I assumed it would be possible.

"It is not possible to use aliased expression results in order
by clauses.  In these cases, the required syntax is the degree 
number of the expression in the output set.  And this has no 
relationship to your use of an existential subquery in your SQL 
statement."
Ok, I didn't know about the number 'feature' (which is
pretty awkward to say the least, where are aliasses for
anyway). 

A possible way would be:
select * from table
where field1 in (select first 10 (field2 * 3) AS Foo from
employee_project order by foo)

You can do (works:)
select * from table
where field1 in (select first 10 (field2 * 3) from
employee_project order by (field2 * 3))

which requires non-standard code as well. The problem with
the number usage (which is again, really awkward) is that
the order in the select list is then important when the
ORDER BY code is generated (the sql is generated with an
engine) and this order is unknown to that code. 

I get the feeling a feature request to have aliased fields
in a subquery and alias support in order by would not be
accepted anyway, so I'll leave it at this for now. If you
think it has a chance, I'll spend the time asking for the
feature. 

I do wonder why on earth this limitation is present as an
alias for a column is easy to track down and the query
pre-processor can use generic code to do that (i.e.: for
normal queries AND subqueries, the same code can be used).
Now some limitation is build in which shows that a different
query processor for the subquery is used. 

----------------------------------------------------------------------

Comment By: Helen Borrie (helebor)
Date: 2004-09-27 09:06

Message:
Logged In: YES 
user_id=60469

It's not a bug.  It's invalid syntax to use the alias in this 
context. 

> The aliassing is sometimes required for order by
> clauses on expression columns aliassed in the select
> list. 

It is not possible to use aliased expression results in order by 
clauses.  In these cases, the required syntax is the degree 
number of the expression in the output set.  And this has no 
relationship to your use of an existential subquery in your SQL 
statement.

> As I generate the query with generic code which
> follows ansi standards, I need to use a lame workaround
> for Firebird 1.5.x only. 

The following is not a lame workaround: 

select emp_no,
(select last_name||', '||substring(first_name from 1 for 1)||'.')
  as name_initial 
from employee
where emp_no in (select emp_no from employee_project) 
order by 2

If you can construct a plausible example for what you want, 
you could run it through firebird-architect as a possible 
feature request. 

\-- Helen


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1034951&group_id=9028