Repository: duckdb/duckdb (language: python)

Issue:
Inconsistent results when casting `BIT` and `VARBINARY`
### What happens?

Consider the following test case generated by my fuzzer. The second query and the third query could have inconsistent results. I suppose the second query should be consistently `true,true` and the third query should thus be `ab,cd`. 

### To Reproduce

```sql
CREATE TABLE  t1(c0 VARBINARY NOT NULL);
INSERT INTO t1(c0) VALUES ('ab'), ('cd');
-- INSERT INTO t1(c0) VALUES ('🦆'); -- error
-- INSERT INTO t1(c0) VALUES (NULL); -- error

SELECT t1.c0 FROM t1; -- 'ab', 'cd'
SELECT ((CAST(CAST(t1.c0 AS BIT) AS VARBINARY))<=(t1.c0)) FROM t1; -- true, true (incosistent, could sometimes be false, false)
SELECT t1.c0 FROM t1 WHERE ((CAST(CAST(t1.c0 AS BIT) AS VARBINARY))<=(t1.c0));
-- Expected: 'ab', 'cd' (2 rows)
-- Actual: could be 0 row, 1 row or 2 rows
```

Not sure if the two `INSERT` are needed. Sometimes I could also reproduce it without executing them, but sometimes can't.

Kindly inform me if it's an expected behavior and whether you could reproduce the issue.

### OS:

ubuntu 22.04

### DuckDB Version:

v1.1.1-dev100 9af117f0e6

### 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?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
The use of `VARBINARY` confused me a bit, `BLOB` is more commonly used for this type
Also `col::BIT` can be used in place of `CAST(col AS BIT)` to make this easier to read, especially with multiple casts taking place

Fix patch:
diff --git a/src/common/types/bit.cpp b/src/common/types/bit.cpp
index ebd4ec3ac6e8..f263c2c42975 100644
--- a/src/common/types/bit.cpp
+++ b/src/common/types/bit.cpp
@@ -180,7 +180,7 @@ void Bit::BitToBlob(string_t bit, string_t &output_blob) {
 	idx_t size = output_blob.GetSize();
 
 	output[0] = UnsafeNumericCast<char>(GetFirstByte(bit));
-	if (size > 2) {
+	if (size >= 2) {
 		++output;
 		// First byte in bitstring contains amount of padded bits,
 		// second byte in bitstring is the padded byte,


Test patch:
diff --git a/test/sql/cast/test_bit_cast.test b/test/sql/cast/test_bit_cast.test
index 421ce022e688..76582943af74 100644
--- a/test/sql/cast/test_bit_cast.test
+++ b/test/sql/cast/test_bit_cast.test
@@ -196,6 +196,16 @@ SELECT '1111'::BIT::BLOB;
 ----
 \x0F
 
+query I
+select 'ab'::BLOB::BIT::BLOB;
+----
+ab
+
+query I
+select 'a'::BLOB::BIT::BLOB;
+----
+a
+
 query I
 SELECT bitstring('1111', 33)::BLOB;
 ----