Bug in FoxPro 2.6 Sql? 1

[ChrisCarroll]

Any takers for the following puzzle, which is a simplified version of a query that returns the wrong answer:

Run this from the command line:

set date british
create table err (datedied D, retdate D)
insert into err values ({}, {11/01/1999})
select * from err where ;
  ( (datedied>{1/7/1999} or datedied={}) and ;
    (retdate >{1/7/1999} or retdate ={}) )

&& --> Correctly returns zero records

select * from err where ;
  ( (datedied>{1/7/1999} or datedied={}) and ;
    (retdate >{1/7/1999} or retdate ={}) ) or .F.

&&--> InCorrectly returns 1 record

Are we blind, or is it really, simply wrong??

Chris [sig][/sig]

 

[dgrewe]

This worked for me, so the problem is the use of the date{} function.

set date [red]to[/red] british
create table err (datedied D, retdate D)
insert into err values([red]ctod(" / / &quot , ctod("11/01/99&quot[/red])

*select * from err where ;
* ( (datedied>[red]ctod("01/07/1999&quot[/red] or datedied=[red]ctod(" / / &quot[/red]) and ;
* (retdate >[red]ctod("01/07/1999&quot[/red] or retdate =[red]ctod(" / / &quot[/red]) )

&& --> Correctly returns zero records
select * from err where ;
( (datedied>[red]ctod("01/07/1999&quot[/red] or datedied=[red]ctod(" / / &quot[/red]) and ;
(retdate >[red]ctod(&quot;01/07/1999&quot[/red] or retdate =[red]ctod(&quot; / / &quot[/red]) ) or .F. [sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[ChrisCarroll]

Interesting idea but -

I copied and pasted your code - and it gave me the same wrong result, returning 1 record. In fact, I then remove the dates, and even field values, leaving just constants:

Create Table Err ( A N(1) )
insert into err values(1)

select * from err where ;
  ( (.f. or .t. ) and (.f.  or .f.) )  or .F. 
  && --> InCorrectly returns 1 record

? ( (.f. or .t. ) and (.f.  or .f.) )  or .F. 
   && --> Correctly prints .F.

Are you sure it worked right on yours?

We are on FPW 2.6a on NT 4
[sig][/sig]

 

[dgrewe]

Yep, I just went back and reran it again in both FPW26a and VFP6SP3 with the same results. I'm using Win98. In both selects, in both versions, I got zero records returned.
[sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[ChrisCarroll]

I would be grateful if anyone else out there with FP 2.x could try it and tell me what result they get, and on what FP/OS version. We've tried it on half a dozen machines, all FPW2.6a on NT4 workstation (with last year's fast processor patch applied), and every one gives the wrong answer.

Thanks,

Chris [sig][/sig]

 

[Guest_imported]

Excuse me, but I thought British dates were dd/mm/yy
If that's the case, then the example code needs a bit of rearranging, right?

Hank Castello

http://www.foxprosolutions.com

 

[Guest_imported]

OK - the dates work either way - That'll teach me to log on before having that first cup of coffee!

I ran it in VFP 6.0 (Sorry - but I had to translate to American, it may take 2 cups of coffee before I could do it British!) and it worked fine..

set date american
create table err (datedied D, retdate D)
insert into err values ({}, {^1999-01-11})
select * from err where ;
( (datedied>{^1999-07-01} or datedied={}) and ;
(retdate >{^1999-07-01} or retdate ={}) )
wait str(_tally) window
&& --> Correctly returns zero records

select * from err where ;
( (datedied>{^1999-07-01} or datedied={}) and ;
(retdate >{^1999-07-01} or retdate ={}) ) or .F.
wait str(_tally) window
&&--> Correctly returns 0 records as well

I don't have my 2.6 installed at the moment, so hopefully someone else can help solve this.

Hank Castello

http://www.foxprosolutions.com

 

[AbleA]

Chris,

I also get the same error (1 record returned), using your simplified query:

select * from err where ((.f. or .t.) or (.f. or .f.)) or .f.

Like you, I'm on FPW2.6a and NT4. [sig]<p>Andy Blay<br><a href=mailto:a.d.blay@talk21.com>a.d.blay@talk21.com</a><br><a href= > </a><br> [/sig]

 

[AbleA]

OK, I've got no excuse for that one (i've just had my first coffee).

Of course, the query I used was:

select * from err where ((.f. or .t.) and (.f. or .f.)) or .f. [sig]<p>Andy Blay<br><a href=mailto:a.d.blay@talk21.com>a.d.blay@talk21.com</a><br><a href= > </a><br> [/sig]

 

[ChrisCarroll]

So we have 2 of us get an wrong result running FPW2.6a on NT4, and 1 gets a correct result on Win98.

Any other answers please?

I'm glad to know it's not just me losing my marbles - but where do we go from here :-( ?

[sig][/sig]

 

[ChrisCarroll]

Asking around elsewhere we now have several people reporting the same fault in FPW 2.6a, and one also on FPDos 2.5

dgrewe, (and others with FP2.x) would you mind pasting & running this:

--------------
We find that T3() and T4() are not evaluated - even though it's logically impossible to evaluate the expression without evaluating at least one of them given that T2() is .T.
--------------

? version(), os(), os(1)

Create table Err ( A N(1) )
insert into err values(1)

select * from err where ( (.f. or .t. ) and (.f.  or .f.) )  or .F. into cursor x1
? _tally   && --> InCorrectly returns 1 record

select * from err where ( (.f. or .f. ) and (.f.  or .t.) )  or .F. into cursor x2
? _tally	&& --> Correctly returns 0 record

select * from err where ( (.f. or .t. ) and (.f.  or .f. ) )  or .F. into cursor x3
? _tally && --> InCorrectly returns 1 record
?

Select * from err where ( (T1() or T2() ) and (T3()  or T4()) )  or T5() into cursor x1
? _tally && --> Should have to evaluate at least one of T3() or T4() before knowing the answer

?

Return


*-- Sample Functions
function T1
??"T1  "
return .f.

function T2
??"T2  "
return .t.

function T3
??"T3  "
return .f.

function T4
??"T4  "
return .f.

function T5
??"T5  "
return .f.

Thank you.

ChrisCarroll [sig][/sig]

 

[dgrewe]

OK, Here is the results when I run your code.
FoxPro 2.6 for Windows DOS 07.10 Windows 3.95
1
0
1
T1 T2 T5 T1 T2 T5
1
[sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[dgrewe]

Running the same code on the same computer in VFP I get
Visual FoxPro 06.00.8492.00 for Windows Windows 4.10 Windows 4.10
0
0
0
T1 T2 T3 T4 T5 T1 T2 T3 T4 T1 T2 T1 T2 T3 T4 T3 T4 T5 T1 T2 T3 T4 T5
0

[sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[ChrisCarroll]

So I guess we agree that SQL for FoxPro 2.6 (and 2.5) for Win & Dos wrongly evaluates expressions of the form

(T1 or T2) and (T3 or T4) ) or T5()

when t1 or t2 is .T. and t5 is .F.

Which raises the follow on question(s):

1) What other expressions will evaluate incorrectly
2) How, in particular, can we guarantee that programs that generate SQL strings dynamically will avoid errors?

I find this a rather serious bug. Once you've lost confidence in your ability to evaluate boolean expressions, you've lost confidence in your ability to run queries.

[sig][/sig]