sql-tests/inputs/sql-compatibility-functions.sql

0001 -- A test suite for functions added for compatibility with other databases such as Oracle, MSSQL.
0002 -- These functions are typically implemented using the trait RuntimeReplaceable.
0003
0004 SELECT ifnull(null, 'x'), ifnull('y', 'x'), ifnull(null, null);
0005 SELECT nullif('x', 'x'), nullif('x', 'y');
0006 SELECT nvl(null, 'x'), nvl('y', 'x'), nvl(null, null);
0007 SELECT nvl2(null, 'x', 'y'), nvl2('n', 'x', 'y'), nvl2(null, null, null);
0008
0009 -- type coercion
0010 SELECT ifnull(1, 2.1d), ifnull(null, 2.1d);
0011 SELECT nullif(1, 2.1d), nullif(1, 1.0d);
0012 SELECT nvl(1, 2.1d), nvl(null, 2.1d);
0013 SELECT nvl2(null, 1, 2.1d), nvl2('n', 1, 2.1d);
0014
0015 -- SPARK-16730 cast alias functions for Hive compatibility
0016 SELECT boolean(1), tinyint(1), smallint(1), int(1), bigint(1);
0017 SELECT float(1), double(1), decimal(1);
0018 SELECT date("2014-04-04"), timestamp(date("2014-04-04"));
0019 -- error handling: only one argument
0020 SELECT string(1, 2);
0021
0022 -- SPARK-21555: RuntimeReplaceable used in group by
0023 CREATE TEMPORARY VIEW tempView1 AS VALUES (1, NAMED_STRUCT('col1', 'gamma', 'col2', 'delta')) AS T(id, st);
0024 SELECT nvl(st.col1, "value"), count(*) FROM from tempView1 GROUP BY nvl(st.col1, "value");