sql: CREATE TABLE ... AS in implicit transaction does not use the correct sessiondata

[Heoric]

Describe the problem

set time zone (and other session variables) are not respected when using CREATE TABLE ... AS

To Reproduce

root@localhost:26257/defaultdb> select current_time;
     current_time
----------------------
  08:56:41.935852+00
(1 row)


Time: 1ms total (execution 1ms / network 1ms)


-- set timezone
root@localhost:26257/defaultdb> set time zone -5;
SET


Time: 2ms total (execution 0ms / network 1ms)

-- implicit txn
root@localhost:26257/defaultdb> create table zone as select current_time;
CREATE TABLE AS


Time: 524ms total (execution 523ms / network 0ms)

root@localhost:26257/defaultdb> select * from zone;
     current_time
----------------------
  08:57:09.304304+00
(1 row)


Time: 69ms total (execution 68ms / network 0ms)



-- explicit txn
root@localhost:26257/defaultdb> begin;
BEGIN


Time: 0ms total (execution 1ms / network 0ms)

root@localhost:26257/defaultdb  OPEN> create table zone1 as select current_time;
CREATE TABLE AS


Time: 25ms total (execution 25ms / network 0ms)

root@localhost:26257/defaultdb  OPEN> select * from zone1;
     current_time
----------------------
  03:58:47.277947-05
(1 row)


Time: 3ms total (execution 3ms / network 0ms)

Jira issue: CRDB-11496

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for this report! I am pretty sure the problem is with CREATE TABLE ... AS specifically.

[rafiss]

I believe the problem is that the sessiondata is initialized to use defaults in this part of the schema changer

cockroach/pkg/sql/schema_changer.go

Lines 264 to 270 in 7c88d5b

	p, cleanup := NewInternalPlanner(
	desc,
	txn,
	security.RootUserName(),
	&MemoryMetrics{},
	sc.execCfg,
	sessiondatapb.SessionData{},

I'm not sure how the sessiondata can be plumbed down to here, so moving this over to sql-schema.

[Heoric]

alter table zone add column c time default current_time();

Also have this problem.

[ajwerner]

I'm inclined to move this over to @cockroachdb/bulk-io, though I have a feeling they'll be sad to have it. They have more knowledge about working with this sort of thing than anybody on schema.

[blathers-crl]

cc @cockroachdb/bulk-io

[dt]

Hm. Perhaps the SQL statements should refuse to create jobs if the session has non-default values, since they know that the job operates outside a session and thus won't match what the session would when evaluating some expressions?

[rafiss]

If the error could also include a hint that it would work as desired if you wrap it with BEGIN; ... COMMIT;, and we document a known limitation, then that might be an acceptable solution. Though potentially, that might mean rejecting all CTAS statements in implicit transactions, because of #73498

[ajwerner]

Though potentially, that might mean rejecting all CTAS statements in implicit transactions, because of #73498

We (somebody at CRL before me) wrote a lot of code to support CTAS in implicit transactions writing inside an async job.

[ajwerner]

All of these problems seem to equally affect CREATE MATERIALIZED VIEW

[ajwerner]

We could serialize the session data?

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!