sql: variables within generator expressions aren't resolved properly

[jordanlewis]

Suppose we have the following table definition:

CREATE TABLE a (id int);
INSERT INTO a VALUES (1);

The following query works in Postgres, but fails in CockroachDB with column name "id" not found.

SELECT x.* FROM a, generate_series(id, id + 1) AS x;

Specifying the table name doesn't help either. The following query fails with source name "a" not found in FROM clause:

SELECT x.* FROM a, generate_series(a.id, a.id + 1) AS x;

And in related news, attempting to issue this kind of query via the render list as per the resolution of #13140 produces a panic:

SELECT generate_series(id, id + 1) FROM a;

panic: indexed var linked to different container (*sql.renderNode) &{planner:0xc4204e5c70 source:{info:0xc4201b7580 plan:0xc42089a000} sourceInfo:[0xc4201b7580] ivarHelper:{vars:[{Idx:0 container:0xc420a8fc20} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc420a8fc20} render:[] columns:[] isStar:false numOriginalCols:0 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (<nil>) <nil> [recovered]
        panic: SELECT generate_series(id, id + 1) FROM a;: indexed var linked to different container (*sql.renderNode) &{planner:0xc4204e5c70 source:{info:0xc4201b7580 plan:0xc42089a000} sourceInfo:[0xc4201b7580] ivarHelper:{vars:[{Idx:0 container:0xc420a8fc20} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc420a8fc20} render:[] columns:[] isStar:false numOriginalCols:0 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (<nil>) <nil> [recovered]
        panic: SELECT generate_series(id, id + 1) FROM a;: indexed var linked to different container (*sql.renderNode) &{planner:0xc4204e5c70 source:{info:0xc4201b7580 plan:0xc42089a000} sourceInfo:[0xc4201b7580] ivarHelper:{vars:[{Idx:0 container:0xc420a8fc20} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc420a8fc20} render:[] columns:[] isStar:false numOriginalCols:0 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (<nil>) <nil> [recovered]
        panic: SELECT generate_series(id, id + 1) FROM a;: indexed var linked to different container (*sql.renderNode) &{planner:0xc4204e5c70 source:{info:0xc4201b7580 plan:0xc42089a000} sourceInfo:[0xc4201b7580] ivarHelper:{vars:[{Idx:0 container:0xc420a8fc20} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc420a8fc20} render:[] columns:[] isStar:false numOriginalCols:0 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (<nil>) <nil>

goroutine 316 [running]:
panic(0x52c19c0, 0xc420acdd90)
        /usr/local/Cellar/go/1.7.4_2/libexec/src/runtime/panic.go:500 +0x1a1
github.com/cockroachdb/cockroach/pkg/util/stop.(*Stopper).Recover(0xc42021e000)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/util/stop/stopper.go:185 +0x6e
panic(0x52c19c0, 0xc420acdd90)
        /usr/local/Cellar/go/1.7.4_2/libexec/src/runtime/panic.go:458 +0x243
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*v3Conn).serve.func1(0xc420474800, 0x7fa1080, 0xc420a5eae0)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/v3.go:340 +0x74
panic(0x52c19c0, 0xc420acdd90)
        /usr/local/Cellar/go/1.7.4_2/libexec/src/runtime/panic.go:458 +0x243
github.com/cockroachdb/cockroach/pkg/sql.(*Executor).ExecuteStatements.func1(0xc4205322d5, 0x2a)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/executor.go:465 +0x149
panic(0x5279b60, 0xc420acdd70)
        /usr/local/Cellar/go/1.7.4_2/libexec/src/runtime/panic.go:458 +0x243
github.com/cockroachdb/cockroach/pkg/sql/parser.(*IndexedVarHelper).AssertSameContainer(0xc4204e7388, 0xc420ad59f0)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/parser/indexed_vars.go:139 +0x1ef
github.com/cockroachdb/cockroach/pkg/sql.(*nameResolutionVisitor).VisitPre(0xc4204e7348, 0x5dfefc0, 0xc420ad59f0, 0xc4204a1900, 0x53e1ea0, 0xc4204a1900)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/select_name_resolution.go:74 +0x949
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x5df13c0, 0xc4204e7348, 0x5dfefc0, 0xc420ad59f0, 0x5dfe7c0, 0xc4204a1900, 0x5dfe7c0)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:557 +0x55
github.com/cockroachdb/cockroach/pkg/sql/parser.(*FuncExpr).Walk(0xc4204a7bc0, 0x5df13c0, 0xc4204e7348, 0xc420d8d501, 0x5dfef40)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:218 +0xab
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x5df13c0, 0xc4204e7348, 0x5dfef40, 0xc4204a7bc0, 0x0, 0x0, 0x5dfef40)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:560 +0x19e
github.com/cockroachdb/cockroach/pkg/sql.(*planner).resolveNames(0xc4204e5c70, 0x5dfef40, 0xc4204a7bc0, 0x6325e28, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/select_name_resolution.go:196 +0x206
github.com/cockroachdb/cockroach/pkg/sql.(*planner).analyzeExpr(0xc4204e5c70, 0x5dfef40, 0xc4204a7bc0, 0x6325e28, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/analyze.go:1627 +0x273
github.com/cockroachdb/cockroach/pkg/sql.(*planner).makeGenerator(0xc4204e5c70, 0xc4204a7bc0, 0x43aeb09, 0x5df1d80, 0xc4204e7288, 0x5dfef40)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/generator.go:59 +0x1a3
github.com/cockroachdb/cockroach/pkg/sql.(*planner).getGeneratorPlan(0xc4204e5c70, 0xc4204a7bc0, 0xc4204a7bc0, 0xc420d8d958, 0x4332035, 0xc4204e7288, 0x5dfef40)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/data_source.go:522 +0x39
github.com/cockroachdb/cockroach/pkg/sql.(*planner).getDataSource(0xc4204e5c70, 0x5df1780, 0xc4204a7bc0, 0x0, 0x0, 0x4012f98, 0x10, 0x5286e40, 0x5e05301, 0xc420acdc80)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/data_source.go:319 +0xdd0
github.com/cockroachdb/cockroach/pkg/sql.(*renderNode).transformToCrossJoin(0xc420a8fc20, 0xc4204a7bc0, 0x5e05580, 0x6325e28, 0x0, 0x5e05580, 0x6325e28, 0xc4201b7580, 0xc420ad59f0, 0x3, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/render.go:357 +0x71
github.com/cockroachdb/cockroach/pkg/sql.(*renderNode).initTargets(0xc420a8fc20, 0xc42088e740, 0x1, 0x1, 0x0, 0x0, 0x0, 0x62ef620, 0x62ef620)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/render.go:323 +0x290
github.com/cockroachdb/cockroach/pkg/sql.(*planner).SelectClause(0xc4204e5c70, 0xc4204a1780, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/render.go:245 +0x2ec
github.com/cockroachdb/cockroach/pkg/sql.(*planner).Select(0xc4204e5c70, 0xc420952120, 0x0, 0x0, 0x0, 0x1, 0x0, 0x0, 0x0, 0x0)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/render.go:185 +0x528
github.com/cockroachdb/cockroach/pkg/sql.(*planner).newPlan(0xc4204e5c70, 0x5dff3c0, 0xc420952120, 0x0, 0x0, 0x0, 0x1, 0x1, 0x29, 0x0, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/plan.go:309 +0x206b
github.com/cockroachdb/cockroach/pkg/sql.(*planner).makePlan(0xc4204e5c70, 0x5dff3c0, 0xc420952120, 0xc426fac201, 0x62ef620, 0xc420d8e170, 0x46631d2, 0xed01b237f)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/plan.go:193 +0x81
github.com/cockroachdb/cockroach/pkg/sql.(*Executor).execStmt(0xc420222c60, 0x5dff3c0, 0xc420952120, 0xc4204e5c70, 0xc420d80001, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /Users/jordan/repo/src/github.com/cockroachdb/cockroach/pkg/sql/executor.go:1333 +0xa3

[jordanlewis]

This problem seems a bit tricky to resolve. It may require rethinking how generator expressions should work, and its solution may be tied into whatever we end up doing to support correlated subqueries.

The trouble is that if a data source expression contains a reference to a column, the data source must be "evaluated" once per row. Currently, I believe we assume that data source expressions have no dependent variables and can be evaluated without the context of the rest of the query.

The resolution of this issue is a blocker for ActiveRecord.

[knz]

This is a lateral correlated sub-query. We don't support that yet. Prioritizing for Q1 2017 is unrealistic.

[jordanlewis]

We actually don't need the lateral join listed here to work by Q1 - just the simpler form select generator(x) from table where x is a column in table.

[benesch]

After talking with @knz this morning and digging into Postgres's behavior, it seems that supporting set-generating functions in the SELECT clause was actually a mistake. Which explains why their semantics are so confusing. (Sorry if you already knew all this.)

For posterity, I'm recording my train of thought here. Suppose we see a query like this:

SELECT generate_series(id, id + 1) AS s FROM t

This is broken; table functions in SELECT clauses don't make any sense. We support them for the sake of PostgreSQL compatibility, so we rewrite them immediately to the following form. (This exists today in CockroachDB thanks to #13140.)

SELECT s FROM t, generate_series(t.id, t.id + 1) AS s;
SELECT s FROM t CROSS JOIN generate_series(t.id, t.id+1) AS s;

These are identical queries using alternative syntaxes for CROSS JOIN. Both are inherently flawed. There's an implicit lateral correlated subquery here—otherwise how do the arguments to generate_series reference t? This similar query doesn't work, nor should it

SELECT * FROM t, (SELECT t.id) AS _;

unless you make it a lateral subquery:

SELECT * FROM t, LATERAL (SELECT t.id) AS _;

So Postgres is implicitly rewriting the CROSS JOIN to its more appropriate form:

SELECT s FROM t CROSS JOIN LATERAL (SELECT * FROM generate_series(t.id, t.id+1) AS s) AS _;

This final form is the only semantically sensible one: i.e., the only one where table functions appear in FROM clauses instead of SELECT clauses.

The point of all this is to say that I don't think there is a simple case here. The "simple" case isn't really simple at all; Postgres just hides the lateral cross join when you specify a table function as a column.

[knz]

👍

[jordanlewis]

Probably we should fix this panic before 1.0. @benesch didn't you have an almost finished patch for this? Can we clean it up enough to submit and prevent the crash at least?

[benesch]

Yup, #14369 just needs a bit more work.

[knz]

@benesch how does this sound now? Is there any chance your PR will land for 1.0? Otherwise we can just slip to 1.1 or later.

[benesch]

Give it another look, @knz! :)

[benesch]

#14369 landed! Updating the milestone to 1.1, since the panic is resolved.

[tbg]

Similar-looking report from @HeikoOnnebrink.

---

today I was prototyping some SQL as part of some REST API.
The idea is to have some table that stores requests that are inserted from different sources where RequestKey is the Primary key and is of type SERIAL to ensure global sorting.
CREATE TABLE IF NOT EXISTS DbRequests (
RequestKey serial PRIMARY KEY,
RequestType string NOT NULL,
RequestLock bool NOT NULL default false,
ID string NOT NULL,
etc..
There is a set of consumers (worker processes) that grab requests and process them in some sorted fashion by quering the dbRequestTable
(hope in the future when NOTIFY made its way to Roach I can use smarter way than to query the table in some interval..
In order to feed the workers I use some SQL that, on each execution, returns the oldest request that is not yet processed.
The worker will try to lock the request (Update DbRequests set RequestLock=TRUE where RequestKey = and RequestLock=false)
As there can be several requests for each resource (I use here some ID column that saves the UUID of each resource) the query will supress RequestKeys where at least one record per ID has RequestLock = true
In this way workers process requests fully parallel for different IDs but sequentially for requests belonging to same ID.
The related SQL that does the job is:
SELECT min(requestkey) AS requestkey FROM dbrequests WHERE dcname = $1 GROUP BY id HAVING count (case when requestlock = true then 1 end) = 0 ORDER BY 1 LIMIT 1;
My environment is a 9 node cluster spanning 3 DCs with 3 nodes per DC and all replication settings left default.
I run some tests by in injesting some hundred requests at DC 1 and let workers poll the DB in 2 second interval from DC A, B & C
Basically all works fine as designed .. but after some time remote nodes in DC B and C fail with PANIC in log.
I uploaded log cockroachnodeFailSql.log to https://bitbucket.org/honnebrink/public-file-share/src

HeikoOnnebrink @HeikoOnnebrink 07:14
I repeated the tests and could each time reproduce the problem.. one test run got 2 FATALs in DC B and 1 FATAL in DC C.. second test run failed 3 times in DC B only.. Test was executed with 4444 records..

E170607 06:02:39.961680 3008378 util/log/crash_reporting.go:81  [n4] a panic has occurred!
E170607 06:02:40.632301 3008378 util/log/crash_reporting.go:166  [n4] Reported as error 95496809bee840858d84d16595d14950
panic: indexed var linked to different container (*sql.renderNode) &{planner:0xc421a4ac80 source:{info:0xc42416bc80 plan:0xc42305c000} sourceInfo:[0xc42416bc80] ivarHelper:{vars:[{Idx:0 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:2 container:0xc4236787e0} {Idx:3 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc4236787e0} render:[0xc422377b80 0xc42259f4f0 0xc422377bc8] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false} {Name:CASE WHEN requestlock = true THEN 1 END Typ:{} Hidden:false Omitted:false} {Name:id Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (*sql.renderNode) &{planner:0xc4206e5300 source:{info:0xc4270cd140 plan:0xc421a1da70} sourceInfo:[0xc4270cd140] ivarHelper:{vars:[{Idx:0 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:3 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc423c8a900} render:[0xc421d28480] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}} [recovered]
	panic: SELECT min(requestkey) AS requestkey FROM dbrequests WHERE dcname = $1 GROUP BY id HAVING count (case when requestlock = true then 1 end) = 0 ORDER BY 1 LIMIT 1: indexed var linked to different container (*sql.renderNode) &{planner:0xc421a4ac80 source:{info:0xc42416bc80 plan:0xc42305c000} sourceInfo:[0xc42416bc80] ivarHelper:{vars:[{Idx:0 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:2 container:0xc4236787e0} {Idx:3 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc4236787e0} render:[0xc422377b80 0xc42259f4f0 0xc422377bc8] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false} {Name:CASE WHEN requestlock = true THEN 1 END Typ:{} Hidden:false Omitted:false} {Name:id Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (*sql.renderNode) &{planner:0xc4206e5300 source:{info:0xc4270cd140 plan:0xc421a1da70} sourceInfo:[0xc4270cd140] ivarHelper:{vars:[{Idx:0 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:3 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc423c8a900} render:[0xc421d28480] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}} [recovered]
	panic: SELECT min(requestkey) AS requestkey FROM dbrequests WHERE dcname = $1 GROUP BY id HAVING count (case when requestlock = true then 1 end) = 0 ORDER BY 1 LIMIT 1: indexed var linked to different container (*sql.renderNode) &{planner:0xc421a4ac80 source:{info:0xc42416bc80 plan:0xc42305c000} sourceInfo:[0xc42416bc80] ivarHelper:{vars:[{Idx:0 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:2 container:0xc4236787e0} {Idx:3 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc4236787e0} render:[0xc422377b80 0xc42259f4f0 0xc422377bc8] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false} {Name:CASE WHEN requestlock = true THEN 1 END Typ:{} Hidden:false Omitted:false} {Name:id Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (*sql.renderNode) &{planner:0xc4206e5300 source:{info:0xc4270cd140 plan:0xc421a1da70} sourceInfo:[0xc4270cd140] ivarHelper:{vars:[{Idx:0 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:3 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc423c8a900} render:[0xc421d28480] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}} [recovered]
	panic: SELECT min(requestkey) AS requestkey FROM dbrequests WHERE dcname = $1 GROUP BY id HAVING count (case when requestlock = true then 1 end) = 0 ORDER BY 1 LIMIT 1: indexed var linked to different container (*sql.renderNode) &{planner:0xc421a4ac80 source:{info:0xc42416bc80 plan:0xc42305c000} sourceInfo:[0xc42416bc80] ivarHelper:{vars:[{Idx:0 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:2 container:0xc4236787e0} {Idx:3 container:0xc4236787e0} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc4236787e0} render:[0xc422377b80 0xc42259f4f0 0xc422377bc8] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false} {Name:CASE WHEN requestlock = true THEN 1 END Typ:{} Hidden:false Omitted:false} {Name:id Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}, expected (*sql.renderNode) &{planner:0xc4206e5300 source:{info:0xc4270cd140 plan:0xc421a1da70} sourceInfo:[0xc4270cd140] ivarHelper:{vars:[{Idx:0 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:3 container:0xc423c8a900} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>} {Idx:0 container:<nil>}] container:0xc423c8a900} render:[0xc421d28480] columns:[{Name:requestkey Typ:{} Hidden:false Omitted:false}] isStar:false numOriginalCols:1 ordering:{exactMatchCols:map[] ordering:[] unique:false} explain:0 curSourceRow:[] row:[] noCopy:{}}

goroutine 3008378 [running]:
github.com/cockroachdb/cockroach/pkg/util/stop.(*Stopper).Recover(0xc420637680, 0x7fd0fac4ae68, 0xc42045ac00)
	/go/src/github.com/cockroachdb/cockroach/pkg/util/stop/stopper.go:200 +0xb1
panic(0x18fea00, 0xc423695310)
	/usr/local/go/src/runtime/panic.go:489 +0x2cf
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*v3Conn).serve.func1(0xc420d86000, 0x7fd0fac4ae68, 0xc422822f30)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/v3.go:353 +0x74
panic(0x18fea00, 0xc423695310)
	/usr/local/go/src/runtime/panic.go:489 +0x2cf
github.com/cockroachdb/cockroach/pkg/sql.(*Executor).ExecuteStatements.func1(0xc421b9a29e, 0xa0)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/executor.go:499 +0x131
panic(0x18a86c0, 0xc4236952f0)
	/usr/local/go/src/runtime/panic.go:489 +0x2cf
github.com/cockroachdb/cockroach/pkg/sql/parser.(*IndexedVarHelper).AssertSameContainer(0xc4206e6ab8, 0xc422377430)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/indexed_vars.go:139 +0x18c
github.com/cockroachdb/cockroach/pkg/sql.(*nameResolutionVisitor).VisitPre(0xc4206e6a78, 0x2786120, 0xc422377430, 0x2769a8c, 0x16, 0x2769a8c)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/select_name_resolution.go:74 +0x3c0
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x27764e0, 0xc4206e6a78, 0x2786120, 0xc422377430, 0x2785b60, 0x2769a8c, 0xc4276f5200)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:552 +0x4c
github.com/cockroachdb/cockroach/pkg/sql/parser.(*ComparisonExpr).Walk(0xc423653580, 0x27764e0, 0xc4206e6a78, 0xc42305a801, 0x2785ae0)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:177 +0x56
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x27764e0, 0xc4206e6a78, 0x2785ae0, 0xc423653580, 0x2785ce0, 0xc42305a8e0, 0x2786100)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:555 +0x15a
github.com/cockroachdb/cockroach/pkg/sql/parser.(*CaseExpr).Walk(0xc42259f400, 0x27764e0, 0xc4206e6a78, 0xc42259f401, 0x2785960)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:109 +0xab
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x27764e0, 0xc4206e6a78, 0x2785960, 0xc42259f400, 0x0, 0x0, 0x2785a00)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:555 +0x15a
github.com/cockroachdb/cockroach/pkg/sql/parser.(*FuncExpr).Walk(0xc426da4840, 0x27764e0, 0xc4206e6a78, 0xc42305a901, 0x27860a0)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:224 +0xa3
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x27764e0, 0xc4206e6a78, 0x27860a0, 0xc426da4840, 0x2785ce0, 0x8, 0x1a87500)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:555 +0x15a
github.com/cockroachdb/cockroach/pkg/sql/parser.(*ComparisonExpr).Walk(0xc423653600, 0x27764e0, 0xc4206e6a78, 0x1, 0x2785ae0)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:177 +0x56
github.com/cockroachdb/cockroach/pkg/sql/parser.WalkExpr(0x27764e0, 0xc4206e6a78, 0x2785ae0, 0xc423653600, 0x1, 0x1, 0x2786000)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/parser/walk.go:555 +0x15a
github.com/cockroachdb/cockroach/pkg/sql.(*planner).resolveNames(0xc4206e5300, 0x2785ae0, 0xc423653600, 0xc4203a4380, 0x1, 0x1, 0xc4258c5400, 0x1a, 0x1a, 0x277f1e0, ...)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/select_name_resolution.go:196 +0x205
github.com/cockroachdb/cockroach/pkg/sql.(*planner).analyzeExpr(0xc4206e5300, 0x7fd0fac4ae68, 0xc42416d2c0, 0x2785ae0, 0xc423653600, 0xc4203a4380, 0x1, 0x1, 0xc4258c5400, 0x1a, ...)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/analyze.go:1653 +0x28d
github.com/cockroachdb/cockroach/pkg/sql.(*planner).groupBy(0xc4206e5300, 0x7fd0fac4ae68, 0xc42416d2c0, 0xc423653180, 0xc423c8a900, 0x0, 0x0, 0x0)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/group.go:107 +0x1c72
github.com/cockroachdb/cockroach/pkg/sql.(*planner).SelectClause(0xc4206e5300, 0x7fd0fac4ae68, 0xc42416d2c0, 0xc423653180, 0xc42000f020, 0x1, 0x1, 0xc423058aa0, 0x0, 0x0, ...)
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/render.go:281 +0x424
github.com/cockroachdb/cockroach/pkg/sql.(*planner).Select(0xc4206e5300, 0x7fd0fac4ae68, 0xc42416d2c0, 0xc42416d290, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)

[benesch]

Hmm, that looks unrelated at a first glance. I don't see any generator expressions in that query.

[knz]

Please file a separate issue if needed.

[benesch]

My guess is the CASE clause inside the HAVING clause is the problem, but that's only because that's the most complicated part of the query. @tschottdorf, would you mind to file this as a separate issue?

[tbg]

No, doing so now.

[tbg]

#16388

[benesch]

Punting this back to @jordanlewis, since I did my part for 1.0. 😀

[knz]

This is a dup of #24560 and #20511.