Description:
When using JDBC to connect to MySQL and executing the following SQL queries, an unexpected non-empty result is returned from the intersection of two logically opposite queries, revealing a potential logic bug. The issue arises from comparing values using the LIKE and NOT LIKE operators. Specifically, the intersection between the results of t0.c0 LIKE t1.c0 and t0.c0 NOT LIKE t1.c0 should be empty by definition, yet the query returns data, indicating a flaw in how MySQL handles comparisons involving TINYTEXT and implicit type conversions.
CREATE TABLE t0(c0 TINYTEXT) ;
CREATE TABLE t1 LIKE t0;
INSERT INTO t1(c0) VALUES(16),("⓰");
INSERT INTO t0(c0) VALUES(16);
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0; -- {16, 16}
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; -- {16, ⓰}
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0
INTERSECT
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; -- {16, 16}
How to repeat:
python test.py,and the test.py is as follow:
import mysql.connector
from mysql.connector import Error
sql_commands = """
CREATE DATABASE IF NOT EXISTS fuzz;
USE fuzz;
DROP TABLE IF EXISTS t0, t1;
CREATE TABLE t0(c0 TINYTEXT) ;
CREATE TABLE t1 LIKE t0;
INSERT INTO t1(c0) VALUES(16),("⓰");
INSERT INTO t0(c0) VALUES(16);
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0;
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0;
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0
INTERSECT
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0;
"""
if __name__ == '__main__':
db_config = {
'host': 'localhost',
'database': 'mysql',
'user': 'root',
'password': '1213',
'port': 3306,
'charset': 'utf8mb4',
'collation': 'utf8mb4_unicode_ci'
}
try:
connection = mysql.connector.connect(**db_config)
if connection.is_connected():
print("Connected to MySQL database")
cursor = connection.cursor()
try:
for statement in sql_commands.split(';'):
if statement.strip():
cursor.execute(statement)
print(f"Executed: {statement}")
if statement.strip().upper().startswith('SELECT'):
results = cursor.fetchall()
print("Query results:")
for row in results:
print(row)
connection.commit()
except Error as e:
print(f"SQL execution error: {e}")
connection.rollback()
finally:
cursor.close()
except Error as e:
print(f"Connection error: {e}")
finally:
if 'connection' in locals() and connection.is_connected():
connection.close()
print("Connection closed")
Description: When using JDBC to connect to MySQL and executing the following SQL queries, an unexpected non-empty result is returned from the intersection of two logically opposite queries, revealing a potential logic bug. The issue arises from comparing values using the LIKE and NOT LIKE operators. Specifically, the intersection between the results of t0.c0 LIKE t1.c0 and t0.c0 NOT LIKE t1.c0 should be empty by definition, yet the query returns data, indicating a flaw in how MySQL handles comparisons involving TINYTEXT and implicit type conversions. CREATE TABLE t0(c0 TINYTEXT) ; CREATE TABLE t1 LIKE t0; INSERT INTO t1(c0) VALUES(16),("⓰"); INSERT INTO t0(c0) VALUES(16); SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0; -- {16, 16} SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; -- {16, ⓰} SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0 INTERSECT SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; -- {16, 16} How to repeat: python test.py,and the test.py is as follow: import mysql.connector from mysql.connector import Error sql_commands = """ CREATE DATABASE IF NOT EXISTS fuzz; USE fuzz; DROP TABLE IF EXISTS t0, t1; CREATE TABLE t0(c0 TINYTEXT) ; CREATE TABLE t1 LIKE t0; INSERT INTO t1(c0) VALUES(16),("⓰"); INSERT INTO t0(c0) VALUES(16); SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0; SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 LIKE t1.c0 INTERSECT SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 NOT LIKE t1.c0; """ if __name__ == '__main__': db_config = { 'host': 'localhost', 'database': 'mysql', 'user': 'root', 'password': '1213', 'port': 3306, 'charset': 'utf8mb4', 'collation': 'utf8mb4_unicode_ci' } try: connection = mysql.connector.connect(**db_config) if connection.is_connected(): print("Connected to MySQL database") cursor = connection.cursor() try: for statement in sql_commands.split(';'): if statement.strip(): cursor.execute(statement) print(f"Executed: {statement}") if statement.strip().upper().startswith('SELECT'): results = cursor.fetchall() print("Query results:") for row in results: print(row) connection.commit() except Error as e: print(f"SQL execution error: {e}") connection.rollback() finally: cursor.close() except Error as e: print(f"Connection error: {e}") finally: if 'connection' in locals() and connection.is_connected(): connection.close() print("Connection closed")