SP2

[joseph]

After installing SP2 (8.1.0.257) on the server, I am receiving error
"no current record for fetch operation" when running queries such as:

select p.Name
from Table1 p, Table2 rt, Table3 rn
left outer join Table4 rm on rm.ID = rn.ID
where rn.PKID = 189627
and rt.FKID = rn.FKID
and p.FKXID = rn.FKXID

This type of query still runs on the same database installed on a server
which have no service packs installed.

For the query to run propery this must be changed to:

select p.Name
from Table3 rn
Inner Join Table1 p on p.FKXID = rn.FKXID
Inner Join Table2 rt on rt.FKID = rn.FKID
left outer join Table4 rm on rm.ID = rn.ID
where rn.PKID = 189627

Is this a bug?

Joseph

[Tom W]

Not sure. Surprised that it ever worked. I tried this kind of thing
the other day with Oracle 10g and it failed.

Perhaps Quinn W can give an official opinion.

[Quinn Wildman (CodeGear Developer Support)]

Well, regressions are bad, but this is a bad query as well. Technically,
a query which has implicit and explicit joins is not supported.
However, we don't want regressions. I reproduced this problem against
the sample database and entered a bug in our system. Here's my query:

select a.first_name
from employee a, employee_project b, project c
left outer join proj_dept_budget d on d.proj_id=c.proj_id
where b.proj_id=c.proj_id
and a.emp_no=b.emp_no;

Note that here's another query which combines implicit and explicit
joins which gives the same error and has never worked:

select count(*)
from job left join country on job.job_country = country.country,
employee left join department on employee.dept_no = department.dept_no
where employee.job_code = job.job_code;

[Bill Todd]

joseph wrote:

> Is this a bug?

I do not know if CodeGear would consider it a bug or not but it is
behavior that R&D should not waste time fixing other than to generate a
syntax error IMHO. Mixing SQL 89 and SQL 92 join syntax is not a good
idea. Use the SQL 92 syntax exclusively.

--
Bill Todd (TeamB)

[Dmitry Yemanov]

Bill Todd wrote:
>
> I do not know if CodeGear would consider it a bug or not but it is
> behavior that R&D should not waste time fixing other than to generate a
> syntax error IMHO. Mixing SQL 89 and SQL 92 join syntax is not a good
> idea.

I know that the engine has problems with such queries (this is the issue
for Firebird as well), but rejecting them doesn't sound as a proper
solution to me. FWIW, mixed join styles are allowed by the SQL
specification, so it's also the matter of conformance.

Dmitry

[Bill Todd]

Dmitry Yemanov wrote:

> I know that the engine has problems with such queries (this is the
> issue for Firebird as well), but rejecting them doesn't sound as a
> proper solution to me. FWIW, mixed join styles are allowed by the SQL
> specification, so it's also the matter of conformance.

I agree that in an ideal world it should be fixed. In the real world
where R&D resources are limited I can generate a very very long list of
features that I think are _much_ more important than fixing this
problem. Given that R&D resources are finite I think the best business
decision is to generate an error.

--
Bill Todd (TeamB)

[Thomas Miller]

In FB the optimizer also doesn't function, at all. So that is another
reason not to mix the syntax.

--
Thomas Miller
Chrome Portal Project Manager
CPCUG Programmers SIG Chairperson (formally Delphi)
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://programmers.cpcug.org/
http://sourceforge.net/projects/chromeportal/
http://sourceforge.net/projects/uopl/
http://sourceforge.net/projects/dbexpressplus