SQLActionException[UnsupportedFeatureException: Unknown function: to_object_array and to_object CrateDB 4.2.7 and upwards

Question

I'm facing below error in CrateDB after upgrading Crate version 4.1.8 to 4.2.7

error during main processing: SQLActionException[UnsupportedFeatureException: Unknown function: to_object_array(db_name.tbl_name."object_array_type_col_name")]
  error : {
  "message": "SQLActionException[UnsupportedFeatureException: Unknown function: to_object_array(db_name.tbl_name."object_array_type_col_name")]",
  "code": 4004
}

I'm trying to move data from one table to another using INSERT INTO with subsql query statement in CrateDB from existing table having column with data types OBJECT(DYNAMIC) and ARRAY(OBJECT(DYNAMIC)) and creating temp table with original schema of existing table.

As there is a column_policy = 'dynamic' at table level in original table, there are couple of columns added dynamically with same data types OBJECT(DYNAMIC) and ARRAY(OBJECT(DYNAMIC)).

Below is the full SQL query which I'm using to move the data which is working fine on Crate version 4.1.8 and raise above exception on version 4.2.7.

INSERT INTO temp_tbl (col1, col2_object, col3_object_array, col4, col5, dynamic_col6_object, dynamic_col6_object_array) (SELECT col1, to_object(col2_object), to_object_array(col3_object_array), col4, col5, to_object(dynamic_col6_object), to_object_array(dynamic_col6_object_array) FROM original_tbl);

UPDATE 1:

As mentioned/pointed by @proddata, I did try CAST but facing the below error

error: { "message": "SQLActionException[SQLParseException: The type 'object' of the insert source 'object_col_name' is not convertible to the type 'object' of target column 'object_col_name']", "code": 4000 }

Answer 1

to_object_array() is an internal / undocumented CrateDB function, which is hidden from 4.2 and upwards

Could you try to use <column> :: <type> or cast(<column> AS <type>) instead.

e.g.

SELECT
[] :: ARRAY(OBJECT(DYNAMIC)),
cast([] AS ARRAY(OBJECT(DYNAMIC)))

also see https://crate.io/docs/crate/reference/en/4.6/general/ddl/data-types.html#cast

---

INSERT INTO temp_tbl (col1, col2_object, col3_object_array, col4, col5, dynamic_col6_object, dynamic_col6_object_array)
(SELECT
 col1,
 col2_object :: OBJECT,
 col3_object_array :: ARRAY(OBJECT),
 col4,
 col5,
 dynamic_col6_object :: OBJECT(DYNAMIC),
 dynamic_col6_object_array :: ARRAY(OBJECT(DYNAMIC))
FROM original_tbl);

---

Edit: With some CrateDB version (probably ranging between 4.2.x - 4.5.1) there was a bug that prevented the INSERT of objects from another table, if the object column in the target column has different object properties, that aren't a superset of the source object column. e.g.:

More complete example ...

cr> CREATE TABLE dynamic_objects ( 
        col1 TEXT 
    ) WITH (column_policy = 'dynamic');                                                                                               
-- CREATE OK, 1 row affected  (1.393 sec)

cr> INSERT INTO dynamic_objects (col1, obj_dyn, obj_arr_dyn) VALUES 
        ('Hello', {a = 1}, [{x = 1},{y = 1}]);                                                                                        
-- INSERT OK, 1 row affected  (0.216 sec)

cr> CREATE TABLE dynamic_objects_copy (  
            col1 TEXT  
        ) WITH (column_policy = 'dynamic');                                                                                           
-- CREATE OK, 1 row affected  (1.342 sec)

cr> INSERT INTO dynamic_objects_copy (col1, obj_dyn, obj_arr_dyn) VALUES 
        ('Hello', {b = 1}, [{u = 1},{v = 1}]);                                                                                        
-- INSERT OK, 1 row affected  (0.140 sec)

---

With version 4.2.7 the following query fails:

INSERT INTO dynamic_objects_copy (col1, obj_dyn, obj_arr_dyn) 
    SELECT col1, obj_dyn, obj_arr_dyn FROM dynamic_objects;

Tested with 4.2.7 (workaround for bug crate#11386

INSERT INTO dynamic_objects_copy (col1, obj_dyn, obj_arr_dyn) 
    SELECT col1, obj_dyn::TEXT::OBJECT, obj_arr_dyn::ARRAY(TEXT)::ARRAY(OBJECT) FROM dynamic_objects;

if columns already exist:

INSERT INTO dynamic_objects_copy (col1, obj_dyn, obj_arr_dyn) 
    SELECT col1, obj_dyn::TEXT, obj_arr_dyn::ARRAY(TEXT) FROM dynamic_objects;

---

Tested with 4.6.3 (works)

INSERT INTO dynamic_objects_copy (col1, obj_dyn, obj_arr_dyn) 
    SELECT col1, obj_dyn, obj_arr_dyn FROM dynamic_objects;

 SELECT column_name, data_type  FROM information_schema.columns  
    WHERE table_name = 'dynamic_objects_copy' AND column_name NOT LIKE '%[%';                                                                                                                                            
+-------------+--------------+
| column_name | data_type    |
+-------------+--------------+
| obj_arr_dyn | object_array |
| col1        | text         |
| obj_dyn     | object       |
+-------------+--------------+