Wrong parameter order of ODBC CONVERT

Detail name	Value
Changelog Number	7277
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.0.13
Fix Versions	Exasol 6.1.1
Resolution Date	2019-01-08

Background

Exasol 6.0.x and Exasol 6.1.0 use a different parameter order for CONVERT compared to the ODBC standard.
Use of the standard order leads to an "invalid data type in cast" exception.

Example:

CREATE SCHEMA  test;
CREATE OR REPLACE TABLE t1 (a varchar(100));
INSERT INTO t1 VALUES ('5');
-- This leads to an error in Exasol 6.0.x/6.1.0:
SELECT * from t1 where { fn CONVERT ("A", SQL_INTEGER) } = 5; 

Workaround

Use of a preprocessor script.

CREATE SCHEMA IF NOT EXISTS UTIL;
--/
CREATE OR REPLACE LUA SCRIPT UTIL."FN_CONVERT_REVERSE_ORDER_REPEATED" () RETURNS ROWCOUNT AS
function processconv(sqltext)
		local lasthit = 1
		while (true) do
			local tokens = sqlparsing.tokenize(sqltext)
			local convStart = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,'fn','CONVERT','(')
			if (convStart==nil) then
				break;
			end
			lasthit=convStart[3]
			local convEnd = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,')')
			if (convEnd==nil) then
				error("convert statement not ended properly")
				break;
			end
			local comma = sqlparsing.find(tokens,lasthit+1,true,true,sqlparsing.iswhitespaceorcomment,',' )
			if (comma==nil) then
				error("invalid convert function")
				break;
			end
			local convParam1=table.concat(tokens, '', lasthit+1, comma[1]-1)
			local convParam2=table.concat(tokens, '', comma[1]+1, convEnd[1]-1)
			local convStmt=convParam2..','..convParam1
			sqltext=table.concat(tokens, '',1,lasthit)..convStmt..table.concat(tokens,'', convEnd[1])
		end
		return sqltext
end
/
--/
CREATE OR REPLACE LUA SCRIPT UTIL."PREPROCESSFNCONVREPEATED" () RETURNS ROWCOUNT AS
import('util.fn_convert_reverse_order_repeated', 'fn_convert_reverse_order_repeated')
sqlparsing.setsqltext(fn_convert_reverse_order_repeated.processconv(sqlparsing.getsqltext()))
/

GRANT EXECUTE ON UTIL.PREPROCESSFNCONVREPEATED TO PUBLIC;
GRANT EXECUTE ON UTIL.FN_CONVERT_REVERSE_ORDER_REPEATED TO PUBLIC;
ALTER SYSTEM SET SQL_PREPROCESSOR_SCRIPT=UTIL.PREPROCESSFNCONVREPEATED;
CREATE SCHEMA  test;
CREATE OR REPLACE TABLE t1 (a varchar(100));
INSERT INTO t1 VALUES ('5');
SELECT * from t1 where { fn CONVERT ("A", SQL_INTEGER) } = 5; 

Solution

Exasol 6.1.1 allows the standard parameter order as well as the deprecated order of Exasol 6.0.x/6.1.0 for ODBC convert.

Example:

CREATE SCHEMA  test;
CREATE OR REPLACE TABLE t1 (a varchar(100));
INSERT INTO t1 VALUES ('5');
-- Standard ODBC order:
SELECT * from t1 where { fn CONVERT ("A", SQL_INTEGER) } = 5;
-- Deprecated Exasol 6.0.x/6.1.0 order:  
SELECT * from t1 where { fn CONVERT (SQL_INTEGER, "A") } = 5;