--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;

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;

--echo #
--echo # MDEV-18816: potential range filter for one join table with
--echo #             impossible WHERE for another
--echo #

create table t1 (
  pk int not null primary key, c2 varchar(10) , i1 int,key (c2)
) engine=myisam;
insert into t1 values  (1,'a',-5),(2,'a',null);

create table t2 (
 pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1)
) engine=myisam;
insert into t2 values
  (1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'),
  (7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'),
  (13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a');

let $q=
select 1
  from t1
       left join
       t2 join t1 as t1_a on t2.i1 = t1_a.pk
       on t1.c2 = t2.c1
where t1_a.pk is null and t1_a.i1 != 3;

eval $q;
eval explain extended $q;

drop table t1,t2;

--echo #
--echo # MDEV-18640: TABLE::prune_range_rowid_filters: Conditional jump or
--echo #             move depends on uninitialized value
--echo #

CREATE TABLE t1 (
  pk INT, i INT, PRIMARY KEY (pk), KEY (pk,i)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,10), (7,70), (2,20);

SELECT * FROM t1 WHERE pk < 5;

DROP TABLE t1;

--echo #
--echo # MDEV-18956: Possible rowid filter for subquery for which
--echo #             in_to_exists strategy has been chosen
--echo #

CREATE TABLE t1 (pk int) engine=myisam ;
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (
  pk int auto_increment PRIMARY KEY,
  i1 int, i2 int, c2 varchar(1),
  KEY (i1), KEY (i2)
) engine=myisam;

INSERT INTO t2 VALUES
  (1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
  (6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
  (11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');

SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
EXPLAIN EXTENDED
SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);

DROP TABLE t1,t2;

--echo #
--echo # MDEV-19255: rowid range filter built for range condition
--echo #             that uses in expensive subquery
--echo #

CREATE TABLE t1 (
  pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(a1), KEY(b1)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES
(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'),
(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'),
(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'),
(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'),
(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'),
(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'),
(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'),
(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'),
(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'),
(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'),
(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'),
(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL),
(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'),
(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'),
(107,8,'z'),(108,3,'k'),(109,65,NULL);

CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,1,'i');
INSERT INTO t2 SELECT * FROM t1;

INSERT INTO t1 SELECT pk1+200, a1, b1 FROM t1;
INSERT INTO t1 SELECT pk1+400, a1, b1 FROM t1;

ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;

let $q=
SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
  WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );

eval $q;
eval EXPLAIN EXTENDED $q;
eval EXPLAIN FORMAT=JSON $q;

DROP TABLE t1,t2;

--echo #
--echo # MDEV-21794: Optimizer flag rowid_filter leads to long query
--echo #
create table t10(a int);
insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t11(a int);
insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C;

CREATE TABLE t1 (
  el_id int(10) unsigned NOT NULL ,
  el_index blob NOT NULL,
  el_index_60 varbinary(60) NOT NULL,
  filler blob,

  PRIMARY KEY (el_id),
  KEY el_index (el_index(60)),
  KEY el_index_60 (el_index_60,el_id)
);

insert into t1
select
  A.a+1000*B.a,
  A.a+1000*B.a + 10000,
  A.a+1000*B.a + 10000,
  'filler-data-filler-data'
from
  t11 A, t10 B;
analyze table t1 persistent for all;

--echo # This must not use rowid_filter with key=el_index|el_index_60:
explain
select * from t1
where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%');

drop table t10, t11, t1;


--echo #
--echo # MDEV-22160: SIGSEGV in st_join_table::save_explain_data on SELECT
--echo #

set @save_optimizer_switch= @@optimizer_switch;
SET @@optimizer_switch="index_merge_sort_union=OFF";
CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b));
INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

ANALYZE table t1 PERSISTENT FOR ALL;

explain
SELECT * FROM t1 WHERE a > 0 AND b=0;
SELECT * FROM t1 WHERE a > 0 AND b=0;
drop table t1;
SET @@optimizer_switch=@save_optimizer_switch;


--echo #
--echo # MDEV-28846: Poor performance when rowid filter contains no elements
--echo #

--source include/have_sequence.inc

create table t1 (
  pk int primary key auto_increment,
  nm varchar(32),
  fl1 tinyint default 0,
  fl2 tinyint default 0,
  index idx1(nm, fl1),
  index idx2(fl2)
) engine=myisam;

create table name (
  pk int primary key auto_increment,
  nm bigint
) engine=myisam;

create table flag2 (
  pk int primary key auto_increment,
  fl2 tinyint
) engine=myisam;

insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);

insert into t1(nm,fl2)
  select nm, fl2 from name, flag2 where name.pk = flag2.pk;

analyze table t1 persistent for all;

let $a=
`select concat((select nm from t1 where fl2=0 order by RAND(13) limit 1),'%')`;
eval select '$a' as a;

set optimizer_switch='rowid_filter=on';
eval
explain
select * from t1  where nm like '$a' AND fl2 = 0;
--source include/analyze-format.inc
eval
analyze format=json
select * from t1  where nm like '$a' AND fl2 = 0;
eval
select * from t1  where nm like '$a' AND fl2 = 0;

truncate table name;
truncate table flag2;
truncate table t1;

insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);

insert into t1(nm,fl2)
  select nm, fl2 from name, flag2 where name.pk = flag2.pk;

analyze table t1 persistent for all;

set optimizer_switch='rowid_filter=off';
eval
explain
select * from t1  where nm like '$a' AND fl2 = 0;
--source include/analyze-format.inc
eval
analyze format=json
select * from t1  where nm like '$a' AND fl2 = 0;
eval
select * from t1  where nm like '$a' AND fl2 = 0;

truncate table name;
truncate table flag2;
truncate table t1;

insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
insert into flag2(fl2) select seq mod 10 from seq_1_to_1000 order by rand(19);

insert into t1(nm,fl2)
  select nm, fl2 from name, flag2 where name.pk = flag2.pk;

analyze table t1 persistent for all;

let $a=
`select concat((select nm from t1 where fl2=0 order by RAND(13) limit 1),'%')`;
eval select '$a' as a;

set optimizer_switch='rowid_filter=on';
eval
explain
select * from t1  where nm like '$a' AND fl2 = 0;
eval
select * from t1  where nm like '$a' AND fl2 = 0;

truncate table name;
truncate table flag2;
truncate table t1;

insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
insert into flag2(fl2) select seq mod 100 from seq_1_to_10000 order by rand(19);

insert into t1(nm,fl2)
  select nm, fl2 from name, flag2 where name.pk = flag2.pk;

analyze table t1 persistent for all;

let $a=
`select concat(left((select nm from t1 where fl2=0 order by RAND(13) limit 1),2),'%')`;
eval select '$a' as a;

set optimizer_switch='rowid_filter=on';
eval
explain
select * from t1  where nm like '$a' AND fl2 = 0;
--source include/analyze-format.inc
eval
analyze format=json
select * from t1  where nm like '$a' AND fl2 = 0;
eval
select * from t1  where nm like '$a' AND fl2 = 0;

drop table name, flag2;
drop table t1;

# This test shows that if the container is empty there are no lookups into it

create table t1 (
  pk int primary key auto_increment,
  nm char(255),
  fl1 tinyint default 0,
  fl2 int default 0,
  index idx1(nm, fl1),
  index idx2(fl2)
) engine=myisam;

create table name (
  pk int primary key auto_increment,
  nm bigint
) engine=myisam;

create table flag2 (
  pk int primary key auto_increment,
  fl2 int
) engine=myisam;

insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
insert into flag2(fl2) select seq mod 10 from seq_1_to_10000 order by rand(19);

insert into t1(nm,fl2)
  select nm, fl2 from name, flag2 where name.pk = flag2.pk;

analyze table t1 persistent for all;

let $q=
select * from t1
where
(
  nm like '3400%' or nm like '3402%' or nm like '3403%' or
  nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
  nm like '3409%' or
  nm like '3411%' or nm like '3412%' or nm like '3413%' or
  nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
  nm like '3418%' or nm like '3419%' or
  nm like '3421%' or nm like '3422%' or nm like '3423%' or
  nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
  nm like '3428%' or nm like '3429%' or
  nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
  nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
  nm like '3439%' or
  nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
  nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
  nm like '3448%'
) and fl2 = 0;

eval $q;
--source include/analyze-format.inc
eval analyze format=json $q;

create table t0 select * from t1  where nm like '34%';
delete from t1 using t1,t0 where t1.nm=t0.nm;
--source include/analyze-format.inc
eval analyze format=json $q;

drop table t0;

set optimizer_switch='rowid_filter=default';

drop table name, flag2;
drop table t1;

set @@use_stat_tables=@save_use_stat_tables;