Title: PADR() is sometimes not respected in SQL SELECT statements using WHERE NOT IN (SELECT PADR() FROM )

Status: Send to vfpfeed@microsoft.com

Build: Visual FoxPro 09.00.0000.1720 for Windows
OS: Windows 5.01

The following SELECT statement gives a wrong result when SET VARCHARMAPPING ON and SET ANSI OFF (field 1 is C(10) and field2 is V(10))

SELECT * FROM Cursor1 WHERE field1 NOT IN (SELECT PADR(field2,10) FROM temp) INTO CURSOR result

Steps to Reproduce:

SET ENGINEBEHAVIOR 80

CREATE CURSOR Cursor1 (field1 c(10))
INSERT INTO Cursor1 (field1) VALUES ("Cursor1_1")
INSERT INTO Cursor1 (field1) VALUES ("Cursor1_2")
INSERT INTO Cursor1 (field1) VALUES ("Cursor1_3")
INSERT INTO Cursor1 (field1) VALUES ("Common1")
INSERT INTO Cursor1 (field1) VALUES ("Common2")
INSERT INTO Cursor1 (field1) VALUES ("Common3")
INSERT INTO Cursor1 (field1) VALUES ("Common4")
INSERT INTO Cursor1 (field1) VALUES ("Common5")
INSERT INTO Cursor1 (field1) VALUES ("Common6")
CREATE CURSOR Cursor2 (field2 c(10))
INSERT INTO Cursor2 (field2) VALUES ("Common1")
INSERT INTO Cursor2 (field2) VALUES ("Common2")
INSERT INTO Cursor2 (field2) VALUES ("Common3")
INSERT INTO Cursor2 (field2) VALUES ("Common4")
INSERT INTO Cursor2 (field2) VALUES ("Common5")
INSERT INTO Cursor2 (field2) VALUES ("Common6")
INSERT INTO Cursor2 (field2) VALUES ("Cursor2_1")
INSERT INTO Cursor2 (field2) VALUES ("Cursor2_2")
INSERT INTO Cursor2 (field2) VALUES ("Cursor2_3")

ACTIVATE SCREEN
CLEAR

? "This is the default setting for my machine"
SET varcharmapping ON
SET ANSI OFF
sqlresult()
SET ANSI ON
sqlresult()
SET varcharmapping OFF
SET ANSI OFF
sqlresult()
SET ANSI ON
sqlresult()


PROCEDURE sqlresult()


* This will make the fieldtype of field2 = IIF(SET('VARCHARMAPPING')='ON','V(10)','C(10)')
SELECT PADR(ALLTRIM(field2),10) AS field2 FROM Cursor2 INTO CURSOR temp

SELECT * FROM Cursor1 WHERE field1 NOT IN (SELECT field2 FROM temp) INTO CURSOR result
? "SET('VARCHARMAPPING')="+SET('VARCHARMAPPING'),"SET('Ansi')="+SET('Ansi')

SELECT * FROM Cursor1 WHERE field1 NOT IN (SELECT field2 FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 NOT in (SELECT field2 FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"
SELECT * FROM Cursor1 WHERE field1 IN (SELECT field2 FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 in (SELECT field2 FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"

SELECT * FROM Cursor1 WHERE field1 NOT IN (SELECT PADR(field2,10) FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"
IF SET('VARCHARMAPPING')="ON" AND SET('Ansi')="OFF"
? "******** I would have expected the result to be 3 here because field1 is C(10) and PADR(field2,10) is a fixed string of 10 characters"
ENDIF
SELECT * FROM Cursor1 WHERE field1 IN (SELECT PADR(field2,10) FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"

SELECT * FROM Cursor1 WHERE field1 NOT IN (SELECT CAST(field2 AS c(10)) FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 NOT in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"
SELECT * FROM Cursor1 WHERE field1 IN (SELECT CAST(field2 AS c(10)) FROM temp) INTO CURSOR result
? "SELECT * FROM cursor1 WHERE field1 in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = "+TRANSFORM(_TALLY)+" records"
 
ENDFUNC

Observed Behavior:

This is the default setting for my machine   
SET('VARCHARMAPPING')=ON           SET('Ansi')=OFF   
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT field2 FROM temp) INTO CURSOR result = 9 records                       
SELECT * FROM cursor1 WHERE field1 in (SELECT field2 FROM temp) INTO CURSOR result = 6 records                     
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 9 records                        
******** I would have expected the result to be 3 here because field1 is C(10) and PADR(field2,10) is a fixed string of 10 characters        
SELECT * FROM cursor1 WHERE field1 in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 6 records                      
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 6 records                       
SET('VARCHARMAPPING')=ON           SET('Ansi')=ON   
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT field2 FROM temp) INTO CURSOR result = 3 records                       
SELECT * FROM cursor1 WHERE field1 in (SELECT field2 FROM temp) INTO CURSOR result = 6 records                     
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 6 records                      
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 6 records                       
SET('VARCHARMAPPING')=OFF           SET('Ansi')=OFF   
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT field2 FROM temp) INTO CURSOR result = 3 records                       
SELECT * FROM cursor1 WHERE field1 in (SELECT field2 FROM temp) INTO CURSOR result = 6 records                     
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 6 records                      
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 6 records                       
SET('VARCHARMAPPING')=OFF           SET('Ansi')=ON   
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT field2 FROM temp) INTO CURSOR result = 3 records                       
SELECT * FROM cursor1 WHERE field1 in (SELECT field2 FROM temp) INTO CURSOR result = 6 records                     
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 6 records                      
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 3 records                        
SELECT * FROM cursor1 WHERE field1 in (SELECT cast(field2 as C(10)) FROM temp) INTO CURSOR result = 6 records                       

Expected Behavior:

SET('VARCHARMAPPING')=ON           SET('Ansi')=OFF   
SELECT * FROM cursor1 WHERE field1 NOT in (SELECT padr(field2,10) FROM temp) INTO CURSOR result = 3 records                        

Remarks:

The workaround is to use CAST(field2 AS C(10)) instead of PADR(field2,10) but that means that existing code has to be changed.

This is a simplified version of existing code that runs correctly in VFP8. It failed to work because of the SET VARCHARMAPPING ON, a CHAR was changed to VARCHAR in a SQL SELECT and a PADR() in a SQL SELECT will not change a VARCHAR to CHAR, even though the PADR() returns a fixed length string.

The following program demonstrates the PADR() issue.

SET ENGINEBEHAVIOR 80

SET varcharmapping OFF
fieldtest()
SET varcharmapping ON
fieldtest()

PROCEDURE fieldtest()

? "SET('VARCHARMAPPING')="+SET('VARCHARMAPPING')

CREATE CURSOR Cursor1 (field1 c(10))

SELECT field1,ALLTRIM(field1),IIF(.T.,field1,field1),PADR(field1,10),CAST(field1 AS C(10)) FROM Cursor1 INTO CURSOR result

AFIELDS(laFields)

? "FieldType="+laFields[1,2],"ALLTRIM Type="+laFields[2,2],"IIF Type="+laFields[3,2],"PADR Type="+laFields[4,2],"CAST Type="+laFields[5,2]

CREATE CURSOR Cursor1 (field1 v(10))

SELECT field1,ALLTRIM(field1),IIF(.T.,field1,field1),PADR(field1,10),CAST(field1 AS C(10)) FROM Cursor1 INTO CURSOR result

AFIELDS(laFields)

? "FieldType="+laFields[1,2],"ALLTRIM Type="+laFields[2,2],"IIF Type="+laFields[3,2],"PADR Type="+laFields[4,2],"CAST Type="+laFields[5,2]

IF SET('VARCHARMAPPING')="ON"
? "******** I would have expected the PADR fieldtype to be C because PADR(field1,10) is a fixed string of 10 characters"
ENDIF

The result is:

SET('VARCHARMAPPING')=OFF          
FieldType=C    ALLTRIM Type=C      IIF Type=C   PADR Type=C      CAST Type=C    
FieldType=V   ALLTRIM Type=V     IIF Type=V  PADR Type=V     CAST Type=C    
SET('VARCHARMAPPING')=ON          
FieldType=C    ALLTRIM Type=V     IIF Type=V  PADR Type=C      CAST Type=C    
FieldType=V   ALLTRIM Type=V     IIF Type=V  PADR Type=V     CAST Type=C    
******** I would have expected the PADR fieldtype to be C because PADR(field1,10) is a fixed string of 10 characters         

Regards,