MySQL Bugs: #11325: Wrong date comparison in views

Bug #11325	Wrong date comparison in views
Submitted:	14 Jun 2005 16:08	Modified:	24 Jun 2005 17:12
Reporter:	Jair Karim Tinoco Castrejón	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.6 beta	OS:	Windows (Windows XP Professional)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In a view the date comparison sentence
select * from jortiempos where fecha='2005.06.10';
Doesn't produce results but the sentence
select * from jortiempos where fecha='2005-06-10';
It does produce the correct resultset.

Jair Karim

How to repeat:
This is the primary table:

C:\Archivos de programa\MySQL\MySQL Server 5.0\bin>mysqlshow -u sadascon -p sada
smov -t jornadas
Enter password: ********
Database: sadasmov  Table: jornadas
+------------+----------+-------------------+------+-----+---------+-------+----
-----------------------------+---------+
| Field      | Type     | Collation         | Null | Key | Default | Extra | Pri
vileges                      | Comment |
+------------+----------+-------------------+------+-----+---------+-------+----
-----------------------------+---------+
| ID_JORNADA | int(11)  |                   | NO   | PRI |         |       | sel
ect,insert,update,references |         |
| DBKEY      | char(40) | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| ZONA       | char(2)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| CENTRO     | char(2)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| TURNO      | char(1)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| CUADRILLA  | char(2)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| RPE        | char(5)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| RPEA1      | char(5)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| RPEA2      | char(5)  | latin1_swedish_ci | YES  |     |         |       | sel
ect,insert,update,references |         |
| fecha      | date     |                   | YES  |     |         |       | sel
ect,insert,update,references |         |
+------------+----------+-------------------+------+-----+---------+-------+----

This is the second table

C:\Archivos de programa\MySQL\MySQL Server 5.0\bin>mysqlshow -u sadascon -p sada
smov -t tiempos
Enter password: ********
Database: sadasmov  Table: tiempos
+----------------+----------+-------------------+------+-----+---------+-------+
---------------------------------+---------+
| Field          | Type     | Collation         | Null | Key | Default | Extra |
 Privileges                      | Comment |
+----------------+----------+-------------------+------+-----+---------+-------+
---------------------------------+---------+
| ID_JORNADA     | int(11)  |                   | YES  | MUL |         |       |
 select,insert,update,references |         |
| DBKEY          | char(40) | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
| ID_TIEMPO      | int(11)  |                   | NO   | PRI |         |       |
 select,insert,update,references |         |
| TIPO_TIEMPO    | char(2)  | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
| FECHAHORA_INI  | datetime |                   | YES  |     |         |       |
 select,insert,update,references |         |
| FECHAHORA_FIN  | datetime |                   | YES  |     |         |       |
 select,insert,update,references |         |
| TRAB_PENDIENTE | int(11)  |                   | YES  |     |         |       |
 select,insert,update,references |         |
| TIPO_COMPLE    | char(2)  | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
| ID_CURSO       | int(11)  |                   | YES  |     |         |       |
 select,insert,update,references |         |
| SEC_PREF       | char(15) | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
| SEC_ALT        | char(15) | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
| TRASLADO       | int(11)  |                   | YES  |     |         |       |
 select,insert,update,references |         |
| DESCRIP        | char(25) | latin1_swedish_ci | YES  |     |         |       |
 select,insert,update,references |         |
+----------------+----------+-------------------+------+-----+---------+-------+
---------------------------------+---------+

This is the create view statement:

create view jortiempos (id_tiempo,id_jornada,fecha,hcp,htp,cuadrilla,rpe,rpea1,rpea2,centro,zona,turno,tipo_tiempo,sec_pref,sec_alt) as select x1.id_tiempo ,x1.id_jornada ,x0.fecha ,x1.fechahora_ini ,x1.fechahora_fin ,x0.cuadrilla ,x0.rpe ,x0.rpea1 ,x0.rpea2 ,x0.centro ,x0.zona ,x0.turno ,x1.tipo_tiempo ,x1.sec_pref ,x1.sec_alt from "sadascon".jornadas x0 , tiempos x1 where (x0.id_jornada = x1.id_jornada ) ;

Verified with latest 5.0 BK tree.

Test case:
create table jornadas(
ID_JORNADA int(11) primary key,
DBKEY char(40),
ZONA char(2),
CENTRO char(2),
TURNO char(1), 
CUADRILLA char(2),
RPE char(5),
RPEA1 char(5),
RPEA2 char(5),
fecha date
);

create table tiempos (
ID_JORNADA int(11),
DBKEY char(40),
ID_TIEMPO int(11),
TIPO_TIEMPO char(2),
FECHAHORA_INI datetime,
FECHAHORA_FIN datetime,
TRAB_PENDIENTE int(11),
TIPO_COMPLE char(2),
ID_CURSO int(11),
SEC_PREF char(15),
SEC_ALT  char(15),
TRASLADO int(11),
DESCRIP  char(25)
);

insert into jornadas(ID_JORNADA, fecha) values (1, now()), (2, '2005-06-10');
insert into tiempos(ID_JORNADA) values(1),(2);

create view jortiempos
(id_tiempo,id_jornada,fecha,hcp,htp,cuadrilla,rpe,rpea1,rpea2,centro,zona,turno,
tipo_tiempo,sec_pref,sec_alt) as select x1.id_tiempo ,x1.id_jornada ,x0.fecha
,x1.fechahora_ini ,x1.fechahora_fin ,x0.cuadrilla ,x0.rpe ,x0.rpea1 ,x0.rpea2
,x0.centro ,x0.zona ,x0.turno ,x1.tipo_tiempo ,x1.sec_pref ,x1.sec_alt from
jornadas x0 , tiempos x1 where (x0.id_jornada = x1.id_jornada ) ;

select * from jortiempos where fecha='2005-06-10';
select * from jortiempos where fecha='2005.06.10';

Wrong comparing method were choosen which results in false comparison.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26383

Fixed in 5.0.9, cset 1.1998.10.1

Documented in 5.0.9 changelog:

<listitem><para>Wrong comparison method used in <literal>VIEW</literal> when relaxed date syntax used (i.e. <literal>2005.06.10</literal>). (Bug #11325)</para></listitem>