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: bring EXPORT under the cost-based optimizer #33469

Closed
knz opened this issue Jan 3, 2019 · 3 comments · Fixed by #39312
Closed

sql: bring EXPORT under the cost-based optimizer #33469

knz opened this issue Jan 3, 2019 · 3 comments · Fixed by #39312
Assignees
@RaduBerinde
Labels
A-disaster-recovery A-sql-optimizer SQL logical planning and optimizations. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@knz
Copy link
Contributor

knz commented Jan 3, 2019

Currently the logic for EXPORT uses a weird interaction/hack because it's a CCL statement, and does not use the CBO to optimize its input query.

The EXPORT planning code must be amended to utilize the CBO, subject to the optimizer session setting.

The litmus test to verify that EXPORT indeed uses the CBO is support for correlated subqueries, for example

> create table t(x int); create table u(x int);
> export into csv 'nodelocal:///foo.csv' from select exists(select * from u where u.x = t.x) from t;

(currently fails)
@knz
Copy link
Contributor Author

knz commented Jan 3, 2019

cc @andy-kimball

@knz knz added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-disaster-recovery A-sql-optimizer SQL logical planning and optimizations. labels Jan 3, 2019
@rolandcrosby
Copy link

@adityamaru27 May not need to do the entire refactor but the same technique used in CTAS should work here.

@RaduBerinde RaduBerinde mentioned this issue Aug 4, 2019
Closed
@RaduBerinde RaduBerinde mentioned this issue Aug 4, 2019
Merged
RaduBerinde added a commit to RaduBerinde/cockroach that referenced this issue Aug 4, 2019
@RaduBerinde
opt: move EXPORT planning to the optimizer
1aa00da 
This change moves the planning of EXPORT to the optimizer. The
planning logic is fairly straightforward and doesn't need to be CCL.

EXPORT can now be used with EXPLAIN and PREPARE.

EXPORT can now only be used with the optimizer (the heuristic planner
will hopefully be removed soon altogether anyway).

One somewhat tricky aspect of the change is around the `KVOptions`
which have string expressions as values (in order to support
placeholders).

Fixes cockroachdb#33469.

Release note: None
RaduBerinde added a commit to RaduBerinde/cockroach that referenced this issue Aug 5, 2019
@RaduBerinde
opt: move EXPORT planning to the optimizer
8c4dc64 
This change moves the planning of EXPORT to the optimizer. The
planning logic is fairly straightforward and doesn't need to be CCL.

EXPORT can now be used with EXPLAIN and PREPARE.

EXPORT can now only be used with the optimizer (the heuristic planner
will hopefully be removed soon altogether anyway).

One somewhat tricky aspect of the change is around the `KVOptions`
which have string expressions as values (in order to support
placeholders).

Fixes cockroachdb#33469.

Release note: None
craig bot pushed a commit that referenced this issue Aug 5, 2019
@RaduBerinde
Merge #39312
60666bd 
39312: opt: move EXPORT planning to the optimizer r=RaduBerinde a=RaduBerinde

#### importccl: move export enterprise check to processor instantiation

This change moves the EXPORT license check from the planning code to
the execution part, when we instantiate the CSV Writer processor. The
planning code will move into the optimizer and making the check from
there is more awkward.

Release note: None

#### opt: move EXPORT planning to the optimizer

This change moves the planning of EXPORT to the optimizer. The
planning logic is fairly straightforward and doesn't need to be CCL.

EXPORT can now be used with EXPLAIN and PREPARE.

EXPORT can now only be used with the optimizer (the heuristic planner
will hopefully be removed soon altogether anyway).

One somewhat tricky aspect of the change is around the `KVOptions`
which have string expressions as values (in order to support
placeholders).

Fixes #33469.

Release note: None


Co-authored-by: Radu Berinde <[email protected]>
@craig craig bot closed this as completed in #39312 Aug 5, 2019
@knz
Copy link
Contributor Author

knz commented Aug 6, 2019

🎉 thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@RaduBerinde RaduBerinde

Labels
A-disaster-recovery A-sql-optimizer SQL logical planning and optimizations. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

opt: move EXPORT planning to the optimizer RaduBerinde/cockroach
4 participants
@rolandcrosby @knz @adityamaru27 @RaduBerinde