Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SELECT-SQL command have different results in VFP8 and VF
Message
From
05/05/2005 06:55:34
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows XP SP2
Network:
Windows 2000 Server
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01010969
Message ID:
01011204
Views:
15
Of course, the EMPTY() it is not the bug generator.

This is a simplest and more general repro code,
any "in and functions" that transform a NULL into a .T. value can show the bug.
CLEAR

CREATE CURSOR taba ( ;
		ID I, ;
		field2 C(1) ;
	)

INSERT INTO taba VALUES( 0101,  " ") 
INSERT INTO taba VALUES( 0201,  "X")

SET ANSI OFF && or ON
SET NULLDISPLAY TO '!NULL!'
SYS(3054,0)

test()

?
? "ADD A JOIN INDEX REMOVE THE OPTIMIZATION BUG"
 INDEX ON id TAG pkey
SELECT taba
TEST()

PROCEDURE test()
select * from taba tabb ;
	LEFT join taba on taba.id=tabb.id;
		WHERE NVL(taba.field2#'X',.T.);
	INTO ARRAY RRR
? "FAIL 1",_TALLY>1 AT 10

select * from taba tabb;
	LEFT join taba on taba.id=tabb.id;
		WHERE NVL(taba.field2#'X',.T.) OR .F.; && a OR condition is not optimizable
	INTO ARRAY RRR
? "FAIL 1OR",_TALLY>1 AT 10

select * from taba tabb;
	LEFT join taba on taba.id=tabb.id;
		HAVING NVL(taba.field2#'X',.T.); && HAVING IS NOT OPTIMIZABLE
		INTO ARRAY RRR
? "FAIL 1H",_TALLY>1 AT 10

select * from taba tabb;
	LEFT join taba on taba.id=tabb.id ;
		WHERE NOT EMPTY(taba.field2);
		INTO ARRAY RRR
? "FAIL 2",_TALLY>1 AT 10		

select * from taba tabb;
	LEFT join taba on taba.id=tabb.id ;
		WHERE NOT EMPTY(taba.field2) OR .F.;
		INTO ARRAY RRR
? "FAIL 2OR",_TALLY>1 AT 10		


select * from taba tabb;
	LEFT join taba on taba.id=tabb.id ;
		HAVING NOT EMPTY(taba.field2);
	INTO ARRAY RRR
? "FAIL 2H",_TALLY>1 AT 10
Not simple for the VFPT implement a sure LEFT JOIN prefilter optimization.
Previous
Reply
Map
View

Click here to load this message in the networking platform