I was confused by this mail, and a tiny bit of testing made me even more confused. On a Firebird 1.5.4 database,

SELECT
R.RDB$RELATION_ID,
RF.RDB$RELATION_NAME,
RF.RDB$FIELD_NAME,
T.RDB$TYPE_NAME,
RF.RDB$FIELD_POSITION,
F.RDB$FIELD_LENGTH,
RF.RDB$SYSTEM_FLAG
FROM
RDB$RELATION_FIELDS RF
LEFT JOIN RDB$FIELDS F ON RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
INNER JOIN RDB$TYPES T ON F.RDB$FIELD_TYPE = T.RDB$TYPE
INNER JOIN RDB$RELATIONS R ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
WHERE T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
order by 1

returns 260 rows, all with R.RDB$RELATION_ID 135. Changing the order of the JOINs (no change in content, and hence, it should make no difference to anything except possibly the plan the optimizer uses) to

SELECT
R.RDB$RELATION_ID,
RF.RDB$RELATION_NAME,
RF.RDB$FIELD_NAME,
T.RDB$TYPE_NAME,
RF.RDB$FIELD_POSITION,
F.RDB$FIELD_LENGTH,
RF.RDB$SYSTEM_FLAG
FROM
RDB$RELATION_FIELDS RF
INNER JOIN RDB$RELATIONS R ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
LEFT JOIN RDB$FIELDS F ON RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
INNER JOIN RDB$TYPES T ON F.RDB$FIELD_TYPE = T.RDB$TYPE
WHERE T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
order by 1

I still get 260 rows, but now the value of R.RDB$RELATION_ID varies. Doing

SELECT
R.RDB$RELATION_ID,
RF.RDB$RELATION_NAME,
RF.RDB$FIELD_NAME,
T.RDB$TYPE_NAME,
RF.RDB$FIELD_POSITION,
F.RDB$FIELD_LENGTH,
RF.RDB$SYSTEM_FLAG
FROM
RDB$RELATION_FIELDS RF
INNER JOIN RDB$RELATIONS R ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
INNER JOIN RDB$FIELDS F ON RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
INNER JOIN RDB$TYPES T ON F.RDB$FIELD_TYPE = T.RDB$TYPE
WHERE T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
order by 1

seems to produce the same result as the above select.

Hence, I suspect your original select produce an incorrect result in Firebird 1.5.4!!! Sounds like something worth looking for in the bug tracker or release notes for Firebird 1.5.5 and report if not found there. Unfortunately, I doubt I'll have time to do this today,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of RB Smissaert
Sent: 28. september 2008 21:38
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] No current record for fetch operation

I had a look at this:

> I think that makes the LEFT JOIN actually be an INNER JOIN

When I change the left join into an inner join I only get the unique field
names and miss out on the duplicate field names

due to one field being in more than one index. So, I think I need the SQL as
it is and I can see no way to work it round

so that the left join comes last.

Maybe somebody can prove me wrong.

Does the SQL run in FB 2.x is?



RBS



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: 28 September 2008 18:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] No current record for fetch operation



Hi!

I agree that Firebird (at least 1.5) poorly handles situations where
LEFT JOIN is written before INNER JOIN. However, I find your particular
SQL a bit contradictory:

When doing INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE),
you basically say that there must be a record in both F and T, whereas
you in the line before has said that F may not exist. I think that makes
the LEFT JOIN actually be an INNER JOIN and in general I'd say it is
wrong to use the right table of a LEFT JOIN in an INNER JOIN (*).

That being said, the error message you report doesn't sound like a
sensible error message for the situation and I think Firebird ought to
be able to execute your query anyway (and I don't know whether this is
what causes the error message or not). Though I don't know what causes
the error, if it has to do with the dialect or something else.

If your third line had contained a reference to RF and not F (thereby
making the whole statement more sensible), I'd say it could be an error
worth looking more into (note, I haven't checked any bug tracker, so it
may already be a known issue).

Set

(*) I sometimes do similar things myself, but then when I know a record
must exist in one of several tables:

SELECT D.* FROM A
LEFT JOIN B on A.A = B.A
LEFT JOIN C on A.A = C.A
INNER JOIN D on D.B = coalesce(B.B, C.B)

RB Smissaert wrote:

> Using FB 1.5 on a converted IB 5.6 dialect 1 database.
> I connect with the ODBC driver from IBPhoenix and this is with ADO in VB6.
>
> When I run this SQL:
>
> SELECT
> R.RDB$RELATION_ID,
> RF.RDB$RELATION_NAME,
> RF.RDB$FIELD_NAME,
> T.RDB$TYPE_NAME,
> RF.RDB$FIELD_POSITION,
> F.RDB$FIELD_LENGTH,
> RF.RDB$SYSTEM_FLAG
> FROM
> RDB$RELATION_FIELDS RF
> LEFT JOIN RDB$FIELDS F ON (RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME)
> INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE)
> INNER JOIN RDB$RELATIONS R ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)

> WHERE
> T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
>
> I get the error message:
>
> [ODBC Firebird driver][Firebird] No current record for fetch operation
>
> And I understand that this is caused by the left join before the 2 inner
> joins. Indeed if I change the left join to an inner join it runs fine.
> There is no such problem with IB 5.6.
>
> It is not a major problem as I can do multiple selects in the application
> and join in SQLite, but I just wonder if this has been fixed in FB 2.0 or
> 2.1 or has this to do with the database dialect?
>
>
> RBS

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


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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links