MySQL Bugs: #115597: Window function applied to BIT column produces non-BIT type

Bug #115597	Window function applied to BIT column produces non-BIT type
Submitted:	15 Jul 2024 16:46	Modified:	14 Nov 2024 17:45
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a BIT column is an argument of LEAD,LAG,FIRST/LAST/Nth_VALUE, or MAX,MIN (used as window functions), we can expect that these functions return BIT. After all, LEAD(X) means "tell me the value of X for next row".
But they return LONGLONG.

I suspect this is because of two things:
(1) window functions use tmp tables to store results
(2) tmp table code sometimes converts BIT input to an integer column.

Note that (2) also has bad effects without window functions, as tmp tables are used for derived tables, CREATE SELECT. You will find examples below.

And the (bad) result is:

+CREATE TABLE t0 (c0  bit(8));
+INSERT INTO t0 (c0) VALUES (65), (66);
+select hex(c0) from t0;
+hex(c0)
+41
+42
+select c0 from t0;
+c0
+A
+B
+select c0, ':', lead(c0) over() from t0;
+c0     :       lead(c0) over()
+A      :       66
+B      :       NULL
+select c0, ':', first_value(c0) over() from t0;
+c0     :       first_value(c0) over()
+A      :       65
+B      :       65
+select c0, ':', max(c0) over() from t0;
+c0     :       max(c0) over()
+A      :       66
+B      :       66
+select max(c0) from t0;
+max(c0)
+B
+set optimizer_switch="derived_merge=off";
+SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;
+c0
+65
+66
+set optimizer_switch="derived_merge=on";
+SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;
+c0
+A
+B
+CREATE TABLE t1 SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;
+desc t1;
+Field  Type    Null    Key     Default Extra
+c0     int unsigned    YES             NULL
+drop table t0,t1;

How to repeat:
CREATE TABLE t0 (c0  bit(8));
# insert ASCII code of 'A' (65) and 'B'
INSERT INTO t0 (c0) VALUES (65), (66);
select hex(c0) from t0;
select c0 from t0;

# Window functions return INT
select c0, ':', lead(c0) over() from t0;
select c0, ':', first_value(c0) over() from t0;
# MAX as window function
select c0, ':', max(c0) over() from t0;

# MAX as non-window aggregate function, returns BIT
select max(c0) from t0;

# Now no window functions; a derived table:

# materialized: INT
set optimizer_switch="derived_merge=off";
SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;
# not materialized: BIT
set optimizer_switch="derived_merge=on";
SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;

# And now CREATE SELECT: INT

CREATE TABLE t1 SELECT c0 FROM (SELECT nullif(c0,1) FROM t0) t0 (c0) ORDER BY c0;
desc t1;

drop table t0,t1;

Salut Monsieur Bichot,

Merci pour votre rapport.

We managed to repeat your report:

+---------+
| hex(c0) |
+---------+
| 41      |
| 42      |
+---------+
+------+
| c0   |
+------+
| A    |
| B    |
+------+
+------+---+-----------------+
| c0   | : | lead(c0) over() |
+------+---+-----------------+
| A    | : |              66 |
| B    | : |            NULL |
+------+---+-----------------+
+------+---+------------------------+
| c0   | : | first_value(c0) over() |
+------+---+------------------------+
| A    | : |                     65 |
| B    | : |                     65 |
+------+---+------------------------+
+------+---+----------------+
| c0   | : | max(c0) over() |
+------+---+----------------+
| A    | : |             66 |
| B    | : |             66 |
+------+---+----------------+
+---------+
| max(c0) |
+---------+
| B       |
+---------+
+------+
| c0   |
+------+
|   65 |
|   66 |
+------+
+------+
| c0   |
+------+
| A    |
| B    |
+------+
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c0    | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

This is now a verified bug for the version 9.0.

This bug is the original bug for the following report:

https://bugs.mysql.com/bug.php?id=116523

BUG#116523 is a duplicate of this bug.

Documented fix as follows in the MySQL 9.2.0 changelog:

    Window functions having BIT values as arguments did not return
    BIT.

Closed.

Thank you , Jon.