CrateDB error: The assembled list of ParameterSymbols is invalid. Missing parameters.

cratedb_regression_15488.py

"""
## About

Regression with CrateDB nightly-5.7.0-2024-01-26-00-02.
The assembled list of ParameterSymbols is invalid. Missing parameters.

-- https://github.com/crate/crate/issues/15488

## Setup

    docker run --rm -it --name=cratedb --publish=4200:4200 --publish=5432:5432 crate/crate:nightly-5.7.0-2024-01-26-00-02 -Cdiscovery.type=single-node
    pip install crate sqlparse

## Observations

The error only happens when all conditions are satisfied,
i.e. the statements MUST HAVE / USE / CONTAIN:

- DDL: A primary key constraint
- QUERY: Parameters for querying
- WHERE: A parameterized `LIMIT` clause
- WHERE: An `IS NULL` clause fragment on any column
- WHERE: At least one other parameterized query clause on any column
"""

from pprint import pprint

import crate.client
import sqlparse


def main():
    conn = crate.client.connect("localhost:4200")
    cursor = conn.cursor()

    sql = """
    DROP TABLE IF EXISTS "testdrive"."foo";
    CREATE TABLE IF NOT EXISTS "testdrive"."foo" (
       "key" TEXT,                          
       PRIMARY KEY ("key")               
    );
    INSERT INTO "testdrive"."foo" (key) VALUES ('foo');
    REFRESH TABLE "testdrive"."foo";
    """
    for stmt in sqlparse.parse(sql):
        cursor.execute(str(stmt))

    cursor.execute(
        """
        SELECT * 
        FROM "testdrive"."foo" 
        WHERE 
            key = ?
            AND 
            key IS NULL
            LIMIT ?
        """,
        parameters=("foo", 5),
    )

    results = cursor.fetchall()
    cursor.close()
    pprint(results)


if __name__ == "__main__":
    main()