Description:
This bug reveals a query correctness issue where SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 returns one row containing special characters, while the semantically equivalent SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 > t1.c0 returns an empty set, despite identical table data and conditions.
How to repeat:
import pymysql
import sys
# MySQL connection configuration
config = {
'host': 'localhost',
'database': 'mysql',
'user': 'root',
'password': '',
'port': 3306,
'charset': 'utf8mb4'
}
# SQL statements
setup_statements = [
"DROP TABLE IF EXISTS t0",
"DROP TABLE IF EXISTS t1",
"CREATE TABLE IF NOT EXISTS t0(c0 TEXT)",
"CREATE TABLE IF NOT EXISTS t1 LIKE t0",
"INSERT IGNORE INTO t0(c0) VALUES(''), (NULL)",
"CREATE UNIQUE INDEX i1 ON t1(c0(1))",
"INSERT INTO t1(c0) VALUES(1)"
]
query_statements = [
"SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0",
"SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 > t1.c0"
]
try:
# Connect to MySQL
connection = pymysql.connect(**config)
with connection.cursor() as cursor:
# Execute setup statements
for sql in setup_statements:
cursor.execute(sql)
connection.commit()
# Execute and print query results
for i, sql in enumerate(query_statements, 1):
cursor.execute(sql)
results = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
print(f"\n{'='*60}")
print(f"Query {i}: {sql}")
print(f"{'='*60}")
print(f"Columns: {columns}")
print(f"{'-'*60}")
if results:
for row in results:
print(row)
else:
print("No results")
print(f"Total rows: {len(results)}")
except Exception as e:
print(f"Error: {e}")
sys.exit(1)
finally:
if 'connection' in locals():
connection.close()
© 2025, Oracle Corporation and/or its affiliates
Description: This bug reveals a query correctness issue where SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 returns one row containing special characters, while the semantically equivalent SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 > t1.c0 returns an empty set, despite identical table data and conditions. How to repeat: import pymysql import sys # MySQL connection configuration config = { 'host': 'localhost', 'database': 'mysql', 'user': 'root', 'password': '', 'port': 3306, 'charset': 'utf8mb4' } # SQL statements setup_statements = [ "DROP TABLE IF EXISTS t0", "DROP TABLE IF EXISTS t1", "CREATE TABLE IF NOT EXISTS t0(c0 TEXT)", "CREATE TABLE IF NOT EXISTS t1 LIKE t0", "INSERT IGNORE INTO t0(c0) VALUES(''), (NULL)", "CREATE UNIQUE INDEX i1 ON t1(c0(1))", "INSERT INTO t1(c0) VALUES(1)" ] query_statements = [ "SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0", "SELECT * FROM t0 STRAIGHT_JOIN t1 ON t0.c0 > t1.c0" ] try: # Connect to MySQL connection = pymysql.connect(**config) with connection.cursor() as cursor: # Execute setup statements for sql in setup_statements: cursor.execute(sql) connection.commit() # Execute and print query results for i, sql in enumerate(query_statements, 1): cursor.execute(sql) results = cursor.fetchall() columns = [desc[0] for desc in cursor.description] print(f"\n{'='*60}") print(f"Query {i}: {sql}") print(f"{'='*60}") print(f"Columns: {columns}") print(f"{'-'*60}") if results: for row in results: print(row) else: print("No results") print(f"Total rows: {len(results)}") except Exception as e: print(f"Error: {e}") sys.exit(1) finally: if 'connection' in locals(): connection.close()