From: firebird-support@yahoogroups.com on behalf of Mercea Paul

>Is possible to have more than one row in table 1 who match the criteria of
>ID from table 2?

No that is not the case, I am matching the one ID from table two against the primary key in table one so only one row can match.

And to be sure I reduced the tables in size to only a few rows and can easily see and verify all numbers.

I don't have the DB at hand here at home but I am almost a 100% sure of this but so any thoughs will be most welcome. I will verify it for sure tomorrow.

Tim


From: firebird-support@yahoogroups.com <mailto:firebird-support%40yahoogroups.com>
[mailto:firebird-support@yahoogroups.com <mailto:firebird-support%40yahoogroups.com> ] On Behalf Of Tim Gahnström
Sent: 19 June 2008 19:54
To: firebird-support@yahoogroups.com <mailto:firebird-support%40yahoogroups.com>
Subject: [firebird-support] Bug or limitation inf FB 1.5

Hi

Today I noticed a bug or limitation in our FB, I can obviously rewrite my
query but I figured there might be in interesting explanation floating
around:

Select * from table1
WHERE table1.id IN
(SELECT FIRST 1 id in
FROM table2
WHERE table2.number > 3
ORDER BY table2.number
)

I figured this should only return one row in total but apparently the system
optimizes the "FIRST 1" part away in the inner query.

I believe it is common that this is not allowed in other DBs but here I get
no complaints, instead I just get the full set of rows mathcing the inner
query returned.

Cheers

Tim









[Non-text portions of this message have been removed]