-- Test queries for《MySQL Order By 注入：行为背后的原理分析》
-- https://sym01.com/posts/2026/mysql-order-by-behavior-analysis/
--
-- Tested on mysql:8.4 (also reproducible on mysql:8.0).
-- Quick start:
--   docker run --rm -d --name mysql-orderby -e MYSQL_ROOT_PASSWORD=test -p 13306:3306 mysql:8.4
--   mysql -h 127.0.0.1 -P 13306 -uroot -ptest < test.sql


-- ============================================================
-- 0. 数据准备
-- ============================================================
CREATE DATABASE IF NOT EXISTS t;
USE t;
CREATE TABLE rows10 (id INT);
INSERT INTO rows10
  WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<10)
  SELECT n FROM s;
SELECT CONCAT('seeded ', COUNT(*), ' rows in t.rows10') AS status FROM rows10;

-- 启用 optimizer_trace（用于后续观察 ORDER BY 消除/物化过程）
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 2000000;


-- ============================================================
-- A. 直接函数调用
-- ============================================================

-- A1. expect: ~0 s, no sleep — plan_is_const() 整段消除
SELECT 1 FROM dual ORDER BY SLEEP(2);

-- A2. expect: ~20 s ≈ 10 × 2 s — RAND_TABLE_BIT 逃过常量消除，每行 sleep
SELECT id FROM rows10 ORDER BY SLEEP(2);

-- A3. expect: ~0 s, no error — val_int() 从未被调用
SELECT 1 FROM dual ORDER BY GTID_SUBSET(2, 2);

-- A4. expect: ~0 s, no error — 同 A3
SELECT id FROM rows10 ORDER BY GTID_SUBSET(2, 2);


-- ============================================================
-- B. 裸标量子查询包装
-- ============================================================

-- B1. expect: ~0 s — 随外层 plan_is_const() 一起被丢弃
SELECT 1 FROM dual ORDER BY '', (SELECT SLEEP(5));

-- B2. expect: ~20 s — uncacheable scalar subquery，每行重新求值
SELECT id FROM rows10 ORDER BY '', (SELECT SLEEP(2));


-- ============================================================
-- C. 派生表包装（即当年 POC 写法）
-- ============================================================

-- C1. expect: ~5 s — derived table 优化阶段独立物化（仅一次）
SELECT 1 FROM dual ORDER BY '',
  (SELECT(1) FROM (SELECT(SLEEP(5)) WHERE(1=1)) test);

-- C2. expect: ~2 s（不是 20 s）— per-item val_str 分支只执行一次
SELECT id FROM rows10 ORDER BY '',
  (SELECT(1) FROM (SELECT(SLEEP(2)) WHERE(1=1)) test);

-- C3. expect: ERROR 1772 Malformed GTID set specification '2'
SELECT 1 FROM dual ORDER BY '',
  (SELECT(1) FROM (SELECT(GTID_SUBSET(2,2)) WHERE(1=1)) test);


-- ============================================================
-- 查看上一条查询的 optimizer trace（手动跑）：
-- SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
-- ============================================================
