Bug #1034 SELECT *, time_to_sec(time) does not overwrite the time column
Submitted: 12 Aug 2003 0:22 Modified: 16 Mar 2006 13:28
Reporter: Denis Banovic Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.14 standard OS:Linux (Red Hat AS 2.1)
Assigned to: Heikki Tuuri CPU Architecture:Any

[12 Aug 2003 0:22] Denis Banovic
Description:
In 4.0.14 there is a strange behaviour by overwriting the column names

in 4.1, the behaviour is the same as in 3.xx versions, 
select *, time_to_sec(time) returns modified time column

in 4.0.XX 
SELECT *, time_to_sec(time) does not overwrite the time column =>you get datetime

SELECT time_to_sec(time),xxx.* will do the right thing, but this happens only in the 4.0.XX version

I think this should be fixed, because this is used in many applications...

Thanks

Denis

I'm using InnoDB

How to repeat:
create table XXX (
id int(11) NOT NULL auto_increment PRIMARY KEY, 
mytime datetime not null
) 

insert into XXX (  mycolumn) values  (  NOW())

select *,time_to_sec(mycolumn) as mycolumn from XXX 

select time_to_sec(mycolumn) as mycolumn, XXX.* from XXX 

Suggested fix:
Just take the select order from the 4.1 version.
[12 Aug 2003 3:37] Alexander Keremidarski
I am affraid I don't understand what do you mean by 
"SELECT *, time_to_sec(time) does not overwrite the time column"

This query is not supposed to overwrite anything.

I used following table in all 3.23, 4.0 and 4.1
CREATE TABLE `x` (
  `id` int(11) NOT NULL auto_increment,
  `mytime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB

and
INSERT INTO x (mytime) VALUES(NOW());

I got exactly the same result in all 3.23, 4.0 and 4.1:

mysql> SELECT *, TIME_TO_SEC(mytime) AS mytime FROM x;
+----+---------------------+--------+
| id | mytime              | mytime |
+----+---------------------+--------+
|  1 | 2003-08-12 13:32:23 |  48743 |
+----+---------------------+--------+
1 row in set (0.00 sec)
 
mysql> SELECT TIME_TO_SEC(mytime) AS mytime, x.* FROM x;
+--------+----+---------------------+
| mytime | id | mytime              |
+--------+----+---------------------+
|  48743 |  1 | 2003-08-12 13:32:23 |
+--------+----+---------------------+

(the only difference is content of mytime column as I ran INSERT in different moments of time)

In all cases there are two columns called `mytime`
[12 Aug 2003 5:57] Denis Banovic
Hi!
This was a very fast response:

Here is a Perl Script to make it easier to understand what I want to say ( sometimes it's not easy with my english) 

#!/usr/bin/perl

use Apache::DBI();
use Data::Dumper;
my $dbh = DBI->connect( "dbi:mysql:mymon;host=localhost;3306,mymon,mymon");

my $th = $dbh->prepare("SELECT *, TIME_TO_SEC(mytime) AS mytime FROM x");
$th->execute() || die $dbh->errstr;

print Dumper $th->fetchrow_hashref;

$th = $dbh->prepare("SELECT TIME_TO_SEC(mytime) as mytime, x.* FROM x");
$th->execute() || die $dbh->errstr;

print Dumper $th->fetchrow_hashref;

Results on 4.0.14:  ( perl 5.61 )
[root@server1 admin]# perl test.pl
$VAR1 = {
          'mytime' => '2003-08-12 08:29:46',
          'id' => '1'
        };
$VAR1 = {
          'mytime' => '30586',
          'id' => '1'
        };
#############
Results on 4.1 and 3.xx ( perl 5.6 and perl 5.8 )

[root@picard tmp]# perl test2.pl
$VAR1 = {
          'id' => '1',
          'mytime' => '53413'
        };
$VAR1 = {
          'id' => '1',
          'mytime' => '2003-08-12 14:50:13'
        };

Is this maybe the problem of Apache::DBI or DBI itself?

I have the latest Apache::DBI on both machines.

Thanks

Denis
[24 Sep 2003 2:39] Michael Widenius
I just tested this with the MySQL 4.0 version from the BK tree:

(/my/mysql-4.0) perl /tmp/skr2
$VAR1 = {
          'id' => '1',
          'mytime' => '45694'
        };
$VAR1 = {
          'id' => '1',
          'mytime' => '2003-09-24 12:41:34'
        };

Are you sure you are using the right libmysqlclient library / MySQL include file in your perl installation ?

Try to use only the static MySQL libraries, to ensure that you don't get a library conflict between DBI:mysql and libmysqlclient.
(I shall try to get DBI:mysql to check this automaticly in the future)

Anyway, I am not 100 % sure that you can depend on fetchrow_hashref to get the id's or columns in any order or which column should override what.
My guess is that at least the order is unspecified.
[29 Jan 2004 13:03] Heikki Tuuri
Hi!

Changing the status of this bug report to 'No feedback', because no feedback in 127 days.

Regards,

Heikki
[29 Jan 2004 23:36] Denis Banovic
Hi!
I was trying out to find more about this behaviour, but without success.
I just now, that when using fetchrow_hashref under 3.xx I'm always getting other result than under 4.xx. I know that it's not mysql's fault.

Thanks

Denis