One of our test cases failed with the DBF driver.
I have 2 queries that only differ in the use of table aliases, but the result is different.
create table a(aid int)
insert into a(aid) values(1)
insert into a(aid) values(2)
create table b(bid int, aid int)
insert into b(bid, aid) values(10, 1)
insert into b(bid, aid) values(11, 1)
insert into b(bid, aid) values(12, 2)
create table c(cid int, bid int)
insert into c(cid, bid) values(100, 10)
insert into c(cid, bid) values(101, 11)
insert into c(cid, bid) values(102, 12)
-- the first query uses a table alias for the outer table
-- the (correct) result is 1 row (aid=1)
from a tha4 where exists
(select 1 from a tha7
inner join b thb8 on tha7.aid=thb8.aid
inner join c thc9 on thb8.bid=thc9.bid
where tha7.aid = tha4.aid
group by tha7.aid having count(thc9.cid) > 1
order by tha4.aid asc
-- the second query does not use a table alias for the outer table
-- the (wrong) result is 2 rows (aid=1,2)
from a where exists
(select 1 from a a1
inner join b b on a1.aid=b.aid
inner join c c on b.bid=c.bid
where a1.aid = a.aid
group by a1.aid having count(c.cid) > 1
order by a.aid asc
Is this an issue with the driver?
>where a1.aid = a.aid
There's two 'a' table quotation in your sql. If you doesn't use alias, a.aid maybe is tha4.aid or a1.aid .
Thanks for the response.
I do not agree, If I use a table with an alias, a reference to the table name should not match.
select * from a x where a.aid = 1
should raise an sql exception as it does with other databases.
In my case this causes an issue because there is a valid binding in the context (outer query) but it is 'stolen' b y the aliased table.
Is there any change this can be corrected?
>select * from a x where a.aid = 1
>should raise an sql exception as it does with other databases.
Checked. Foxpro2.6 and Mysql will work as you said, and Corel Paradox engine won't. The latest version won't use table to match column name, if there's a table alias.
I tried the latest downloads but the change does not seem to be available yet.
The files are still from 2011-10-11 and the behaviour has not changed.
Can you check your downloads site?
Sorry. Upload now. Please download it.