Join with empty result set, again...

Join with empty result set, again...

Post by Miche » Sat, 06 Apr 2002 05:22:22



Hi All,

Improper DDFs can result in strange queries which I learned from my last
post.

I still have a strange join behavior, even when a complete consistency
checks of my DDFs passes in the Pervasive Control Center.

I have a simple relation relation between customer and order, linked a
field called CustNo.

This query returns an emtpy result set:

SELECT "order".orderno, "customer".custno,"customer".name
FROM  "order", "customer"
where "customer".custno = "order".custno

It should list all the orders from all customers, but the result set is
emtpy.

This query returns all orders for customer 1907 as expected, so this
proves that the first query should have returned some results.

SELECT "order".orderno, "customer".custno,"customer".name
FROM  "order", "Customer"
where "Customer".CustNo = 1907

But this query also returns an empty result set,

SELECT "order".orderno, "customer".custno,"customer".name
FROM  "order", "Customer"
where "Order".CustNo = 1907

I am not very experienced with SQL, but I don't understand why two of
these queries are empty.

Any help appreciated.

Best regards,

Michel

 
 
 

Join with empty result set, again...

Post by Bill Bac » Sat, 06 Apr 2002 10:35:32


If there were NO results from the query where Order.CustNo = 1907, then
perhaps there are just no orders for this customer???

Assuming that you checked this...  Try the simpler query:
    SELECT orderno, custno FROM  order where CustNo = 1907
Does this returns data?  If not, how about:
    SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
Do you see any orders for CustNo 1907?

If the data is there, but not being returned on the query, then you may have
an indexing problem.  Look through your indices to see if CustNo is part of
a segmented key.  If it is, try adding an additional restriction on the
second field in the key definition.  If this works, let me know -- I'll
explain why if I need to...
 Goldstar Software Inc.
 Building on Btrieve(R) for the Future(SM)
 Bill Bach

 http://www.goldstarsoftware.com
 *** Pervasive.SQL Development Classes ***
 Chicago: April 22-26, 2002 - See our web site for details!
 *** Pervasive.SQL Service & Support Classes ***
 Chicago: May 14-17, 2002 - See our web site for details!


> Hi All,

> Improper DDFs can result in strange queries which I learned from my last
> post.

> I still have a strange join behavior, even when a complete consistency
> checks of my DDFs passes in the Pervasive Control Center.

> I have a simple relation relation between customer and order, linked a
> field called CustNo.

> This query returns an emtpy result set:

> SELECT "order".orderno, "customer".custno,"customer".name
> FROM  "order", "customer"
> where "customer".custno = "order".custno

> It should list all the orders from all customers, but the result set is
> emtpy.

> This query returns all orders for customer 1907 as expected, so this
> proves that the first query should have returned some results.

> SELECT "order".orderno, "customer".custno,"customer".name
> FROM  "order", "Customer"
> where "Customer".CustNo = 1907

> But this query also returns an empty result set,

> SELECT "order".orderno, "customer".custno,"customer".name
> FROM  "order", "Customer"
> where "Order".CustNo = 1907

> I am not very experienced with SQL, but I don't understand why two of
> these queries are empty.

> Any help appreciated.

> Best regards,

> Michel


 
 
 

Join with empty result set, again...

Post by Michel Brazea » Sun, 07 Apr 2002 20:49:18


Hi Bill,

Thanks for your help. There are orders from CustNo = 1907.

I have found the problem. The CustNo field in the Customer table is of type
Identity, and the CustNo field in the Order table is a signed Integer32. P.SQL
does not seem to compare them properly. When I changed in the DDFs the type from
signed to unsigned it worked fine. This is with P.SQL 2000i SP4. I find that
quite restrictive, but now that this is solved I can move to something else.

Best regards,

Michel


> If there were NO results from the query where Order.CustNo = 1907, then
> perhaps there are just no orders for this customer???

> Assuming that you checked this...  Try the simpler query:
>     SELECT orderno, custno FROM  order where CustNo = 1907
> Does this returns data?  If not, how about:
>     SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
> Do you see any orders for CustNo 1907?

> If the data is there, but not being returned on the query, then you may have
> an indexing problem.  Look through your indices to see if CustNo is part of
> a segmented key.  If it is, try adding an additional restriction on the
> second field in the key definition.  If this works, let me know -- I'll
> explain why if I need to...
>  Goldstar Software Inc.
>  Building on Btrieve(R) for the Future(SM)
>  Bill Bach

>  http://www.goldstarsoftware.com
>  *** Pervasive.SQL Development Classes ***
>  Chicago: April 22-26, 2002 - See our web site for details!
>  *** Pervasive.SQL Service & Support Classes ***
>  Chicago: May 14-17, 2002 - See our web site for details!


> > Hi All,

> > Improper DDFs can result in strange queries which I learned from my last
> > post.

> > I still have a strange join behavior, even when a complete consistency
> > checks of my DDFs passes in the Pervasive Control Center.

> > I have a simple relation relation between customer and order, linked a
> > field called CustNo.

> > This query returns an emtpy result set:

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "customer"
> > where "customer".custno = "order".custno

> > It should list all the orders from all customers, but the result set is
> > emtpy.

> > This query returns all orders for customer 1907 as expected, so this
> > proves that the first query should have returned some results.

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "Customer"
> > where "Customer".CustNo = 1907

> > But this query also returns an empty result set,

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "Customer"
> > where "Order".CustNo = 1907

> > I am not very experienced with SQL, but I don't understand why two of
> > these queries are empty.

> > Any help appreciated.

> > Best regards,

> > Michel

--

Michel
------
http://www.intervalsoftware.com
Envision Image Library
Spider Object Database
Spider Container and Persistent Classes  (freeware)
WordShare string localization  (freeware)

 
 
 

Join with empty result set, again...

Post by Bill Bac » Tue, 09 Apr 2002 05:34:49


Ooohh -- that is bad.  I'll have to do some testing & get Pervasive to look into
fixing that.  The comparison should work no matter WHAT data type is used.
 Goldstar Software Inc.
 Building on Btrieve(R) for the Future(SM)
 Bill Bach

 http://www.goldstarsoftware.com
 *** Pervasive.SQL Development Classes ***
 Chicago: April 22-26, 2002 - See our web site for details!
 *** Pervasive.SQL Service & Support Classes ***
 Chicago: May 14-17, 2002 - See our web site for details!

> Hi Bill,

> Thanks for your help. There are orders from CustNo = 1907.

> I have found the problem. The CustNo field in the Customer table is of type
> Identity, and the CustNo field in the Order table is a signed Integer32. P.SQL
> does not seem to compare them properly. When I changed in the DDFs the type from
> signed to unsigned it worked fine. This is with P.SQL 2000i SP4. I find that
> quite restrictive, but now that this is solved I can move to something else.

> Best regards,

> Michel


> > If there were NO results from the query where Order.CustNo = 1907, then
> > perhaps there are just no orders for this customer???

> > Assuming that you checked this...  Try the simpler query:
> >     SELECT orderno, custno FROM  order where CustNo = 1907
> > Does this returns data?  If not, how about:
> >     SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
> > Do you see any orders for CustNo 1907?

> > If the data is there, but not being returned on the query, then you may have
> > an indexing problem.  Look through your indices to see if CustNo is part of
> > a segmented key.  If it is, try adding an additional restriction on the
> > second field in the key definition.  If this works, let me know -- I'll
> > explain why if I need to...
> >  Goldstar Software Inc.
> >  Building on Btrieve(R) for the Future(SM)
> >  Bill Bach

> >  http://www.goldstarsoftware.com
> >  *** Pervasive.SQL Development Classes ***
> >  Chicago: April 22-26, 2002 - See our web site for details!
> >  *** Pervasive.SQL Service & Support Classes ***
> >  Chicago: May 14-17, 2002 - See our web site for details!


> > > Hi All,

> > > Improper DDFs can result in strange queries which I learned from my last
> > > post.

> > > I still have a strange join behavior, even when a complete consistency
> > > checks of my DDFs passes in the Pervasive Control Center.

> > > I have a simple relation relation between customer and order, linked a
> > > field called CustNo.

> > > This query returns an emtpy result set:

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "customer"
> > > where "customer".custno = "order".custno

> > > It should list all the orders from all customers, but the result set is
> > > emtpy.

> > > This query returns all orders for customer 1907 as expected, so this
> > > proves that the first query should have returned some results.

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "Customer"
> > > where "Customer".CustNo = 1907

> > > But this query also returns an empty result set,

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "Customer"
> > > where "Order".CustNo = 1907

> > > I am not very experienced with SQL, but I don't understand why two of
> > > these queries are empty.

> > > Any help appreciated.

> > > Best regards,

> > > Michel

> --

> Michel
> ------
> http://www.intervalsoftware.com
> Envision Image Library
> Spider Object Database
> Spider Container and Persistent Classes  (freeware)
> WordShare string localization  (freeware)

 
 
 

Join with empty result set, again...

Post by Bill Bac » Tue, 09 Apr 2002 06:43:27


I did some additional tests, and found the system to be working properly.  I
therefore suspect your DDF's & data files might be slightly out of match.

This is especially more clear when I get back to your original statement:

Quote:> > But this query also returns an empty result set,

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "Customer"
> > where "Order".CustNo = 1907

If the problem were with comparisons with the AutoInc/Identity field, then this
query would work properly.  Try to simplify this query to the following:
    SELECT * FROM Order WHERE CustNo = 1907
If this also gives no results, then the SQL engine is miscomparing the simple
integer field CustNo.  I expect that if you do a BUTIL -STAT of the data file, you'd
find that the field may be actually stored in the key structure as an unsigned int.
When you set the DDF's to the same, it fixed it.  Of course, the problem could also
be with a _different_ segment of the key which starts with CustNo.
 Goldstar Software Inc.
 Building on Btrieve(R) for the Future(SM)
 Bill Bach

 http://www.goldstarsoftware.com
 *** Pervasive.SQL Development Classes ***
 Chicago: April 22-26, 2002 - See our web site for details!
 *** Pervasive.SQL Service & Support Classes ***
 Chicago: May 14-17, 2002 - See our web site for details!

> Hi Bill,

> Thanks for your help. There are orders from CustNo = 1907.

> I have found the problem. The CustNo field in the Customer table is of type
> Identity, and the CustNo field in the Order table is a signed Integer32. P.SQL
> does not seem to compare them properly. When I changed in the DDFs the type from
> signed to unsigned it worked fine. This is with P.SQL 2000i SP4. I find that
> quite restrictive, but now that this is solved I can move to something else.

> Best regards,

> Michel


> > If there were NO results from the query where Order.CustNo = 1907, then
> > perhaps there are just no orders for this customer???

> > Assuming that you checked this...  Try the simpler query:
> >     SELECT orderno, custno FROM  order where CustNo = 1907
> > Does this returns data?  If not, how about:
> >     SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
> > Do you see any orders for CustNo 1907?

> > If the data is there, but not being returned on the query, then you may have
> > an indexing problem.  Look through your indices to see if CustNo is part of
> > a segmented key.  If it is, try adding an additional restriction on the
> > second field in the key definition.  If this works, let me know -- I'll
> > explain why if I need to...
> >  Goldstar Software Inc.
> >  Building on Btrieve(R) for the Future(SM)
> >  Bill Bach

> >  http://www.goldstarsoftware.com
> >  *** Pervasive.SQL Development Classes ***
> >  Chicago: April 22-26, 2002 - See our web site for details!
> >  *** Pervasive.SQL Service & Support Classes ***
> >  Chicago: May 14-17, 2002 - See our web site for details!


> > > Hi All,

> > > Improper DDFs can result in strange queries which I learned from my last
> > > post.

> > > I still have a strange join behavior, even when a complete consistency
> > > checks of my DDFs passes in the Pervasive Control Center.

> > > I have a simple relation relation between customer and order, linked a
> > > field called CustNo.

> > > This query returns an emtpy result set:

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "customer"
> > > where "customer".custno = "order".custno

> > > It should list all the orders from all customers, but the result set is
> > > emtpy.

> > > This query returns all orders for customer 1907 as expected, so this
> > > proves that the first query should have returned some results.

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "Customer"
> > > where "Customer".CustNo = 1907

> > > But this query also returns an empty result set,

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "Customer"
> > > where "Order".CustNo = 1907

> > > I am not very experienced with SQL, but I don't understand why two of
> > > these queries are empty.

> > > Any help appreciated.

> > > Best regards,

> > > Michel

> --

> Michel
> ------
> http://www.intervalsoftware.com
> Envision Image Library
> Spider Object Database
> Spider Container and Persistent Classes  (freeware)
> WordShare string localization  (freeware)

 
 
 

Join with empty result set, again...

Post by Brendt He » Wed, 10 Apr 2002 03:10:44


Bill, just a FYI:

We have found a situation where empty result sets can be returned when
working with legacy data, related to the NULL flag.  It seems that, if
you join/compare on a Nullable field, the field has the Null flag set
to 0 (not null), but the data is Null, then empty resultsets can be
returned from the query, even if none of the data you are selecting
specifically has this issue.

In our case, this results from using an old version of the Classic
Control for Btrieve with new PSQL files with the True Null flag.
Creating or updating a record using the old control does not update
the Null value.

Of course, this will require a cleanup of the data in the near future
(that's on my plate), since there are fields that show as Null in
PCC/SQL, but not null in the CC or using Access 2.0's Btrieve access.

In any case, I wonder if this may be the situation with this set of
data - some NULL values in his Order table...


> I did some additional tests, and found the system to be working properly.  I
> therefore suspect your DDF's & data files might be slightly out of match.

> This is especially more clear when I get back to your original statement:
> > > But this query also returns an empty result set,

> > > SELECT "order".orderno, "customer".custno,"customer".name
> > > FROM  "order", "Customer"
> > > where "Order".CustNo = 1907

> If the problem were with comparisons with the AutoInc/Identity field, then this
> query would work properly.  Try to simplify this query to the following:
>     SELECT * FROM Order WHERE CustNo = 1907
> If this also gives no results, then the SQL engine is miscomparing the simple
> integer field CustNo.  I expect that if you do a BUTIL -STAT of the data file, you'd
> find that the field may be actually stored in the key structure as an unsigned int.
> When you set the DDF's to the same, it fixed it.  Of course, the problem could also
> be with a _different_ segment of the key which starts with CustNo.
>  Goldstar Software Inc.
>  Building on Btrieve(R) for the Future(SM)
>  Bill Bach

>  http://www.goldstarsoftware.com
>  *** Pervasive.SQL Development Classes ***
>  Chicago: April 22-26, 2002 - See our web site for details!
>  *** Pervasive.SQL Service & Support Classes ***
>  Chicago: May 14-17, 2002 - See our web site for details!


> > Hi Bill,

> > Thanks for your help. There are orders from CustNo = 1907.

> > I have found the problem. The CustNo field in the Customer table is of type
> > Identity, and the CustNo field in the Order table is a signed Integer32. P.SQL
> > does not seem to compare them properly. When I changed in the DDFs the type from
> > signed to unsigned it worked fine. This is with P.SQL 2000i SP4. I find that
> > quite restrictive, but now that this is solved I can move to something else.

> > Best regards,

> > Michel


> > > If there were NO results from the query where Order.CustNo = 1907, then
> > > perhaps there are just no orders for this customer???

> > > Assuming that you checked this...  Try the simpler query:
> > >     SELECT orderno, custno FROM  order where CustNo = 1907
> > > Does this returns data?  If not, how about:
> > >     SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
> > > Do you see any orders for CustNo 1907?

> > > If the data is there, but not being returned on the query, then you may have
> > > an indexing problem.  Look through your indices to see if CustNo is part of
> > > a segmented key.  If it is, try adding an additional restriction on the
> > > second field in the key definition.  If this works, let me know -- I'll
> > > explain why if I need to...
> > >  Goldstar Software Inc.
> > >  Building on Btrieve(R) for the Future(SM)
> > >  Bill Bach

> > >  http://www.goldstarsoftware.com
> > >  *** Pervasive.SQL Development Classes ***
> > >  Chicago: April 22-26, 2002 - See our web site for details!
> > >  *** Pervasive.SQL Service & Support Classes ***
> > >  Chicago: May 14-17, 2002 - See our web site for details!


> > > > Hi All,

> > > > Improper DDFs can result in strange queries which I learned from my last
> > > > post.

> > > > I still have a strange join behavior, even when a complete consistency
> > > > checks of my DDFs passes in the Pervasive Control Center.

> > > > I have a simple relation relation between customer and order, linked a
> > > > field called CustNo.

> > > > This query returns an emtpy result set:

> > > > SELECT "order".orderno, "customer".custno,"customer".name
> > > > FROM  "order", "customer"
> > > > where "customer".custno = "order".custno

> > > > It should list all the orders from all customers, but the result set is
> > > > emtpy.

> > > > This query returns all orders for customer 1907 as expected, so this
> > > > proves that the first query should have returned some results.

> > > > SELECT "order".orderno, "customer".custno,"customer".name
> > > > FROM  "order", "Customer"
> > > > where "Customer".CustNo = 1907

> > > > But this query also returns an empty result set,

> > > > SELECT "order".orderno, "customer".custno,"customer".name
> > > > FROM  "order", "Customer"
> > > > where "Order".CustNo = 1907

> > > > I am not very experienced with SQL, but I don't understand why two of
> > > > these queries are empty.

> > > > Any help appreciated.

> > > > Best regards,

> > > > Michel

> > --

> > Michel
> > ------
> > http://www.intervalsoftware.com
> > Envision Image Library
> > Spider Object Database
> > Spider Container and Persistent Classes  (freeware)
> > WordShare string localization  (freeware)

 
 
 

Join with empty result set, again...

Post by Miche » Wed, 10 Apr 2002 04:23:40


Hi Bill,

Thanks for your help. I am actually more confused now as a I have
multiple sets of DDF files and can't seem to reproduce the previous
results. Now all the queries seem to display properly the orders for
CustNo = 1907. Our client will be trying out the new DDF files, and I
will work it from there.

Best regards,

Michel


> If there were NO results from the query where Order.CustNo = 1907, then
> perhaps there are just no orders for this customer???

> Assuming that you checked this...  Try the simpler query:
>     SELECT orderno, custno FROM  order where CustNo = 1907
> Does this returns data?  If not, how about:
>     SELECT orderno, custno FROM  order where CustNo > 1900 ORDER BY CustNo
> Do you see any orders for CustNo 1907?

> If the data is there, but not being returned on the query, then you may have
> an indexing problem.  Look through your indices to see if CustNo is part of
> a segmented key.  If it is, try adding an additional restriction on the
> second field in the key definition.  If this works, let me know -- I'll
> explain why if I need to...
>  Goldstar Software Inc.
>  Building on Btrieve(R) for the Future(SM)
>  Bill Bach

>  http://www.goldstarsoftware.com
>  *** Pervasive.SQL Development Classes ***
>  Chicago: April 22-26, 2002 - See our web site for details!
>  *** Pervasive.SQL Service & Support Classes ***
>  Chicago: May 14-17, 2002 - See our web site for details!


> > Hi All,

> > Improper DDFs can result in strange queries which I learned from my last
> > post.

> > I still have a strange join behavior, even when a complete consistency
> > checks of my DDFs passes in the Pervasive Control Center.

> > I have a simple relation relation between customer and order, linked a
> > field called CustNo.

> > This query returns an emtpy result set:

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "customer"
> > where "customer".custno = "order".custno

> > It should list all the orders from all customers, but the result set is
> > emtpy.

> > This query returns all orders for customer 1907 as expected, so this
> > proves that the first query should have returned some results.

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "Customer"
> > where "Customer".CustNo = 1907

> > But this query also returns an empty result set,

> > SELECT "order".orderno, "customer".custno,"customer".name
> > FROM  "order", "Customer"
> > where "Order".CustNo = 1907

> > I am not very experienced with SQL, but I don't understand why two of
> > these queries are empty.

> > Any help appreciated.

> > Best regards,

> > Michel