#!/usr/bin/env python3 """BUG-086 - MariaDB: a non-integral RANGE window frame offset is rounded to an integer when the ORDER BY column is an integer type, so the frame covers the wrong rows. The reference is the frame's own definition: RANGE BETWEEN d PRECEDING AND d FOLLOWING covers the rows whose ORDER BY value is in [v-d, v+d]. python3 repro.py [--port 3400] Exits 1 if the defect is present (expected on MariaDB, not on MySQL). """ import argparse, sys import pymysql OFFSETS = ['0.25', '0.49', '0.5', '0.51', '0.75', '1.0', '1.49', '1.5', '1.51', '2.0', '2.5'] TYPES = ['INT', 'BIGINT', 'SMALLINT', 'DECIMAL(10,2)', 'DOUBLE', 'FLOAT'] 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 bug086') q('USE bug086') failures = [] # --- fixture: distinct integer keys 0..9 ------------------------------ q('DROP TABLE IF EXISTS d1') q('CREATE TABLE d1(i INT PRIMARY KEY, x INT) ENGINE=InnoDB') q('INSERT INTO d1 VALUES ' + ','.join('(%d,1)' % k for k in range(10))) if q('SELECT count(*) FROM d1')[0][0] != 10: sys.exit('REFUSING TO RUN: fixture d1 is not 10 rows') # --- 1. headline ------------------------------------------------------ print('1. RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING, keys 0..9, at i=5') got = q('SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i ' 'RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c FROM d1) z ' 'WHERE i = 5')[0][0] ref = q('SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 - 0.5 AND b.i <= 5 + 0.5')[0][0] print(' window frame width = %s' % got) print(' exact frame [4.5, 5.5] contains %s row' % ref) if got != ref: failures.append('symmetric 0.5 frame width %s, exact %s' % (got, ref)) # --- 2. the gate ------------------------------------------------------ print('\n2. the gate: fractional part of the offset') print(' %-9s %-9s %-9s' % ('offset', 'window', 'exact')) for d in OFFSETS: w = q('SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i ' 'RANGE BETWEEN %s PRECEDING AND %s FOLLOWING) c FROM d1) z ' 'WHERE i = 5' % (d, d))[0][0] r = q('SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 - %s AND b.i <= 5 + %s' % (d, d))[0][0] bad = w != r print(' %-9s %-9s %-9s %s' % (d, w, r, 'WRONG' if bad else '')) if bad: failures.append('offset %s: width %s, exact %s' % (d, w, r)) # --- 3. the two bounds round differently ------------------------------ print('\n3. one-sided frames: PRECEDING and FOLLOWING round differently') print(' %-9s %-18s %-18s' % ('offset', 'd PRECEDING..CURR', 'CURR..d FOLLOWING')) for d in ['0.49', '0.5', '0.51', '1.49', '1.5', '1.51']: wp = q('SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i ' 'RANGE BETWEEN %s PRECEDING AND CURRENT ROW) c FROM d1) z ' 'WHERE i = 5' % d)[0][0] rp = q('SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 - %s AND b.i <= 5' % d)[0][0] wf = q('SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i ' 'RANGE BETWEEN CURRENT ROW AND %s FOLLOWING) c FROM d1) z ' 'WHERE i = 5' % d)[0][0] rf = q('SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 AND b.i <= 5 + %s' % d)[0][0] tag = ('P-WRONG ' if wp != rp else '') + ('F-WRONG' if wf != rf else '') print(' %-9s %-18s %-18s %s' % (d, '%s (exact %s)' % (wp, rp), '%s (exact %s)' % (wf, rf), tag)) if wp != rp: failures.append('%s PRECEDING: %s, exact %s' % (d, wp, rp)) if wf != rf: failures.append('%s FOLLOWING: %s, exact %s' % (d, wf, rf)) # --- 4. which ORDER BY types ------------------------------------------ print('\n4. ORDER BY column type') for typ in TYPES: q('DROP TABLE IF EXISTS ty') q('CREATE TABLE ty(i %s PRIMARY KEY) ENGINE=InnoDB' % typ) q('INSERT INTO ty VALUES ' + ','.join('(%d)' % k for k in range(10))) if q('SELECT count(*) FROM ty')[0][0] != 10: sys.exit('REFUSING TO RUN: fixture for %s is not 10 rows' % typ) w = q('SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i ' 'RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c FROM ty) z ' 'WHERE i = 5')[0][0] r = q('SELECT COUNT(*) FROM ty b WHERE b.i >= 5 - 0.5 AND b.i <= 5 + 0.5')[0][0] bad = w != r print(' %-14s offset 0.5 -> width %s, exact %s %s' % (typ, w, r, 'WRONG' if bad else 'ok')) if bad: failures.append('ORDER BY %s: width %s, exact %s' % (typ, w, r)) # --- 5. aggregate values, not just counts ----------------------------- print('\n5. it changes aggregate values') q('DROP TABLE IF EXISTS w2') q('CREATE TABLE w2(id INT PRIMARY KEY, i INT, x INT) ENGINE=InnoDB') q('INSERT INTO w2 VALUES ' + ','.join('(%d,%d,%d)' % (k, k % 10, k) for k in range(1, 41))) if q('SELECT count(*) FROM w2')[0][0] != 40: sys.exit('REFUSING TO RUN: fixture w2 is not 40 rows') for frame, lo, hi in ( ('RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING', 'a.i - 0.5', 'a.i + 0.5'), ('RANGE BETWEEN CURRENT ROW AND 0.5 FOLLOWING', 'a.i', 'a.i + 0.5'), ('RANGE BETWEEN 1.5 PRECEDING AND 1.5 FOLLOWING', 'a.i - 1.5', 'a.i + 1.5')): r1 = q('SELECT id, SUM(x) OVER (ORDER BY i %s) FROM w2 ORDER BY id' % frame) r2 = q('SELECT a.id, (SELECT SUM(b.x) FROM w2 b WHERE b.i >= %s AND b.i <= %s) ' 'FROM w2 a ORDER BY a.id' % (lo, hi)) ndiff = sum(1 for p, s in zip(r1, r2) if str(p[1]) != str(s[1])) print(' %-48s %d of 40 rows differ' % (frame[6:], ndiff)) if ndiff: failures.append('%s: %d of 40 rows differ' % (frame, ndiff)) print('\n' + '=' * 62) if failures: print('BUG-086 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-086 not reproduced on %s' % version) return 0 if __name__ == '__main__': sys.exit(main())