/*
I made further test using same data, the numbers of rows for the bug to take effect is determined thru trial and error method.
The bug depends on two conditions :
1) Depends on number of rows 
OR 
2) Depends on both number of rows and how the data is inserted.
*/

-- Load data from (https://bugs.mysql.com/bug.php?id=99257)
\. data_set.sql

-- DROP unneeded columns
ALTER TABLE t1 DROP COLUMN external_order_id;
ALTER TABLE t2 DROP COLUMN state;

-- Create additinal test tables
CREATE TABLE a1 LIKE t1;
CREATE TABLE a2 LIKE t2;

tee /tmp/bug113587-test2-2-condition-1.txt
SHOW CREATE TABLE a1\G
SHOW CREATE TABLE a2\G

PAGER tail -5

/* ********************************************
** 1) Depends on number of rows
*/
-- Table a1 needs more than 181894 rows to be affected by Bug113587 
-- INSERT ... LIMIT 1; 
-- or
-- DELETE FROM a1 LIMIT 1; 
-- Does make the query Bug113587 affected or not 
TRUNCATE TABLE a1; INSERT INTO a1(order_revision_id) SELECT order_revision_id FROM t1 LIMIT 181894; -- OK
EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
INSERT INTO a1(order_revision_id) SELECT order_revision_id FROM t1 LIMIT 1;
EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
DELETE FROM a1 LIMIT 1;
EXPLAIN SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM a1 t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC;

tee /tmp/bug113587-test2-2-condition-2.txt

/* ******************************************** 
** 2) Depends on both number of rows and how the data is inserted
*/
-- Table a2 needs more than 606397 rows to be affected by Bug113587
TRUNCATE TABLE a2; INSERT INTO a2(order_revision_id) SELECT NULL FROM t2 LIMIT 606397; -- OK 
EXPLAIN SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
-- INSERT ... LIMIT 1;
-- or
-- DELETE FROM a2 LIMIT 1;
-- DOES NOT make the query Bug113587 affected
INSERT INTO a2(order_revision_id) SELECT NULL FROM t2 LIMIT 1;
EXPLAIN SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
DELETE FROM a2 LIMIT 1;
EXPLAIN SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
-- We need to refresh the data and increase by 1 the number of rows for it to be Bug113587 affected
TRUNCATE TABLE a2; INSERT INTO a2(order_revision_id) SELECT NULL FROM t2 LIMIT 606498; -- Bug113587 
EXPLAIN SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
        SELECT DISTINCT t1.order_id FROM t1 INNER JOIN a2 t2 USING (order_revision_id) ORDER BY t1.order_id DESC;
notee