Bug #114333 Lead/lag window functions returning BLOB instead of VARCHAR
Submitted: 13 Mar 10:50 Modified: 13 Mar 11:26
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: window function

[13 Mar 10:50] Pedro Ferreira
Connect the client with the `--column-type-info` option for detailed information in the result. Run:

SELECT lag(t0.c0) OVER () FROM t0; --outputs BLOB

The same happens with the `lead` window function. The default value for this case is NULL, which should be within the VARCHAR range.

Is this an issue?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the statements above.
[13 Mar 11:26] MySQL Verification Team
Hi Mr. Ferreira,

Thank you for your bug report.

This proves to be a bug in the code and in the documentation.

However, this is a very small bug.

It is just a tiny bug in the calculation of the length of the string attribute in the tuple.

String function will return BLOB for the lengths larger then 2048 bytes.

However, LAG and LEAD return log values, even when there is a single row with 10 characters inserted:

SELECT lag(t0.c0) OVER () FROM t0;
Field   1:  `lag(t0.c0) OVER ()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  utf8mb4_0900_ai_ci (255)
Length:     8208
Max_length: 0
Decimals:   0
Flags:      BLOB

Length simply can not be 8208 ...... 

It impacts version 8.0 and all higher versions. It is repeatable on all platforms.

Verified as reported and as repeated with added analysis.

Thank you.