Bug #12454 Do not convert datetime in datetime where sort result set in join.

• Submitted: 9 Aug 2005 9:35
• Modified: 15 Aug 2005 7:51
• Reporter: Valentin Komissarov
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.11
• OS: Windows (Win32)
• CPU Architecture: Any

[9 Aug 2005 9:35] Valentin Komissarov

Description:
Do not convert datetime in datetime where sort result set in join (and left join).

How to repeat:
#
# Structure for the `table1` table : 
#

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `ID` int(11) NOT NULL auto_increment,
  `ID_Table2` int(11) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `table2` table : 
#

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `ID` int(11) NOT NULL auto_increment,
  `Value` varchar(50) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Data for the `table1` table  (LIMIT 0,500)
#

INSERT INTO `table1` (`ID`, `ID_Table2`) VALUES 
  (1,1),
  (2,2),
  (3,3);

COMMIT;

#
# Data for the `table2` table  (LIMIT 0,500)
#

INSERT INTO `table2` (`ID`, `Value`) VALUES 
  (1,'09.08.2005'),
  (2,'01.08.2005'),
  (3,'01.07.2005');

COMMIT;

#REPEAT BUG

select STR_TO_DATE(t2.Value,GET_FORMAT(DATE,'EUR')) as DateValue
from table1 t
left join table2 t2 on t2.ID=t.ID_Table2
/*ORDER BY DateValue*/;

#RESULT IS DATETIME
09.08.2005
01.08.2005
01.07.2005

select STR_TO_DATE(t2.Value,GET_FORMAT(DATE,'EUR')) as DateValue
from table1 t
left join table2 t2 on t2.ID=t.ID_Table2
ORDER BY DateValue;

#RESULT NOT IS DATETIME !!!
2005-07-01
2005-08-01
2005-08-09

[9 Aug 2005 9:36] Valentin Komissarov

#RESULT NOT IS DATETIME !!! RESULT IS VARCHAR
2005-07-01
2005-08-01
2005-08-09

[10 Aug 2005 7:35] Valentin Komissarov

CAST() - in this queryes makes as itself

[11 Aug 2005 18:26] Aleksey Kishkin

Hi!

mysql> create table ttt select STR_TO_DATE(t2.Value,GET_FORMAT(DATE,'EUR')) as DateValue
    -> from table1 t
    -> left join table2 t2 on t2.ID=t.ID_Table2;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table ttt \G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `DateValue` date default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Could you please to write more detailed how you checked type of results? What client did you use?

[15 Aug 2005 7:51] Valentin Komissarov

Hi Aleksey.

Test in
MySQL 5.0.11-beta-nt

Bug closed.
Thanks.

PS.
I use EMS MySQL Manager 3.3.0.4 Lite
I understand, that the mistake can disappear in the client, but more often it is a mistake of a server.

Best Regards.