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.

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

ALTER COLUMN TYPE to lower integer range (Ex int8 to int2) should fail #54844

Closed
vy-ton opened this issue Sep 27, 2020 · 6 comments · Fixed by #55095
Closed

ALTER COLUMN TYPE to lower integer range (Ex int8 to int2) should fail #54844

vy-ton opened this issue Sep 27, 2020 · 6 comments · Fixed by #55095

Comments

@vy-ton
Copy link
Contributor

vy-ton commented Sep 27, 2020

Steps to reproduce

root@127.0.0.1:55511/defaultdb> create table foo (i int8);
CREATE TABLE

root@127.0.0.1:55511/defaultdb> insert into foo values (-9223372036854775807);
INSERT 1

root@127.0.0.1:55511/defaultdb> SET enable_experimental_alter_column_type_general = true;

root@127.0.0.1:55511/defaultdb> alter table foo alter column i type int2;
NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
ALTER TABLE

root@127.0.0.1:55511/defaultdb> show create foo;
  table_name |        create_statement
-------------+----------------------------------
  foo        | CREATE TABLE public.foo (
             |     i INT2 NULL,
             |     FAMILY "primary" (i, rowid)
             | )
(1 row)

Desired behavior
Our docs indicate that only increasing precision is allowed and that the existing data must remain valid. This ALTER COLUMN TYPE violates that.

I realized I was looking at 20.1 docs, but there's no mention in 20.2 docs that this would be valid.

In PG,

vy=# alter table foo alter column i type int2;
ERROR:  smallint out of range
@vy-ton vy-ton added this to Triage in SQL Foundations via automation Sep 27, 2020
@blathers-crl
Copy link

blathers-crl bot commented Sep 27, 2020

Hi @vy-ton, please add a C-ategory label to your issue. Check out the label system docs.

While you're here, please consider adding an A- label to help keep our repository tidy.

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

@thoszhang thoszhang removed this from Triage in SQL Foundations Sep 29, 2020
@otan
Copy link
Contributor

otan commented Oct 1, 2020

hehe, yeah it's broken because we don't validate precisions on casts....


demo@127.0.0.1:58266/defaultdb> select '-9223372036854775807'::int::smallint;
          int2
------------------------
  -9223372036854775807
(1 row)

@otan
Copy link
Contributor

otan commented Oct 1, 2020

cc @solongordon i think this may be a release blocker as it puts invalid data into the table.
i have a fix in #55095.

@otan otan moved this from Triage to 20.2 stability in SQL Features (Deprecated - use SQL Experience board) Oct 2, 2020
@solongordon solongordon added this to Triage in SQL Sessions - Deprecated via automation Nov 12, 2020
@solongordon solongordon moved this from Triage to November 2020 in SQL Sessions - Deprecated Nov 12, 2020
@solongordon
Copy link
Contributor

As discussed separately, we deemed @otan's fix too risky for 20.2 and would like to disable such ALTER operations in 20.2.1. @otan, would you please make this change? Then on master, we can merge the fix and re-enable these operations.

@z0mb1ek
Copy link

z0mb1ek commented May 7, 2021

Hi. In what version it will be enable once again?

@vy-ton
Copy link
Contributor Author

vy-ton commented May 7, 2021

@z0mb1ek This will be enabled in 21.1. You can check out the latest 21.1 beta release or wait for GA release which is coming very soon

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
No open projects
Development

Successfully merging a pull request may close this issue.

4 participants