Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
LEFT JOIN creates an empty ResultSet in some cases
Tobi
2008-12-18 01:08:35.0
Hi everybody,

I work with the DBF_JDBC-Driver (20.11.2008) and there might be a problem.

I have two tables t1 and t2. If I make a Left Join and no row matches the condition, in some cases the ResultSet is empty. Unfortunately this is not in all cases. Maybe you have an idea, if this is a bug or my fault.

Here is a small example:

CREATE TABLE t1 (number INT);
CREATE TABLE t2 (number INT, fromDate Date, tillDate Date);
INSERT INTO t1 (number) VALUES (1);
INSERT INTO t2 (number, fromDate, tillDate) VALUES (1, '2008-11-11', '2008-12-12');
SELECT * FROM t1 LEFT JOIN t2 on 1=1;
=> ResultSet
---------------------------------------------------
| t1.number | t2.number | fromDate | tillDate |
| 1 | 1 | 2008-11-11 | 2008-12-12 |
---------------------------------------------------
SELECT * FROM t1 LEFT JOIN t2 on 1=2;
=> ResultSet is empty

But I expect the ResultSet
---------------------------------------------------
| t1.number | t2.number | fromDate | tillDate |
| 1 | null | null | null |
---------------------------------------------------

Am I right?

Now I use some statements, that are more realistic and also create an unexpected empty ResultSet:

SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
SELECT * FROM t1 LEFT JOIN t2 on t2.number=2;
SELECT * FROM t1 LEFT JOIN t2 on t1.number=2;

Last but not least a statement, that creates the expected ResultSet (not empty)
SELECT * FROM t1 LEFT JOIN t2 on t1.number!=t2.number

What do you think?
Re:LEFT JOIN creates an empty ResultSet in some cases
HXTT Support
2008-12-18 01:24:59.0
>SELECT * FROM t1 LEFT JOIN t2 on 1=1;
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number;

>SELECT * FROM t1 LEFT JOIN t2 on 1=2;
>But I expect the ResultSet
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number!=t2.number;

>SELECT * FROM t1 LEFT JOIN t2 on t2.number=2;
It should be ELECT * FROM t1 LEFT JOIN t2 on t1.number=2;

>SELECT * FROM t1 LEFT JOIN t2 on t1.number=2;
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number=1;

>SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
It should be SELECT * FROM t1 right JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
Re:Re:LEFT JOIN creates an empty ResultSet in some cases
Tobi
2008-12-18 01:35:47.0
Thanks for the answer. But it doesn't solve my problem.

Most of the statements where just examples.

In my case, I need this statement:

>>SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEENt2.fromDate AND t2.tillDate;
>It should be SELECT * FROM t1 right JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;

1) Why do you use RIGHT JOIN? I want to see at least all entries from t1.
2) Even with RIGHT JOIN I get an empty ResultSet?!?
Re:Re:Re:LEFT JOIN creates an empty ResultSet in some cases
HXTT Support
2008-12-18 01:49:44.0
>1) Why do you use RIGHT JOIN? I want to see at least all entries from t1.
Then you should use LEFT JOIN, but a condition on t2 will filter all unsatisfied rows.
Re:Re:Re:Re:LEFT JOIN creates an empty ResultSet in some cases
Tobi
2008-12-18 02:24:00.0
> but a condition on t2 will filter all unsatisfied rows.

This is how DBF_JDBC works. But it's not what I expect when I use LEFT JOIN.

As a workaround, I use

select * from t1 LEFT JOIN t2 on t1.number=t2.number AND t2.fromDate<=CURRENT_DATE OR t2.fromdate IS NULL) AND (CURRENT_DATE<=t2.tilldate OR t2.tilldate IS NULL);

I don't know why, but it works in my tested cases.
Re:Re:Re:Re:Re:LEFT JOIN creates an empty ResultSet in some cases
HXTT Support
2008-12-18 02:42:13.0
Yeah. You can use t2.tilldate=null too.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap