Bug #102602 Newer versions falsely return None type
Submitted: 15 Feb 2021 15:43 Modified: 23 Feb 2021 15:53
Reporter: Hans-Peter Frey Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:> 8.0.6 OS:MacOS
Assigned to: CPU Architecture:x86
Tags: ifnull, None type

[15 Feb 2021 15:43] Hans-Peter Frey
Description:
When querying "SELECT IFNULL(MAX(id), 0) FROM TABLE", new versions of mysql-connector-python will return 'None' instead of 0, if there is no id in the table.

The latest version of mysql-connector-python for which this still works correctly is 8.0.6.

How to repeat:
Running the above-mentioned query on an empty MySQL / MariaDB table with the column id.
[16 Feb 2021 12:56] MySQL Verification Team
Hello Hans-Peter Frey,

Thank you for the report and feedback.
I tried to reproduce this issue at my end but not seeing as reported. Could you please provide a sample test case (python script, exact table CREATE statement, MySQL server version details etc) to reproduce this issue? Thank you.

- 
## dummy n empty table table 
create table bug102602(id int not null);

- VS 2019, Python 3.7

import mysql.connector
import sys
import platform

print("OS: {} {}".format(platform.system(), platform.release()))
print("Python:", format(sys.version))
driver  = mysql.connector
print("Driver: {} {}".format(driver.__name__, driver.__version__))
print()

dbCon = mysql.connector.connect(host="localhost", port=3306, user="root", passwd="", database="bug")
dbCur = dbCon.cursor(buffered=True, dictionary=True)

dbCur.execute("SELECT IFNULL(MAX(id), 0) FROM bug102602;")
row = dbCur.fetchall()

for x in row:
  print(x)

dbCur.close()
dbCon.close()
-----------------------------------------------

OS: Windows 10
Python: 3.7.8 (tags/v3.7.8:4b47a5b6ba, Jun 28 2020, 08:53:46) [MSC v.1916 64 bit (AMD64)]
Driver: mysql.connector 8.0.23

{'IFNULL(MAX(id), 0)': 0}
Press any key to continue . . .

regards,
Umesh
[23 Feb 2021 15:53] Hans-Peter Frey
It turns out that I made that query on a MariaDB database and not on a MySQL (we have both types of databases in the company).
I guess this explains the issue.
Thank you very much for you help!