srielau commented on code in PR #44093:
URL: https://github.com/apache/spark/pull/44093#discussion_r1425569964
##########
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
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+NULL
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'1707\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+1707
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'invalid_cast_error_expected\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'invalid_cast_error_expected'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 70,
+ "fragment" : "EXECUTE IMMEDIATE 'SELECT \\'invalid_cast_error_expected\\''
INTO res_id"
+ } ]
Review Comment:
Can we return a better error here?
##########
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
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+NULL
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'1707\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+1707
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'invalid_cast_error_expected\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'invalid_cast_error_expected'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 70,
+ "fragment" : "EXECUTE IMMEDIATE 'SELECT \\'invalid_cast_error_expected\\''
INTO res_id"
+ } ]
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES (?)' INTO res_id USING 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INVALID_STATEMENT_FOR_EXECUTE_INTO",
+ "sqlState" : "07501",
+ "messageParameters" : {
+ "sqlString" : "INSERT INTO x VALUES (?)"
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view WHERE ? = id' USING id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNRESOLVED_VARIABLE",
+ "sqlState" : "42883",
+ "messageParameters" : {
+ "searchPath" : "`SYSTEM`.`SESSION`",
+ "variableName" : "`id`"
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view where ? = id and :first = name'
USING 1, 'name2' as first
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INVALID_QUERY_BOTH_POSITIONAL_AND_NAMED_PARAMETERS_PRESENT",
+ "sqlState" : "42613"
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELCT F(
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'''",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+a'
+
+EXECUTE IMMEDIATE b
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'a'",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR a = 'na'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first' USING CONCAT(a
, "me1") as first
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'('",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT id, name FROM tbl_view WHERE id = ?' INTO a, b USING
10
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'name1'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 81,
Review Comment:
We need to fix the context here. IIUC the '?' is typed as INT and thus id =
? needs to cast id as an INT and that is failing.
A random user does not stand a chance here....
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/executeImmediate.scala:
##########
@@ -0,0 +1,186 @@
+/*
+ * 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.
+ */
+
+package org.apache.spark.sql.catalyst.analysis
+
+import scala.util.{Either, Left, Right}
+
+import org.apache.spark.SparkException
+import org.apache.spark.sql.AnalysisException
+import org.apache.spark.sql.catalyst.expressions.{Alias, Expression,
NamedExpression, VariableReference}
+import org.apache.spark.sql.catalyst.parser.{ParseException, ParserInterface}
+import org.apache.spark.sql.catalyst.plans.logical.{LogicalPlan, SetVariable}
+import org.apache.spark.sql.catalyst.rules.Rule
+import org.apache.spark.sql.catalyst.trees.TreePattern.{EXECUTE_IMMEDIATE,
TreePattern}
+import org.apache.spark.sql.connector.catalog.CatalogManager
+import
org.apache.spark.sql.errors.QueryCompilationErrors.unresolvedVariableError
+import org.apache.spark.sql.types.StringType
+
+/**
+ * Logical plan representing execute immediate query.
+ *
+ * @param args
+ * parameters of query
+ * @param query
+ * query string or variable
+ * @param targetVariables
+ * variables to store the result of the query
+ */
+case class ExecuteImmediateQuery(
+ args: Seq[Expression],
+ query: Either[String, UnresolvedAttribute],
+ targetVariables: Option[Seq[UnresolvedAttribute]],
+ parser: ParserInterface)
+ extends UnresolvedLeafNode {
+ final override val nodePatterns: Seq[TreePattern] = Seq(EXECUTE_IMMEDIATE)
+}
+
+/**
+ * This rule substitutes execute immediate query node with plan that is passed
as string literal
+ * or session parameter.
+ */
+class SubstituteExecuteImmediate(val catalogManager: CatalogManager)
+ extends Rule[LogicalPlan]
+ with ColumnResolutionHelper {
+
+ def resolveVariable(e: Expression): Expression = {
+
+ /**
+ * We know that the expression is either UnresolvedAttribute or Alias, as
passed from the
+ * parser. If it is an UnresolvedAttribute, we look it up in the catalog
and return it. If it
+ * is an Alias, we resolve the child and return an Alias with the same
name.
+ */
+ e match {
+ case u: UnresolvedAttribute =>
+ getVariableReference(u.nameParts)
+ case a: Alias =>
+ Alias(resolveVariable(a.child), a.name)()
+
+ case other =>
+ throw SparkException.internalError(
+ "Unexpected variable expression in ParametrizedQuery: " + other)
+ }
+ }
+
+ def resolveArguments(expressions: Seq[Expression]): Seq[Expression] = {
+ expressions.map { exp =>
+ if (exp.resolved) {
+ exp
+ } else {
+ resolveVariable(exp)
+ }
+ }
+ }
+
+ def extractQueryString(either: Either[String, UnresolvedAttribute]): String
= {
+ either match {
+ case Left(v) => v
+ case Right(u) =>
+ val varReference = getVariableReference(u.nameParts)
+
+ if (!varReference.dataType.sameType(StringType)) {
+ throw new AnalysisException(
+ errorClass = "INVALID_VARIABLE_TYPE_FOR_QUERY_EXECUTE_IMMEDIATE",
+ messageParameters = Map("varType" ->
varReference.dataType.simpleString))
+ }
+
+ // Call eval with null value passed instead of a row.
+ // This is ok as this is variable and invoking eval should
+ // be independent of row value.
+ varReference.eval(null).toString
+ }
+ }
+
+ override def apply(plan: LogicalPlan): LogicalPlan =
+ plan.resolveOperatorsWithPruning(_.containsPattern(EXECUTE_IMMEDIATE),
ruleId) {
+ case ExecuteImmediateQuery(expressions, query, targetVariablesOpt,
parser) =>
+ val queryString = extractQueryString(query)
+ val plan = parseStatement(parser, queryString, targetVariablesOpt)
+
+ val posNodes = plan.collect { case p: LogicalPlan =>
+ p.expressions.flatMap(_.collect { case n: PosParameter => n })
+ }.flatten
+ val namedNodes = plan.collect { case p: LogicalPlan =>
+ p.expressions.flatMap(_.collect { case n: NamedParameter => n })
+ }.flatten
+
+ val queryPlan = if (expressions.isEmpty || (posNodes.isEmpty &&
namedNodes.isEmpty)) {
+ plan
+ } else if (posNodes.nonEmpty && namedNodes.nonEmpty) {
+ throw new AnalysisException(
+ errorClass =
"INVALID_QUERY_BOTH_POSITIONAL_AND_NAMED_PARAMETERS_PRESENT",
+ messageParameters = Map.empty)
+ } else {
+ if (posNodes.nonEmpty) {
+ PosParameterizedQuery(
+ plan,
+ // We need to resolve arguments before Resolution batch to make
sure
+ // that some rule does not accidently resolve our parameters.
+ // We do not want this as they can resolve some unsupported
parameters
+ resolveArguments(expressions))
+ } else {
+ val namedExpressions = expressions.collect { case (e:
NamedExpression) => e }
Review Comment:
If the statement uses named parameters and some USING expression is unnamed,
then ignoring it would provide a poor experience for debugging.
Note that we don't allow "null keys" for spark.sql() either.
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/executeImmediate.scala:
##########
@@ -0,0 +1,186 @@
+/*
+ * 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.
+ */
+
+package org.apache.spark.sql.catalyst.analysis
+
+import scala.util.{Either, Left, Right}
+
+import org.apache.spark.SparkException
+import org.apache.spark.sql.AnalysisException
+import org.apache.spark.sql.catalyst.expressions.{Alias, Expression,
NamedExpression, VariableReference}
+import org.apache.spark.sql.catalyst.parser.{ParseException, ParserInterface}
+import org.apache.spark.sql.catalyst.plans.logical.{LogicalPlan, SetVariable}
+import org.apache.spark.sql.catalyst.rules.Rule
+import org.apache.spark.sql.catalyst.trees.TreePattern.{EXECUTE_IMMEDIATE,
TreePattern}
+import org.apache.spark.sql.connector.catalog.CatalogManager
+import
org.apache.spark.sql.errors.QueryCompilationErrors.unresolvedVariableError
+import org.apache.spark.sql.types.StringType
+
+/**
+ * Logical plan representing execute immediate query.
+ *
+ * @param args
+ * parameters of query
+ * @param query
+ * query string or variable
+ * @param targetVariables
+ * variables to store the result of the query
+ */
+case class ExecuteImmediateQuery(
+ args: Seq[Expression],
+ query: Either[String, UnresolvedAttribute],
+ targetVariables: Option[Seq[UnresolvedAttribute]],
+ parser: ParserInterface)
+ extends UnresolvedLeafNode {
+ final override val nodePatterns: Seq[TreePattern] = Seq(EXECUTE_IMMEDIATE)
+}
+
+/**
+ * This rule substitutes execute immediate query node with plan that is passed
as string literal
+ * or session parameter.
+ */
+class SubstituteExecuteImmediate(val catalogManager: CatalogManager)
+ extends Rule[LogicalPlan]
+ with ColumnResolutionHelper {
+
+ def resolveVariable(e: Expression): Expression = {
+
+ /**
+ * We know that the expression is either UnresolvedAttribute or Alias, as
passed from the
+ * parser. If it is an UnresolvedAttribute, we look it up in the catalog
and return it. If it
+ * is an Alias, we resolve the child and return an Alias with the same
name.
+ */
+ e match {
+ case u: UnresolvedAttribute =>
+ getVariableReference(u.nameParts)
+ case a: Alias =>
+ Alias(resolveVariable(a.child), a.name)()
+
+ case other =>
+ throw SparkException.internalError(
+ "Unexpected variable expression in ParametrizedQuery: " + other)
+ }
+ }
+
+ def resolveArguments(expressions: Seq[Expression]): Seq[Expression] = {
+ expressions.map { exp =>
+ if (exp.resolved) {
+ exp
+ } else {
+ resolveVariable(exp)
+ }
+ }
+ }
+
+ def extractQueryString(either: Either[String, UnresolvedAttribute]): String
= {
+ either match {
+ case Left(v) => v
+ case Right(u) =>
+ val varReference = getVariableReference(u.nameParts)
+
+ if (!varReference.dataType.sameType(StringType)) {
+ throw new AnalysisException(
+ errorClass = "INVALID_VARIABLE_TYPE_FOR_QUERY_EXECUTE_IMMEDIATE",
+ messageParameters = Map("varType" ->
varReference.dataType.simpleString))
+ }
+
+ // Call eval with null value passed instead of a row.
+ // This is ok as this is variable and invoking eval should
+ // be independent of row value.
+ varReference.eval(null).toString
+ }
+ }
+
+ override def apply(plan: LogicalPlan): LogicalPlan =
+ plan.resolveOperatorsWithPruning(_.containsPattern(EXECUTE_IMMEDIATE),
ruleId) {
+ case ExecuteImmediateQuery(expressions, query, targetVariablesOpt,
parser) =>
+ val queryString = extractQueryString(query)
+ val plan = parseStatement(parser, queryString, targetVariablesOpt)
+
+ val posNodes = plan.collect { case p: LogicalPlan =>
+ p.expressions.flatMap(_.collect { case n: PosParameter => n })
+ }.flatten
+ val namedNodes = plan.collect { case p: LogicalPlan =>
+ p.expressions.flatMap(_.collect { case n: NamedParameter => n })
+ }.flatten
+
+ val queryPlan = if (expressions.isEmpty || (posNodes.isEmpty &&
namedNodes.isEmpty)) {
+ plan
+ } else if (posNodes.nonEmpty && namedNodes.nonEmpty) {
+ throw new AnalysisException(
+ errorClass =
"INVALID_QUERY_BOTH_POSITIONAL_AND_NAMED_PARAMETERS_PRESENT",
+ messageParameters = Map.empty)
+ } else {
+ if (posNodes.nonEmpty) {
+ PosParameterizedQuery(
+ plan,
+ // We need to resolve arguments before Resolution batch to make
sure
+ // that some rule does not accidently resolve our parameters.
+ // We do not want this as they can resolve some unsupported
parameters
+ resolveArguments(expressions))
+ } else {
+ val namedExpressions = expressions.collect { case (e:
NamedExpression) => e }
+
+ NameParameterizedQuery(
+ plan,
+ namedExpressions.map(_.name),
+ // We need to resolve arguments before Resolution batch to make
sure
+ // that some rule does not accidently resolve our parameters.
+ // We do not want this as they can resolve some unsupported
parameters.
+ resolveArguments(namedExpressions))
+ }
+ }
+
+ targetVariablesOpt
+ .map(variables => {
+ SetVariable(variables, queryPlan)
+ })
+ .getOrElse { queryPlan }
+ }
+
+ private def parseStatement(
+ parser: ParserInterface,
+ queryString: String,
+ targetVariables: Option[Seq[Expression]]): LogicalPlan = {
+ // If targetVariables is defined, statement needs to be a query.
Review Comment:
The result set of a non-query is the "wild west". There is poor
documentation or no documention.
In the case of DML we must not support return of metadata as a result set in
general because we would break future:
SELECT FROM INSERT.
Which is in the standard.
This problem needs to be solved in general. Let's not make its resolution a
precondition for this feature.
##########
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
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+NULL
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'1707\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+1707
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'invalid_cast_error_expected\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'invalid_cast_error_expected'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 70,
+ "fragment" : "EXECUTE IMMEDIATE 'SELECT \\'invalid_cast_error_expected\\''
INTO res_id"
+ } ]
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES (?)' INTO res_id USING 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INVALID_STATEMENT_FOR_EXECUTE_INTO",
+ "sqlState" : "07501",
+ "messageParameters" : {
+ "sqlString" : "INSERT INTO x VALUES (?)"
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view WHERE ? = id' USING id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNRESOLVED_VARIABLE",
+ "sqlState" : "42883",
+ "messageParameters" : {
+ "searchPath" : "`SYSTEM`.`SESSION`",
+ "variableName" : "`id`"
Review Comment:
Which id? There are two. If we had the fragment information, we would know
it's about the USING part.
##########
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
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+NULL
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'1707\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT res_id
+-- !query schema
+struct<res_id:int>
+-- !query output
+1707
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT \'invalid_cast_error_expected\'' INTO res_id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'invalid_cast_error_expected'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 70,
+ "fragment" : "EXECUTE IMMEDIATE 'SELECT \\'invalid_cast_error_expected\\''
INTO res_id"
+ } ]
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'INSERT INTO x VALUES (?)' INTO res_id USING 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INVALID_STATEMENT_FOR_EXECUTE_INTO",
+ "sqlState" : "07501",
+ "messageParameters" : {
+ "sqlString" : "INSERT INTO x VALUES (?)"
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view WHERE ? = id' USING id
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNRESOLVED_VARIABLE",
+ "sqlState" : "42883",
+ "messageParameters" : {
+ "searchPath" : "`SYSTEM`.`SESSION`",
+ "variableName" : "`id`"
+ }
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * FROM tbl_view where ? = id and :first = name'
USING 1, 'name2' as first
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INVALID_QUERY_BOTH_POSITIONAL_AND_NAMED_PARAMETERS_PRESENT",
+ "sqlState" : "42613"
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELCT F(
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'''",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+a'
+
+EXECUTE IMMEDIATE b
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'a'",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+SET VAR sql_string = 'SELECT * from tbl_view where name = :first or id =
:second'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR a = 'na'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT * from tbl_view where name = :first' USING CONCAT(a
, "me1") as first
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'('",
+ "hint" : ""
+ }
+}
Review Comment:
Should we make this a feature not supported? We could allow expression and
then catch it semanatically.
##########
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'
Review Comment:
Done
--
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]
