时间戳

首页 > 解决方案 > SQLActionException[UnsupportedFeatureException：未知函数：to_object_array 和 to_object CrateDB 4.2.7 及更高版本

sql - SQLActionException[UnsupportedFeatureException：未知函数：to_object_array 和 to_object CrateDB 4.2.7 及更高版本

问题描述

4.1.8将 Crate 版本升级到后，我在 CrateDB 中遇到以下错误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
}

我正在尝试使用INSERT INTOCrateDB 中的 subsql 查询语句从具有数据类型列的现有表中将数据从一个表移动到另一个表，OBJECT(DYNAMIC)并使用现有ARRAY(OBJECT(DYNAMIC))表的原始模式创建临时表。

由于column_policy = 'dynamic'原始表中有一个表级别，因此有几列动态添加了相同的数据类型OBJECT(DYNAMIC)和ARRAY(OBJECT(DYNAMIC)).

下面是我用来移动在 Crate 版本上运行良好的数据并在版本4.1.8上引发上述异常的完整 SQL 查询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);

更新 1：

正如@proddata 提到/指出的那样，我确实尝试过 CAST 但面临以下错误

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 }

标签： sqlcratecratedb

解决方案

to_object_array()是一个内部/未记录的 CrateDB 函数，从 4.2 及更高版本隐藏

你可以尝试使用<column> :: <type>或cast(<column> AS <type>)代替。

例如

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

另请参阅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);

编辑：对于某些 CrateDB 版本（可能介于 4.2.x - 4.5.1 之间）INSERT，如果目标列中的对象列具有不同的对象属性（不是超集），则存在阻止来自另一个表的对象的错误源对象列。例如：

更完整的例子...

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)

对于 4.2.7 版，以下查询失败：

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

用 4.2.7 测试（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;

如果列已经存在：

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

用 4.6.3 测试（工作）

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

推荐阅读