sql: internal error: indexed var must be bound to a container before evaluation

[maddyblue]

CREATE TABLE table0 (
    col0 INTERVAL,
    FAMILY fam0 (col0),
    PRIMARY KEY (col0 ASC),
    INDEX (col0 DESC),
    INDEX (col0 ASC),
    UNIQUE (col0 DESC),
    UNIQUE (col0 ASC),
    UNIQUE (col0),
    UNIQUE (col0),
    UNIQUE (col0 ASC)
);

CREATE TABLE table1 (
    col0  REGTYPE,
    col1  BIT(5) NULL,
    col2  BYTES NOT NULL,
    col3  TIMESTAMP NOT NULL,
    col4  INT2 NULL,
    col5  TIME,
    col6  REGTYPE NOT NULL,
    col7  BYTES NULL,
    col8  STRING NULL,
    col9  NAME,
    col10 FLOAT4 NULL,
    col11 "char",
    col12 REGNAMESPACE NOT NULL,
    col13 INTERVAL NULL,
    col14 CHAR NOT NULL,
    col15 REGPROC NOT NULL,
    FAMILY fam0 (col1, col4),
    FAMILY fam1 (col7, col6, col14, col8),
    FAMILY fam2 (col2, col0),
    FAMILY fam3 (col10),
    FAMILY fam4 (col3),
    FAMILY fam5 (col15, col13, col5),
    FAMILY fam6 (col11, col12),
    FAMILY fam7 (col9),
    PRIMARY KEY (col4 DESC, col1)
);

  SELECT max(tab_32879.col8::STRING) OVER (
            PARTITION BY tab_32879.col0
                ORDER BY tab_32879.col8, tab_32879.col12 ASC
                  GROUPS BETWEEN tab_32879.col4 FOLLOWING AND UNBOUNDED FOLLOWING
         )::STRING AS col_93981,
         tab_32879.col4 AS col_93982,
         B'0011101101111010110100001101110001000011' AS col_93983,
         statement_timestamp():::TIMESTAMP::TIMESTAMP AS col_93984,
         '9Mv2MtF':::STRING AS col_93985,
         similar_to_escape(
            CASE
            WHEN false THEN e'kJXC\x1b8A':::STRING
            ELSE 'sc':::STRING
            END::STRING,
            'V.5+':::STRING::STRING,
            e'\'':::STRING::STRING
         )::BOOL AS col_93986
    FROM defaultdb.public.table0 AS tab_32877,
         defaultdb.public.table0 AS tab_32878,
         defaultdb.public.table1 AS tab_32879
GROUP BY tab_32879.col0, tab_32879.col12, tab_32879.col8, tab_32879.col4
  HAVING inet_same_family(
            'ef24:51c6:6da:b9f:b20b:c64a:6cc7:764b/102':::INET::INET,
            'b292:a3b6:9f30:db8f:c376:c6d7:2b52:4b3f/70':::INET::INET
         )::BOOL
   LIMIT 78:::INT8;

pq: internal error: indexed var must be bound to a container before evaluation
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/indexed_vars.go:75: in Eval()
github.com/cockroachdb/cockroach/pkg/sql/distsqlpb/processors.go:91: in initFromAST()
github.com/cockroachdb/cockroach/pkg/sql/distsqlpb/processors.go:194: in InitFromAST()
github.com/cockroachdb/cockroach/pkg/sql/distsql_plan_window.go:141: in createWindowFnSpec()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:3112: in createPlanForWindow()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2400: in createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2349: in createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2311: in createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:833: in PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:903: in execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:738: in dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:417: in execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:98: in execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1205: in execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1141: in run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:443: in ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:578: in func1()

Assigning to yall because window functions show up in the stack.

[yuzefovich]

Here is a reduced reproduction:

CREATE TABLE t (a INT PRIMARY KEY);

SELECT rank() OVER (ORDER BY a GROUPS a PRECEDING) FROM t;

With optimizer on, we get internal error as above, with CBO off - pq: column reference @1 not allowed in this context. Postgres gives us ERROR: argument of GROUPS must not contain variables. For some reason, I thought it was actually allowed, and I think I might have told Justin so as well.

[maddyblue]

@justinj well I guess sqlsmith is working with the new CBO window functions 🤷

[justinj]

nice! thanks for finding. will investigate.