1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
--source include/have_innodb.inc
SET SESSION STORAGE_ENGINE='InnoDB';
set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
set optimizer_switch='extended_keys=on';
--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 # End of 10.0 tests
--echo #
set use_stat_tables= @tmp_ust;
set optimizer_use_condition_selectivity= @tmp_oucs;
SET SESSION STORAGE_ENGINE=DEFAULT;
|
