cloud-fan commented on code in PR #44093:
URL: https://github.com/apache/spark/pull/44093#discussion_r1425043738
##########
sql/core/src/test/resources/sql-tests/results/execute-immediate.sql.out:
##########
@@ -0,0 +1,655 @@
+-- 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)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE x (id INT) USING csv
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE sql_string STRING
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = \'name1\''
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SET spark.sql.ansi.enabled=true'
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
+-- !query
+EXECUTE IMMEDIATE 'CREATE TEMPORARY VIEW IDENTIFIER(:tblName) AS SELECT id,
name FROM tbl_view' USING 'tbl_view_tmp' as tblName
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view_tmp'
+-- !query schema
+struct<id:int,name:string>
+-- !query output
+10 name1
+20 name2
+30 name3
+40 name4
+50 name5
+60 name6
+70 name7
+
+
+-- !query
+EXECUTE IMMEDIATE 'DESCRIBE IDENTIFIER(:tblName)' USING 'tbl_view_tmp' as
tblName
+-- !query schema
+struct<col_name:string,data_type:string,comment:string>
+-- !query output
+id int
+name string
+
+
+-- !query
+EXECUTE IMMEDIATE 'DESCRIBE IDENTIFIER(:tblName)' USING 'x' as tblName
+-- !query schema
+struct<col_name:string,data_type:string,comment:string>
+-- !query output
+id int
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = \'name1\''
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = ? or name = ?'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE a STRING
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR a = 'name1'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING 'name1', 'name3'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+30 name3 {"f1":3,"s2":{"f2":303,"f3":"c"}}
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING a, 'name2'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
'name1', 'name3'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+30 name3 {"f1":3,"s2":{"f2":303,"f3":"c"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING a,
'name2'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
(a, 'name2')
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES(?)' USING 1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT * from x
+-- !query schema
+struct<id:int>
+-- !query output
+1
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE b INT
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR b = 40
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING 40 as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING b as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING 40 as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING 'name7' as first, b as second
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT tbl_view.*, :first as p FROM tbl_view WHERE name =
:first' USING 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>,p:string>
+-- !query output
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}} name7
+
+
+-- !query
+EXECUTE IMMEDIATE 'SET VAR sql_string = ?' USING 'SELECT id from tbl_view
where name = :first'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT sql_string
+-- !query schema
+struct<sql_string:string>
+-- !query output
+SELECT id from tbl_view where name = :first
+
+
+-- !query
+DECLARE res_id INT
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string INTO res_id USING 'name7' as first
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+70
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string INTO res_id USING a as first
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+10
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT 42' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+42
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT id, name FROM tbl_view WHERE id = ?' INTO b, a USING
10
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT b, a
+-- !query schema
+struct<b:int,a:string>
+-- !query output
+10 name1
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view where id = ? AND name = ?' USING b
as first, a
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT 42 WHERE 2 = 1' INTO res_id
Review Comment:
does the target variable have to be pre-defined?
##########
sql/core/src/test/resources/sql-tests/results/execute-immediate.sql.out:
##########
@@ -0,0 +1,655 @@
+-- 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)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE x (id INT) USING csv
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE sql_string STRING
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = \'name1\''
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SET spark.sql.ansi.enabled=true'
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
+-- !query
+EXECUTE IMMEDIATE 'CREATE TEMPORARY VIEW IDENTIFIER(:tblName) AS SELECT id,
name FROM tbl_view' USING 'tbl_view_tmp' as tblName
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view_tmp'
+-- !query schema
+struct<id:int,name:string>
+-- !query output
+10 name1
+20 name2
+30 name3
+40 name4
+50 name5
+60 name6
+70 name7
+
+
+-- !query
+EXECUTE IMMEDIATE 'DESCRIBE IDENTIFIER(:tblName)' USING 'tbl_view_tmp' as
tblName
+-- !query schema
+struct<col_name:string,data_type:string,comment:string>
+-- !query output
+id int
+name string
+
+
+-- !query
+EXECUTE IMMEDIATE 'DESCRIBE IDENTIFIER(:tblName)' USING 'x' as tblName
+-- !query schema
+struct<col_name:string,data_type:string,comment:string>
+-- !query output
+id int
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = \'name1\''
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = ? or name = ?'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE a STRING
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR a = 'name1'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING 'name1', 'name3'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+30 name3 {"f1":3,"s2":{"f2":303,"f3":"c"}}
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING a, 'name2'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
'name1', 'name3'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+30 name3 {"f1":3,"s2":{"f2":303,"f3":"c"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING a,
'name2'
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = ? or name = ?' USING
(a, 'name2')
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+20 name2 {"f1":2,"s2":{"f2":202,"f3":"b"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES(?)' USING 1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT * from x
+-- !query schema
+struct<id:int>
+-- !query output
+1
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE b INT
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR b = 40
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING 40 as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string USING b as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING 40 as second, 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first or id = :second'
USING 'name7' as first, b as second
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+40 name4 {"f1":4,"s2":{"f2":404,"f3":"d"}}
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT tbl_view.*, :first as p FROM tbl_view WHERE name =
:first' USING 'name7' as first
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>,p:string>
+-- !query output
+70 name7 {"f1":7,"s2":{"f2":707,"f3":"g"}} name7
+
+
+-- !query
+EXECUTE IMMEDIATE 'SET VAR sql_string = ?' USING 'SELECT id from tbl_view
where name = :first'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT sql_string
+-- !query schema
+struct<sql_string:string>
+-- !query output
+SELECT id from tbl_view where name = :first
+
+
+-- !query
+DECLARE res_id INT
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string INTO res_id USING 'name7' as first
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+70
+
+
+-- !query
+EXECUTE IMMEDIATE sql_string INTO res_id USING a as first
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+10
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT 42' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+42
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT id, name FROM tbl_view WHERE id = ?' INTO b, a USING
10
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT b, a
+-- !query schema
+struct<b:int,a:string>
+-- !query output
+10 name1
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view where id = ? AND name = ?' USING b
as first, a
+-- !query schema
+struct<id:int,name:string,data:struct<f1:int,s2:struct<f2:int,f3:string>>>
+-- !query output
+10 name1 {"f1":1,"s2":{"f2":101,"f3":"a"}}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT 42 WHERE 2 = 1' INTO res_id
Review Comment:
does the target variable have to be pre-defined?
--
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]
