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

--source include/have_innodb.inc
# This test is slow on buildbot.
--source include/big_test.inc
--source include/default_optimizer_switch.inc

SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';

set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
set optimizer_switch='extended_keys=on';
SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent=0;

--source selectivity.test

set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
set @tmp_ust= @@use_stat_tables;
set @tmp_oucs= @@optimizer_use_condition_selectivity;


--echo #
--echo # MDEV-6808: MariaDB 10.0.13 crash with optimizer_use_condition_selectivity > 1
--echo #
set @tmp_mdev6808= @@optimizer_use_condition_selectivity;
SET optimizer_use_condition_selectivity = 2;
CREATE TABLE t1 (
  event_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (event_id)
) ENGINE=InnoDB;

CREATE TABLE t2 (
  repost_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  subject_type varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  subject_id int(11) unsigned NOT NULL,
  object_type varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  object_id int(11) unsigned NOT NULL,
  is_private int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (repost_id),
  UNIQUE KEY `BETWEEN` (subject_type,subject_id,object_type,object_id,is_private),
  KEY SUBJECT (subject_type,subject_id),
  KEY OBJECT (object_type,object_id)
) ENGINE=InnoDB;

SELECT
  *
FROM 
   t2, t1 
WHERE 
    t2.object_type = 'event' AND 
    t2.object_id = t1.event_id AND 
    t2.is_private = 0 AND 
    t2.subject_id = 127994 AND 
    t2.subject_type in ('user')
;
DROP TABLE t1, t2;
set optimizer_use_condition_selectivity=@tmp_mdev6808;

--echo #
--echo # MDEV-6442: Assertion `join->best_read < double(...)' failed with optimizer_use_condition_selectivity >=3, ...
--echo #
SET use_stat_tables = PREFERABLY;
SET optimizer_use_condition_selectivity = 3;

CREATE TABLE t1 ( a VARCHAR(3), b VARCHAR(8), KEY (a,b) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('USA','Chinese'),('USA','English');

CREATE TABLE t2 (i INT) ENGINE=InnoDB;

SELECT * FROM t1, t2 WHERE ( 't', 'o' ) IN ( 
  SELECT t1_2.b, t1_1.a FROM t1 AS t1_1 STRAIGHT_JOIN t1 AS t1_2 ON ( t1_2.a = t1_1.b ) 
);
DROP TABLE t1,t2;

--echo # 
--echo # MDEV-6738: use_stat_table + histograms crashing optimizer
--echo # 

set use_stat_tables='preferably';
set optimizer_use_condition_selectivity=4;

--echo # Need innodb because there is a special kind of field_bit for non-myisam tables
create table t1(col1 int, col2 bit(1) DEFAULT NULL) engine=innodb;

select * from t1 where col2 != true;

drop table t1;

--echo #
--echo # MDEV-7413: optimizer_use_condition_selectivity > 2 crashes 10.0.15+maria-1~wheezy
--echo #

CREATE TABLE t1 (
  parent_id int,
  child_group_id int,
  child_user_id int,
  KEY (parent_id,child_group_id,child_user_id)
) ENGINE=InnoDB;

CREATE TABLE t2 (
  id int,
  lower_group_name varchar(255),
  directory_id int(20),
  UNIQUE KEY (directory_id)
) ENGINE=InnoDB;

CREATE TABLE t3 (id int) ENGINE=InnoDB;

insert into t1 values (1,1,1),(2,2,2);
insert into t2 values (10,'foo',10),(20,'bar',20);
insert into t3 values (101),(102);
set use_stat_tables = PREFERABLY, optimizer_use_condition_selectivity = 3;

select * from t1, t2, t3 
where t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10;

drop table t1,t2,t3;

--echo #
--echo # MDEV-9187: duplicate of bug mdev-9628
--echo #

set use_stat_tables = preferably;
set optimizer_use_condition_selectivity=3;
 
CREATE TABLE t1 (f1 char(32)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('foo'),('bar'),('qux');
ANALYZE TABLE t1;

SELECT * FROM t1 WHERE f1 < 'm';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE f1 < 'm';

CREATE TABLE t2 (f1 TEXT) ENGINE=InnoDB;
INSERT INTO t2 VALUES ('foo'),('bar'),('qux');
ANALYZE TABLE t2;

SELECT * FROM t2 WHERE f1 <> 'qux';
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE f1 <> 'qux';

DROP TABLE t1,t2;

--echo #
--echo # End of 10.0 tests
--echo #


--echo #
--echo # Start of 10.1 tests
--echo #

--echo #
--echo # MDEV-11060: sql/protocol.cc:532: void Protocol::end_statement(): Assertion `0' failed
--echo #


set optimizer_use_condition_selectivity=4;

--disable_warnings
drop view if exists v1;
--enable_warnings

create table t1 (a int not null, b int, c int) engine=InnoDB;
create trigger trgi before insert on t1 for each row set new.a=if(new.a is null,new.b,new.c);

create table t2 (d int, e int) engine=InnoDB;
update t1, t2 set a=NULL, b=2, c=NULL where b=d and e=200;

create view v1 as select * from t1, t2 where d=2;
--error ER_BAD_NULL_ERROR
insert v1 (a,c) values (NULL, 20);

drop table t1,t2;
drop view v1;

--echo #
--echo # End of 10.1 tests
--echo #

--echo #
--echo # MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity
--echo #

set
  @tmp_jcl=@@join_cache_level,
  @tmp_sel=@@optimizer_use_condition_selectivity;
set
  join_cache_level=3,
  optimizer_use_condition_selectivity=2;

CREATE TABLE t1 (
  c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int,
  c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int,
  c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int,
  c29 int, c30 int, c31 int, c32 int, c33 int, c34 int
) ENGINE=InnoDB;

SELECT * FROM t1
WHERE
  (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
   c11, c12, c13, c14, c15, c16, c17, c18, c19,
   c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
   c30, c31, c32, c33, c34) IN (SELECT * FROM t1) ;

set
  join_cache_level=@tmp_jcl,
  optimizer_use_condition_selectivity=@tmp_sel;
drop table t1;

--echo # 
--echo # MDEV-25013: SIGSEGV in best_extension_by_limited_search | SIGSEGV in restore_prev_nj_state
--echo # 

SET join_cache_level=3;
CREATE TABLE t1 (
  TEXT1 TEXT, TEXT2 TEXT, TEXT3 TEXT, TEXT4 TEXT, TEXT5 TEXT,
  TEXT6 TEXT, TEXT7 TEXT, TEXT8 TEXT, TEXT9 TEXT, TEXT10 TEXT,
  TEXT11 TEXT, TEXT12 TEXT,TEXT13 TEXT,TEXT14 TEXT,TEXT15 TEXT,
  TEXT16 TEXT,TEXT17 TEXT,TEXT18 TEXT,TEXT19 TEXT,TEXT20 TEXT,
  TEXT21 TEXT,TEXT22 TEXT,TEXT23 TEXT,TEXT24 TEXT,TEXT25 TEXT,
  TEXT26 TEXT,TEXT27 TEXT,TEXT28 TEXT,TEXT29 TEXT,TEXT30 TEXT,
  TEXT31 TEXT,TEXT32 TEXT,TEXT33 TEXT,TEXT34 TEXT,TEXT35 TEXT,
  TEXT36 TEXT,TEXT37 TEXT,TEXT38 TEXT,TEXT39 TEXT,TEXT40 TEXT,
  TEXT41 TEXT,TEXT42 TEXT,TEXT43 TEXT,TEXT44 TEXT,TEXT45 TEXT,
  TEXT46 TEXT,TEXT47 TEXT,TEXT48 TEXT,TEXT49 TEXT,TEXT50 TEXT
) ENGINE=InnoDB;
EXPLAIN SELECT 1 FROM t1 NATURAL JOIN t1 AS t2;

set join_cache_level=@tmp_jcl;
drop table t1;
--echo #
--echo # End of 10.1 tests
--echo #

set use_stat_tables= @tmp_ust;
set optimizer_use_condition_selectivity= @tmp_oucs;
set @@global.histogram_size=@save_histogram_size;
SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;
SET GLOBAL innodb_stats_persistent=@save_stats_persistent;

--echo #
--echo # MDEV-30313 Sporadic assertion `cond_selectivity <= 1.0' failure in get_range_limit_read_cost
--echo #

CREATE TABLE t (a CHAR(8), b INT, c TIMESTAMP, KEY(b,c)) ENGINE=InnoDB;
INSERT INTO t VALUES
('g',1,'1980-09-26'),('l',2,'1979-10-07'),('e',3,'1992-04-22'),
('v',9,'1975-09-21'),('w',3,'1973-10-06'),('y',8,'1986-10-28'),
('a',4,'2015-02-15'),('v',9,'1980-01-13'),('f',1,'1972-02-27'),
('z',7,'1981-05-25'),('z',8,'1980-06-14'),('c',9,'1985-01-24'),
('x',5,'1999-12-14'),('h',3,'1994-12-18'),('j',6,'1985-08-17'),
('b',6,'1989-08-02'),('h',6,'2024-07-06'),('h',4,'2024-02-10'),
('s',1,'1981-07-21'),('c',2,'1988-09-16'),('e',3,'1981-08-26'),
('a',2,'1986-05-23'),('l',0,'1997-12-19'),('b',5,'2018-05-01'),
('q',2,'1990-01-01'),('v',9,'1982-10-12'),('x',2,'2005-04-29'),
('f',8,'2005-08-20'),('d',3,'2002-01-24'),('b',9,'1982-02-04'),
('a',4,'1978-04-12'),('c',9,'1984-06-08'),('n',9,'1983-10-19'),
('l',1,'2023-01-05'),('f',2,'1988-11-18'),('a',9,'1977-11-11'),
('k',2,'1980-09-27'),('i',7,'1988-08-09'),('e',4,'1992-07-30'),
('l',5,'1980-01-01'),('h',5,'2011-12-24'),('d',6,'2035-03-28'),
('h',7,'1994-05-14'),('y',1,'1990-01-01'),('x',6,'1981-09-12'),
('x',9,'1980-01-01'),('s',9,'1995-11-09'),('i',4,'1980-01-01'),
('p',4,'1980-01-01'),('a',6,'2026-05-05'),('c',6,'1991-09-23'),
('l',8,'1980-01-01'),('n',4,'1999-09-15'),('b',1,'2011-07-23'),
('a',9,'1980-01-01'),('a',0,'1977-12-21'),('v',6,'1986-10-29'),
('r',0,'1997-03-27'),('a',9,'2000-05-05'),('x',1,'1990-01-01'),
('n',7,'1985-08-01'),('m',6,'1994-09-14'),('s',9,'2009-09-27'),
('r',8,'2028-10-30'),('e',6,'1982-08-31'),('x',0,'1989-12-21'),
('d',0,'1984-06-24'),('r',6,'1982-02-11'),('a',3,'1997-10-22'),
('s',9,'2007-08-29'),('a',3,'1990-01-01'),('o',1,'2015-02-10'),
('x',0,'1978-08-30'),('k',5,'1989-06-15'),('b',0,'1984-08-21'),
('v',0,'1990-01-01'),('a',9,'1993-06-23'),('n',5,'1979-11-10'),
('o',8,'2024-08-31'),('k',6,'1983-12-25'),('y',5,'2013-02-19'),
('a',9,'1989-12-03'),('k',4,'1973-08-07'),('o',7,'1988-03-19'),
('o',3,'2007-01-07'),('t',6,'1990-02-22'),('f',4,'2032-10-22'),
('p',0,'1977-09-12'),('f',3,'2036-11-26'),('a',9,'2008-06-26'),
('k',2,'2004-09-11'),('x',1,'2005-07-28'),('s',8,'2027-08-28'),
('a',8,'2000-06-11'),('a',7,'2005-05-20'),('u',9,'1980-01-01'),
('v',5,'1990-01-01'),('x',7,'1984-11-01'),('a',1,'2006-05-14');

SELECT b FROM t WHERE a > 'a' GROUP BY b HAVING b >= 6 OR b <= 0;

# Cleanup
DROP TABLE t;

--echo #
--echo # MDEV-30693: Assertion `dbl_records <= s->records' failed in apply_selectivity_for_table on SELECT
--echo #
set @tmp_oucs= @@optimizer_use_condition_selectivity;
CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB;
SELECT * FROM (SELECT * FROM t1) a JOIN (SELECT * FROM (SELECT * FROM t1 GROUP BY c) d WHERE c>1) b ON a.c=b.c;
DROP TABLE t1;
SET optimizer_use_condition_selectivity=@tmp_oucs;

--echo #
--echo # End of 11.0 tests
--echo #