mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
4e9322e2ff
Raise notes if indexes cannot be used:
- in case of data type or collation mismatch (diferent error messages).
- in case if a table field was replaced to something else
(e.g. Item_func_conv_charset) during a condition rewrite.
Added option to write warnings and notes to the slow query log for
slow queries.
New variables added/changed:
- note_verbosity, with is a set of the following options:
basic - All old notes
unusable_keys - Print warnings about keys that cannot be used
for select, delete or update.
explain - Print unusable_keys warnings for EXPLAIN querys.
The default is 'basic,explain'. This means that for old installations
the only notable new behavior is that one will get notes about
unusable keys when one does an EXPLAIN for a query. One can turn all
of all notes by either setting note_verbosity to "" or setting sql_notes=0.
- log_slow_verbosity has a new option 'warnings'. If this is set
then warnings and notes generated are printed in the slow query log
(up to log_slow_max_warnings times per statement).
- log_slow_max_warnings - Max number of warnings written to
slow query log.
Other things:
- One can now use =ALL for any 'set' variable to set all options at once.
For example using "note_verbosity=ALL" in a config file or
"SET @@note_verbosity=ALL' in SQL.
- mysqldump will in the future use @@note_verbosity=""' instead of
@sql_notes=0 to disable notes.
- Added "enum class Data_type_compatibility" and changing the return type
of all Field::can_optimize*() methods from "bool" to this new data type.
Reviewer & Co-author: Alexander Barkov <bar@mariadb.com>
- The code that prints out the notes comes mainly from Alexander
478 lines
16 KiB
Text
478 lines
16 KiB
Text
drop table if exists t1,t2;
|
|
set @@note_verbosity=replace(@@note_verbosity,"explain","");
|
|
create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam;
|
|
insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6);
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '6-6' for key 'a'
|
|
explain select * from t1 where a is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a a 5 const 3 Using where; Using index
|
|
explain select * from t1 where a is null and b = 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index
|
|
explain select * from t1 where a is null and b = 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index
|
|
explain select * from t1 where a=2 and b = 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const a,b a 9 const,const 1 Using index
|
|
explain select * from t1 where a<=>b limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index
|
|
explain select * from t1 where (a is null or a > 0 and a < 2) and b < 5 limit 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a,b a 9 NULL 2 Using where; Using index
|
|
explain select * from t1 where (a is null or a = 7) and b=7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index
|
|
explain select * from t1 where (a is null or a = 7) and b=7 order by a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort
|
|
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index
|
|
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
|
|
explain select * from t1 where a > 1 and a < 3 limit 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index
|
|
explain select * from t1 where a > 8 and a < 9;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index
|
|
select * from t1 where a is null;
|
|
a b
|
|
NULL 7
|
|
NULL 9
|
|
NULL 9
|
|
select * from t1 where a is null and b = 7;
|
|
a b
|
|
NULL 7
|
|
select * from t1 where a<=>b limit 2;
|
|
a b
|
|
1 1
|
|
2 2
|
|
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
|
|
a b
|
|
1 1
|
|
2 2
|
|
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
|
|
a b
|
|
NULL 9
|
|
NULL 9
|
|
select * from t1 where (a is null or a = 7) and b=7;
|
|
a b
|
|
7 7
|
|
NULL 7
|
|
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
|
|
a b
|
|
NULL 7
|
|
NULL 9
|
|
NULL 9
|
|
select * from t1 where a > 1 and a < 3 limit 1;
|
|
a b
|
|
2 2
|
|
select * from t1 where a > 8 and a < 9;
|
|
a b
|
|
create table t2 like t1;
|
|
insert into t2 select * from t1;
|
|
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
|
|
explain select * from t1 where a is null and b = 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where
|
|
explain select * from t1 where a is null and b = 2 and c=0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where
|
|
explain select * from t1 where a is null and b = 7 and c=0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where
|
|
explain select * from t1 where a=2 and b = 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 1 Using where
|
|
explain select * from t1 where a<=>b limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where
|
|
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a,b a 5 NULL 5 Using where
|
|
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where
|
|
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where
|
|
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 3 Using where
|
|
explain select * from t1 where a > 1 and a < 3 limit 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 1 Using where
|
|
explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a,b a 5 NULL 4 Using where
|
|
explain select * from t1 where a > 8 and a < 9;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 1 Using where
|
|
explain select * from t1 where b like "6%";
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range b b 12 NULL 1 Using where
|
|
select * from t1 where a is null;
|
|
a b c
|
|
NULL 7 0
|
|
NULL 9 0
|
|
NULL 9 0
|
|
select * from t1 where a is null and b = 7 and c=0;
|
|
a b c
|
|
NULL 7 0
|
|
select * from t1 where a<=>b limit 2;
|
|
a b c
|
|
1 1 0
|
|
2 2 0
|
|
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
|
|
a b c
|
|
1 1 0
|
|
2 2 0
|
|
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
|
|
a b c
|
|
NULL 9 0
|
|
NULL 9 0
|
|
select * from t1 where (a is null or a = 7) and b=7 and c=0;
|
|
a b c
|
|
7 7 0
|
|
NULL 7 0
|
|
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
|
|
a b c
|
|
NULL 7 0
|
|
NULL 9 0
|
|
NULL 9 0
|
|
select * from t1 where b like "6%";
|
|
a b c
|
|
6 6 0
|
|
drop table t1;
|
|
rename table t2 to t1;
|
|
alter table t1 modify b int null;
|
|
insert into t1 values (7,null), (8,null), (8,7);
|
|
explain select * from t1 where a = 7 and (b=7 or b is null);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index
|
|
select * from t1 where a = 7 and (b=7 or b is null);
|
|
a b
|
|
7 7
|
|
7 NULL
|
|
explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a,b a 5 const 5 Using where; Using index
|
|
select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
|
|
a b
|
|
7 NULL
|
|
7 7
|
|
NULL 7
|
|
explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index
|
|
select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
|
|
a b
|
|
7 NULL
|
|
7 7
|
|
NULL 7
|
|
NULL 9
|
|
NULL 9
|
|
create table t2 (a int);
|
|
insert into t2 values (7),(8);
|
|
explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index
|
|
drop index b on t1;
|
|
explain select * from t2,t1 where t1.a=t2.a and b is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index
|
|
select * from t2,t1 where t1.a=t2.a and b is null;
|
|
a a b
|
|
7 7 NULL
|
|
8 8 NULL
|
|
explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index
|
|
select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
|
|
a a b
|
|
7 7 7
|
|
7 7 NULL
|
|
8 8 7
|
|
8 8 NULL
|
|
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
|
|
1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index
|
|
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
|
|
a a b
|
|
7 7 7
|
|
7 NULL 7
|
|
8 8 7
|
|
8 NULL 7
|
|
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
|
|
1 SIMPLE t1 ref_or_null a a 5 test.t2.a 5 Using where; Using index
|
|
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
|
|
a a b
|
|
7 7 NULL
|
|
7 7 7
|
|
7 NULL 7
|
|
8 8 NULL
|
|
8 8 7
|
|
8 NULL 7
|
|
insert into t2 values (null),(6);
|
|
delete from t1 where a=8;
|
|
explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
|
|
1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
|
|
explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
|
|
1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
|
|
select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
|
|
a a b
|
|
7 7 NULL
|
|
7 7 7
|
|
7 NULL 7
|
|
8 NULL 7
|
|
NULL NULL 7
|
|
NULL NULL 9
|
|
NULL NULL 9
|
|
6 6 6
|
|
6 NULL 7
|
|
drop table t1,t2;
|
|
CREATE TABLE t1 (
|
|
id int(10) unsigned NOT NULL auto_increment,
|
|
uniq_id int(10) unsigned default NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY idx1 (uniq_id)
|
|
) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (
|
|
id int(10) unsigned NOT NULL auto_increment,
|
|
uniq_id int(10) unsigned default NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
|
|
INSERT INTO t1 VALUES (11,5),(12,6),(13,7),(14,8),(15,9);
|
|
INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
|
|
explain select id from t1 where uniq_id is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL idx1 NULL NULL NULL 15 Using where
|
|
explain select id from t1 where uniq_id =1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const idx1 idx1 5 const 1
|
|
UPDATE t1 SET id=id+100 where uniq_id is null;
|
|
UPDATE t2 SET id=id+100 where uniq_id is null;
|
|
select id from t1 where uniq_id is null;
|
|
id
|
|
101
|
|
102
|
|
105
|
|
106
|
|
109
|
|
110
|
|
select id from t2 where uniq_id is null;
|
|
id
|
|
101
|
|
102
|
|
105
|
|
106
|
|
109
|
|
110
|
|
DELETE FROM t1 WHERE uniq_id IS NULL;
|
|
DELETE FROM t2 WHERE uniq_id IS NULL;
|
|
SELECT * FROM t1 ORDER BY uniq_id, id;
|
|
id uniq_id
|
|
3 1
|
|
4 2
|
|
7 3
|
|
8 4
|
|
11 5
|
|
12 6
|
|
13 7
|
|
14 8
|
|
15 9
|
|
SELECT * FROM t2 ORDER BY uniq_id, id;
|
|
id uniq_id
|
|
3 1
|
|
4 2
|
|
7 3
|
|
8 4
|
|
DROP table t1,t2;
|
|
CREATE TABLE `t1` (
|
|
`order_id` char(32) NOT NULL default '',
|
|
`product_id` char(32) NOT NULL default '',
|
|
`product_type` int(11) NOT NULL default '0',
|
|
PRIMARY KEY (`order_id`,`product_id`,`product_type`)
|
|
) ENGINE=MyISAM;
|
|
CREATE TABLE `t2` (
|
|
`order_id` char(32) NOT NULL default '',
|
|
`product_id` char(32) NOT NULL default '',
|
|
`product_type` int(11) NOT NULL default '0',
|
|
PRIMARY KEY (`order_id`,`product_id`,`product_type`)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 (order_id, product_id, product_type) VALUES
|
|
('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
|
|
('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
|
|
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
|
|
INSERT INTO t2 (order_id, product_id, product_type) VALUES
|
|
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
|
|
select t1.* from t1
|
|
left join t2 using(order_id, product_id, product_type)
|
|
where t2.order_id=NULL;
|
|
order_id product_id product_type
|
|
select t1.* from t1
|
|
left join t2 using(order_id, product_id, product_type)
|
|
where t2.order_id is NULL;
|
|
order_id product_id product_type
|
|
3d7ce39b5d4b3e3d22aaafe9b633de51 1206029 3
|
|
3d7ce39b5d4b3e3d22aaafe9b633de51 5880836 3
|
|
drop table t1,t2;
|
|
create table t1 (id int);
|
|
insert into t1 values (null), (0);
|
|
create table t2 (id int);
|
|
insert into t2 values (null);
|
|
select * from t1, t2 where t1.id = t2.id;
|
|
id id
|
|
alter table t1 add key id (id);
|
|
select * from t1, t2 where t1.id = t2.id;
|
|
id id
|
|
drop table t1,t2;
|
|
create table t1 (
|
|
id integer,
|
|
id2 integer not null,
|
|
index (id),
|
|
index (id2)
|
|
);
|
|
insert ignore into t1 values(null,null),(1,1);
|
|
Warnings:
|
|
Warning 1048 Column 'id2' cannot be null
|
|
select * from t1;
|
|
id id2
|
|
NULL 0
|
|
1 1
|
|
select * from t1 where id <=> null;
|
|
id id2
|
|
NULL 0
|
|
select * from t1 where id <=> null or id > 0;
|
|
id id2
|
|
NULL 0
|
|
1 1
|
|
select * from t1 where id is null or id > 0;
|
|
id id2
|
|
NULL 0
|
|
1 1
|
|
select * from t1 where id2 <=> null or id2 > 0;
|
|
id id2
|
|
1 1
|
|
select * from t1 where id2 is null or id2 > 0;
|
|
id id2
|
|
1 1
|
|
delete from t1 where id <=> NULL;
|
|
select * from t1;
|
|
id id2
|
|
1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int);
|
|
CREATE TABLE t2 (a int, b int, INDEX idx(a));
|
|
CREATE TABLE t3 (b int, INDEX idx(b));
|
|
CREATE TABLE t4 (b int, INDEX idx(b));
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4);
|
|
INSERT INTO t2 VALUES (1, 1), (3, 1);
|
|
INSERT INTO t3 VALUES
|
|
(NULL), (NULL), (NULL), (NULL), (NULL),
|
|
(NULL), (NULL), (NULL), (NULL), (NULL);
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t4 SELECT * FROM t3;
|
|
INSERT INTO t3 SELECT * FROM t4;
|
|
INSERT INTO t3 VALUES (2), (3);
|
|
ANALYZE table t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
15972
|
|
EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
|
|
LEFT JOIN t3 ON t2.b=t3.b;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
|
|
1 SIMPLE t2 ref idx idx 5 test.t1.a 1 Using where
|
|
1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using where; Using index
|
|
FLUSH STATUS ;
|
|
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
|
|
LEFT JOIN t3 ON t2.b=t3.b;
|
|
a a b b
|
|
1 1 1 NULL
|
|
2 NULL NULL NULL
|
|
3 3 1 NULL
|
|
4 NULL NULL NULL
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
4
|
|
SHOW STATUS LIKE "handler_read%";
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 6
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 5
|
|
DROP TABLE t1,t2,t3,t4;
|
|
CREATE TABLE t1 (
|
|
a int(11) default NULL,
|
|
b int(11) default NULL,
|
|
KEY a (a,b)
|
|
);
|
|
INSERT INTO t1 VALUES (0,10),(0,11),(0,12);
|
|
CREATE TABLE t2 (
|
|
a int(11) default NULL,
|
|
b int(11) default NULL,
|
|
KEY a (a)
|
|
);
|
|
INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12);
|
|
SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b;
|
|
a b a b
|
|
3 11 0 11
|
|
3 12 0 12
|
|
drop table t1, t2;
|
|
End of 5.0 tests
|
|
#
|
|
# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3
|
|
#
|
|
CREATE TABLE t1 (
|
|
f3 int(11),
|
|
f10 varchar(1),
|
|
KEY (f3)
|
|
);
|
|
INSERT INTO t1 VALUES ('9','k'),(NULL,'r');
|
|
SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL);
|
|
f3 f10
|
|
DROP TABLE t1;
|
|
## end of 10.6 tests
|
|
set @@note_verbosity=default;
|