Description:
My program runs the query
"PREPARE stmt FROM "SELECT id_col,time_6,time_0 FROM test_time WHERE test_time.time_0 IN (?, '06:06:06.000000','00:00:00');";
set @time_val='07:59:59';
execute stmt USING @time_val;".
I expected MySQL to return the data which time_0 equals '07:59:59' or '06:06:06' or '00:00:00' ".
It gave me nothings.The same problem occurs when I query time data with precision, but the problem is slightly different, as shown below.
query:PREPARE stmt FROM "SELECT a FROM t WHERE a IN ( , );
type of a IN('07:59:59','06:06:06') IN(?,'06:06:06') IN(?,?)
set @val_1='07:59:59'; set @val_1='07:59:59';set @val_2='06:06:06';
execute stmt USING @val_1; execute stmt USING @val_1,@val_2;
--------------------------------------------------------------------------------------------------
time can find the data which
a equals '07:59:59' or find nothing find nothing
'06:06:06'
time(n) can find the data which can find the data which can find the data which
a equals '07:59:59' or a equals '07:59:59' a equals '07:59:59' or '06:06:06'
'06:06:06'
How to repeat:
CREATE TABLE test_time(id_col int,`time_6` time(6) DEFAULT '01:01:01.000000', `time_0` time);
insert into test_time(id_col,time_6,time_0) values
(31,'07:59:59.000000', '07:59:59'),
(32,'07:59:59.000000', '07:59:59'),
(51,'00:00:00.000000', '00:00:00'),
(52,'00:00:00.000000', '00:00:00'),
(71,'06:06:06.000000', '06:06:06'),
(72,'06:06:06.000000', '06:06:06');
prepare stmt from "SELECT id_col,time_6,time_0 FROM test_time WHERE test_time.time_6 IN (?, '06:06:06.000000','00:00:00');";
set @time_val='07:59:59';
execute stmt USING @time_val;
Description: My program runs the query "PREPARE stmt FROM "SELECT id_col,time_6,time_0 FROM test_time WHERE test_time.time_0 IN (?, '06:06:06.000000','00:00:00');"; set @time_val='07:59:59'; execute stmt USING @time_val;". I expected MySQL to return the data which time_0 equals '07:59:59' or '06:06:06' or '00:00:00' ". It gave me nothings.The same problem occurs when I query time data with precision, but the problem is slightly different, as shown below. query:PREPARE stmt FROM "SELECT a FROM t WHERE a IN ( , ); type of a IN('07:59:59','06:06:06') IN(?,'06:06:06') IN(?,?) set @val_1='07:59:59'; set @val_1='07:59:59';set @val_2='06:06:06'; execute stmt USING @val_1; execute stmt USING @val_1,@val_2; -------------------------------------------------------------------------------------------------- time can find the data which a equals '07:59:59' or find nothing find nothing '06:06:06' time(n) can find the data which can find the data which can find the data which a equals '07:59:59' or a equals '07:59:59' a equals '07:59:59' or '06:06:06' '06:06:06' How to repeat: CREATE TABLE test_time(id_col int,`time_6` time(6) DEFAULT '01:01:01.000000', `time_0` time); insert into test_time(id_col,time_6,time_0) values (31,'07:59:59.000000', '07:59:59'), (32,'07:59:59.000000', '07:59:59'), (51,'00:00:00.000000', '00:00:00'), (52,'00:00:00.000000', '00:00:00'), (71,'06:06:06.000000', '06:06:06'), (72,'06:06:06.000000', '06:06:06'); prepare stmt from "SELECT id_col,time_6,time_0 FROM test_time WHERE test_time.time_6 IN (?, '06:06:06.000000','00:00:00');"; set @time_val='07:59:59'; execute stmt USING @time_val;