Bug #103872 Window function result type not consistent with schema
Submitted: 1 Jun 2021 7:40 Modified: 2 Jun 2021 8:09
Reporter: Zhifeng Hu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2021 7:40] Zhifeng Hu
Description:
The result of window function becomes LONG while the schema says BIT.

How to repeat:
schema:

CREATE TABLE `tbl_0` (
  `col_0` bit(8) DEFAULT NULL,
  `col_2` bit(1) NOT NULL,
  `col_39` double DEFAULT '9344.804442253544',
  `col_4` bit(1) NOT NULL DEFAULT b'0',
  `col_59` bit(1) DEFAULT NULL,
  PRIMARY KEY (`col_4`,`col_2`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `idx_1` (`col_2`,`col_4`),
  UNIQUE KEY `idx_2` (`col_4`,`col_0`,`col_59`,`col_39`,`col_2`),
  KEY `idx_3` (`col_2`,`col_0`,`col_39`),
  KEY `idx_4` (`col_4`,`col_2`,`col_59`),
  KEY `idx_5` (`col_2`,`col_4`),
  UNIQUE KEY `idx_6` (`col_2`,`col_59`,`col_4`,`col_0`),
  KEY `idx_7` (`col_39`,`col_2`,`col_4`,`col_0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

data:

insert into tbl_0 value (0xA4, 0x00, 9344.804442253544 , 0x00, 0x00);

query:

select   first_value( col_2 ) over w from tbl_0 window w as (partition by col_2 order by col_0,col_2,col_39,col_4,col_59 ) order by col_0,col_2,col_39,col_4,col_59 , first_value( col_39 ) over w limit 670 for update

result with --column-info

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]> select   first_value( col_2 ) over w from tbl_0 window w as (partition by col_2 order by col_0,col_2,col_39,col_4,col_59 ) order by col_0,col_2,col_39,col_4,col_59 , first_value( col_39 ) over w limit 670 for update
    -> ;
Field   1:  `first_value( col_2 ) over w`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      UNSIGNED NUM

+-----------------------------+
| first_value( col_2 ) over w |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.001 sec)

expect:

Database changed
MySQL [test]> select   first_value( col_2 ) over w from tbl_0 window w as (partition by col_2 order by col_0,col_2,col_39,col_4,col_59 ) order by col_0,col_2,col_39,col_4,col_59 , first_value( col_39 ) over w limit 670 for update
    -> ;
Field   1:  `first_value( col_2 ) over w`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BIT
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY MULTIPLE_KEY UNSIGNED NO_DEFAULT_VALUE

+-----------------------------+
| first_value( col_2 ) over w |
+-----------------------------+
|                             |
+-----------------------------+
1 row in set (0.001 sec)
[2 Jun 2021 8:09] MySQL Verification Team
Hello Zhifeng Hu,

Thank you for the report and test case.
Observed this with 8.0.25 build.

regards,
Umesh