Issue Details (XML | Word | Printable)

Key: CORE-1313
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Alexander Tyutik
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

RDB$DB_KEY not supported in derived tables and merge command

Created: 09/Jun/07 06:14 AM   Updated: 02/May/08 11:26 AM
Component/s: None
Affects Version/s: 2.1 Alpha 1
Fix Version/s: 2.1 Beta 1, 2.0.5

Time Tracking:
Not Specified

Issue Links:
Relate
 


 Description  « Hide
I've got error when trying to run queries like this:

SELECT T1.*
FROM TABLE T1
RIGHT JOIN (SELECT T.RDB$DB_KEY AS DBKEY FROM TABLE T) T2 ON T2.DBKEY = T1.RDB$DB_KEY;

or this:

MERGE INTO TABLE T1
USING (SELECT T.RDB$DB_KEY AS DBKEY FROM TABLE T) T2
ON T2.DBKEY = T1.RDB$DB_KEY
WHEN NOT MATCHED THEN INSERT ...



Error is something like this:

Column unknown.
T.DB_KEY.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 15/Jun/07 09:19 AM
Compilation problem of MERGE with RDB$DB_KEY's should be solved now.
Issue in SELECT wasn't reproducable.

But anyway, your queries will not work well since it compare dbkeys from different tables. :-)

Alexander Tyutik added a comment - 15/Jun/07 03:54 PM
Adriano, what did you meen about diffirent tables?

Adriano dos Santos Fernandes added a comment - 15/Jun/07 09:31 PM
Second part of the fix is committed.

Adriano dos Santos Fernandes added a comment - 15/Jun/07 09:49 PM
You're comparing T (or T2).RDB$DB_KEY with T1.RDB$DB_KEY.
DBKEY from one table will never be equal to DBKEY from another one.

Alexander Tyutik added a comment - 15/Jun/07 09:55 PM
But all T, T1 and T2 are aliases for same table "TABLE". Such queries can be used for some performance tricks. Is there are some problems with DB_KEYs in such cases?

Adriano dos Santos Fernandes added a comment - 15/Jun/07 10:24 PM
Your query confused-me, since it doesn't parse correctly.

You used the reserved word TABLE without double-quoted.
And I removed this thinking you meaning "select from the table ...".

Now I see there is a problem with RDB$DB_KEY and aliases, not only with derived tables.

Pavel Cisar added a comment - 25/Jul/07 11:22 AM
Fix verified for Beta 1, test added. Can't reproduce the issue with first query, but it was included in test anyway.

Vlad Khorsun added a comment - 02/May/08 11:26 AM
Backported into 2.0.5