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: | |
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
[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