Unexpected result when casting negative integer to `BIT`

[suyZhong]

What happens?

Consider the following test case. The third query should return an empty result, as CAST(CAST(t1.c1 AS BIT) AS INTEGER) is evaluated to -1 and the expression should be false. However, the query returns -1, which is unexpected.

I found in PostgreSQL, the second query would be evaluated as true. Either the second or the third query is unexpected.

To Reproduce

CREATE TABLE  t1( c1 INTEGER);
INSERT INTO t1(c1) VALUES (-1);

SELECT t1.c1 FROM t1; -- -1
SELECT (1 BETWEEN -1 AND CAST(CAST(t1.c1 AS BIT) AS INTEGER)) FROM t1; -- false
SELECT t1.c1 FROM t1 WHERE (1 BETWEEN -1 AND CAST(CAST(t1.c1 AS BIT) AS INTEGER));
-- Expected: empty
-- Actual: -1

OS:

ubuntu 22.04

DuckDB Version:

v0.0.1 367aa8d

DuckDB Client:

CLI

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 nightly 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

[soerenwolfers]

This does return an empty result on '1.0.1-dev5078' (and likely much earlier). 0.0.1 is quite old.

EDIT: Maybe you have a typo in your post, since you're linked commit is actually more recent than 1.0.1-dev5078. In that case, ignore my comment.

[suyZhong]

I just copied the version from the DuckDB CLI. Something like this:

./duckdb 
v0.0.1 367aa8d
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D 

I downloaded the nightly build through this link https://artifacts.duckdb.org/latest/duckdb-binaries-linux.zip according to the installation page. Not sure if there's a typo issue in the CLI.

[carlopi]

Thanks for raising this side quest about v0.0.1: discussion and fix is here #13730.

---

On the original issue, I have on latest nightly:

v1.0.1-dev5143 1e883cd4d8
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D CREATE TABLE  t1( c1 INTEGER);
D INSERT INTO t1(c1) VALUES (-1);
D 
D SELECT t1.c1 FROM t1; -- -1
┌───────┐
│  c1   │
│ int32 │
├───────┤
│    -1 │
└───────┘
D SELECT (1 BETWEEN -1 AND CAST(CAST(t1.c1 AS BIT) AS INTEGER)) FROM t1; -- false
┌────────────────────────────────────────────────────────┐
│ (1 BETWEEN -1 AND CAST(CAST(t1.c1 AS BIT) AS INTEGER)) │
│                        boolean                         │
├────────────────────────────────────────────────────────┤
│ false                                                  │
└────────────────────────────────────────────────────────┘
D SELECT t1.c1 FROM t1 WHERE (1 BETWEEN -1 AND CAST(CAST(t1.c1 AS BIT) AS INTEGER));
┌────────┐
│   c1   │
│ int32  │
├────────┤
│ 0 rows │
└────────┘

So possibly this has been solved in the mean time?

[carlopi]

Actually solved in #13595