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]