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