Hi Kamil,

The issue you have with SET RELATION is expected.

First of all, Character NOCPTRANS data type does not represent binary data. Unfortunately Character (binary) is very often used as a synonym and the word "binary" makes people think about the data type as something it is not. The NOCPTRANS key word specifies only one thing - no code page translation is performed during reading/writing into the field. That means, as soon as value is loaded into memory, it is treated as a character value in the current code page. Character NOCPTRANS data type represents character data.

VFP considers indexes as indexes on expressions, even if an index is built on one field. Therefore, the NOCPTRANS modifier for a field doesn't make any difference, index works with values and all character values are considered to be encoded using table's code page. When current code page is different from the table's code page, any attempt to look for a character value within the index causes implicit translation of the value into table's code page first. There is no way a value from a NOCPTRANS field can be found unless, of course, the code page translation doesn't modify the value at all.

In VFP9, there is a new data type called Varbinary, both Varbinary fields and indexes on Varbinary values are not subject to a codepage translation.

The incorrect result produced by SQL SELECT looks like a BUG to me and happens due to the same reason. Below is the code to demonstrate it (the code uses some new VFP9 features). If it is executed with CPCURRENT()==1252, SQL SELECT returns incorrect result every time index on T1251 table is used to optimize the JOIN.

CLEAR
CLOSE DATABASES all
SET COLLATE TO "MACHINE" 
SET ANSI ON

?CPCURRENT()

IF .T.
	_screen.FontCharSet=204
	cRussian1251=0hE0E1E2E3E4E5B8E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FCFAFBFDFEFFC0C1C2C3C4C5A8C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DCDADBDDDEDF

	CREATE TABLE T1251 CODEPAGE=1251 (f0 I,f1 C(66) NOCPTRANS)
	*CREATE TABLE T1251 CODEPAGE=1251 (f0 I,f1 Q(66))
	INSERT INTO T1251 VALUES (1,cRussian1251)
	LIST

	CREATE TABLE TCurr CODEPAGE=1252 (f2 I, f3 C(66) NOCPTRANS)
	*CREATE TABLE TCurr  CODEPAGE=1252 (f2 I, f3 Q(66))
	INSERT INTO TCurr VALUES (2,cRussian1251)
	LIST 
ELSE
	USE T1251
	DELETE TAG all
	USE TCurr IN 0 
	SELECT TCurr
	DELETE TAG all
ENDIF

SYS(3054,12)

SELECT f0,f2 FROM FORCE T1251 JOIN TCurr ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
SELECT f0,f2 FROM FORCE TCurr JOIN T1251 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
select T1251 
INDEX ON f1 TAG f1

select TCurr 
INDEX ON f3 TAG f3

SELECT f0,f2 FROM FORCE T1251 JOIN TCurr ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
SELECT f0,f2 FROM FORCE TCurr JOIN T1251 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()

SYS(3054,0)

RETURN 

It actually works fine if fields are not using NOCPTRANS and values can be translated back and forth without any loss. For example, the following code produces correct result with CPCURRENT()==1251 and CPCURRENT()==866.

CLEAR
CLOSE DATABASES all
SET COLLATE TO "MACHINE" 
SET ANSI ON

?CPCURRENT()

IF .T.
	_screen.FontCharSet=204
	
	cRussian1251=0hE0E1E2E3E4E5B8E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FCFAFBFDFEFFC0C1C2C3C4C5A8C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DCDADBDDDEDF

	if(CPCURRENT()=866)
		cRussian1251=CPCONVERT(1251,866,cRussian1251)
	ENDIF 

	CREATE TABLE T1251 CODEPAGE=1251 (f0 I,f1 C(66))
	INSERT INTO T1251 VALUES (1,cRussian1251)
	LIST

	CREATE TABLE T866 CODEPAGE=866 (f2 I, f3 C(66))
	INSERT INTO T866 VALUES (2,cRussian1251)
	LIST 
ELSE
	USE T1251
	DELETE TAG all
	USE T866 IN 0 
	SELECT T866 
	DELETE TAG all
ENDIF

GO TOP IN T1251 
GO TOP IN T866
?T1251.f1==T866.f3 

SYS(3054,12)

SELECT f0,f2 FROM FORCE T1251 JOIN T866 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
SELECT f0,f2 FROM FORCE T866 JOIN T1251 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
select T1251 
INDEX ON f1 TAG f1

select T866 
INDEX ON f3 TAG f3

SELECT f0,f2 FROM FORCE T1251 JOIN T866 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()
?
SELECT f0,f2 FROM FORCE T866 JOIN T1251 ON f1=f3 INTO CURSOR res
list
?RECCOUNT()

SYS(3054,0)

RETURN 

Thanks,
Aleksey.