core/src/test/resources/sql/asof.iq - calcite - Git at Google

 # asof.iq - ASOF Join query tests
 #
 # 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.
 #
 !use post
 !set outputformat mysql

 # These results have been validated against DuckDB
 # Note that DuckDB has a slightly different syntax for ASOF joins,
 # so the queries have to be rewritten.
 # Also, DuckDB compares nulls in keys as larger than any value,
 # so the results differ for tuples with null keys.  We believe that
 # the behavior of DuckDB is wrong, since logically the result of an
 # ASOF JOIN should always be a subset of the result produced by
 # the corresponding normal JOIN.

 # Test case for https://issues.apache.org/jira/browse/CALCITE-6641
 # Compiling programs with ASOF joins can report obscure errors
 SELECT *
 FROM (VALUES (2, 3)) AS t1(k, t)
 ASOF JOIN (VALUES (2.0, 0.0)) AS t2(k, t)
 MATCH_CONDITION t2.t < t1.t
 ON t1.k = t2.k;
 +---+---+-----+-----+
 | K | T | K0  | T0  |
 +---+---+-----+-----+
 | 2 | 3 | 2.0 | 0.0 |
 +---+---+-----+-----+
 (1 row)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t < t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 | 1 | 3 |  1 |  2 |
 | 1 | 4 |  1 |  3 |
 | 2 | 3 |  2 |  0 |
 +---+---+----+----+
 (3 rows)

 !ok

 # Same test, no explicit table references

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k1, ts1)
 ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k2, ts2)
 MATCH_CONDITION ts2 < ts1
 ON k1 = k2;
 +----+-----+----+-----+
 | K1 | TS1 | K2 | TS2 |
 +----+-----+----+-----+
 |  1 |   3 |  1 |   2 |
 |  1 |   4 |  1 |   3 |
 |  2 |   3 |  2 |   0 |
 +----+-----+----+-----+
 (3 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t > t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 | 1 | 0 |  1 |  2 |
 | 1 | 1 |  1 |  2 |
 | 1 | 2 |  1 |  3 |
 | 2 | 3 |  2 | 10 |
 +---+---+----+----+
 (4 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t >= t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 | 1 | 0 |  1 |  2 |
 | 1 | 1 |  1 |  2 |
 | 1 | 2 |  1 |  2 |
 | 1 | 3 |  1 |  3 |
 | 2 | 3 |  2 | 10 |
 +---+---+----+----+
 (5 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t <= t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 | 1 | 2 |  1 |  2 |
 | 1 | 3 |  1 |  3 |
 | 1 | 4 |  1 |  3 |
 | 2 | 3 |  2 |  0 |
 +---+---+----+----+
 (4 rows)

 !ok

 # Same tests with LEFT ASOF JOIN

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t < t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 |   | 0 |    |    |
 | 1 |   |    |    |
 | 1 | 0 |    |    |
 | 1 | 1 |    |    |
 | 1 | 2 |    |    |
 | 1 | 3 |  1 |  2 |
 | 1 | 4 |  1 |  3 |
 | 2 | 3 |  2 |  0 |
 | 3 | 4 |    |    |
 +---+---+----+----+
 (9 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t > t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 |   | 0 |    |    |
 | 1 |   |    |    |
 | 1 | 0 |  1 |  2 |
 | 1 | 1 |  1 |  2 |
 | 1 | 2 |  1 |  3 |
 | 1 | 3 |    |    |
 | 1 | 4 |    |    |
 | 2 | 3 |  2 | 10 |
 | 3 | 4 |    |    |
 +---+---+----+----+
 (9 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t >= t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 |   | 0 |    |    |
 | 1 |   |    |    |
 | 1 | 0 |  1 |  2 |
 | 1 | 1 |  1 |  2 |
 | 1 | 2 |  1 |  2 |
 | 1 | 3 |  1 |  3 |
 | 1 | 4 |    |    |
 | 2 | 3 |  2 | 10 |
 | 3 | 4 |    |    |
 +---+---+----+----+
 (9 rows)

 !ok

 SELECT *
 FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
 LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
 MATCH_CONDITION t2.t <= t1.t
 ON t1.k = t2.k;
 +---+---+----+----+
 | K | T | K0 | T0 |
 +---+---+----+----+
 |   | 0 |    |    |
 | 1 |   |    |    |
 | 1 | 0 |    |    |
 | 1 | 1 |    |    |
 | 1 | 2 |  1 |  2 |
 | 1 | 3 |  1 |  3 |
 | 1 | 4 |  1 |  3 |
 | 2 | 3 |  2 |  0 |
 | 3 | 4 |    |    |
 +---+---+----+----+
 (9 rows)

 !ok

 !use blank
 # The following 3 tables and ASOF JOIN comprise the test case for [CALCITE-7228]
 # https://issues.apache.org/jira/browse/CALCITE-7228 Validator rejects legal ASOF JOIN program
 CREATE TABLE source_stream_a (
     common_join_attribute SMALLINT,
     join_key_a2 VARCHAR,
     join_key_a1 VARCHAR,
     event_timestamp TIMESTAMP
 );
 (0 rows modified)

 !update

 CREATE TABLE reference_table_b (
     join_key_b1 VARCHAR NOT NULL,
     common_join_attribute SMALLINT,
     event_timestamp TIMESTAMP
 );
 (0 rows modified)

 !update

 CREATE TABLE source_stream_c (
     join_key_b1 VARCHAR NOT NULL,
     join_key_c2 VARCHAR NOT NULL,
     common_join_attribute SMALLINT,
     epoch_ts_c BIGINT,
     timestamp_c TIMESTAMP,
     event_timestamp TIMESTAMP
 );
 (0 rows modified)

 !update

 SELECT t1.* FROM source_stream_a AS t1
 LEFT JOIN reference_table_b AS t2
 ON t1.common_join_attribute = t2.common_join_attribute AND t1.join_key_a1 = t2.join_key_b1
 LEFT ASOF JOIN source_stream_c AS t3
 MATCH_CONDITION ( t1.event_timestamp >= t3.event_timestamp )
 ON t1.common_join_attribute = t3.common_join_attribute AND t1.join_key_a2 = t3.join_key_c2
  AND t1.join_key_a1 = t3.join_key_b1;
 +-----------------------+-------------+-------------+-----------------+
 | COMMON_JOIN_ATTRIBUTE | JOIN_KEY_A2 | JOIN_KEY_A1 | EVENT_TIMESTAMP |
 +-----------------------+-------------+-------------+-----------------+
 +-----------------------+-------------+-------------+-----------------+
 (0 rows)

 !ok

 # End asof.iq
	# asof.iq - ASOF Join query tests
	#
	# 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.
	#
	!use post
	!set outputformat mysql

	# These results have been validated against DuckDB
	# Note that DuckDB has a slightly different syntax for ASOF joins,
	# so the queries have to be rewritten.
	# Also, DuckDB compares nulls in keys as larger than any value,
	# so the results differ for tuples with null keys. We believe that
	# the behavior of DuckDB is wrong, since logically the result of an
	# ASOF JOIN should always be a subset of the result produced by
	# the corresponding normal JOIN.

	# Test case for https://issues.apache.org/jira/browse/CALCITE-6641
	# Compiling programs with ASOF joins can report obscure errors
	SELECT *
	FROM (VALUES (2, 3)) AS t1(k, t)
	ASOF JOIN (VALUES (2.0, 0.0)) AS t2(k, t)
	MATCH_CONDITION t2.t < t1.t
	ON t1.k = t2.k;
	+---+---+-----+-----+
	\| K \| T \| K0 \| T0 \|
	+---+---+-----+-----+
	\| 2 \| 3 \| 2.0 \| 0.0 \|
	+---+---+-----+-----+
	(1 row)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t < t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| 1 \| 3 \| 1 \| 2 \|
	\| 1 \| 4 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 0 \|
	+---+---+----+----+
	(3 rows)

	!ok

	# Same test, no explicit table references

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k1, ts1)
	ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k2, ts2)
	MATCH_CONDITION ts2 < ts1
	ON k1 = k2;
	+----+-----+----+-----+
	\| K1 \| TS1 \| K2 \| TS2 \|
	+----+-----+----+-----+
	\| 1 \| 3 \| 1 \| 2 \|
	\| 1 \| 4 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 0 \|
	+----+-----+----+-----+
	(3 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t > t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| 1 \| 0 \| 1 \| 2 \|
	\| 1 \| 1 \| 1 \| 2 \|
	\| 1 \| 2 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 10 \|
	+---+---+----+----+
	(4 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t >= t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| 1 \| 0 \| 1 \| 2 \|
	\| 1 \| 1 \| 1 \| 2 \|
	\| 1 \| 2 \| 1 \| 2 \|
	\| 1 \| 3 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 10 \|
	+---+---+----+----+
	(5 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t <= t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| 1 \| 2 \| 1 \| 2 \|
	\| 1 \| 3 \| 1 \| 3 \|
	\| 1 \| 4 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 0 \|
	+---+---+----+----+
	(4 rows)

	!ok

	# Same tests with LEFT ASOF JOIN

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t < t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| \| 0 \| \| \|
	\| 1 \| \| \| \|
	\| 1 \| 0 \| \| \|
	\| 1 \| 1 \| \| \|
	\| 1 \| 2 \| \| \|
	\| 1 \| 3 \| 1 \| 2 \|
	\| 1 \| 4 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 0 \|
	\| 3 \| 4 \| \| \|
	+---+---+----+----+
	(9 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t > t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| \| 0 \| \| \|
	\| 1 \| \| \| \|
	\| 1 \| 0 \| 1 \| 2 \|
	\| 1 \| 1 \| 1 \| 2 \|
	\| 1 \| 2 \| 1 \| 3 \|
	\| 1 \| 3 \| \| \|
	\| 1 \| 4 \| \| \|
	\| 2 \| 3 \| 2 \| 10 \|
	\| 3 \| 4 \| \| \|
	+---+---+----+----+
	(9 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t >= t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| \| 0 \| \| \|
	\| 1 \| \| \| \|
	\| 1 \| 0 \| 1 \| 2 \|
	\| 1 \| 1 \| 1 \| 2 \|
	\| 1 \| 2 \| 1 \| 2 \|
	\| 1 \| 3 \| 1 \| 3 \|
	\| 1 \| 4 \| \| \|
	\| 2 \| 3 \| 2 \| 10 \|
	\| 3 \| 4 \| \| \|
	+---+---+----+----+
	(9 rows)

	!ok

	SELECT *
	FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2, 3), (3, 4)) AS t1(k, t)
	LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
	MATCH_CONDITION t2.t <= t1.t
	ON t1.k = t2.k;
	+---+---+----+----+
	\| K \| T \| K0 \| T0 \|
	+---+---+----+----+
	\| \| 0 \| \| \|
	\| 1 \| \| \| \|
	\| 1 \| 0 \| \| \|
	\| 1 \| 1 \| \| \|
	\| 1 \| 2 \| 1 \| 2 \|
	\| 1 \| 3 \| 1 \| 3 \|
	\| 1 \| 4 \| 1 \| 3 \|
	\| 2 \| 3 \| 2 \| 0 \|
	\| 3 \| 4 \| \| \|
	+---+---+----+----+
	(9 rows)

	!ok

	!use blank
	# The following 3 tables and ASOF JOIN comprise the test case for [CALCITE-7228]
	# https://issues.apache.org/jira/browse/CALCITE-7228 Validator rejects legal ASOF JOIN program
	CREATE TABLE source_stream_a (
	common_join_attribute SMALLINT,
	join_key_a2 VARCHAR,
	join_key_a1 VARCHAR,
	event_timestamp TIMESTAMP
	);
	(0 rows modified)

	!update

	CREATE TABLE reference_table_b (
	join_key_b1 VARCHAR NOT NULL,
	common_join_attribute SMALLINT,
	event_timestamp TIMESTAMP
	);
	(0 rows modified)

	!update

	CREATE TABLE source_stream_c (
	join_key_b1 VARCHAR NOT NULL,
	join_key_c2 VARCHAR NOT NULL,
	common_join_attribute SMALLINT,
	epoch_ts_c BIGINT,
	timestamp_c TIMESTAMP,
	event_timestamp TIMESTAMP
	);
	(0 rows modified)

	!update

	SELECT t1.* FROM source_stream_a AS t1
	LEFT JOIN reference_table_b AS t2
	ON t1.common_join_attribute = t2.common_join_attribute AND t1.join_key_a1 = t2.join_key_b1
	LEFT ASOF JOIN source_stream_c AS t3
	MATCH_CONDITION ( t1.event_timestamp >= t3.event_timestamp )
	ON t1.common_join_attribute = t3.common_join_attribute AND t1.join_key_a2 = t3.join_key_c2
	AND t1.join_key_a1 = t3.join_key_b1;
	+-----------------------+-------------+-------------+-----------------+
	\| COMMON_JOIN_ATTRIBUTE \| JOIN_KEY_A2 \| JOIN_KEY_A1 \| EVENT_TIMESTAMP \|
	+-----------------------+-------------+-------------+-----------------+
	+-----------------------+-------------+-------------+-----------------+
	(0 rows)

	!ok

	# End asof.iq