CAST(something AS ARRAY datatype) #7880
Comments
dmitry-lipetsk
added a commit
to dmitry-lipetsk/firebird
that referenced
this issue
Nov 27, 2023
These changes force a server to return correct datatype for CAST(something as array datatype) - array type instead type of element.
1) Test of CAST(arrayColumn as ARRAY)
SQL> set sqlda_display on;
SQL> SELECT CAST(COL_I4 AS VARCHAR(128)[0:2]) FROM TEST_ARR1;
INPUT message field count: 0
OUTPUT message field count: 1
01: sqltype: 540 ARRAY Nullable scale: 0 subtype: 0 len: 8
: name: CAST alias: CAST
: table: owner:
CAST
=================
111:0
Usage of UDFs with SCALAR_ARRAY-argument. These functions return different information about array: DIM, DTYPE, SUBTYPE, SCALE
SQL> -- Bad column type: VARCHAR instead ARRAY
SQL> -- FETCH returns incorrect data
SQL> SELECT IBP_UDF__GET_ARR_DIM(CAST(COL_I4 AS VARCHAR(128)[0:2])) FROM TEST_ARR1;
IBP_UDF__GET_ARR_DIM
====================
1
SQL> SELECT IBP_UDF__GET_ARR_DESC_DTYPE(CAST(COL_I4 AS VARCHAR(128)[0:2])) FROM TEST_ARR1;
IBP_UDF__GET_ARR_DESC_DTYPE
===========================
9 SQL> SELECT IBP_UDF__GET_ARR_DESC_LENGTH(CAST(COL_I4 AS VARCHAR(128)[0:2])) FROM TEST_ARR1; IBP_UDF__GET_ARR_DESC_LENGTH
============================
4 SQL> SELECT IBP_UDF__ADTYPE__VCH64(CAST(COL_I4 AS VARCHAR(128)[0:2])) FROM TEST_ARR1; IBP_UDF__ADTYPE__VCH64
======================
3 SQL> SELECT IBP_UDF__ADTYPE__CH64(CAST(COL_I4 AS VARCHAR(128)[0:2])) FROM TEST_ARR1;
IBP_UDF__ADTYPE__CH64
=====================
1
SQL> -- Bad column type: INTEGER instead ARRAY
SQL> -- FETCH returns conversion error from string "#x01#x01#x01#x00#x04# ....."
SQL> SELECT IBP_UDF__GET_ARR_DIM(CAST(COL_I4 AS INTEGER[0:2])) FROM TEST_ARR1;
IBP_UDF__GET_ARR_DIM
====================
1
SQL> SELECT IBP_UDF__GET_ARR_DESC_DTYPE(CAST(COL_I4 AS INTEGER[0:2])) FROM TEST_ARR1;
IBP_UDF__GET_ARR_DESC_DTYPE
===========================
9
SQL> SELECT IBP_UDF__ADTYPE__VCH64(CAST(COL_I4 AS INTEGER[0:2])) FROM TEST_ARR1;
IBP_UDF__ADTYPE__VCH64
======================
3
SQL> SELECT IBP_UDF__ADTYPE__CH64(CAST(COL_I4 AS INTEGER[0:2])) FROM TEST_ARR1;
IBP_UDF__ADTYPE__CH64
=====================
1
2) TEST OF CAST(? AS array datatype)
SQL> set sqlda_display on;
SQL> SELECT CAST(? AS INTEGER[0:2]) FROM RDB$DATABASE;
INPUT message field count: 1
01: sqltype: 540 ARRAY Nullable scale: 0 subtype: 0 len: 8
: name: alias:
: table: owner:
OUTPUT message field count: 1
01: sqltype: 540 ARRAY Nullable scale: 0 subtype: 0 len: 8
: name: CAST alias: CAST
: table: owner:
CAST
=================
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
CREATE TABLE TEST_ARR1 (COL_I4 INTEGER[0:2]);-- insert array [1,2,3] (through API)
INSERT INTO TEST_ARR1 (COL_I4) VALUES (?);--- result is OK.
SELECT COL_I4 FROM TEST_ARR1;-- Bad column type: VARCHAR instead ARRAY
-- FETCH returns incorrect data
SELECT CAST(COL_I4 AS VARCHAR(128)[0:2]) FROM TEST_ARR1;-- Bad column type: INTEGER instead ARRAY
-- FETCH returns conversion error from string "#x01#x01#x01#x00#x04# ....."
SELECT CAST(COL_I4 AS INTEGER[0:2]) FROM TEST_ARR1;-- Wrong parameter type: INTEGER
SELECT CAST(? AS INTEGER[0:2]) FROM TEST_ARR1;The text was updated successfully, but these errors were encountered: