milastdbx commented on code in PR #44093:
URL: https://github.com/apache/spark/pull/44093#discussion_r1422679449
##########
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:
Updates tests for everything suggested except
`We should have a wider variety of statement types. E.g. CREATE, DESCRIBE,
SET (config)`
These statements are defined in SparkSqlParser which is defined in sql/core
project.
sql/core depends on catalyst and I cannot add reference to it.
I will try to move SparkSqlParser from core to catalyst and fix this, but
will send seperate PR for that
--
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]
