MySQL Bugs: #107501: Zero-length SUBSTRING of a LONGBLOB returns a str

Bug #107501	Zero-length SUBSTRING of a LONGBLOB returns a str
Submitted:	7 Jun 2022 12:03	Modified:	27 Jul 2022 13:37
Reporter:	Szymon Ka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
Value of zero-length SUBSTRING taken on binary (LONGBLOB) column is of str type.

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

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_unicode': 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'test'
cursor.execute('INSERT INTO tbl VALUES(%s)', (in_val,))
cursor.execute('SELECT SUBSTRING(c1, 1, 0) FROM tbl')
res = cursor.fetchone()
print(type(res[0]))
assert type(res[0]) == bytearray

Suggested fix:
To keep compatibility with 8.0.28 release of mysql-connector-python (and 8.0.29 in pure mode), the returned type should be bytearray. However, please note that the return type for 'SELECT c1 FROM tbl' is bytes. Was it intended to return different type when using SUBSTRING?

Hello Szymon Ka,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
This bug was fixed by BUG#34283402, when binary data starting with 0x00 is returned as empty string.
Thank you for your bug report.