Bug: JoinMethod handling is not correct

[apstndb]

TL;DR

• The current JoinMethod is not correct.
• APPLY and LOOP is not a valid JOIN prefixes.

Description

Only documented join_method(not hint) is HASH, so APPLY and LOOP are not valid join_method.

condition_join_operator:
  {
    [[INNER] [ join_method ] JOIN](https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#inner_join)
    | [FULL [OUTER] [ join_method ] JOIN](https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#full_join)
    | [LEFT [OUTER] [ join_method ] JOIN](https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#left_join)
    | [RIGHT [OUTER] [ join_method ] JOIN](https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#right_join)
  }

join_method:
  { HASH }

SELECT * FROM Singers APPLY JOIN Albums USING (SingerId);
SELECT * FROM Singers LOOP JOIN Albums USING (SingerId);

are interpreted as

SELECT * FROM Singers AS APPLY JOIN Albums USING (SingerId);
SELECT * FROM Singers AS LOOP JOIN Albums USING (SingerId);

It can be verified using .*.

spanner> SELECT APPLY.* FROM Singers APPLY JOIN Albums USING (SingerId);
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 3        | Alice     | Trentor  | NULL       | 1991-10-02 |
| 4        | Lea       | Martin   | NULL       | 1991-11-09 |
+----------+-----------+----------+------------+------------+
7 rows in set (7.15 msecs)

spanner> SELECT LOOP.* FROM Singers LOOP JOIN Albums USING (SingerId);
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 3        | Alice     | Trentor  | NULL       | 1991-10-02 |
| 4        | Lea       | Martin   | NULL       | 1991-11-09 |
+----------+-----------+----------+------------+------------+
7 rows in set (5.38 msecs)

I believe this bug can break valid queries, so it is needed to be fixed.

Note

There is a undocument LOOKUP join method. It is unimplemented, but it can be parsed.

https://github.com/google/zetasql/blob/a516c6b26d183efc4f56293256bba92e243b7a61/zetasql/parser/zetasql.tm#L7713-L7718

spanner> SELECT * FROM Singers LOOKUP JOIN Albums USING (SingerId);
ERROR: spanner: code = "InvalidArgument", desc = "Invalid value for join_type/join_method hint: LOOKUP_JOIN."

Related issue

• Invalid testcases #211

[apstndb]

Resolved #213