sql: possibly incorrectly optimizing away one of the projections about cockroach HOT 5 OPEN

It seems that postgres requires an explicit cast for both:

yuzefovich=# CREATE TYPE debug_t AS ENUM('test1', 'test2');
CREATE TYPE
yuzefovich=# CREATE TABLE debug (                          
    id INT NOT NULL,
    enum_col debug_t DEFAULT NULL,
    bytes_col TEXT DEFAULT NULL,
    PRIMARY KEY(id)
);

INSERT INTO debug ("id", "enum_col", "bytes_col") VALUES (1, 'test1', 'some_bytes_yo');
CREATE TABLE
INSERT 0 1
yuzefovich=# UPDATE
        debug
SET
        enum_col = (CASE id WHEN 1 THEN NULL ELSE NULL END),
        bytes_col = (CASE id WHEN 1 THEN NULL ELSE NULL END)       
WHERE
        id IN (1);
ERROR:  column "enum_col" is of type debug_t but expression is of type text
LINE 4:  enum_col = (CASE id WHEN 1 THEN NULL ELSE NULL END),
                     ^
HINT:  You will need to rewrite or cast the expression.

However, I took a closer look at what CRDB does, and I'm a bit concerned about what I'm seeing. In particular, it seems that in your example we actually optimize away the second CASE expression (because two CASE expressions are exactly the same), so we try to use the single CASE expression that is typed as debug_t for both columns, and that fails. Adding an explicit cast to either expression makes them distinct, so both CASE expressions end up being used.

I'm wondering whether we shouldn't be applying this optimization rule to remove one of the seemingly-redundant expressions depending on the context. I'll check with my colleagues.

from cockroach.

It looks like this behavior might not be easy to change, and it has existed for a long time. Also since this behavior matches postgres, we're going to treat this as "enhancement", but it's unlikely we'll get to it soon.

from cockroach.

Hi @AllyMarthaJ, please add branch-* labels to identify which branch(es) this C-bug affects.

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

from cockroach.

Thanks for the report!

IIUC this is the expected behavior, and the error contains a hint on how to go around it:

ERROR: value type debug_t doesn't match type bytes of column "bytes_col"
SQLSTATE: 42804
HINT: you will need to rewrite or cast the expression

I think what happens is that we have two untyped expressions, both of them having NULL value, and we perform the type checking on the first one (get debug_t type) and automatically apply the same type to the second expression.

If we add an explicit cast to either of the expressions, then it works:

UPDATE
	debug
SET
	enum_col = (CASE id WHEN 1 THEN NULL ELSE NULL END)::debug_t,
	bytes_col = (CASE id WHEN 1 THEN NULL ELSE NULL END)
WHERE
	id IN (1);
-- or
UPDATE
	debug
SET
	enum_col = (CASE id WHEN 1 THEN NULL ELSE NULL END),
	bytes_col = (CASE id WHEN 1 THEN NULL ELSE NULL END)::BYTES
WHERE
	id IN (1);

I'll close this as behaving as expected -- cc @cockroachdb/sql-queries in case my understanding of type checking is incorrect.

from cockroach.

Hmm, that makes sense as an explanation, but I fail to understand why applying the same type to the second expression is expected behaviour! Especially since that the cast needn't happen on the entire expression — just one of the values inside a when is sufficient.

Ideally I wouldn't have to cast either expression, but maybe my understanding of types in the context of postgres/crdb is wrong.

Thanks for taking a look regardless 🙂

from cockroach.