path: root/mysql-test/main/rowid_filter.test

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

--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(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,'x');
INSERT INTO t2 SELECT * FROM t1;

let $q=
SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = 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),(0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4),(3,3),(3,4),(3,5),(8,8),(8,9),(1,0),(2,0),(0,0),(0,0);
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;

set @@use_stat_tables=@save_use_stat_tables;

--echo #
--echo # MDEV-21633
--echo # Assertion `tmp >= 0' failed in best_access_path with rowid_filter=ON
--echo #

set @save_optimizer_switch= @@optimizer_switch;
SET optimizer_switch='rowid_filter=on';

CREATE TABLE t1 (
    pk INT AUTO_INCREMENT,
    a INT,
    b VARCHAR(8),
    KEY(a),
    PRIMARY KEY(pk),
    KEY (a,pk)
) ENGINE=MyISAM;

INSERT INTO t1 (a,b) VALUES
    (NULL,'d'),(9,'b'),(2,'x'),(5,'k'),(NULL,'d'),(3,'s'),(5,'k'),(1,'r'),
    (8,'l'),(3,'z'),(1,'c'),(1,'q'),(NULL,'x'),(NULL,'p'),(NULL,'z'),(7,'a'),
    (0,'i'),(3,'s'),(NULL,'h'),(4,'p'),(1,'i'),(4,'f'),(1,'c'),(NULL,'a'),
    (NULL,'x'),(1,'b'),(NULL,'n'),(NULL,'h'),(5,'i'),(6,'e'),(NULL,'i'),
    (7,'e'),(1,'r'),(NULL,'z'),(1,'i'),(14,'c'),(6,'u'),(3,'b'),(4,'z'),
    (2,'c'),(70,'d'),(NULL,'p'),(21,'j'),(6,'e'),(5,'c'),(13,'i'),(42,'d'),
    (80,'s'),(14,'t'),(9,'a'),(0,'2'),(0,NULL),(0,NULL),(0,NULL),(0,''),
    (0,''),(0,'1'),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,''),(0,''),
    (0,NULL),(0,''),(0,''),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,NULL),
    (0,NULL),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,NULL),
    (0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,''),(0,''),(0,''),(0,''),
    (0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,'');

CREATE TABLE t2 (c INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);

SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';

explain SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';

SET optimizer_switch='rowid_filter=off';

SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';

SET @@optimizer_switch=@save_optimizer_switch;

# Cleanup
DROP TABLE t1, t2;