At 07:49 AM 15/10/2009, you wrote:

>I just ran into something bizarre.
>
>The following query:
>
>select first 10 custkey from customer where ....
>
>returns 10 rows, as I'd expect. However, if I put that in a subselect, like:
>
>select custkey from customer where custkey in
>(select first 10 custkey from customer where ....)
>
>This one returns 48 rows. Custkey is the primary key on the Customer table, so there can't be more than one customer with the same custkey.
>
>So, it appears that when when the "first n" is within a subselect, it is ignored, as far as I can tell.
>
>Is this a bug in Firebird? Or is there some legitimate reason that it was designed that way?
>
>I am running Firebird 1.0 if that affects the answer.

First of all, part of the problem *could involve* an implementation deficiency that would have been addressed in later versions of Firebird. Fb 1.0 is soooo old now, it would be hard for someone to recall. You could look at the bug fix reports in newer versions and dig up changes over the past 7 years.

Secondly, when you do self-referencing subqueries, you must use relation aliases to separate the outer set from the inner set.
e.g.
select c1.custkey from customer c1
where c1.custkey in
(select first 10 c2.custkey from customer c2
where c2.something = c1.something ....
order by c2.whatever )

So try correcting that syntax, and see whether the results come closer to your expectations.

On the other hand, if your example is similar to what you actually want to do, I wonder why you need the self-referencing subquery at all. If you just want the first 10 custkey values, why not get them directly?

select first 10 custkey from customer
where something = aValue /* or ? for a parameterised search input */
order by whatever

./heLen