|
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (
|
a INT, b VARCHAR(1), c INT,
|
KEY(a), KEY(b)
|
) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES
|
(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
|
(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
|
(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
|
(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
|
CREATE TABLE t2 (
|
pk INT, d VARCHAR(1), e INT,
|
PRIMARY KEY(pk), KEY(d,e)
|
) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES
|
(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
|
(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
|
(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
|
(15,'g',6),(16,'x',7),(17,'f',8);
|
SELECT * FROM t1,t2 WHERE a=d;
|
SHOW WARNINGS;
|
returns warnings as expected:
+---------+------+---------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x' |
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'd' |
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'r' |
|
....
|
If I change the engine to InnoDB, the warnings are not displayed any more:
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (
|
a INT, b VARCHAR(1), c INT,
|
KEY(a), KEY(b)
|
) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES
|
(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
|
(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
|
(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
|
(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
|
CREATE TABLE t2 (
|
pk INT, d VARCHAR(1), e INT,
|
PRIMARY KEY(pk), KEY(d,e)
|
) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES
|
(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
|
(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
|
(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
|
(15,'g',6),(16,'x',7),(17,'f',8);
|
SELECT * FROM t1,t2 WHERE a=d;
|
SHOW WARNINGS;
|
The problem happens because in case of InnoDB, execution goes through do_field_string():
#0 Field_num::get_int (this=0x7fff8e8dbe20,
|
cs=0x555556b78c80 <my_charset_latin1>, from=0x7fff8e81c68e "d\002", len=1,
|
rnd=0x7ffff7f7c768, unsigned_max=4294967295, signed_min=-2147483648,
|
signed_max=2147483647) at /home/bar/maria-git/server.10.2/sql/field.cc:1585
|
#1 0x0000555555c405e1 in Field_long::store (this=0x7fff8e8dbe20,
|
from=0x7fff8e81c68e "d\002", len=1, cs=0x555556b78c80 <my_charset_latin1>)
|
at /home/bar/maria-git/server.10.2/sql/field.cc:4027
|
#2 0x0000555555c5de78 in do_field_string (copy=0x7fff8e8dbda0)
|
at /home/bar/maria-git/server.10.2/sql/field_conv.cc:372
|
Field_num::get_int() does not produce the warnings because count_cuted_field is equal to CHECK_FIELD_IGNORE.
In case of MyISAM the optimizer chooses a different plan, execution goes through this code:
#3 0x00005555559864a4 in Value_source::Converter_strntod_with_warn::Converter_strntod_with_warn (this=0x7ffff7f33a30, thd=0x7fff98709070, filter=...,
|
cs=0x555556b78c80 <my_charset_latin1>, str=0x7fff9866db8e "x\001",
|
length=1) at /home/bar/maria-git/server.10.2/sql/field.h:202
|
#4 0x0000555555c4c137 in Field_varstring::val_real (this=0x7fff9866dc88)
|
at /home/bar/maria-git/server.10.2/sql/field.cc:7437
|
#5 0x0000555555c7f6ea in Item_field::val_real (this=0x7fff8d422f98)
|
at /home/bar/maria-git/server.10.2/sql/item.cc:2463
|
#6 0x0000555555c9c96c in Arg_comparator::compare_real (this=0x7fff8d423140)
|
at /home/bar/maria-git/server.10.2/sql/item_cmpfunc.cc:864
|
#7 0x0000555555caeec0 in Arg_comparator::compare (this=0x7fff8d423140)
|
at /home/bar/maria-git/server.10.2/sql/item_cmpfunc.h:87
|
The warnings ARE displayed, because Converter_strntod_with_warn checks for thd->no_errors rather than thd->count_cuted_fields.
The execution path for InnoDB should be fixed to call do_field_int() instead of do_field_string().
This will force Field_long to produce warnings on Field_long::store("d", 1, &my_charset_latin1).
|