#!/usr/bin/env python3 """BUG-084 - MariaDB: a negative constant against an indexed UNSIGNED column is clamped to zero and used as the range bound without adjusting the operator, so MIN/MAX skips the row holding 0 or returns a value for an empty predicate. MySQL 9.4.0 is correct on every case. python3 repro.py [--port 3400] Exits 1 if the defect is present (expected on MariaDB, not on MySQL). """ import argparse, sys import pymysql TYPES = [('TINYINT UNSIGNED', 255), ('SMALLINT UNSIGNED', 65535), ('INT UNSIGNED', 4294967295), ('BIGINT UNSIGNED', 18446744073709551615)] NEGS = ['-1', '-2', '-0.5', '-1e0', "'-1'"] OPS = ['<', '<=', '>', '>=', '='] def main(): ap = argparse.ArgumentParser() ap.add_argument('--port', type=int, default=3400) ap.add_argument('--host', default='127.0.0.1') ap.add_argument('--user', default='root') a = ap.parse_args() conn = pymysql.connect(host=a.host, port=a.port, user=a.user, autocommit=True) cur = conn.cursor() def q(sql): cur.execute(sql) return cur.fetchall() version = q('SELECT version()')[0][0] print('server: %s (port %d)\n' % (version, a.port)) q('CREATE DATABASE IF NOT EXISTS bug084') q('USE bug084') failures = [] # --- 1. headline ------------------------------------------------------ print('1. MIN(u) skips the row holding 0') q('DROP TABLE IF EXISTS t') q('CREATE TABLE t(u INT UNSIGNED, KEY(u)) ENGINE=InnoDB') q('INSERT INTO t VALUES (0),(1),(2),(3)') if q('SELECT count(*) FROM t')[0][0] != 4: sys.exit('REFUSING TO RUN: fixture t is not 4 rows') got, cnt = q('SELECT MIN(u), count(*) FROM t WHERE u > -1')[0] ref = q('SELECT MIN(u) FROM t IGNORE INDEX(u) WHERE u > -1')[0][0] rows = [r[0] for r in q('SELECT u FROM t WHERE u > -1 ORDER BY u')] print(' SELECT MIN(u), count(*) FROM t WHERE u > -1') print(' MIN(u) = %s (correct %s)' % (got, ref)) print(' count(*) = %s' % cnt) print(' rows the same predicate returns: %s' % rows) print(' EXPLAIN: %s' % q('EXPLAIN SELECT MIN(u) FROM t WHERE u > -1')[0][-1]) if got != ref: failures.append('MIN(u) WHERE u > -1 gave %s, expected %s (count=%s)' % (got, ref, cnt)) # --- 2. inventing rows ------------------------------------------------ print('\n2. MIN/MAX return a value for a predicate that matches nothing') # NOTE: MIN/MAX must be selected ALONE here. Adding count(*) to the same # select list suppresses the "Select tables optimized away" shortcut for the # equality case, which hides the defect -- see section 2b. # Each aggregate must be selected ALONE. Putting MIN and MAX in one select # list, or adding count(*), changes which path the optimiser takes and can # hide the defect -- see section 2b. for agg, pred in (('MIN', 'u = -1'), ('MAX', 'u = -1'), ('MAX', 'u <= -1')): g2 = q('SELECT %s(u) FROM t WHERE %s' % (agg, pred))[0][0] r2 = q('SELECT %s(u) FROM t IGNORE INDEX(u) WHERE %s' % (agg, pred))[0][0] cnt2 = q('SELECT count(*) FROM t IGNORE INDEX(u) WHERE %s' % pred)[0][0] print(' %s(u) WHERE %-8s -> %-6s (correct %s, %d rows match)' % (agg, pred, g2, r2, cnt2)) if str(g2) != str(r2): failures.append('%s(u) WHERE %s gave %s over %d rows' % (agg, pred, g2, cnt2)) print('\n2b. how the query is written decides whether the shortcut runs') for sel in ('MIN(u)', 'MIN(u), count(*)', 'MIN(u), MAX(u)'): v = q('SELECT %s FROM t WHERE u = -1' % sel)[0][0] print(' SELECT %-18s FROM t WHERE u = -1 -> %s' % (sel, v)) print(' (correct is NULL in every spelling; anything that forces a scan') print(' skips the shortcut and happens to give the right answer)') # --- 3. the matrix ---------------------------------------------------- print('\n3. which combinations, across every unsigned width and spelling') combos = {} checked = 0 for typ, mx in TYPES: q('DROP TABLE IF EXISTS tu') q('CREATE TABLE tu(u %s, KEY(u)) ENGINE=InnoDB' % typ) q('INSERT INTO tu VALUES (0),(1),(2),(3)') if q('SELECT count(*) FROM tu')[0][0] != 4: sys.exit('REFUSING TO RUN: fixture for %s is not 4 rows' % typ) for K in NEGS: for agg in ('MIN', 'MAX'): for op in OPS: try: g3 = q('SELECT %s(u) FROM tu WHERE u %s %s' % (agg, op, K))[0][0] r3 = q('SELECT %s(u) FROM tu IGNORE INDEX(u) WHERE u %s %s' % (agg, op, K))[0][0] except Exception: continue checked += 1 if str(g3) != str(r3): combos.setdefault('%s(u) WHERE u %s K' % (agg, op), set()).add(typ) print(' %d checks across %d types x %d spellings' % (checked, len(TYPES), len(NEGS))) for key in sorted(combos): print(' %-24s WRONG on %d of %d unsigned widths' % (key, len(combos[key]), len(TYPES))) failures.append('%s wrong on %d unsigned widths' % (key, len(combos[key]))) if not combos: print(' none wrong') # --- 4. control: constants at or above zero, and above the maximum ---- print('\n4. controls') okc = True for typ, mx in TYPES: q('DROP TABLE IF EXISTS tc') q('CREATE TABLE tc(u %s, KEY(u)) ENGINE=InnoDB' % typ) q('INSERT INTO tc VALUES (0),(1),(2),(3)') for K in ('0', '1', str(mx), str(mx + 1)): for agg in ('MIN', 'MAX'): for op in OPS: try: g4 = q('SELECT %s(u) FROM tc WHERE u %s %s' % (agg, op, K))[0][0] r4 = q('SELECT %s(u) FROM tc IGNORE INDEX(u) WHERE u %s %s' % (agg, op, K))[0][0] except Exception: continue if str(g4) != str(r4): okc = False print(' constants >= 0, including above the type maximum: %s' % ('all correct' if okc else 'UNEXPECTED disagreement')) print('\n' + '=' * 62) if failures: print('BUG-084 PRESENT on %s - %d failing checks:' % (version, len(failures))) for f in failures[:8]: print(' - %s' % f) if len(failures) > 8: print(' ... and %d more' % (len(failures) - 8)) return 1 print('BUG-084 not reproduced on %s' % version) return 0 if __name__ == '__main__': sys.exit(main())