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 }

proddata · Accepted Answer · 2021-09-13T15:52:45.167

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       |
+-------------+--------------+

I already tried that and 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 } ``` — hgsongra, Aug 04 '21 at 13:43
Also could you please provide the reference for to_object_array() is an internal / undocumented CrateDB function? — hgsongra, Aug 04 '21 at 13:50
Are you trying to run multiple insert statemets? Could be the same error as https://github.com/crate/crate/issues/11386 -> This was fixed with v4.5.2. A workaround is to cast the object column to `TEXT` or `ARRAY(TEXT)` — proddata, Aug 04 '21 at 14:07
> Also could you please provide the reference for to_object_array() is an internal / undocumented CrateDB function? That is just my conclusion from looking at our docs and internal discussion :) I think this functions where implemented for better driver/client compatibility, but were not necessarily meant to be user accessible. — proddata, Aug 04 '21 at 14:16
With typecast with TEXT and ARRAY(TEXT) it works fine for existing columns in a table. but it's not working for dynamic columns which are available in the source table with data types `OBJECT` and `ARRAY(OBJECT)` and not into the destination table. In that case, the dynamic columns with `OBJECT` and `ARRAY(OBJECT)` are getting converted `TEXT` and `ARRAY(TEXT)` — hgsongra, Aug 04 '21 at 14:30
Are you trying to transfer the complete table? Could you use the schema from the original table and transfer it to the new one? (e.g. with `SHOW CREATE TABLE old_table`) ? — proddata, Aug 04 '21 at 16:05
Yes, Using the existing table schema to create a temp table will work as expected but it is a different usecase. Can't use the same schema. — hgsongra, Aug 04 '21 at 16:40
Since this moved away from the original problem and is more use case related, I would suggest to move it to https://community.crate.io/ where we would gladly help you with your specific use-case — proddata, Aug 04 '21 at 17:07
please add this to your answer as well so anyone can have one place for a specific scenario. So I'm having some of the columns dynamically added with data types object and object array which need special casting as follows. Like if the column is dynamically added with data type object array, it needs to be cast using `CAST("col_name" AS ARRAY(OBJECT))` and similarly, if a column is having object type it needs to first cast to text first and then to object type `CAST(CAST("col_name" AS TEXT) AS OBJECT)` — hgsongra, Sep 13 '21 at 11:57
You only need to explicitly cast to `OBJECT` if the column doesn't exist yet, otherwise CrateDB would handle the cast implicitly. This all should be resolved with newer CrateDB versions. — proddata, Sep 13 '21 at 15:46
with crate 4.2.7, I've to explicitly cast it, otherwise, it was throwing an error mentioned in UPDATE 1. — hgsongra, Sep 14 '21 at 05:14

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

1 Answers1