[DBAL-434] Incorrect type mapping on Oracle Platform Created: 30/Jan/13  Updated: 17/Feb/13  Resolved: 30/Jan/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Damián Nohales Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: None
Environment:

Oracle 10g on Windows, using Doctrine 2 with Symfony2



 Description   

Hello,

I'm noticed a strange behaviour when I executed a migration for my Oracle database. Doctrine detects changes on all date columns, generating queries like:

ALTER TABLE the_table MODIFY (the_date DATE DEFAULT NULL);

even if the entity property was not modified. Check in out the Doctrine 2 source code I've detected that Doctrine map the DATE Oracle columns as datetime type, but entity date property are mapped as date (as it should be), so Doctrine believes that entity property and column type are different and generate the alter query.

I found the problem on the method Doctrine\DBAL\Platforms\OraclePlatform::initializeDoctrineTypeMappings, when it say 'date' => 'datetime' should say 'date' => 'date', that fix the migration problem and does not break my application.

Was a typo?

Thanks!



 Comments   
Comment by Benjamin Eberlei [ 30/Jan/13 ]

The problem is that Oracle has a "DATE" type, which is actually a DATETIME. That is why we map it to Doctrine's Datetime type.

As a workaround, you can set this information yourself using:

$conn->getDatabasePlatform()->registerDoctrineTypeMapping('date', 'date');

Be aware this is a global change for all columns. If you map DateTimes to a TIMESTAMP field you are good to go though.

Comment by John Kary [ 17/Feb/13 ]

I ran into this same issue with the default OraclePlatform configuration. I was trying to run doctrine:schema:update --force from the Symfony2 Console on my Oracle database, and ALTER statements were generated for some DATE columns that were fully up to date when looking at the actual table.

But the major issue I ran into was these unnecessary ALTER statements were for DATE columns with NOT NULL constraints, resulting in a query like ALTER TABLE your_table MODIFY (created_at_date DATE NOT NULL); Yet when running this query, Oracle throws an error: ORA-01442: column to be modified to NOT NULL is already NOT NULL. So I could no longer use doctrine:schema:update to update my schema during development against Oracle.

While technically correct, Oracle DATE types actually store time data in addition to date data, I don't agree that this should be considered Doctrine's default behavior.

I believe Oracle DATE columns should be date-only (e.g. Day, Month, Year) and time data should be disregarded. All other drivers except SQLServer map "date" fields to Doctrine's "date" type. I would rather see someone wanting to store time data with their DATE field need to make the suggested change, instead of the other way around:

$conn->getDatabasePlatform()->registerDoctrineTypeMapping('date', 'datetime');

Oracle 11g Release 1 DATE type docs: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847)

Comment by Benjamin Eberlei [ 17/Feb/13 ]

Well the other thing is, that we cannot change this for BC reasons.

We could introduce a new platform that solves this issue though.

Generated at Tue Jul 29 08:41:49 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.