1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
# name: test/sql/json/test_json_serialize_sql.test
# group: [json]
require json
# Example with simple query
statement ok
SELECT json_serialize_sql('SELECT 1 + 2 FROM tbl1');
# Example with skip_null and skip_empty
statement ok
SELECT json_serialize_sql('SELECT *, 1 + 2 FROM tbl1', skip_null := true, skip_empty := true);
# Example with subquery
statement ok
SELECT json_serialize_sql('SELECT * FROM (SELECT 1 + 2)', skip_null := true, skip_empty := true);
# Example with all parameters
statement ok
SELECT json_serialize_sql('SELECT * FROM (SELECT 1 + 2)', skip_default := true, skip_empty := true, skip_null := true, format := true);
# Example with syntax error
query I
SELECT json_serialize_sql('SELECT AND LAUNCH ROCKETS WHERE 1 = 1');
----
<REGEX>:.*syntax error at or near.*
# Test Deserialize
query I
SELECT json_deserialize_sql(json_serialize_sql('SELECT 1 + 2 FROM tbl1'));
----
SELECT (1 + 2) FROM tbl1
query I
SELECT json_deserialize_sql(json_serialize_sql('SELECT *, 1 + 2 FROM tbl1'));
----
SELECT *, (1 + 2) FROM tbl1
query I
SELECT json_deserialize_sql(json_serialize_sql('SELECT * FROM (SELECT 1 + 2)'));
----
SELECT * FROM (SELECT (1 + 2))
query I
SELECT json_deserialize_sql(json_serialize_sql('SELECT * FROM t1 JOIN t2 USING("$id")'));
----
SELECT * FROM t1 INNER JOIN t2 USING ("$id")
statement error
SELECT json_deserialize_sql(json_serialize_sql('SELECT AND LAUNCH ROCKETS WHERE 1 = 1'));
----
Parser Error: Error parsing json: parser: syntax error at or near "AND"
# Test Execute JSON Serialized SQL
query I
SELECT * FROM json_execute_serialized_sql(json_serialize_sql('SELECT 1 + 2'));
----
3
statement ok
CREATE TABLE tbl2 (a INT, b INT, c INT);
statement ok
INSERT INTO tbl2 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
query III
SELECT * FROM json_execute_serialized_sql(json_serialize_sql('SELECT * FROM tbl2'));
----
1 2 3
4 5 6
7 8 9
query I
SELECT * FROM json_execute_serialized_sql(json_serialize_sql('SELECT a + b + c FROM tbl2'));
----
6
15
24
query I
PRAGMA json_execute_serialized_sql(
'{"error":false,"statements":[{"node":{"type":"SELECT_NODE","modifiers":[],"cte_map":{"map":[]},"select_list":[{"class":"FUNCTION","type":"FUNCTION","alias":"","function_name":"+","schema":"","children":[{"class":"FUNCTION","type":"FUNCTION","alias":"","function_name":"+","schema":"","children":[{"class":"COLUMN_REF","type":"COLUMN_REF","alias":"","column_names":["a"]},{"class":"COLUMN_REF","type":"COLUMN_REF","alias":"","column_names":["b"]}],"filter":null,"order_bys":{"type":"ORDER_MODIFIER","orders":[]},"distinct":false,"is_operator":true,"export_state":false,"catalog":""},{"class":"COLUMN_REF","type":"COLUMN_REF","alias":"","column_names":["c"]}],"filter":null,"order_bys":{"type":"ORDER_MODIFIER","orders":[]},"distinct":false,"is_operator":true,"export_state":false,"catalog":""}],"from_table":{"type":"BASE_TABLE","alias":"","sample":null,"schema_name":"","table_name":"tbl2","column_name_alias":[],"catalog_name":""},"where_clause":null,"group_expressions":[],"group_sets":[],"aggregate_handling":"STANDARD_HANDLING","having":null,"sample":null,"qualify":null}}]}'
)
----
6
15
24
# Test execute json serialized sql with multiple nested type tags
query I
select json_serialize_sql($$select '10'::blob$$);
----
<!REGEX>:.*query_location:.*11
# TODO: We should add an option for the deserializer to allow missing properties in the JSON if they can be default constructed
# Alternatively, make them optional for all the Deserializer's.
statement error
SELECT * FROM json_execute_serialized_sql(json_serialize_sql('SELECT * FROM tbl2', skip_null := true, skip_empty := true));
----
Parser Error: Expected but did not find property 'cte_map' in json object
# Test execute json serialized sql with multiple nested type tags
query II
SELECT * FROM json_execute_serialized_sql(json_serialize_sql('WITH a(i) as (SELECT 1) SELECT a1.i as i1, a2.i as i2 FROM a as a1, a as a2'));
----
1 1
# Missing select nodes should throw an error
statement error
SELECT json_deserialize_sql('{ "statements": [ {"expression_class": "BOUND_COMPARISON"}]}');
----
Parser Error: Error parsing json: no select node found in json
statement error
SELECT * FROM json_execute_serialized_sql('{ "statements": [ {"expression_class": "BOUND_COMPARISON"}]}');
----
Parser Error: Error parsing json: no select node found in json
# Test execute json serialized sql with multiple select nodes
query I
SELECT json_deserialize_sql(json_serialize_sql('SELECT 1;SELECT 2'));
----
SELECT 1; SELECT 2
|
