Added support for star-expanding tuples and numeric tuple indexing#7298
Added support for star-expanding tuples and numeric tuple indexing#7298ericharmeling merged 1 commit intomasterfrom
Conversation
|
This change is |
v20.1/scalar-expressions.md
Outdated
| CockroachDB also supports expanding all elements of a tuple as a single row with the `.*` notation. For example: | ||
| {% include copy-clipboard.html %} | ||
| ~~~ sql | ||
| > SELECT (t).* FROM (SELECT (1,'b',2.3) AS t) |
There was a problem hiding this comment.
maybe use an example where the values in the table are named, that will show how the column names are derived from the tuple labels
ericharmeling
left a comment
There was a problem hiding this comment.
Reviewable status:
complete! 0 of 0 LGTMs obtained (waiting on @knz)
v20.1/scalar-expressions.md, line 742 at r1 (raw file):
Previously, knz (kena) wrote…
maybe use an example where the values in the table are named, that will show how the column names are derived from the tuple labels
That does sound useful. I'm not finding the syntax on naming individual values in a tuple. Could you provide an example?
knz
left a comment
There was a problem hiding this comment.
Reviewable status:
v20.1/scalar-expressions.md, line 742 at r1 (raw file):
SELECT (t).* FROM (SELECT (1,'b',2.3) AS t)That does sound useful. I'm not finding the syntax on naming individual values in a tuple. Could you provide an example?
Two things:
- my comment was suggesting to use a table-generated tuple to explain this. Here's an example:
> create table t(x int, y char);
> select (t).* from t;or another example, based on yours:
> SELECT (t).* FROM (SELECT (1,'b',2.3) AS t(x,y,z))- your question makes me realize that cockroachdb also enables the user to label their tuples, but we haven't documented that yet! (The reason why is probably because it's very esoteric and probably not very useful in practice). For your entertainment:
> select ((1,'b') as a,b); -- here's a labeled tuple for you
> select pg_typeof( ((1,'b') as a,b) ); -- explains the tuple and its labels
> select (@1).* from (select ((1,'b') as a,b)); -- produces two columns 'a', 'b', as per the section documented in this PR
ericharmeling
left a comment
There was a problem hiding this comment.
Reviewable status:
v20.1/scalar-expressions.md, line 742 at r1 (raw file):
Awesome. Thank you!
A few follow-up questions:
- I'm not seeing how that first example
CREATE TABLEstatement (create table t(x int, y char);) differs from a normalCREATE TABLEstatement. Do allCREATE TABLEstatements generate tuples from their column definitions? Also, I'm getting an error when trying to select from that table as if it were a tuple:
> create table t(x int, y char);
CREATE TABLE
> select (t).* from t;
ERROR: column "t" does not exist
SQLSTATE: 42703
- That second example
SELECTstatement returns an error for me:
> SELECT (t).* FROM (SELECT (1,'b',2.3) AS t(x,y,z));
invalid syntax: statement ignored: at or near "(": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
SELECT (t).* FROM (SELECT (1,'b',2.3) AS t(x,y,z))
^
HINT: try \h <SOURCE>
- Less related to the examples you listed, but significantly, I realized that I was running the original example
SELECTstatement (SELECT (t).* FROM (SELECT (1,'b',2.3) AS t);) on a build frommasterthat I built 2020/05/12. On the v20.1.0 stable release version available at https://binaries.cockroachdb.com/cockroach-v20.1.0.darwin-10.9-amd64.tgz (CockroachDB CCL v20.1.0 (x86_64-apple-darwin14, built 2020/05/05 00:01:50, go1.13.9)), the output for that statement is different (and likely unexpected):
> SELECT (t).* FROM (SELECT (1,'b',2.3) AS t);
?column? | ?column? | ?column?
-----------+----------+-----------
1 | 1 | 1
(1 row)
The other example statement in this PR works as expected on the stable v20.1.0 release.
knz
left a comment
There was a problem hiding this comment.
Reviewable status:
v20.1/scalar-expressions.md, line 742 at r1 (raw file):
Thanks for double-checking. I was wrong. Serves me well for not checking my examples myself (I was serving from memory).
Let's rewind:
> create table t(x int, y char);
> select (t).* from t;This one is valid in pg but not yet supported in crdb. I had preferred if we could use it as an example here, but alas we cannot (yet).
> SELECT (t).* FROM (SELECT (1,'b',2.3) AS t(x,y,z);That was wrong syntax; I really meant this:
> SELECT (t).* FROM (SELECT (1,'b',2.3)) AS t(x,y,z);However again we're runnign into the problem above, that (t).* does not expand if t points to a table.
However, the following works:
> WITH tuples AS (SELECT (t.*) AS tuple FROM (SELECT 1,'b',2.3) AS t(x,y,z)) -- this builds the tuples, with labels
SELECT (tuple).* FROM tuples; -- this expands the tuples and restores the column labelsThe argument here is that the notation (t.*) creates tuples and (t).* is its inverse. Just explaining that structuring operators have inverses (like we have for JSON) too is of general interest, I think.
as to your last example:
> SELECT (t).* FROM (SELECT (1,'b',2.3) AS t);that's valid and expected. The syntax (1,'b',2.3) AS t creates a label-less tuple column called t. So (t).* expands it into label-less columns.
|
@ericharmeling, @knz, what's left to do on this PR? |
c744566 to
0a81e0d
Compare
ericharmeling
left a comment
There was a problem hiding this comment.
Sorry for the delayed response on this. I updated the PR, following your latest comment. Please re-review - no rush. Thanks!
Reviewable status:
knz
left a comment
There was a problem hiding this comment.
Reviewed 1 of 1 files at r2.
Reviewable status:
|
@lnhsingh Ping on review :) |
lnhsingh
left a comment
There was a problem hiding this comment.
Sorry for the delay! LGTM
Reviewable status:
|
@lnhsingh No worries! TFTR |
Fixes #7427. Summary of changes: - Create a new v20.2 folder, and copy the 20.1 content to it; delete the various "new or changed in v20.1" callouts from the copied files - Copy includes to a new '_includes/v20.2' folder. - Copy images to a new 'images/v20.2' folder. - Edit image link text in the referencing documents as needed to point to the right places. - Update the various YAML config files with the new version and release info. - Create a new sidebar JSON file for v20.2. - Do *not* copy over anything related to CockroachCloud for v20.2. This includes editing page frontmatter to remove build directives that pull in certain pages for CockroachCloud. - Update the CockroachDB local YAML config to exclude 19.2 from the build process (this file is used to speed up local builds). - Various edits to the 'Upgrade to CockroachDB v20.2' docs, changing version references as appropriate, and removing some version-specific limitations. - Update the base YAML config to use more specific version information as required by templates in the `install-cockroachdb-linux` etc. pages - Miscellaneous edits to CLI output as appropriate. - Backports: Add Eric's 'scalar expressions' update from #7298; port forward Amruta's changes from #7479.
Fixes #7427. Summary of changes: - Create a new v20.2 folder, and copy the 20.1 content to it; delete the various "new or changed in v20.1" callouts from the copied files - Copy includes to a new '_includes/v20.2' folder. - Copy images to a new 'images/v20.2' folder. - Edit image link text in the referencing documents as needed to point to the right places. - Update the various YAML config files with the new version and release info. - Create a new sidebar JSON file for v20.2. - Do *not* copy over anything related to CockroachCloud for v20.2. This includes editing page frontmatter to remove build directives that pull in certain pages for CockroachCloud. - Update the CockroachDB local YAML config to exclude 19.2 from the build process (this file is used to speed up local builds). - Various edits to the 'Upgrade to CockroachDB v20.2' docs, changing version references as appropriate, and removing some version-specific limitations. - Update the base YAML config to use more specific version information as required by templates in the `install-cockroachdb-linux` etc. pages - Miscellaneous edits to CLI output as appropriate. - Backports: Add Eric's 'scalar expressions' update from #7298; port forward Amruta's changes from #7479. - Finally: Add stub release notes page for v20.2.0-alpha.1, which will be filled in by #7426.
Fixes #7427. Summary of changes: - Create a new v20.2 folder, and copy the 20.1 content to it; delete the various "new or changed in v20.1" callouts from the copied files - Copy includes to a new '_includes/v20.2' folder. - Copy images to a new 'images/v20.2' folder. - Edit image link text in the referencing documents as needed to point to the right places. - Update the various YAML config files with the new version and release info. - Create a new sidebar JSON file for v20.2. - Do *not* copy over anything related to CockroachCloud for v20.2. This includes editing page frontmatter to remove build directives that pull in certain pages for CockroachCloud. - Update the CockroachDB local YAML config to exclude 19.2 from the build process (this file is used to speed up local builds). - Various edits to the 'Upgrade to CockroachDB v20.2' docs, changing version references as appropriate, and removing some version-specific limitations. - Update the base YAML config to use more specific version information as required by templates in the `install-cockroachdb-linux` etc. pages - Miscellaneous edits to CLI output as appropriate. - Backports: Add Eric's 'scalar expressions' update from #7298; port forward Amruta's changes from #7479. - Finally: Add stub release notes page for v20.2.0-alpha.1, which will be filled in by #7426.
Fixes #6910.