Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
Table alias binding in inner queries
Rob Gansevles
2011-10-28 08:35:04.0
Hello,

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.

Test setup:
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)
select tha4.aid
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)
select a.aid
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?

Thanks,

Rob
Re:Table alias binding in inner queries
HXTT Support
2011-10-28 18:42:35.0
It's normal.

>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 .
Re:Re:Table alias binding in inner queries
Rob Gansevles
2011-10-30 03:58:09.0
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.
For example
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?

Rob
Re:Re:Re:Table alias binding in inner queries
HXTT Support
2011-10-30 05:04:07.0
>For example
>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.
Re:Re:Re:Re:Table alias binding in inner queries
Rob Gansevles
2011-10-31 05:17:02.0
Hi,

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?

Rob
Re:Re:Re:Re:Re:Table alias binding in inner queries
HXTT Support
2011-10-31 05:54:29.0
Sorry. Upload now. Please download it.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap