Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT STATEMENT produces different results in VFP 8 and VFP 9

Status
Not open for further replies.

paparupps

Programmer
Nov 3, 2010
3
US
I am having a problem where a query produces different results based on whether it's run in VFP 8 or VFP 9. Here is the query :

SELECT DISTINCT gage.gageno, gage.loc, gage.lastused, gage.duedate, gage.outofserv, gage.gagedesc, gage.customer, gagepart.partno
FROM gage LEFT JOIN gagepart ON ALLTRIM(gage.gageno) = ALLTRIM(gagepart.gageno)
WHERE LEFT(gage.gageno,6) == '13-01-' INTO CURSOR fred

The following are settings in both versions :

ANSI = OFF
EXACT = OFF
ENGINEBEHAVIOR = 70

When run in 9, the query includes a record where the GAGEPART table contains a record with a blank gageno field. When run in 8, it does not. I know I could rectify this by replacing the = in the JOIN clause with a ==, but I'm looking for the reason why this happens. Is there some other setting I need to look at ?

Any insight here would be appreciated.
 
not sure if enginebehaviour affects SET SQLBUFFERING


but try SET SQLBUFFERING OFF


hth
n
 
Thanks, but that doesn't alleviate the problem. I was sure the issues was with EXACT or ANSI until I verified that the settings were the same.
 
Just at a glance, I'd bet using Alltrim() in the join expression is gumming this one up.
 
Indeed as gage.gageno always starts with 13-01 this is a weird join.

Why Enginebehavior 70 at all? You only need this in case of a group by erroring in VFP8 or 9.
I don't know of change in behaviour about blank fields.

What is the blank field really? ISBLANK(), ISNULL(), EMPTY()?
Comparing anything to .NULL. yields the boolean result .NULL. As .NULL. is not .F. it may cause a join or not.

In comparison:
Code:
If .null.
? .t.
Else
? .f.
endif

But .NULL. behavior should be the same in VFP8 and 9 and there is no setting involved.

Are you sure the blank value is coming from the gagepart table? You do a LEFT join, that means you get ALL records of gage, even if there is no join, so gagepart.partno can introduce .NULL.s in your result, even thoug no record in gagepart has a .NULL in partno and even if the partno field is not nullable. That's the nature of left joins vs inner joins.

But indeed that again should be the same in both VFP8 and 9.

Bye, Olaf.
 
Just out of curiosity, have you tried running it with ENGINEBEHAVIOR set to 80? Since you are comparing VFP 8.0 with 9.0, it would seem a logical thing to try - although I can't off-hand think of any compatibility issue that would explain what you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
To all,

Thanks for your answers. We have decided that we are going to set ANSI on to make sure the behavior of the queries is the same in 8 and 9. I have tried this and it works. I was looking for a way to have to change the least amount of programs as we migrate to 9. Believe it or not, I just recently finished converting the old Fox DOS 2.6 code to VFP 8 and we are now getting everything into 9 so we can use a tool we found to get us out of Foxpro completely, unfortunately. The problem was never the individual query, but rather the difference between how the SQL commands work differently in 8 and 9.

 
Indeed, if you lok into the help description, ANSI ON causes about the same as EXACT OFF for queries. And indeed in VFP9 EXACT has no meaning in SQL comparisons as ANSI is controlling the comparison behavior.
I never did VFP8, so was ANSI perhaps introduced in VFP9?

Indpendant of that, we oftern talk about an ansi sql standard, eg ansi sql-92 standard. In that context sql comparisons have to be inexact, otherwise "Smith " and "Smith " are not equal, so in case you compare table columns of different size, you would never get a match. You could only get matches, if fields are same length.

Besides, this is also a reason to avoid ALLTRIM in queries. ANSI ON causes an easier to use string comparison.

Bye, Olaf.
 
If there is any change in SET ANSI between 8.0 and 9.0, it is not documented. Putting the Help pages for the two versions side by side, the only significant difference I can see is that the 9.0 version refers to binary as well as string expressions. But that's clearly not relevant in this case.

It would be nice to see a definitive answer for this one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
SET ANSI is definitely not new. It was added when SQL was added to the language in Foxpro 1.0. :)

Be careful when fiddling with it, though. It has teeth!

In combination with SET EXACT and (another one that slips my mind right now) you can bring the query engine to its knees. Even simple queries against small data sets can become cripplingly slow.

They improved the query engine in later versions to mitigate this somewhat, but it can still set up a perfect storm if you hold your head just right.
 
Dan, I doubt it was always there. EXACT is old, but I remember finding SET ANSI as a new feature I don't remember in VFP7 or 6.
Since I skipped VFP8 I didn't knew it existed there, too.

ANSI codepages are old, and maybe also ansi behavior of queries is old, but the seperation of exact for normal code and ANSI for queries was introduced late - I am almost sure. Nevertheless there should not be a difference between version 8 and 9.

Bye, Olaf.
 
Also, slow queries mostly are cause by wrong indexing or wrong collation setting, so indexes are not used, because their collation sequence differs from the current setting. I skip that feature and keep it at MACHINE, you get ASCII/ANSI (codepage) sorting that way instead of german or any language specific sorting, but it's faster. And it has nothing to do with the ANSI string comparison behavior.

Bye, Olaf.
 
Olaf, dear, you say yourself ANSI codepages are old but you doubt it was always there?

It was. You may have "discovered" it later in life but that doesn't mean it wasn't there.
 
I don't doubt ANSI codepages were not always there,
I doubt SET ANSI was always there.
SET ANSI has to do with sql engine behavior, not with codepages.

Bye, Olaf.
 
For what it's worth, SET ANSI was present in FPD 2.6. Just took a quick look at the Help file for that version. Can't see any obvious differences with the present version of the command.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Thanks Mike,

that shifts something right.

I remember a discussion with wOOdy about SET EXACT and queries and he pointed me to SET ANSI for EXACT has no influence there and I remember this must have been around 2007, when we already were at VFP9, so I was only aware of it very late. Sorry to bother.

Bye, Olaf.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top