sql: adding too many enum values may render tables using them inaccessible

[arulajmani]

Describe the problem

I have no clue why this is happening but this seems quite bad.

@ajwerner I ran into this when trying to test the byte representation stuff we were discussing about on #61406.

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

demo@127.0.0.1:26257/movr> CREATE TYPE typ AS ENUM('a');
CREATE TYPE

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

demo@127.0.0.1:26257/movr> CREATE TABLE tab(a typ, b typ[]);
CREATE TABLE

Time: 22ms total (execution 7ms / network 15ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'b' BEFORE 'a';
ALTER TYPE

Time: 29ms total (execution 5ms / network 24ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'c' BEFORE 'b';
ALTER TYPE

Time: 27ms total (execution 5ms / network 23ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'd' BEFORE 'c';
ALTER TYPE

Time: 27ms total (execution 5ms / network 22ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'e' BEFORE 'd';
ALTER TYPE

Time: 26ms total (execution 5ms / network 22ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'f' BEFORE 'e';
ALTER TYPE

Time: 40ms total (execution 5ms / network 35ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'g' BEFORE 'f';
ALTER TYPE

Time: 75ms total (execution 5ms / network 70ms)

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'h' BEFORE 'g';
ALTER TYPE

Time: 40ms total (execution 5ms / network 35ms)

demo@127.0.0.1:26257/movr> SELECT * FROM tab;
  a | b
----+----
(0 rows)

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

demo@127.0.0.1:26257/movr> ALTER TYPE typ ADD VALUE 'i' BEFORE 'h';
ALTER TYPE

Time: 67ms total (execution 5ms / network 63ms)

demo@127.0.0.1:26257/movr> SELECT * FROM tab;
ERROR: unsupported comparison operator: a IN (b'\x0080':::public.typ, b'\x01':::@100059, b'\x02':::@100059, b'\x04':::@100059, b'\b':::@100059, b'\x10':::@100059, b' ':::@100059, b'@':::@100059, b'\x80':::@100059): could not find [0 56 48] in enum "public.typ" representation PhysicalReps: [[0 128] [1] [2] [4] [8] [16] [32] [64] [128]]; LogicalReps: [i h g f e d c b a]
SQLSTATE: 22023
demo@127.0.0.1:26257/movr>

[rafiss]

random thought: Maybe it relates to the limit on number of times we can find a "midpoint" floating point number.

[ajwerner]

it's not a floating point number

[rafiss]

Oh I see now that it's bytes -- I was thinking of this but forgot the details:

cockroach/pkg/sql/enum/enum.go

Line 51 in 8187c25

func GenByteStringBetween(prev []byte, next []byte, spacing ByteSpacing) []byte {

[ajwerner]

I got to the bottom of this. It was a misunderstanding of b'' string literals. We can write regular string literals in the postgres format like this:

> SELECT '\x565656'::BYTES = 'VVV'::BYTES;
true

However, for our b'' literals, you need the \x escape per character:

> SELECT b'\x565656'::BYTES = 'VVV'::BYTES;
false
> SELECT b'\x565656'::BYTES = 'V5656'::BYTES;
true

We'd need to encode the enum as:

> SELECT b'\x56\x56\x56'::BYTES = 'VVV'::BYTES;
true

or

> SELECT b'VVV'::BYTES = 'VVV'::BYTES;
true

Instead, I'm going to make a change to encode it as:

> SELECT x'565656'::BYTES = 'VVV'::BYTES;
true