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: needed column not produced when a CTE is used multiple times #39708

Closed
yuzefovich opened this issue Aug 16, 2019 · 3 comments
Closed

sql: internal error: needed column not produced when a CTE is used multiple times #39708

yuzefovich opened this issue Aug 16, 2019 · 3 comments
Assignees
@justinj
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.

Comments

@yuzefovich
Copy link
Member

Running query 1 of TPC-DS benchmark:

WITH
    customer_total_return
        AS (
            SELECT
                sr_customer_sk AS ctr_customer_sk,
                sr_store_sk AS ctr_store_sk,
                sum(sr_fee) AS ctr_total_return
            FROM
                store_returns, date_dim
            WHERE
                sr_returned_date_sk = d_date_sk
                AND d_year = 2000
            GROUP BY
                sr_customer_sk, sr_store_sk
        )
SELECT
    c_customer_id
FROM
    customer_total_return AS ctr1, store, customer
WHERE
    ctr1.ctr_total_return
    > (
            SELECT
                avg(ctr_total_return) * 1.2
            FROM
                customer_total_return AS ctr2
            WHERE
                ctr1.ctr_store_sk = ctr2.ctr_store_sk
        )
    AND s_store_sk = ctr1.ctr_store_sk
    AND s_state = 'TN'
    AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY
    c_customer_id
LIMIT
    100;

produces an internal error:

pq: internal error: needed column not produced by group-by input
DETAIL: stack trace:
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1021: buildGroupByInput()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:855: buildGroupBy()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:198: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:493: buildSelect()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:192: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1216: buildLookupJoin()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:213: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1216: buildLookupJoin()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:213: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1139: buildSort()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:207: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1116: buildLimitOffset()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:204: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:535: buildProject()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:195: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1404: buildWith()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:255: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1139: buildSort()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:207: buildRelational()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:117: build()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:105: Build()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:167: makeOptimizerPlan()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:728: makeExecPlan()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:615: dispatchToExecutionEngine()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:416: execStmtInOpenState()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:98: execStmt()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1211: execCmd()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1140: run()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:442: ServeConn()
/go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:584: func1()
/usr/local/go/src/runtime/asm_amd64.s:1337: goexit()

The dataset can be imported with RESTORE DATABASE tpcds FROM 'gs://cockroach-fixtures/workload/tpcds/scalefactor=1/backup'; (it's about 1GB of data).
@yuzefovich yuzefovich added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Aug 16, 2019
@yuzefovich yuzefovich mentioned this issue Aug 16, 2019
Open
7 tasks
@yuzefovich
Copy link
Member Author

The same problem occurs when running query 81:

WITH
	customer_total_return
		AS (
			SELECT
				cr_returning_customer_sk AS ctr_customer_sk,
				ca_state AS ctr_state,
				sum(cr_return_amt_inc_tax)
					AS ctr_total_return
			FROM
				catalog_returns, date_dim, customer_address
			WHERE
				cr_returned_date_sk = d_date_sk
				AND d_year = 2001
				AND cr_returning_addr_sk = ca_address_sk
			GROUP BY
				cr_returning_customer_sk, ca_state
		)
SELECT
	c_customer_id,
	c_salutation,
	c_first_name,
	c_last_name,
	ca_street_number,
	ca_street_name,
	ca_street_type,
	ca_suite_number,
	ca_city,
	ca_county,
	ca_state,
	ca_zip,
	ca_country,
	ca_gmt_offset,
	ca_location_type,
	ctr_total_return
FROM
	customer_total_return AS ctr1,
	customer_address,
	customer
WHERE
	ctr1.ctr_total_return
	> (
			SELECT
				avg(ctr_total_return) * 1.2
			FROM
				customer_total_return AS ctr2
			WHERE
				ctr1.ctr_state = ctr2.ctr_state
		)
	AND ca_address_sk = c_current_addr_sk
	AND ca_state = 'TN'
	AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY
	c_customer_id,
	c_salutation,
	c_first_name,
	c_last_name,
	ca_street_number,
	ca_street_name,
	ca_street_type,
	ca_suite_number,
	ca_city,
	ca_county,
	ca_state,
	ca_zip,
	ca_country,
	ca_gmt_offset,
	ca_location_type,
	ctr_total_return
LIMIT
	100;

@yuzefovich
Copy link
Member Author

And with query 30:

WITH
	customer_total_return
		AS (
			SELECT
				wr_returning_customer_sk AS ctr_customer_sk,
				ca_state AS ctr_state,
				sum(wr_return_amt) AS ctr_total_return
			FROM
				web_returns, date_dim, customer_address
			WHERE
				wr_returned_date_sk = d_date_sk
				AND d_year = 2000
				AND wr_returning_addr_sk = ca_address_sk
			GROUP BY
				wr_returning_customer_sk, ca_state
		)
SELECT
	c_customer_id,
	c_salutation,
	c_first_name,
	c_last_name,
	c_preferred_cust_flag,
	c_birth_day,
	c_birth_month,
	c_birth_year,
	c_birth_country,
	c_login,
	c_email_address,
	c_last_review_date_sk,
	ctr_total_return
FROM
	customer_total_return AS ctr1,
	customer_address,
	customer
WHERE
	ctr1.ctr_total_return
	> (
			SELECT
				avg(ctr_total_return) * 1.2
			FROM
				customer_total_return AS ctr2
			WHERE
				ctr1.ctr_state = ctr2.ctr_state
		)
	AND ca_address_sk = c_current_addr_sk
	AND ca_state = 'AR'
	AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY
	c_customer_id,
	c_salutation,
	c_first_name,
	c_last_name,
	c_preferred_cust_flag,
	c_birth_day,
	c_birth_month,
	c_birth_year,
	c_birth_country,
	c_login,
	c_email_address,
	c_last_review_date_sk,
	ctr_total_return
LIMIT
	100;

@justinj
Copy link
Contributor

justinj commented Sep 12, 2019

Appears to have been fixed by #40523

@justinj justinj closed this as completed Sep 12, 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.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@justinj @yuzefovich