MySQL Bugs: #13922: Mysql insert into with the select query takes default values.

Bug #13922	Mysql insert into with the select query takes default values.
Submitted:	11 Oct 2005 10:57	Modified:	11 Oct 2005 12:13
Reporter:	Roshan Abraham	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have the following query from pHp

$query = "insert into exportbankdetails (ContractorBank_ID, Contractor_ID, ContractorBank_BSB_No, ContractorBank_Account, ContractorBank_Account_No, ContractorBank_Pay_Pct, ContractorBank_Pay, ContractorBank_TT_Overseas, ContractorBank_Default, Date_Last_Modified, Inactive, ContractorBank_Bank, ContractorBank_Address) select * from contractorbank where contractorbank_id = '2132'" 

all the data i want gets moved to exportbankdetails perfectly fine. 
However, there is a column in the contractorbank table called Date_Last_Modified
1) Name of column = Date_Last_Modified
2) Datatype of column = TIMESTAMP
3) Default value = CURRENT_TIMESTAMP

when the insert statement is called all the values in the row with contractorbank_id = '2132' gets transferred to exportbankdetails but the Date_Last_Modified is not the value in row 2132 but the current time when the insert query is executed. 

I guess this should not happen and the data in the row 2132 should be moved accross.

Please do let me know if you need any more information regarding this!

How to repeat:
Tried to create a new table with a column having default value as CURRENT_TIMESTAMP but cannot do so.
I guess this is because the contractorbank table was created on an earlier version of mysql and then bought to 4.1.7 
In the earlier version of mysql the default value of CURRENT_TIMESTAMP could be used for timestamps.

Suggested fix:
I guess that inserting data into any table from any other table should use the values held by the table in the select portion of the query.
Please do let me know if you need any more information to this.

Thank you for a problem report. Yes, additional information is really needed. Please, send the results of "SHOW CREATE TABLE exportbankdetails" command.

By the way, you can use many functions to provide a default values for timestamp columns (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html). Try to use NOW() simply.

What do we really need is a repeatable sequence of actions that demonstrates a problem. Please, try to provide one, and use newer version of MySQL (4.1.14). Your 4.1.7 is really old.

As requested here are the exportbankdetails 

'exportbankdetails', 'CREATE TABLE `exportbankdetails` (
  `ContractorBank_ID` int(11) default NULL,
  `Contractor_ID` int(11) default NULL,
  `ContractorBank_BSB_No` varchar(100) default NULL,
  `ContractorBank_Account` varchar(100) default NULL,
  `ContractorBank_Account_No` varchar(100) default NULL,
  `ContractorBank_Pay_Pct` decimal(18,4) default '0.0000',
  `ContractorBank_Pay` decimal(19,4) default '0.0000',
  `ContractorBank_TT_Overseas` tinyint(1) default '0',
  `ContractorBank_Default` tinyint(1) default NULL,
  `Date_Last_Modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Inactive` tinyint(1) default '0',
  `ContractorBank_Bank` varchar(30) default NULL,
  `ContractorBank_Address` varchar(200) default NULL,
  `ExportBankDetails_ID` int(11) NOT NULL auto_increment,
  `Invoice_ID` int(10) unsigned default NULL,
  `timesheet_id` int(10) unsigned default NULL,
  `Payroll_ID` int(10) unsigned default NULL,
  PRIMARY KEY  (`ExportBankDetails_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

I've got it. It's all because of "on update" clause in:

Date_Last_Modified` timestamp NOT NULL default CURRENT_TIMESTAMP 
on update CURRENT_TIMESTAMP,

According to the manual (http://dev.mysql.com/doc/mysql/en/news-4-1-2.html):

"Added support for DEFAULT CURRENT_TIMESTAMP  and for ON UPDATE CURRENT_TIMESTAMP  specifications for TIMESTAMP columns. Now you can explicitly say that a TIMESTAMP  column should be set automatically to the current timestamp for INSERT and/or UPDATE  statements, or even prevent the column from updating automatically. Only one column with such an auto-set feature per table is supported. TIMESTAMP columns created with earlier versions of MySQL behave as before. Behavior of TIMESTAMP columns that were created without explicit specification of default/on as earlier depends on its position in table: If it is the first TIMESTAMP column, it be treated as having been specified as TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. In other cases, it would be treated as a TIMESTAMP DEFAULT 0  column. NOW is supported as an alias for CURRENT_TIMESTAMP."

So, it is the intended behaviour. You have to remove ON UPDATE clause or just change the logic of your actions.

Thanks Valeriy Kravchuk.
Excellent Job ;)