[jira] [Created] (SPARK-19135) SQL: Type inconsistencies with Structs, Arrays and Nulls

["Furcy Pin (JIRA)" <ji...@apache.org>]

Furcy Pin created SPARK-19135:
---------------------------------

             Summary: SQL: Type inconsistencies with Structs, Arrays and Nulls
                 Key: SPARK-19135
                 URL: https://issues.apache.org/jira/browse/SPARK-19135
             Project: Spark
          Issue Type: Bug
    Affects Versions: 2.1.0
            Reporter: Furcy Pin


Hi, I wrote down a few queries to illustrate various problems I ran into
when using structs, arrays and nulls with spark 2.1.0

{code}
-- A) this is ok
SELECT ARRAY_CONTAINS(ARRAY(0, NULL), 0) ;
spark > true 
hive  > true 


-- B) Both Spark and Hive fail, because NULL doesn't have the same type as INT(NULL)
SELECT ARRAY_CONTAINS(ARRAY(0, NULL), NULL) ;
spark > Error in query: cannot resolve 'array_contains(array(CAST(0 AS INT), CAST(NULL AS INT)), NULL)' due to data type mismatch: Null typed values cannot be used as arguments; line 1 pos 7;
hive  > FAILED: SemanticException Line 0:-1 Argument type mismatch 'TOK_NULL': "int" expected at function ARRAY_CONTAINS, but "void" is found


-- C) The expected behavior is not clear to me for this one, shouldn't we expect true ?
SELECT ARRAY_CONTAINS(ARRAY(0, NULL), INT(NULL)) ;
spark > NULL
hive  > false


-- D) Both Spark and Hive fail, for the same reason as B)
SELECT ARRAY_CONTAINS(ARRAY(STRUCT(0)), STRUCT(NULL)) ;
spark > Error in query: cannot resolve 'array_contains(array(named_struct('col1', 0)), named_struct('col1', NULL))' due to data type mismatch: Arguments must be an array followed by a value of same type as the array members; line 1 pos 7;
hive  > FAILED: SemanticException [Error 10016]: Line 1:40 Argument type mismatch 'TOK_NULL': "struct<col1:int>" expected at function ARRAY_CONTAINS, but "struct<col1:void>" is found

-- E) Apparently Spark considers that STRUCT(0) doesn't have the same type has STRUCT(INT(NULL)). Hive now recognizes struct(null), where it didn't recognize null.
SELECT ARRAY_CONTAINS(ARRAY(STRUCT(0), STRUCT(INT(NULL))), STRUCT(INT(NULL))) ;
spark > Error in query: cannot resolve 'array_contains(array(named_struct('col1', 0)), named_struct('col1', CAST(NULL AS INT)))' due to data type mismatch: Arguments must be an array followed by a value of same type as the array members; line 1 pos 7;
hive  > true

-- F) Same problem as E), showing it is not related to array_contains
SELECT STRUCT(INT(NULL)) IN (STRUCT(0), STRUCT(INT(NULL))) ;
spark > Error in query: cannot resolve '(named_struct('col1', CAST(NULL AS INT)) IN (named_struct('col1', 0), named_struct('col1', CAST(NULL AS INT))))' due to data type mismatch: Arguments must be same type; line 1 pos 25;
hive  > true

-- G) This one is the most worring, apparently Spark considers that STRUCT(col) where col is an INT is not the same type as STRUCT(0)
CREATE TABLE test(c INT) ;
INSERT INTO TABLE test VALUES (0) ;
SELECT 
NAMED_STRUCT("p", INT(0)) IN (NAMED_STRUCT("p", INT(c)))
FROM test
;
spark > Error in query: cannot resolve '(named_struct('p', CAST(0 AS INT)) IN (named_struct('p', CAST(test.`c` AS INT))))' due to data type mismatch: Arguments must be same type; line 2 pos 26;
hive  > true 
{code}





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org