Repository: tobymao/sqlglot

Issue:
DuckDB POSITIONAL JOIN syntax
DuckDB supports a unique positional join type which matches up rows of equal length tables, [documented here](https://duckdb.org/docs/sql/query_syntax/from#positional-joins). Currently sqlglot's duckdb dialect does not recognize this syntax and interprets it as an alias for a normal join.

Here's a minimal repro, using the example from the documentation:

```python
from sqlglot import parse_one

parsed = parse_one("""
SELECT df1.*, df2.*
FROM df1
POSITIONAL JOIN df2;
""", dialect="duckdb")

print(f"""
{parsed!r}
--------------------
{parsed.sql(dialect="duckdb", pretty=True)}
""")
```

Which prints

```
Select(
  expressions=[
    Column(
      this=Star(),
      table=Identifier(this=df1, quoted=False)),
    Column(
      this=Star(),
      table=Identifier(this=df2, quoted=False))],
  from=From(
    this=Table(
      this=Identifier(this=df1, quoted=False),
      alias=TableAlias(
        this=Identifier(this=POSITIONAL, quoted=False)))),
  joins=[
    Join(
      this=Table(
        this=Identifier(this=df2, quoted=False)))])
--------------------
SELECT
  df1.*,
  df2.*
FROM df1 AS POSITIONAL, df2
```

Discussion:

Patch:
diff --git a/README.md b/README.md
index 0bfedc66..e8eae258 100644
--- a/README.md
+++ b/README.md
@@ -150,7 +150,7 @@ sql = """
 */
 SELECT
   tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,
-  CAST(x AS INT), # comment 3
+  CAST(x AS SIGNED), # comment 3
   y               -- comment 4
 FROM
   bar /* comment 5 */,
@@ -367,7 +367,9 @@ diff(parse_one("SELECT a + b, c, d"), parse_one("SELECT c, a - b, d"))
       this=Identifier(this=a, quoted=False)),
     expression=Column(
       this=Identifier(this=b, quoted=False)))),
-  Keep(source=Identifier(this=d, quoted=False), target=Identifier(this=d, quoted=False)),
+  Keep(
+    source=Column(this=Identifier(this=a, quoted=False)),
+    target=Column(this=Identifier(this=a, quoted=False))),
   ...
 ]
 ```
@@ -492,6 +494,7 @@ make docs-serve
 ```
 make style  # Only linter checks
 make unit   # Only unit tests
+make test   # Unit and integration tests
 make check  # Full test suite & linter checks
 ```
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 4a7bd04b..4ba3ac34 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -199,6 +199,7 @@ class DuckDB(Dialect):
             "LOGICAL": TokenType.BOOLEAN,
             "ONLY": TokenType.ONLY,
             "PIVOT_WIDER": TokenType.PIVOT,
+            "POSITIONAL": TokenType.POSITIONAL,
             "SIGNED": TokenType.INT,
             "STRING": TokenType.VARCHAR,
             "UBIGINT": TokenType.UBIGINT,
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index fb808fda..5f000540 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -507,8 +507,9 @@ class Parser(metaclass=_Parser):
     }
 
     JOIN_METHODS = {
-        TokenType.NATURAL,
         TokenType.ASOF,
+        TokenType.NATURAL,
+        TokenType.POSITIONAL,
     }
 
     JOIN_SIDES = {
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 8676eee4..201a3c04 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -317,6 +317,7 @@ class TokenType(AutoName):
     PERCENT = auto()
     PIVOT = auto()
     PLACEHOLDER = auto()
+    POSITIONAL = auto()
     PRAGMA = auto()
     PREWHERE = auto()
     PRIMARY_KEY = auto()


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index b80d507c..35daff09 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -213,6 +213,7 @@ class TestDuckDB(Validator):
             parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b"
         )
 
+        self.validate_identity("SELECT df1.*, df2.* FROM df1 POSITIONAL JOIN df2")
         self.validate_identity("MAKE_TIMESTAMP(1992, 9, 20, 13, 34, 27.123456)")
         self.validate_identity("MAKE_TIMESTAMP(1667810584123456)")
         self.validate_identity("SELECT EPOCH_MS(10) AS t")