Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
maddyblue opened this issue Jun 19, 2019 · 9 comments · Fixed by #40832
Closed

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

maddyblue opened this issue Jun 19, 2019 · 9 comments · Fixed by #40832
Assignees
@justinj
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-sqlsmith

Comments

@maddyblue
Copy link
Contributor 

CREATE TABLE table1 (
	col0  TIMESTAMPTZ,
	col1  BIT(22),
	col2  NAME,
	col3  INT8 NULL,
	col4  "char" NOT NULL,
	col5  CHAR NOT NULL,
	col6  CHAR NOT NULL,
	col7  INET NOT NULL,
	col8  INT2 NULL,
	col9  REGNAMESPACE NOT NULL,
	col10 REGTYPE NULL,
	col11 OID NOT NULL,
	col12 REGTYPE NULL,
	col13 BIT(42) NOT NULL,
	col14 INT8 NULL,
	col15 FLOAT8 NULL,
	FAMILY fam0 (col9, col4, col0, col1, col6),
	FAMILY fam1 (col10, col2, col5, col14, col15, col7, col11, col13, col12, col3, col8),
	PRIMARY KEY (col6 ASC),
	INDEX (col7 DESC, col2, col5 DESC, col1, col12 ASC, col0 DESC, col15 ASC)
);

    WITH with_124059 (col_1570955) AS (
									SELECT *
									  FROM (
											VALUES (NULL),
											       (2609206515:::OID),
											       (4037323730:::OID),
											       (1711655002:::OID),
											       (3423528832:::OID)
									       ) AS tab_536190 (col_1570955)
                                   )
  SELECT tab_536191.col11 AS col_1570956,
         ARRAY[
			2917945630780613107:::INT8,
			6992170413345928079:::INT8,
			5859734096475146714:::INT8,
			(-6072750870110614877):::INT8
         ] AS col_1570957,
         tab_536191.col11 AS col_1570958,
         stddev(tab_536191.col8::INT8)::DECIMAL AS col_1570959,
         min(tab_536191.col5::STRING) OVER (
			PARTITION BY tab_536191.col13, tab_536191.col4
			    ORDER BY tab_536191.col1, tab_536191.col13 DESC, tab_536191.col11, tab_536191.col5
			        ROWS BETWEEN tab_536191.col8 FOLLOWING AND 9223372036854775807:::INT8 FOLLOWING
         )::STRING AS col_1570960,
         (-0.45292767816968504):::FLOAT8 AS col_1570961,
         current_timestamp():::DATE::DATE AS col_1570962
    FROM defaultdb.public.table1 AS tab_536191
GROUP BY tab_536191.col4, tab_536191.col8, tab_536191.col11, tab_536191.col1, tab_536191.col13, tab_536191.col5
ORDER BY tab_536191.col13 DESC, tab_536191.col8 DESC, tab_536191.col11
   LIMIT 9:::INT8;

github.com/cockroachdb/cockroach/pkg/sql/sem/tree/indexed_vars.go:77: Eval()
github.com/cockroachdb/cockroach/pkg/sql/distsqlpb/processors.go:92: initFromAST()
github.com/cockroachdb/cockroach/pkg/sql/distsqlpb/processors.go:195: InitFromAST()
github.com/cockroachdb/cockroach/pkg/sql/distsql_plan_window.go:141: createWindowFnSpec()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:3113: createPlanForWindow()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2401: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2332: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2350: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2312: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2312: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:834: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:896: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:732: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:419: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:101: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1206: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1142: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:444: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:582: func1()
runtime/asm_amd64.s:1337: goexit()
@maddyblue maddyblue added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-sqlsmith labels Jun 19, 2019
@yuzefovich
Copy link
Member

This is the same problem as #38090, so all three modes do not allow for variables as offsets (not only GROUPS). I will fix this shortly, after #37251 is merged.

@yuzefovich yuzefovich mentioned this issue Jun 19, 2019
Closed
@maddyblue
Copy link
Contributor Author

Ping. Can you take a look at this again?

@yuzefovich
Copy link
Member

Thanks, yes, I'll take a look.

@maddyblue
Copy link
Contributor Author

Ping.

@yuzefovich
Copy link
Member

I pinged Justin, and he responded with "Sorry, the thing I thought would be the easy solution won't work I think, so I need to find some time to dig in and figure out what needs to happen here."

I'll assign him to look into this issue since I'm lacking the context of optimizer to fix this properly.

@yuzefovich yuzefovich assigned justinj and unassigned yuzefovich Jul 16, 2019
@maddyblue
Copy link
Contributor Author

Oh oh, the reducer worked really well for this:

CREATE TABLE table1 (col5 CHAR, col8 INT2);

SELECT
	min(tab_536191.col5) OVER (
		ROWS
			BETWEEN
				tab_536191.col8 FOLLOWING
			AND
				9223372036854775807 FOLLOWING
	)
FROM
	table1 AS tab_536191
GROUP BY
	tab_536191.col8, tab_536191.col5;

@maddyblue
Copy link
Contributor Author

ping @justinj

This is the only pending sqlsmith bug.

@maddyblue
Copy link
Contributor Author

plong

@justinj
Copy link
Contributor

justinj commented Aug 19, 2019

Sorry, will get to it—have some other stuff I've been working through that needs to get in, but I will come back to this.

justinj pushed a commit to justinj/cockroach that referenced this issue Sep 17, 2019
opt,sql: disallow column references in ROWS/RANGE
f6994e2 
Fixes cockroachdb#38286.

This is a departure from the behaviour of the heuristic planner but is
actually the same behaviour as Postgres:

```
d=# create table x (a int primary key);
CREATE TABLE
d=# select rank() over (order by a range between a preceding and unbounded following) from x;
ERROR:  argument of RANGE must not contain variables
LINE 1: select rank() over (order by a range between a preceding and...
```

Our behaviour now:
```
root@127.0.0.1:60679/movr> CREATE TABLE x (a INT8 PRIMARY KEY)
CREATE TABLE

root@127.0.0.1:60679/movr> SELECT
    rank() OVER (ORDER BY a RANGE BETWEEN a PRECEDING AND UNBOUNDED FOLLOWING)
FROM
    x
pq: ROWS or RANGE cannot contain variables
```

Release justification: Category 2: Fixes a bug and brings behaviour more
in-line with Postgres.

Release note (sql change): column references are no longer allowed in
ROWS/RANGE clauses in window functions.
@justinj justinj mentioned this issue Sep 17, 2019
Merged
justinj pushed a commit to justinj/cockroach that referenced this issue Sep 17, 2019
opt,sql: disallow column references in ROWS/RANGE
79d0a24 
Fixes cockroachdb#38286.

This is a departure from the behaviour of the heuristic planner but is
actually the same behaviour as Postgres:

```
d=# create table x (a int primary key);
CREATE TABLE
d=# select rank() over (order by a range between a preceding and unbounded following) from x;
ERROR:  argument of RANGE must not contain variables
LINE 1: select rank() over (order by a range between a preceding and...
```

Our behaviour now:
```
root@127.0.0.1:60679/movr> CREATE TABLE x (a INT8 PRIMARY KEY)
CREATE TABLE

root@127.0.0.1:60679/movr> SELECT
    rank() OVER (ORDER BY a RANGE BETWEEN a PRECEDING AND UNBOUNDED FOLLOWING)
FROM
    x
pq: ROWS or RANGE cannot contain variables
```

Release justification: Category 2: Fixes a bug and brings behaviour more
in-line with Postgres.

Release note (sql change): column references are no longer allowed in
ROWS/RANGE clauses in window functions.
justinj pushed a commit to justinj/cockroach that referenced this issue Sep 18, 2019
opt,sql: disallow column references in ROWS/RANGE
27ca53c 
Fixes cockroachdb#38286.

This is a departure from the behaviour of the heuristic planner but is
actually the same behaviour as Postgres:

```
d=# create table x (a int primary key);
CREATE TABLE
d=# select rank() over (order by a range between a preceding and unbounded following) from x;
ERROR:  argument of RANGE must not contain variables
LINE 1: select rank() over (order by a range between a preceding and...
```

Our behaviour now:
```
root@127.0.0.1:60679/movr> CREATE TABLE x (a INT8 PRIMARY KEY)
CREATE TABLE

root@127.0.0.1:60679/movr> SELECT
    rank() OVER (ORDER BY a RANGE BETWEEN a PRECEDING AND UNBOUNDED FOLLOWING)
FROM
    x
pq: ROWS or RANGE cannot contain variables
```

Release justification: Category 2: Fixes a bug and brings behaviour more
in-line with Postgres.

Release note (sql change): column references are no longer allowed in
ROWS/RANGE clauses in window functions.
craig bot pushed a commit that referenced this issue Sep 19, 2019
Merge #40832
94ef651 
40832: opt,sql: disallow column references in ROWS/RANGE r=justinj a=justinj

Fixes #38286.

This is a departure from the behaviour of the heuristic planner but is
actually the same behaviour as Postgres:

```
d=# create table x (a int primary key);
CREATE TABLE
d=# select rank() over (order by a range between a preceding and unbounded following) from x;
ERROR:  argument of RANGE must not contain variables
LINE 1: select rank() over (order by a range between a preceding and...
```

Our behaviour now:
```
root@127.0.0.1:60679/movr> CREATE TABLE x (a INT8 PRIMARY KEY)
CREATE TABLE

root@127.0.0.1:60679/movr> SELECT
    rank() OVER (ORDER BY a RANGE BETWEEN a PRECEDING AND UNBOUNDED FOLLOWING)
FROM
    x
pq: ROWS or RANGE cannot contain variables
```

Release justification: Category 2: Fixes a bug and brings behaviour more
in-line with Postgres.

Release note (sql change): column references are no longer allowed in
ROWS/RANGE clauses in window functions.

Co-authored-by: Justin Jaffray <justin@cockroachlabs.com>
@craig craig bot closed this as completed in 7a8fdfb Sep 19, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@justinj justinj

Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-sqlsmith
Projects
None yet
Milestone
No milestone
3 participants
@maddyblue @justinj @yuzefovich