srielau commented on code in PR #44093:
URL: https://github.com/apache/spark/pull/44093#discussion_r1420801268
##########
sql/core/src/test/resources/sql-tests/inputs/execute-immediate.sql:
##########
@@ -0,0 +1,96 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMPORARY VIEW tbl_view AS SELECT * FROM VALUES
+ (10, "name1", named_struct("f1", 1, "s2", named_struct("f2", 101, "f3",
"a"))),
+ (20, "name2", named_struct("f1", 2, "s2", named_struct("f2", 202, "f3",
"b"))),
+ (30, "name3", named_struct("f1", 3, "s2", named_struct("f2", 303, "f3",
"c"))),
+ (40, "name4", named_struct("f1", 4, "s2", named_struct("f2", 404, "f3",
"d"))),
+ (50, "name5", named_struct("f1", 5, "s2", named_struct("f2", 505, "f3",
"e"))),
+ (60, "name6", named_struct("f1", 6, "s2", named_struct("f2", 606, "f3",
"f"))),
+ (70, "name7", named_struct("f1", 7, "s2", named_struct("f2", 707, "f3",
"g")))
+AS tbl_view(id, name, data);
+CREATE TABLE x (id INT) USING csv;
+
+DECLARE sql_string STRING;
+SET VAR sql_string = 'SELECT * from tbl_view where name = "name1"';
Review Comment:
I understand the temptation, but better stay clear of double quotes. We have
a config to switch them to identifiers, An dI have not given up the dream to
change the default....)
##########
common/utils/src/main/resources/error/error-classes.json:
##########
@@ -2261,6 +2273,12 @@
},
"sqlState" : "42000"
},
+ "INVALID_STATEMENT_FOR_EXECUTE_INTO" : {
+ "message" : [
+ "The INTO clause of EXECUTE IMMEDIATE is only valid for queries but the
given statement isn't: <sqlString>."
+ ],
+ "sqlState" : "42606"
Review Comment:
"An invalid hexadecimal constant has been detected"?
How about:
"The option specified on PREPARE or EXECUTE is not valid."
(07 is all about dynamic SQL)
```suggestion
"sqlState" : "07501"
```
##########
docs/sql-ref-syntax-aux-exec-imm.md:
##########
@@ -0,0 +1,86 @@
+---
+layout: global
+title: EXECUTE IMMEDIATE
+displayTitle: EXECUTE IMMEDIATE
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+Executes a sql statement provided as a `STRING`, optionally passing
`arg_exprN` to parameter markers and assigning the results to `var_nameN`.
+
+### Syntax
+
+```sql
+EXECUTE IMMEDIATE sql_string
+ [ INTO (var_name [, …] ) ]
+ [ USING ( arg_expr [ AS ] [alias] [, …] ) ]
+```
+
+### Parameters
+
+* **sql_string**
+
+ A STRING expression producing a well-formed SQL statement.
+
+* **INTO ( var_name [, …] )**
+
+ Optionally returns the results of a single row query into SQL variables.
+ If the query returns no rows the result is NULL.
+ - `var_name`
+ A SQL variable. A variable may not be referenced more than once.
+
+* **USING (arg_expr [, …] )**
+
+ Optionally, if sql_string contains parameter markers, binds in values to the
parameters.
+ - `arg_expr`
+ An expression that binds to a parameter marker.
+ If the parameter markers are unnamed the binding is by position.
+ For unnamed parameter markers, binding is by name.
+ - `alias`
+ Overrides the name used to bind `arg_expr` to a named parameter marker
+
+ Each named parameter marker must be matched once. Not all arg_expr must be
matched.
+
+
+### Examples
+
+```sql
+-- A self-contained execution using a literal string
+EXECUTE IMMEDIATE ‘SELECT SUM(c1) FROM VALUES(?), (?)’ USING (5, 6);
Review Comment:
And the example need to be fixed. Best lead with USING without parens, since
that's closer to the standard.
Also, you need to fix the single quotes (copy post-problem from docs). '
```suggestion
EXECUTE IMMEDIATE 'SELECT SUM(c1) FROM VALUES(?), (?)' USING 5, 6;
```
##########
sql/core/src/test/resources/sql-tests/inputs/execute-immediate.sql:
##########
@@ -0,0 +1,96 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMPORARY VIEW tbl_view AS SELECT * FROM VALUES
+ (10, "name1", named_struct("f1", 1, "s2", named_struct("f2", 101, "f3",
"a"))),
+ (20, "name2", named_struct("f1", 2, "s2", named_struct("f2", 202, "f3",
"b"))),
+ (30, "name3", named_struct("f1", 3, "s2", named_struct("f2", 303, "f3",
"c"))),
+ (40, "name4", named_struct("f1", 4, "s2", named_struct("f2", 404, "f3",
"d"))),
+ (50, "name5", named_struct("f1", 5, "s2", named_struct("f2", 505, "f3",
"e"))),
+ (60, "name6", named_struct("f1", 6, "s2", named_struct("f2", 606, "f3",
"f"))),
+ (70, "name7", named_struct("f1", 7, "s2", named_struct("f2", 707, "f3",
"g")))
+AS tbl_view(id, name, data);
+CREATE TABLE x (id INT) USING csv;
+
+DECLARE sql_string STRING;
+SET VAR sql_string = 'SELECT * from tbl_view where name = "name1"';
+
+-- test execute immediate without parameters
+EXECUTE IMMEDIATE sql_string;
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = "name1"';
+
+-- test positional paramete
+SET VAR sql_string = 'SELECT * from tbl_view where name = ? or name = ?';
+DECLARE a STRING;
+SET VAR a = "name1";
+EXECUTE IMMEDIATE sql_string USING "name1", "name3";
+EXECUTE IMMEDIATE sql_string USING a, "name2";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
"name1", "name3";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING a,
"name2";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
(a, "name2");
+-- test positonal command
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES(?)' USING 1;
+SELECT * from x;
+
+-- test named parameters paramete
Review Comment:
```suggestion
-- test named parameters
```
##########
docs/sql-ref-syntax-aux-exec-imm.md:
##########
@@ -0,0 +1,86 @@
+---
+layout: global
+title: EXECUTE IMMEDIATE
+displayTitle: EXECUTE IMMEDIATE
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+Executes a sql statement provided as a `STRING`, optionally passing
`arg_exprN` to parameter markers and assigning the results to `var_nameN`.
+
+### Syntax
+
+```sql
+EXECUTE IMMEDIATE sql_string
+ [ INTO (var_name [, …] ) ]
Review Comment:
Since we changed the grammar we need to fix the docs here
##########
sql/core/src/test/resources/sql-tests/inputs/execute-immediate.sql:
##########
@@ -0,0 +1,96 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMPORARY VIEW tbl_view AS SELECT * FROM VALUES
+ (10, "name1", named_struct("f1", 1, "s2", named_struct("f2", 101, "f3",
"a"))),
+ (20, "name2", named_struct("f1", 2, "s2", named_struct("f2", 202, "f3",
"b"))),
+ (30, "name3", named_struct("f1", 3, "s2", named_struct("f2", 303, "f3",
"c"))),
+ (40, "name4", named_struct("f1", 4, "s2", named_struct("f2", 404, "f3",
"d"))),
+ (50, "name5", named_struct("f1", 5, "s2", named_struct("f2", 505, "f3",
"e"))),
+ (60, "name6", named_struct("f1", 6, "s2", named_struct("f2", 606, "f3",
"f"))),
+ (70, "name7", named_struct("f1", 7, "s2", named_struct("f2", 707, "f3",
"g")))
+AS tbl_view(id, name, data);
+CREATE TABLE x (id INT) USING csv;
+
+DECLARE sql_string STRING;
+SET VAR sql_string = 'SELECT * from tbl_view where name = "name1"';
+
+-- test execute immediate without parameters
+EXECUTE IMMEDIATE sql_string;
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = "name1"';
+
+-- test positional paramete
+SET VAR sql_string = 'SELECT * from tbl_view where name = ? or name = ?';
+DECLARE a STRING;
+SET VAR a = "name1";
+EXECUTE IMMEDIATE sql_string USING "name1", "name3";
+EXECUTE IMMEDIATE sql_string USING a, "name2";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
"name1", "name3";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING a,
"name2";
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
(a, "name2");
+-- test positonal command
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES(?)' USING 1;
+SELECT * from x;
+
+-- test named parameters paramete
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second';
+DECLARE b INT;
+SET VAR b = 40;
+EXECUTE IMMEDIATE sql_string USING 40 as second, "name7" as first;
+EXECUTE IMMEDIATE sql_string USING b as second, "name7" as first;
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING 40 as second, "name7" as first;
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING "name7" as first, b as second;
+-- named parameter used multiple times
+EXECUTE IMMEDIATE 'SELECT tbl_view.*, :first as p FROM tbl_view WHERE name =
:first' USING "name7" as first;
+
+-- test named command and setup for next test
+EXECUTE IMMEDIATE 'SET VAR sql_string = ?' USING 'SELECT id from tbl_view
where name = :first';
+SELECT sql_string;
+
+-- test into
+DECLARE res_id INT;
+EXECUTE IMMEDIATE sql_string INTO res_id USING "name7" as first;
+SELECT res_id;
+EXECUTE IMMEDIATE sql_string INTO res_id USING a as first;
+SELECT res_id;
+
+-- test into without using
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second';
+EXECUTE IMMEDIATE 'SELECT 42' INTO res_id;
+SELECT res_id;
+
+-- multiple INTOs
+EXECUTE IMMEDIATE 'SELECT id, name FROM tbl_view WHERE id = ?' INTO b, a USING
10;
+SELECT b, a;
+
+-- use AS for using positional params
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view where id = ? AND name = ?' USING b
as first, a;
+
+-- test errors
Review Comment:
I don't see tests for:
* NULL on empty query
* error on too many rows
* error on mismatch between INTO and SELECT list cardinality
* Testing on implcicit casting between SELECT list and INTO, as well as
errors if not possible.
* Duplicate alias in USING.
* Duplicate INTO entry
* We should have a wider variety of statement types. E.g. CREATE, DESCRIBE,
SET (config)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
