Bug #107502 Output of SUBSTRING on LONGBLOB column truncated if starts with NULL byte
Submitted: 7 Jun 2022 12:14 Modified: 21 Jul 2022 12:03
Reporter: Szymon Ka Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.29 OS:Ubuntu
Assigned to: CPU Architecture:x86

[7 Jun 2022 12:14] Szymon Ka 
Description:
When SUBSTRING is used on a LONGBLOB column and the starting position points to a NULL byte, empty sequence is returned. 

mysql-connector-python version: 8.0.29 - installed via pip from a binary distribution (.whl)
Python: 3.7

It works as expected on 8.0.28 and on 8.0.29 with pure mode.

How to repeat:
import mysql.connector

db_conn_parameters = {
    'host': '127.0.0.1',
    'database': 'test',
    'user': 'root',
    'password': '',
    'ssl_disabled': True,
    'charset': 'utf8',
    'use_uicode': True,
    'use_pure': False,
    'sql_mode': 'TRADITIONAL',
    'autocommit': True,
}

conn = mysql.connector.connect(**db_conn_parameters)
cursor = conn.cursor()
cursor.execute('DROP TABLE IF EXISTS tbl')
cursor.execute('CREATE TABLE tbl(c1 LONGBLOB)')

in_val = b'\x01\x01\x00'
cursor.execute('INSERT INTO tbl VALUES(%s)', (in_val,))
cursor.execute('SELECT SUBSTRING(c1, 3, 1) FROM tbl')
res = cursor.fetchone()
print(len(res[0]))  # prints "0" on 8.0.29
assert len(res[0]) == 1
[7 Jun 2022 14:58] MySQL Verification Team 
Hello Szymon Ka,

Thank you for the report.

regards,
Umesh
[21 Jul 2022 12:02] Nuno Mariz 
Posted by developer:
 
Fixed by BUG#34283402.
Thank you for the bug report.