window_part3.sql.out

-- Automatically generated by SQLQueryTestSuite
-- !query
CREATE TEMPORARY VIEW tenk2 AS SELECT * FROM tenk1
-- !query schema
struct<>
-- !query output



-- !query
CREATE TABLE empsalary (
    depname string,
    empno integer,
    salary int,
    enroll_date date
) USING parquet
-- !query schema
struct<>
-- !query output



-- !query
INSERT INTO empsalary VALUES
  ('develop', 10, 5200, date '2007-08-01'),
  ('sales', 1, 5000, date '2006-10-01'),
  ('personnel', 5, 3500, date '2007-12-10'),
  ('sales', 4, 4800, date '2007-08-08'),
  ('personnel', 2, 3900, date '2006-12-23'),
  ('develop', 7, 4200, date '2008-01-01'),
  ('develop', 9, 4500, date '2008-01-01'),
  ('sales', 3, 4800, date '2007-08-01'),
  ('develop', 8, 6000, date '2006-10-01'),
  ('develop', 11, 5200, date '2007-08-15')
-- !query schema
struct<>
-- !query output



-- !query
create table datetimes (
    id int,
    f_time timestamp,
    f_timetz timestamp,
    f_interval timestamp,
    f_timestamptz timestamp,
    f_timestamp timestamp
) using parquet
-- !query schema
struct<>
-- !query output



-- !query
insert into datetimes values
(1, timestamp '11:00', cast ('11:00 BST' as timestamp), cast ('1 year' as timestamp), cast ('2000-10-19 10:23:54+01' as timestamp), timestamp '2000-10-19 10:23:54'),
(2, timestamp '12:00', cast ('12:00 BST' as timestamp), cast ('2 years' as timestamp), cast ('2001-10-19 10:23:54+01' as timestamp), timestamp '2001-10-19 10:23:54'),
(3, timestamp '13:00', cast ('13:00 BST' as timestamp), cast ('3 years' as timestamp), cast ('2001-10-19 10:23:54+01' as timestamp), timestamp '2001-10-19 10:23:54'),
(4, timestamp '14:00', cast ('14:00 BST' as timestamp), cast ('4 years' as timestamp), cast ('2002-10-19 10:23:54+01' as timestamp), timestamp '2002-10-19 10:23:54'),
(5, timestamp '15:00', cast ('15:00 BST' as timestamp), cast ('5 years' as timestamp), cast ('2003-10-19 10:23:54+01' as timestamp), timestamp '2003-10-19 10:23:54'),
(6, timestamp '15:00', cast ('15:00 BST' as timestamp), cast ('5 years' as timestamp), cast ('2004-10-19 10:23:54+01' as timestamp), timestamp '2004-10-19 10:23:54'),
(7, timestamp '17:00', cast ('17:00 BST' as timestamp), cast ('7 years' as timestamp), cast ('2005-10-19 10:23:54+01' as timestamp), timestamp '2005-10-19 10:23:54'),
(8, timestamp '18:00', cast ('18:00 BST' as timestamp), cast ('8 years' as timestamp), cast ('2006-10-19 10:23:54+01' as timestamp), timestamp '2006-10-19 10:23:54'),
(9, timestamp '19:00', cast ('19:00 BST' as timestamp), cast ('9 years' as timestamp), cast ('2007-10-19 10:23:54+01' as timestamp), timestamp '2007-10-19 10:23:54'),
(10, timestamp '20:00', cast ('20:00 BST' as timestamp), cast ('10 years' as timestamp), cast ('2008-10-19 10:23:54+01' as timestamp), timestamp '2008-10-19 10:23:54')
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
failed to evaluate expression CAST('11:00 BST' AS TIMESTAMP): [CAST_INVALID_INPUT] The value '11:00 BST' of the type "STRING" cannot be cast to "TIMESTAMP" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 2, position 24) ==
(1, timestamp '11:00', cast ('11:00 BST' as timestamp), cast ('1 year' as timestamp), ...
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
; line 1 pos 22


-- !query
WITH cte (x) AS (
        SELECT * FROM range(1, 36, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following)
-- !query schema
struct<x:bigint,sum(x) OVER (ORDER BY x ASC NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING):bigint>
-- !query output
1	4
11	33
13	39
15	45
17	51
19	57
21	63
23	69
25	75
27	81
29	87
3	9
31	93
33	99
35	68
5	15
7	21
9	27


-- !query
WITH cte (x) AS (
        SELECT * FROM range(1, 36, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following)
-- !query schema
struct<x:bigint,sum(x) OVER (ORDER BY x ASC NULLS FIRST RANGE BETWEEN (- 1) FOLLOWING AND 1 FOLLOWING):bigint>
-- !query output
1	1
11	11
13	13
15	15
17	17
19	19
21	21
23	23
25	25
27	27
29	29
3	3
31	31
33	33
35	35
5	5
7	7
9	9


-- !query
WITH cte (x) AS (
        select 1 union all select 1 union all select 1 union all
        SELECT * FROM range(5, 50, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following)
-- !query schema
struct<x:bigint,sum(x) OVER (ORDER BY x ASC NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING):bigint>
-- !query output
1	2
1	3
1	7
11	33
13	39
15	45
17	51
19	57
21	63
23	69
25	75
27	81
29	87
31	93
33	99
35	105
37	111
39	117
41	123
43	129
45	135
47	141
49	96
5	13
7	21
9	27


-- !query
WITH cte (x) AS (
        select 1 union all select 1 union all select 1 union all
        SELECT * FROM range(5, 50, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following)
-- !query schema
struct<x:bigint,sum(x) OVER (ORDER BY x ASC NULLS FIRST RANGE BETWEEN (- 1) FOLLOWING AND 1 FOLLOWING):bigint>
-- !query output
1	3
1	3
1	3
11	11
13	13
15	15
17	17
19	19
21	21
23	23
25	25
27	27
29	29
31	31
33	33
35	35
37	37
39	39
41	41
43	43
45	45
47	47
49	49
5	5
7	7
9	9


-- !query
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0
-- !query schema
struct<count(1) OVER (PARTITION BY four ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING):bigint>
-- !query output



-- !query
create table t1 (f1 int, f2 int) using parquet
-- !query schema
struct<>
-- !query output



-- !query
insert into t1 values (1,1),(1,2),(2,2)
-- !query schema
struct<>
-- !query output



-- !query
select f1, sum(f1) over (partition by f1
                         range between 1 preceding and 1 following)
from t1 where f1 = f2
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH",
  "errorSubClass" : "RANGE_FRAME_WITHOUT_ORDER",
  "messageParameters" : {
    "sqlExpr" : "\"(PARTITION BY f1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 25,
    "stopIndex" : 108,
    "fragment" : "(partition by f1\n                         range between 1 preceding and 1 following)"
  } ]
}


-- !query
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2
-- !query schema
struct<f1:int,sum(f1) OVER (PARTITION BY f1 ORDER BY f2 ASC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING):bigint>
-- !query output
1	1
2	2


-- !query
select f1, sum(f1) over (partition by f1, f1 order by f2
range between 2 preceding and 1 preceding)
from t1 where f1 = f2
-- !query schema
struct<f1:int,sum(f1) OVER (PARTITION BY f1, f1 ORDER BY f2 ASC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING):bigint>
-- !query output
1	NULL
2	NULL


-- !query
select f1, sum(f1) over (partition by f1, f2 order by f2
range between 1 following and 2 following)
from t1 where f1 = f2
-- !query schema
struct<f1:int,sum(f1) OVER (PARTITION BY f1, f2 ORDER BY f2 ASC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING):bigint>
-- !query output
1	NULL
2	NULL


-- !query
SELECT rank() OVER (ORDER BY length('abc'))
-- !query schema
struct<RANK() OVER (ORDER BY length(abc) ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):int>
-- !query output
1


-- !query
SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "_LEGACY_ERROR_TEMP_1034",
  "messageParameters" : {
    "clauseName" : "WHERE"
  }
}


-- !query
SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException

The query operator `Join` contains one or more unsupported
expression types Aggregate, Window or Generate.
Invalid expressions: [row_number() OVER (ORDER BY spark_catalog.default.empsalary.salary ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)]


-- !query
SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException

The query operator `Aggregate` contains one or more unsupported
expression types Aggregate, Window or Generate.
Invalid expressions: [RANK() OVER (ORDER BY 1 ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)]


-- !query
SELECT * FROM rank() OVER (ORDER BY random())
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.catalyst.parser.ParseException
{
  "errorClass" : "PARSE_SYNTAX_ERROR",
  "sqlState" : "42000",
  "messageParameters" : {
    "error" : "'BY'",
    "hint" : ""
  }
}


-- !query
SELECT * FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "_LEGACY_ERROR_TEMP_1034",
  "messageParameters" : {
    "clauseName" : "WHERE"
  }
}


-- !query
SELECT * FROM empsalary WHERE rank() OVER (ORDER BY random())
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "_LEGACY_ERROR_TEMP_1034",
  "messageParameters" : {
    "clauseName" : "WHERE"
  }
}


-- !query
select rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.catalyst.parser.ParseException
{
  "errorClass" : "PARSE_SYNTAX_ERROR",
  "sqlState" : "42000",
  "messageParameters" : {
    "error" : "'BY'",
    "hint" : ": extra input 'BY'"
  }
}


-- !query
SELECT range(1, 100) OVER () FROM empsalary
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
Undefined function: range. This function is neither a built-in/temporary function, nor a persistent function that is qualified as spark_catalog.default.range.; line 1 pos 7


-- !query
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'ntile(0)' due to data type mismatch: Buckets expression must be positive, but got: 0; line 1 pos 7


-- !query
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'nth_value(spark_catalog.default.tenk1.four, 0)' due to data type mismatch: The 'offset' argument of nth_value must be greater than zero but it is 0.; line 1 pos 7


-- !query
DROP TABLE empsalary
-- !query schema
struct<>
-- !query output



-- !query
DROP TABLE datetimes
-- !query schema
struct<>
-- !query output



-- !query
DROP TABLE t1
-- !query schema
struct<>
-- !query output