Unexpected result when comparing BLOB #14567
Description
What happens?
Consider the following test case generated by my fuzzer. The query returns 33 rows; however, the expression (t1.c0)>=(CAST(t0.c0 AS BLOB)) is evaluated to false for three of the rows returned, which I suppose is unexpected.
To Reproduce
CREATE TABLE t1(c0 BLOB);
CREATE TABLE t0(c0 BIT);
CREATE VIEW v0(c0) AS SELECT 1 FROM t1, t0 GROUP BY t0.c0;
INSERT INTO t0(c0) VALUES ( NULL);
INSERT INTO t0(c0) VALUES (0);
INSERT INTO t0(c0) VALUES ( 1);
INSERT INTO t1(c0) VALUES (X'41');
INSERT INTO t1(c0) VALUES ( X'123456');
INSERT INTO t1(c0) VALUES ('2119350449');
INSERT INTO t1(c0) VALUES ( X'48656C6C6F');
INSERT INTO t1(c0) VALUES (E'\\xabcd');
INSERT INTO t1(c0) VALUES (CAST('' AS BLOB));SELECT t0.c0, t1.c0, (t1.c0)>=(CAST(t0.c0 AS BLOB)) FROM t0, v0, t1 WHERE ((t1.c0)>=(CAST(t0.c0 AS BLOB)));┌──────────────────────────────────┬─────────────┬────────────────────────────────┐
│ c0 │ c0 │ (t1.c0 >= CAST(t0.c0 AS BLOB)) │
│ bit │ blob │ boolean │
├──────────────────────────────────┼─────────────┼────────────────────────────────┤
│ 00000000000000000000000000000001 │ \xABcd │ true │
│ 00000000000000000000000000000001 │ x48656C6C6F │ true │
│ 00000000000000000000000000000001 │ x41 │ true │
│ 00000000000000000000000000000001 │ x123456 │ true │
│ 00000000000000000000000000000001 │ 2119350449 │ true │
│ 00000000000000000000000000000001 │ \xABcd │ true │
│ 00000000000000000000000000000001 │ x48656C6C6F │ true │
│ 00000000000000000000000000000001 │ x41 │ true │
│ 00000000000000000000000000000001 │ x123456 │ true │
│ 00000000000000000000000000000001 │ 2119350449 │ true │
│ 00000000000000000000000000000001 │ \xABcd │ true │
│ 00000000000000000000000000000001 │ x48656C6C6F │ true │
│ 00000000000000000000000000000001 │ x41 │ true │
│ 00000000000000000000000000000001 │ x123456 │ true │
│ 00000000000000000000000000000001 │ 2119350449 │ true │
│ 00000000000000000000000000000000 │ \xABcd │ true │
│ 00000000000000000000000000000000 │ x48656C6C6F │ true │
│ 00000000000000000000000000000000 │ x41 │ true │
│ 00000000000000000000000000000000 │ x123456 │ true │
│ 00000000000000000000000000000000 │ 2119350449 │ true │
│ 00000000000000000000000000000000 │ │ false │
│ 00000000000000000000000000000000 │ \xABcd │ true │
│ 00000000000000000000000000000000 │ x48656C6C6F │ true │
│ 00000000000000000000000000000000 │ x41 │ true │
│ 00000000000000000000000000000000 │ x123456 │ true │
│ 00000000000000000000000000000000 │ 2119350449 │ true │
│ 00000000000000000000000000000000 │ │ false │
│ 00000000000000000000000000000000 │ \xABcd │ true │
│ 00000000000000000000000000000000 │ x48656C6C6F │ true │
│ 00000000000000000000000000000000 │ x41 │ true │
│ 00000000000000000000000000000000 │ x123456 │ true │
│ 00000000000000000000000000000000 │ 2119350449 │ true │
│ 00000000000000000000000000000000 │ │ false │
├──────────────────────────────────┴─────────────┴────────────────────────────────┤
│ 33 rows 3 columns │
└─────────────────────────────────────────────────────────────────────────────────┘
OS:
Ubuntu 22.04
DuckDB Version:
v1.1.2-dev285 5090b73
DuckDB Client:
CLI
Hardware:
No response
Full Name:
Suyang Zhong
Affiliation:
NUS
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a source build
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have