--disable_warnings
DROP DATABASE IF EXISTS dbt3_s001;
--enable_warnings

CREATE DATABASE dbt3_s001;

use dbt3_s001;

--disable_query_log
--disable_result_log
--disable_warnings
--source include/dbt3_s001.inc
--enable_warnings
--enable_result_log
--enable_query_log

CREATE INDEX i_l_quantity ON lineitem(l_quantity);

CREATE INDEX i_o_totalprice ON orders(o_totalprice);

set @save_use_stat_tables= @@use_stat_tables;

set @@use_stat_tables=preferably;

--disable_result_log
--disable_warnings
ANALYZE TABLE lineitem, orders;
--enable_warnings
--enable_result_log

show create table lineitem;
show create table orders;

set optimizer_use_condition_selectivity=2;

let $with_filter=
set statement optimizer_switch='rowid_filter=on' for;

let $without_filter=
set statement optimizer_switch='rowid_filter=off' for;

select
  100 *
  (select count(*) from lineitem
   WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45
  )
  /
  (select count(*) from lineitem
   where l_shipdate BETWEEN '1997-01-01' AND '1997-06-30')
  as correct_r_filtered_when_using_l_shipdate;

let $q1=
SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
         l_quantity > 45;

eval $with_filter EXPLAIN $q1;
eval $with_filter EXPLAIN FORMAT=JSON $q1;
eval $with_filter ANALYZE $q1;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q1;
--sorted_result
eval $with_filter $q1;

eval $without_filter EXPLAIN $q1;
eval $without_filter EXPLAIN FORMAT=JSON $q1;
eval $without_filter ANALYZE $q1;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q1;
--sorted_result
eval $without_filter $q1;

let $q2=
SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
         o_totalprice between 200000 and 230000;

eval $with_filter EXPLAIN $q2;
eval $with_filter EXPLAIN FORMAT=JSON $q2;
eval $with_filter ANALYZE $q2;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q2;
--sorted_result
eval $with_filter $q2;

eval $without_filter EXPLAIN $q2;
eval $without_filter EXPLAIN FORMAT=JSON $q2;
eval $without_filter ANALYZE $q2;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q2;
--sorted_result
eval $without_filter $q2;

let $q3=
SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
         l_quantity > 45 AND
         o_totalprice between 180000 and 230000;

eval $with_filter EXPLAIN $q3;
eval $with_filter EXPLAIN FORMAT=JSON $q3;
eval $with_filter ANALYZE $q3;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q3;
--sorted_result
eval $with_filter $q3;

eval $without_filter EXPLAIN $q3;
eval $without_filter EXPLAIN FORMAT=JSON $q3;
eval $without_filter ANALYZE $q3;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q3;
--sorted_result
eval $without_filter $q3;

# Check different optimization
eval $with_filter EXPLAIN SELECT STRAIGHT_JOIN o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
  FROM lineitem JOIN orders ON o_orderkey=l_orderkey
  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
         l_quantity > 45 AND
         o_totalprice between 180000 and 230000;

let $q4=
SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
  WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
        o_totalprice between 200000 and 230000;

eval $with_filter EXPLAIN $q4;
eval $with_filter EXPLAIN FORMAT=JSON $q4;
eval $with_filter ANALYZE $q4;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q4;
--sorted_result
eval $with_filter $q4;

eval $without_filter EXPLAIN $q4;
eval $without_filter EXPLAIN FORMAT=JSON $q4;
eval $without_filter ANALYZE $q4;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q4;
--sorted_result
eval $without_filter $q4;

--echo #
--echo # MDEV-18413: find constraint correlated indexes
--echo #

ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate);

--echo # Filter on l_shipdate is not used because it participates in
--echo # the same constraint as l_receiptdate.
--echo # Access is made on l_receiptdate.
let $q5=
SELECT l_shipdate, l_receiptdate, o_totalprice
FROM orders, lineitem
WHERE o_orderkey=l_orderkey AND
      l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND
      l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND
      o_totalprice BETWEEN 200000 AND 250000;

eval $with_filter EXPLAIN $q5;
eval $with_filter EXPLAIN FORMAT=JSON $q5;
eval $with_filter ANALYZE $q5;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q5;
--sorted_result
eval $with_filter $q5;

eval $without_filter EXPLAIN $q5;
eval $without_filter EXPLAIN FORMAT=JSON $q5;
eval $without_filter ANALYZE $q5;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q5;
--sorted_result
eval $without_filter $q5;

ALTER TABLE orders ADD COLUMN o_totaldiscount double;
UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000);
CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount);

ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount);

--echo # Filter on o_totalprice is not used because it participates in
--echo # the same constraint as o_discount.
--echo # Access is made on o_discount.
let $q6=
SELECT o_totaldiscount, o_totalprice, l_shipdate
FROM orders, lineitem
WHERE o_orderkey=l_orderkey AND
      o_totaldiscount BETWEEN 18000 AND 20000 AND
      o_totalprice BETWEEN 200000 AND 220000 AND
      l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';

eval $with_filter EXPLAIN $q6;
eval $with_filter EXPLAIN FORMAT=JSON $q6;
eval $with_filter ANALYZE $q6;
--source include/analyze-format.inc
eval $with_filter ANALYZE FORMAT=JSON $q6;
--sorted_result
eval $with_filter $q6;

eval $without_filter EXPLAIN $q6;
eval $without_filter EXPLAIN FORMAT=JSON $q6;
eval $without_filter ANALYZE $q6;
--source include/analyze-format.inc
eval $without_filter ANALYZE FORMAT=JSON $q6;
--sorted_result
eval $without_filter $q6;

CREATE VIEW v1 AS
SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01';

let $q7=
SELECT o_totaldiscount, o_totalprice, l_shipdate
FROM v1, lineitem
WHERE o_orderkey=l_orderkey AND
      o_totaldiscount BETWEEN 18000 AND 20000 AND
      o_totalprice BETWEEN 200000 AND 220000 AND
      l_shipdate BETWEEN '1996-10-01' AND '1996-12-01';

eval $with_filter EXPLAIN $q7;
--replace_regex /"filtered": [0-9e\.\-+]*,/"filtered": "REPLACED",/
eval $with_filter EXPLAIN FORMAT=JSON $q7;
--replace_column 11 #
eval $with_filter ANALYZE $q7;
--replace_regex /("(r_(total|table|other)_time_ms|r_buffer_size|r_filling_time_ms|filtered)": )[^, \n]*/\1"REPLACED"/
eval $with_filter ANALYZE FORMAT=JSON $q7;
--sorted_result
eval $with_filter $q7;

eval $without_filter EXPLAIN $q7;
--replace_regex /"filtered": [0-9e\.\-+]*,/"filtered": "REPLACED",/
eval $without_filter EXPLAIN FORMAT=JSON $q7;
--replace_column 11 #
eval $without_filter ANALYZE $q7;
--replace_regex /("(r_(total|table|other)_time_ms|r_buffer_size|r_filling_time_ms|filtered)": )[^, \n]*/\1"REPLACED"/
eval $without_filter ANALYZE FORMAT=JSON $q7;
--sorted_result
eval $without_filter $q7;

ALTER TABLE lineitem DROP CONSTRAINT l_date;
ALTER TABLE orders DROP CONSTRAINT o_price;
ALTER TABLE orders DROP COLUMN o_totaldiscount;
DROP VIEW v1;

DROP DATABASE dbt3_s001;

use test;