DBAL-434: Incorrect type mapping on Oracle Platform

[doctrinebot]

Jira issue originally created by user eagleoneraptor:

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!

[doctrinebot]

Comment created by @beberlei:

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.

[doctrinebot]

Comment created by johnkary:

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)

[doctrinebot]

Comment created by @beberlei:

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

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

[doctrinebot]

Issue was closed with resolution "Can't Fix"

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.