-- Reproducer: LIMIT-scaled range cost compared against unscaled table scan cost
--
-- Ingredients:
--   * `ns` index: leading column matched by equality but ~97.5% skewed toward 0,
--     so a range on it reads nearly the whole table plus a clustered lookup per row
--   * ORDER BY (d, name): not covered by any index, so filesort is mandatory and
--     early termination on LIMIT is impossible
--   * month(d)/dayofmonth(d): non-sargable, contribute no selectivity, so
--     rows_after_filter lands far above the true result size
--   * LIMIT 5000 with rows_after_filter ~8500: the LIMIT must be BINDING, i.e.
--     below the estimated output, for the discount to be applied. This is the trigger.

CREATE TABLE t1 (
  id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ns   INT NOT NULL,
  obj  VARCHAR(64) NOT NULL,
  d    DATETIME NOT NULL,
  name VARCHAR(64) NOT NULL,
  sel  INT NOT NULL,
  pad  CHAR(100) NOT NULL DEFAULT '',
  KEY ns (ns, obj)
) ENGINE=InnoDB;

INSERT INTO t1 (ns, obj, d, name, sel, pad)
SELECT
  IF(seq % 40 = 0, seq % 7 + 1, 0),                     -- ~97.5% of rows have ns = 0
  CONCAT('obj', seq),
  '2015-01-01' + INTERVAL (seq % 4000) DAY
                + INTERVAL (seq % 86400) SECOND,        -- ~4000 distinct dates
  CONCAT('name', seq % 10000),
  IF(seq % 47 = 0, 1, seq % 97 + 2),                    -- sel = 1 for ~2.1% of rows
  'x'
FROM seq_1_to_400000;

ANALYZE TABLE t1 PERSISTENT FOR ALL;

-- Confirm the estimate exceeds the LIMIT below. Expect rows_after_filter ~8500
-- in the trace; if it comes out under 5000 the discount will not engage and the
-- repro shows nothing. Adjust the `seq % 47` modulus if needed.

-- Bad plan: range on ns, ~396k index entries read to return ~23 rows
EXPLAIN
SELECT id, obj, d, name, pad FROM t1
WHERE ns = 0 AND sel = 1 AND month(d) = 8 AND dayofmonth(d) = 12
ORDER BY d, name LIMIT 5000;

-- Good plan: identical query, LIMIT raised above rows_after_filter
EXPLAIN
SELECT id, obj, d, name, pad FROM t1
WHERE ns = 0 AND sel = 1 AND month(d) = 8 AND dayofmonth(d) = 12
ORDER BY d, name LIMIT 20000;

-- Trace for the LIMIT 5000 case
SET SESSION optimizer_trace = 'enabled=on';
SELECT id, obj, d, name, pad FROM t1
WHERE ns = 0 AND sel = 1 AND month(d) = 8 AND dayofmonth(d) = 12
ORDER BY d, name LIMIT 5000;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- Expected in the trace for LIMIT 5000:
--
--   considered_execution_plans -> best_access_path -> considered_access_paths
--     access_type "scan" chosen, cost C_scan
--     access_type "ref" index "ns", cost C_ref, where C_ref >> C_scan
--
--   attaching_conditions_to_tables -> range_scan_alternatives
--     index "ns" carries "cost_with_limit" << C_scan, "chosen": true
--     chosen_range_access_summary installs the range plan
--
--   test_if_skip_sort_order: []      <- sort cannot be skipped, so the
--                                       early-termination assumption behind
--                                       cost_with_limit does not hold
--
-- For LIMIT 20000 the limit is non-binding, attached_conditions_computation is
-- empty, and the table scan chosen in phase 1 survives.
