#!/usr/bin/env python3
"""BUG-076: MariaDB MIN/MAX via index returns a value failing the WHERE clause.

To seek in an integer index a non-integral bound must be converted with ceil for
>= and floor for <=.  MariaDB rounds instead, so the bound lands one step
outward and an extra value slips past it.  The error appears exactly when the
fractional part reaches 0.5.

    python3 repro.py [--port 3400]
"""
import argparse, sys
import pymysql

ap = argparse.ArgumentParser()
ap.add_argument("--port", type=int, default=3400)
ap.add_argument("--host", default="127.0.0.1")
a = ap.parse_args()

con = pymysql.connect(host=a.host, port=a.port, user="root", autocommit=True)
cur = con.cursor()


def g(q):
    cur.execute(q)
    return cur.fetchall()


print("server:", g("SELECT version()")[0][0])
g("CREATE DATABASE IF NOT EXISTS bug076")
g("USE bug076")
g("DROP TABLE IF EXISTS t")
g("CREATE TABLE t(i INT, KEY(i)) ENGINE=InnoDB")
g("INSERT INTO t VALUES (-3),(-2),(-1),(0),(1),(2),(3)")
g("ANALYZE TABLE t")

bad = 0
print("\n  %-14s %-10s %-10s %-16s %s"
      % ("predicate", "MIN idx", "correct", "rows matching", "verdict"))
for pred in ("i >= -0.40", "i >= -0.49", "i >= -0.499999", "i >= -0.50",
             "i >= -0.500001", "i >= -0.51", "i >= -2.5", "i >= -0.5e0"):
    idx = g("SELECT MIN(i) FROM t FORCE INDEX(i) WHERE %s" % pred)[0][0]
    ref = g("SELECT MIN(i) FROM t IGNORE INDEX(i) WHERE %s" % pred)[0][0]
    rows = g("SELECT group_concat(i ORDER BY i) FROM t IGNORE INDEX(i) "
             "WHERE %s" % pred)[0][0]
    ok = str(idx) == str(ref)
    if not ok:
        bad += 1
    print("  %-14s %-10s %-10s %-16s %s"
          % (pred, idx, ref, rows,
             "ok" if ok else "<<< value does not satisfy the predicate"))

print("\n  MAX side:")
for pred in ("i <= 0.49", "i <= 0.50", "i <= 0.60", "i <= 1.5"):
    idx = g("SELECT MAX(i) FROM t FORCE INDEX(i) WHERE %s" % pred)[0][0]
    ref = g("SELECT MAX(i) FROM t IGNORE INDEX(i) WHERE %s" % pred)[0][0]
    ok = str(idx) == str(ref)
    if not ok:
        bad += 1
    print("  %-14s %-10s %-10s %s"
          % (pred, idx, ref, "ok" if ok else "<<< WRONG"))

print("\n  the equality form -- no index hint, ordinary query:")
for q in ("SELECT count(*) FROM t WHERE i = -0.51",
          "SELECT count(*) FROM t WHERE i = 0.49",
          "SELECT count(*) FROM t WHERE i IN (0.49, 1.5)",
          "SELECT count(*) FROM t WHERE i BETWEEN 0.4 AND 0.6"):
    got = g(q)[0][0]
    flag = "" if got == 0 else "   <<< WRONG, no integer equals that constant"
    if got != 0:
        bad += 1
    print("    %-52s -> %s%s" % (q, got, flag))

cur.execute("SELECT i, (i = -0.51) FROM t WHERE i = -0.51")
rows = cur.fetchall()
if rows:
    bad += 1
    print("\n    a row was returned together with its own predicate's value:")
    print("      SELECT i, (i = -0.51) FROM t WHERE i = -0.51  ->  %s" % (rows,))
    print("      the row is returned, and the predicate on it is false")

print("\n  count(*) and SELECT * disagree:")
c1 = g("SELECT count(*) FROM t WHERE i = 0.49")[0][0]
cur.execute("SELECT * FROM t WHERE i = 0.49")
c2 = len(cur.fetchall())
if c1 != c2:
    bad += 1
print("    count(*) = %s   but SELECT * returns %s row(s)%s"
      % (c1, c2, "" if c1 == c2 else "   <<< CONTRADICTION"))

print("\n  the engine contradicts itself on the MIN/MAX case:")
for q in ("SELECT (-1 >= -0.5)",
          "SELECT count(*) FROM t WHERE i >= -0.5 AND i < 0",
          "SELECT group_concat(i ORDER BY i) FROM t WHERE i >= -0.5",
          "SELECT MIN(i) FROM t FORCE INDEX(i) WHERE i >= -0.5",
          "SELECT i FROM t FORCE INDEX(i) WHERE i >= -0.5 ORDER BY i LIMIT 1"):
    print("    %-62s -> %s" % (q, g(q)[0][0]))

cur.execute("EXPLAIN SELECT MIN(i) FROM t FORCE INDEX(i) WHERE i >= -0.5")
print("    plan:", cur.fetchall()[0][-1])

print("\n%d predicate(s) returned a value that fails the WHERE clause" % bad)
sys.exit(1 if bad else 0)