CONVERSION_MODE option

Function

Restricts implicit conversion between binary data types (BINARY, VARBINARY, and LONG BINARY) and other non-binary data types (BIT, TINYINT, SMALLINT, INT, UNSIGNED INT, BIGINT, UNSIGNED BIGINT, CHAR, VARCHAR, and LONG VARCHAR) on various operations.

Allowed values

0, 1

Default

Scope

Can be set either publicly or temporarily. DBA permissions are not required to set this option.

Description

The default value of 0 maintains implicit conversion behavior prior to version 12.7. Setting CONVERSION_MODE to 1 restricts implicit conversion of binary data types to any other non-binary data type on INSERT, UPDATE, and in queries. The restrict binary conversion mode also applies to LOAD TABLE default values and CHECK constraint. The use of this option prevents implicit data type conversions of encrypted data that would result in semantically meaningless operations.

Implicit conversion restrictions

The CONVERSION_MODE option restrict binary mode value of 1 restricts implicit conversion for the following operations.

LOAD TABLE The restrict implicit binary conversion mode applies to LOAD TABLE with CHECK constraint or default value.

For example:

CREATE TABLE t3 (c1 INT,
		   csi SMALLINT,
		   cvb VARBINARY(2),
		   CHECK (csi<cvb));
SET TEMPORARY OPTION CONVERSION_MODE = 1;

The following request:

LOAD TABLE t3(c1 ',', csi ',', cvb ',')
	     FROM 't3.inp'
	     QUOTES OFF ESCAPES OFF
	     ROW DELIMITED BY '\n'

fails with the message:

"Invalid data type comparison in predicate
(t3.csi < t3.cvb), [-1001013] ['QFA13']"

INSERT The restrict implicit binary conversion mode applies to INSERT...SELECT, INSERT...VALUE, and INSERT...LOCATION.

For example:

CREATE TABLE t1 (c1 INT PRIMARY KEY,
		   cbt  BIT NULL
		   cti  TINYINT,
		   csi  SMALLINT,
		   cin  INTEGER,
		   cui  UNSIGNED INTEGER,
		   cbi  BIGINT,
		   cub  UNSIGNED BIGINT,
		   cch  CHAR(10),
		   cvc  VARCHAR(10),
		   cbn  BINARY(8),
		   cvb  VARBINARY(8),
		   clb  LONG BINARY,
		   clc  LONG VARCHAR));

CREATE TABLE t2 (c1 INT PRIMARY KEY,
		   cbt  BIT NULL,
		   cti  TINYINT,
		   csi  SMALLINT,
		   cin  INTEGER,
		   cui  UNSIGNED INTEGER,
		   cbi  BIGINT,
		   cub  UNSIGNED BIGINT,
		   cch  CHAR(10),
		   cvc  VARCHAR(10),
		   cbn  BINARY(8),
		   cvb  VARBINARY(8),
		   clb  LONG BINARY,
		   clc  LONG VARCHAR));

CREATE TABLE t4 (c1 INT, cin INT DEFAULT 0x31);

SET TEMPORARY OPTION CONVERSION_MODE = 1;

The following request:

INSERT INTO t1(c1, cvb) SELECT 99, cin FROM T2
WHERE c1=1

fails with the message:

"Unable to convert column 'cvb' to the requested datatype (varbinary) from datatype (integer).
[-1013043] ['QCA43']"

The following request:

INSERT INTO t4 VALUES (1, DEFAULT)

fails with the message:

"Unable to convert column 'cin' to the requested datatype (integer) from datatype (varbinary).
[-1013043] ['QCA43']"

UPDATE The restrict implicit binary conversion mode applies to the following types of UPDATE:

UPDATE SET VALUE FROM expression (including constant)
UPDATE SET VALUE FROM other column
UPDATE SET VALUE FROM host variable
JOIN UPDATE SET VALUE FROM column of other table

For example, the following request:

UPDATE t1 SET cbi=cbn WHERE c1=1

fails with the message:

"Unable to implicitly convert column 'cbi' to datatype (bigint) from datatype (binary). [-1000187] ['QCB87']"

Positioned INSERT and positioned UPDATE via updatable cursor The restrict implicit binary conversion mode applies to the following types of INSERT and UPDATE via updatable cursor:

PUT cursor-name USING ... host-variable
Positioned UPDATE from another column
Positioned UPDATE from a constant
Positioned UPDATE from a host variable

For example, the following request:

BEGIN
	DECLARE curs SCROLL CURSOR FOR SELECT * FROM t1
	FOR UPDATE;
	OPEN curs WITH HOLD;
	FETCH curs;
	UPDATE t1 SET cbi=cbn WHERE CURRENT OF curs;
END

fails with the message:

"Unable to implicitly convert column 'cbn' to datatype (bigint) from datatype (binary). [-1000187] ['QCB87']"

Queries The restrict implicit binary conversion mode applies to all aspects of queries in general.

Comparison Operators When CONVERSION_MODE = 1, the restriction applies to the following operators: =, !=, <, <=, >=, <>, !>, !< BETWEEN .. AND IN used in a search condition for the following clauses: WHERE clause HAVING clause CHECK clause ON phrase in a join IF/CASE expression

For example, the following query:
```
SELECT COUNT(*) FROM T1
WHERE cvb IN (SELECT csi FROM T2)
```
fails with the message:
```
"Invalid data type comparison in predicate (t1.cvb IN (SELECT t1.csi ...)), [-1001013] ['QFA13']"
```
String Functions When CONVERSION_MODE = 1, the restriction applies to the following string functions: CHAR CHAR_LENGTH DIFFERENCE LCASE LEFT LOWER LTRIM PATINDEX RIGHT RTRIM SIMILAR SORTKEY SOUNDEX SPACE STR TRIM UCASE UPPER

For example, the following query:
```
SELECT ASCII(cvb) FROM t1 WHERE c1=1
```
fails with the message:
```
"Data exception - data type conversion is not possible. Argument to ASCII must be string, [-1009145] ['QFA2E']"
```
The following functions allow either a string argument or a binary argument. When CONVERSION_MODE = 1, the restriction applies to mixed type arguments, that is, one argument is string and the other argument is binary.INSERTSTR LOCATE REPLACE STRING STUFF

For example, the following query:
```
SELECT STRING(cvb, cvc) FROM t1 WHERE c1=1
```
where the column cvb is defined as VARBINARY and the column cvc is defined as VARCHAR, fails with the message:
```
"Data exception - data type conversion is not possible. Arguments to STRING must be all binary or all string, [-1009145] ['QFA2E']"
```
The restriction does not apply to the following string functions: BIT_LENGTH BYTE_LENGTH CHARINDEX LENGTH OCTET_LENGTH REPEAT REPLICATE SUBSTRING
Arithmetic Operations and Functions When CONVERSION_MODE = 1, the restriction applies to the following operators used in arithmetic operations: +, -, *, /

The restriction applies to the following bitwise operators used in bitwise expressions: & (AND), | (OR), ^ (XOR)

The restriction also applies to integer arguments of the following functions: ROUND “TRUNCATE” TRUNCNUM

For example, the following query:
```
SELECT ROUND(4.4, cvb) FROM t1 WHERE C1=1
```
fails with the message:
```
"Data exception - data type conversion is not possible. Second Argument to ROUND cannot be converted into an integer, [-1009145] ['QFA2E']"
```
Integer Argument to Various Functions When CONVERSION_MODE = 1, the restriction applies to integer argument of the following functions: ARGN SUBSTRING DATEADD YMD

For example, the following query:
```
SELECT ARGN(cvb, csi, cti) FROM t1 WHERE c1=1
```
fails with the message:
```
"Data exception - data type conversion is not possible. First Argument to ARGN cannot be converted to an integer, [-1009145] ['QFA2E']"
```
Analytical Functions, Aggregate Functions, and Numeric Functions When CONVERSION_MODE = 1, no further restriction applies to analytical functions, aggregate functions, and numeric functions that require numeric expressions as arguments.