sql: added a NOTICE that a table created using CREATE TABLE AS does not inherit the primary key

Dedej-Bergin commented 1 day ago

Previously we had no notice when the user runs CREATE TABLE ... AS statement. Now we have a notice making the user aware of it's limitations.

Fixes: #131675 Release note (sql change): Added an informational notice to the result of CREATE TABLE ... AS statements that describes that indexes and constraints are not copied to the new table.

cockroach-teamcity commented 1 day ago

This change is

Dedej-Bergin commented 19 hours ago

Wanted to point out that for my logic test I initially tried using query T noticetrace like below but it was giving me a test failure.

query T noticetrace
CREATE TABLE t2 AS SELECT * FROM t1;
----
NOTICE: CREATE TABLE ... AS does not copy overindexes, default expressions, or constraints; the new tablehas a hidden rowid primary key column

This also failed:

query T noticetrace
CREATE TABLE t2 AS SELECT * FROM t1;
----
NOTICE: setting transaction isolation level to SERIALIZABLE due to schema change
NOTICE: CREATE TABLE ... AS does not copy overindexes, default expressions, or constraints; the new tablehas a hidden rowid primary key column

Gives me the below error: // can repro this by running ./dev testlogic --files=create_table

=== RUN   TestReadCommittedLogic_create_table/create_table_as_notice
   logic.go:3049:

       /var/lib/engflow/worker/work/1/exec/bazel-out/k8-fastbuild/bin/pkg/ccl/logictestccl/tests/local-read-committed/local-read-committed_test_/local-read-committed_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/create_table:1126: CREATE TABLE t2 AS SELECT * FROM t1;
       expected:
           NOTICE: CREATE TABLE ... AS does not copy overindexes, default expressions, or constraints; the new tablehas a hidden rowid primary key column

       but found (query options: "noticetrace") :
           NOTICE: setting transaction isolation level to SERIALIZABLE due to schema change
           NOTICE: CREATE TABLE ... AS does not copy overindexes, default expressions, or constraints; the new tablehas a hidden rowid primary key column

[21:14:54] --- done: /var/lib/engflow/worker/work/1/exec/bazel-out/k8-fastbuild/bin/pkg/ccl/logictestccl/tests/local-read-committed/local-read-committed_test_/local-read-committed_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/create_table with config local-read-committed: 174 tests, 1 failures
[21:14:56] --- total progress: 174 statements
--- total: 174 tests, 1 failures
   --- FAIL: TestReadCommittedLogic_create_table/create_table_as_notice (0.06s)

rafiss commented 17 hours ago

oh, i missed this message earlier:

Wanted to point out that for my logic test I initially tried using query T noticetrace like below but it was giving me a test failure.

To avoid that issue, we can skip this test under configurations that use a weaker isolation level:

# Ignore weaker isolation levels, since that will cause additional NOTICEs that we don't want to check in this test.
skipif config weak-iso-level-configs
query T noticetrace
CREATE TABLE t2 AS SELECT * FROM t1;

Dedej-Bergin commented 15 hours ago

bors r+

craig[bot] commented 14 hours ago

Build succeeded:

cockroachdb / cockroach

sql: added a NOTICE that a table created using CREATE TABLE AS does not inherit the primary key #135845