ASOF JOIN syntax: Restricted use of keywords¶

The ASOF JOIN feature behaves as follows:

Before the change:

The use of ASOF and MATCH_CONDITION as object names, object aliases, session variables, and bind variables is not restricted.

After the change:

ASOF and MATCH_CONDITION are new keywords. The use of these keywords in SELECT commands and commands that set or use session or bind variables is restricted.

Names of objects

If a SELECT statement uses ASOF or MATCH_CONDITION as the name of a table, view, or inline view, you must identify it as follows:

• If the object was created with double quotes in the name, use the same double-quoted name.

• If the object was created without double quotes in the name, use double quotes and capital letters.

For example, the following statements are no longer allowed and return errors:

SELECT * FROM asof;
WITH match_condition AS (SELECT * FROM T1)
  SELECT * FROM match_condition;

Copy

If you created the objects with double quotes, fix the problem by using double quotes:

SELECT * FROM "asof";
WITH "match_condition" AS (SELECT * FROM T1)
  SELECT * FROM "match_condition";

Copy

If you created the objects without double quotes, fix the problem by using double quotes and capital letters:

SELECT * FROM "ASOF";
WITH "MATCH_CONDITION" AS (SELECT * FROM T1)
  SELECT * FROM "MATCH_CONDITION";

Copy

Note

Snowflake recommends that you discontinue the use of these object names in your applications.

Names of aliases

If a SELECT statement uses ASOF or MATCH_CONDITION as an alias, you must use AS before the alias or double-quote the alias. For example, the following statements are no longer allowed and return errors:

SELECT * FROM t1 asof;
SELECT * FROM t2 match_condition;

Copy

Fix the problem in one of the following ways:

SELECT * FROM t1 AS asof;
SELECT * FROM t1 "asof";
SELECT * FROM t2 AS match_condition;
SELECT * FROM t2 "match_condition";

Copy

Names of variables

If you are using session variables or bind variables with the name ASOF or MATCH_CONDITION, and their names were not double-quoted when they were created, they must be renamed or removed.

For example, you can no longer set a session variable named asof:

set asof ='2024/01/15';

Copy

001003 (42000): SQL compilation error:
syntax error line 1 at position 4 unexpected 'asof'.

However, you can set a variable that is explicitly double-quoted and named "asof" or "ASOF":

set "asof" ='2024/01/15';

Copy

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

The same rules apply to bind variables, such as :asof and :match_condition.

Ref: 1138