SQLActionException[UnsupportedFeatureException: Unknown func

Question

在将Crate版本4.1.8升级到4.2.7后，我在CrateDB中面临以下错误

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 INTO与subsql查询语句在CrateDB从现有表具有列的数据类型OBJECT(DYNAMIC)和ARRAY(OBJECT(DYNAMIC))和创建temp表与现有表的原始模式。

由于原表在表级有一个column_policy = 'dynamic'，所以动态添加了几个具有相同数据类型的列OBJECT(DYNAMIC)和ARRAY(OBJECT(DYNAMIC))。

下面是完整的SQL查询，我使用它来移动在Crate版本4.1.8上工作良好的数据，并在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 }

Answer 1

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