Minimal reproduction of a false-positive InvalidQueryDefinition in sql_metadata.

Environment

• sql_metadata==3.0.1
• sqlglot==30.21.0
• python 3.12

Bug

A valid multi-CTE SQL statement (e.g. Trino/Athena/Presto dialect) raises
"WITH clause without a main statement is not valid SQL" when a non-first
CTE contains a map/array bracket-subscript expression, e.g.
ARRAY_JOIN(some_map['key'], ' ').

Root cause appears to be in sql_metadata's dialect auto-detection
(DialectParser._detect_dialects in dialect_parser.py): any "[" in the raw
SQL causes it to try sqlglot's TSQL-based BracketedTableDialect first
(treating "[" as bracket-quoted identifiers). Under that dialect,
sqlglot's WARN-level parse of ARRAY_JOIN(map['key'], ' ') silently drops
the trailing main SELECT statement, leaving a bare WITH AST node, which
is a false "degraded" check miss (no exp.Command, no bad table/column
names) -- so it's accepted as the winning parse even though it's wrong.
This then surfaces later as InvalidQueryDefinition when .tables /
.columns etc. are accessed.

Query is valid: Trino/Athena use [ only for map/array element access,
not identifier quoting, so this heuristic misfires for any query that
legitimately uses that syntax more than once across CTEs.

Expected

Parser(sql).tables (or any property that triggers query_type) should
succeed, as it does when there's only a single CTE, or when the bracket
subscript is replaced with element_at(m, 'key').

Actual

sql_metadata.exceptions.InvalidQueryDefinition: WITH clause without a
main statement is not valid SQL

from sql_metadata import Parser

# Fails: two CTEs, bracket-subscript map access in the second CTE.
FAILING_SQL = """
WITH t AS (SELECT map_agg(k, v) AS m FROM foo),
t2 AS (SELECT ARRAY_JOIN(m['key'], ' ') AS joined FROM t)
SELECT joined FROM t2
"""

# Passes: single CTE with the same bracket-subscript pattern.
PASSING_SQL_SINGLE_CTE = """
WITH t AS (SELECT map_agg(k, v) AS m FROM foo)
SELECT ARRAY_JOIN(m['key'], ' ') AS joined FROM t
"""

# Passes: two CTEs, but using element_at() instead of bracket subscript.
PASSING_SQL_ELEMENT_AT = """
WITH t AS (SELECT map_agg(k, v) AS m FROM foo),
t2 AS (SELECT ARRAY_JOIN(element_at(m, 'key'), ' ') AS joined FROM t)
SELECT joined FROM t2
"""

if __name__ == "__main__":
    for name, sql in [
        ("FAILING_SQL (two CTEs + bracket subscript)", FAILING_SQL),
        ("PASSING_SQL_SINGLE_CTE", PASSING_SQL_SINGLE_CTE),
        ("PASSING_SQL_ELEMENT_AT", PASSING_SQL_ELEMENT_AT),
    ]:
        try:
            tables = Parser(sql).tables
            print(f"{name}: OK -> {tables}")
        except Exception as exc:  # noqa: BLE001
            print(f"{name}: RAISED {type(exc).__name__}: {exc}")