Empty DATE fields cannot be retrieved

[fnicollet]

Hello,

First of all, thanks a lot for your work on this project, it is incredibly useful :)

I am running latest PG 9.5 (9.5.3), using oracle_fdw-1.4.0-pg95-win64 that I downloaded from Github, with Oracle instantclient-basic-windows.x64-12.1.0.2.0. This is all running on my machine on Windows 10 64-Bits.
I successfully used IMPORT FOREIGN SCHEMA but when I try to select one of the tables, I get the following error:

select * from esi."esi_itin_itineraire2" limit 10;

ERREUR:  valeur du champ date/time en dehors des limites : « 0000-00-00 00:00:00 AD »
CONTEXT:  converting column "date_inscription_pdesi" for foreign table scan of "esi_itin_itineraire2", row 2

Means (in french) that the value is out of date/time bounds.
The field of type DATE, nullable. And it does indeed contain some null values:

I looked at the issues and found some related to date encoding but fixed in the 1.4.0 changelog.
I think it might be related to the fact that the value is null and not considered null during the "transfer" or something.

Thanks for your help,
Fabien

[laurenz]

It works here (on 32-bit Windows and Linux) with the latest version from git, and I don't think that has changed since 1.4.0.

In Oracle:

SQL> CREATE TABLE datetest (
        id NUMBER(5) CONSTRAINT datetest_pkey PRIMARY KEY,
        d DATE
     ) SEGMENT CREATION IMMEDIATE;
SQL> INSERT INTO datetest VALUES (1, CURRENT_DATE);
SQL> INSERT INTO datetest VALUES (2, NULL);
SQL> INSERT INTO datetest VALUES (3, to_date('2100-01-01', 'YYYY-MM-DD'));
SQL> INSERT INTO datetest VALUES (4, to_date('100-01-01 BC', 'YYYY-MM-DD BC'));
SQL> COMMIT;

In PostgreSQL:

test=> CREATE FOREIGN TABLE datetest (
          id integer OPTIONS (key 'true') NOT NULL,
          d date
       ) SERVER oracle OPTIONS (table 'DATETEST');
test=> SELECT * FROM datetest;
 id |       d
----+---------------
  1 | 2016-05-20
  2 |
  3 | 2100-01-01
  4 | 0100-01-01 BC
(4 Zeilen)

NULLs should come across as NULLs.

• Can you provide the Oracle and PostgreSQL table definitions?
• Can you provide a self-contained test case that exhibits the behaviour?
• Can you verify in Oracle that the columns are indeed NULL?

[fnicollet]

Thanks for the quick answer!

While during your tests, I found out that it doesn't fail on records with NULL values, it failes on some records which even have a date value.

Here is a describe of the Oracle table:

CODE_ITIN                                 NOT NULL NUMBER(11)
 NOM                                                VARCHAR2(250)
 L_ESI_ITIN_TYPE_ESI                                VARCHAR2(100)
 L_ESI_CTRL_SOUS_TYPE_ESI                           VARCHAR2(100)
 NUMERO_DOSSIER                                     VARCHAR2(20)
 NOM_PROPRIETAIRE                                   VARCHAR2(100)
 L_ESI_CTRL_PROPRIETAIRE                            VARCHAR2(100)
 COMMUNE_ESI                                        VARCHAR2(100)
 NUMERO_INSEE                                       NUMBER(5)
 CANTON_ESI                                         VARCHAR2(100)
 COMMUNAUTE_DE_COMMUNES                             VARCHAR2(100)
 NUMERO_SIREN_COMCOM                                VARCHAR2(100)
 PAYS_ESI                                           VARCHAR2(100)
 L_ESI_CTRL_FEDERATION                              VARCHAR2(100)
 L_ESI_CTRL_ETAPE_INSCRIPTION                       VARCHAR2(100)
 DATE_INSCRIPTION_PDESI                             DATE
 DATE_PASSAGE_CDESI                                 DATE
 L_ESI_CTRL_NIVEAU_INSCR                            VARCHAR2(100)
 NOTATION_ENVIRONNEMENT                             NUMBER(10,1)
 L_ESI_CTRL_AVIS_ENV                                VARCHAR2(100)
 COMMENTAIRE_ENVIRONNEMENT                          VARCHAR2(250)
 DATE_AVIS_ENVIRONNEMENT                            DATE
 DOCUMENT_ENVIRONNMENT                              VARCHAR2(250)
 NOTATION_SPORT                                     NUMBER(10,1)
 L_ESI_CTRL_AVIS_SPORT                              VARCHAR2(100)
 COMMENTAIRE_SPORT                                  VARCHAR2(250)
 DATE_AVIS_SPORT                                    DATE
 DOCUMENT_SPORT                                     VARCHAR2(250)
 NOTATION_TOURISME                                  NUMBER(10,1)
 L_ESI_CTRL_AVIS_TOURISME                           VARCHAR2(100)
 COMMENTAIRE_TOURISME                               VARCHAR2(250)
 DATE_CTRL_AVIS_TOURISME                            DATE
 DOCUMENT_TOURISME                                  VARCHAR2(250)
 NOTATION_TRANSPORT                                 NUMBER(10,1)
 L_ESI_CTRL_AVIS_TRANSPORT                          VARCHAR2(100)
 COMMENTAIRE_TRANSPORT                              VARCHAR2(250)
 DATE_AVIS_TRANSPORT                                DATE
 DOCUMENT_TRANSPORT                                 VARCHAR2(250)
 NOTATION_ROUTE                                     NUMBER(10,1)
 L_ESI_CTRL_AVIS_ROUTE                              VARCHAR2(100)
 COMMENTAIRE_ROUTE                                  VARCHAR2(250)
 DATE_AVIS_ROUTE                                    DATE
 DOCUMENT_ROUTE                                     VARCHAR2(250)
 NOTATION_FINALE                                    NUMBER(10,1)
 LONGUEUR                                           NUMBER(10,2)
 L_ESI_ITIN_CTRL_VOCATION                           VARCHAR2(100)
 L_ESI_ITIN_CTRL_VOCATION_SECND                     VARCHAR2(100)
 L_ESI_ITIN_CTRL_LABEL                              VARCHAR2(100)
 L_ESI_CTRL_MO                                      VARCHAR2(100)
 L_ESI_ITIN_CTRL_PRAT_HIV                           VARCHAR2(100)
 L_ESI_CTRL_RAND_MOT                                VARCHAR2(100)
 L_ESI_CTRL_HANDICAP_MOTEUR                         VARCHAR2(5)
 L_ESI_CTRL_HANDICAP_AUDITIF                        VARCHAR2(5)
 L_ESI_CTRL_HANDICAP_VISUEL                         VARCHAR2(5)
 L_ESI_CTRL_HANDICAP_MENTAL                         VARCHAR2(5)
 COMMUNE_DEPART                                     VARCHAR2(250)
 DESCRIPTION_DEPART                                 VARCHAR2(250)
 COMMUNE_ARRIVEE                                    VARCHAR2(250)
 DESCRIPTION_ARRIVEE                                VARCHAR2(250)
 L_ESI_ITIN_CTRL_PARCOURS                           VARCHAR2(100)
 L_ESI_CTRL_DIFFICULTE                              VARCHAR2(100)
 TEMPS_PARCOURS                                     VARCHAR2(50)
 PROXIMITE_BASE_SPORT_NATURE                        VARCHAR2(250)
 PROX_ETABLISSEMENT_SCOLAIRE                        VARCHAR2(250)
 L_ESI_CTRL_ACCES_SCOLAIRE                          VARCHAR2(100)
 EVENEMENT_SPORTIF                                  VARCHAR2(250)
 DENIVELEE                                          NUMBER(5)
 AUTORIS_DE_PASSAGE                                 VARCHAR2(5)
 L_ESI_CTRL_BALISAGE                                VARCHAR2(100)
 L_ESI_ITIN_CTRL_BAL_TYPE                           VARCHAR2(100)
 L_ESI_CTRL_BAL_GEST                                VARCHAR2(100)
 L_ESI_CTRL_BAL_ETAT                                VARCHAR2(100)
 SUBVENTION_DEP                                     VARCHAR2(100)
 PROMOTION                                          VARCHAR2(100)
 NOM_TOPOGUIDE                                      VARCHAR2(100)
 COMMENTAIRE                                        VARCHAR2(250)
 L_ESI_CTRL_NOM_PROJET                              VARCHAR2(100)
 APB                                                VARCHAR2(10)
 RESERVE_NATURELLE                                  VARCHAR2(10)
 PNR                                                VARCHAR2(10)
 NATURA2000                                         VARCHAR2(10)
 SAGE                                               VARCHAR2(10)
 ENS                                                VARCHAR2(10)
 ZNIEFF1                                            VARCHAR2(10)
 ZNIEFF2                                            VARCHAR2(10)
 ZICO                                               VARCHAR2(10)
 ZPPAUP                                             VARCHAR2(10)
 SITE_CLASSE                                        VARCHAR2(10)
 SITE_INSCRIT                                       VARCHAR2(10)
 ZPENS                                              VARCHAR2(10)
 L_ESI_CTRL_EXISTENCE_ESI                           VARCHAR2(50)
 L_ESI_CTRL_NATURE_ESI                              VARCHAR2(50)
 ACCES_ESI                                          VARCHAR2(250)
 L_ESI_CTRL_MOD_EXPLOIT                             VARCHAR2(50)
 L_ESI_CTRL_PERIODE_OUV                             VARCHAR2(200)
 L_ESI_CTRL_MODALITE_OUV                            VARCHAR2(50)
 L_ESI_CTRL_ORGANISATION_ESI                        VARCHAR2(50)
 L_ESI_CTRL_MODALITE_ACCES                          VARCHAR2(50)
 L_ESI_CTRL_PERIODE_FREQ                            VARCHAR2(50)
 L_ESI_CTRL_FREQ_TOUR                               VARCHAR2(50)
 L_ESI_CTRL_IMP_TOUR                                VARCHAR2(50)
 L_ESI_CTRL_FREQ_SPORTIVE                           VARCHAR2(50)
 L_ESI_CTRL_IMP_SPORTIVE                            VARCHAR2(50)
 L_ESI_CTRL_FREQ_SOCIALE                            VARCHAR2(50)
 L_ESI_CTRL_IMP_SOCIALE                             VARCHAR2(50)
 ATTRAIT_ESI                                        VARCHAR2(250)
 L_ESI_CTRL_ATTRACTIVITE                            VARCHAR2(50)
 EQUIPEMENTS_ADAPTES                                VARCHAR2(10)
 APPEL_SECOURS                                      VARCHAR2(10)
 ACCES_SECURISE                                     VARCHAR2(10)
 DESC_ACCES_SECOURS                                 VARCHAR2(250)
 DECRIPTIF_SECURITE                                 VARCHAR2(250)
 GESTIONNAIRE_ESI                                   VARCHAR2(250)
 L_ESI_CTRL_CONVENTION_ESI                          VARCHAR2(100)
 PRESENCE_STATIONNEMENT                             VARCHAR2(10)
 ETAT_AIRE_STATIONNEMENT                            VARCHAR2(250)
 CONTRAINTES_REGLEMENTAIRES                         VARCHAR2(10)
 DENIVELEE_PLUS                                     NUMBER(5)
 DENIVELEE_MOINS                                    NUMBER(5)
 LONGUEUR_PLUS                                      NUMBER(10)
 LONGUEUR_MOINS                                     NUMBER(10)
 TEMPS_PLUS                                         VARCHAR2(20)
 TEMPS_MOINS                                        VARCHAR2(20)
 USER_MAINTENANCE                                   VARCHAR2(250)
 DATE_MAINTENANCE                                   DATE
 STATIONNEMENT_CONVENTIONNE                         VARCHAR2(200)
 LONGUEUR_ZERO                                      NUMBER(10)
 TEMPS_ZERO                                         NUMBER(10)
 DESCRIPTION_ITIN                                   VARCHAR2(254)
 DATE_DEBUT                                         DATE
 DATE_FIN                                           DATE
 ETAT                                               NUMBER(10)
 ID_EVENEMENT                                       NUMBER(11)
 GEOMETRIE                                          MDSYS.SDO_GEOMETRY
 TEMPS_PLUS_HMN                                     VARCHAR2(50)
 TEMPS_MOINS_HMN                                    VARCHAR2(50)
 TEMPS_ZERO_HMN                                     VARCHAR2(50)
 TEMPS_PARCOURS_HMN                                 VARCHAR2(50)
 CROQUIS1                                           VARCHAR2(250)
 CROQUIS2                                           VARCHAR2(250)
 EXPORT_INTERNET                                    VARCHAR2(5)
 CLASSE_1                                           VARCHAR2(10)
 CLASSE_2                                           VARCHAR2(10)
 CLASSE_3                                           VARCHAR2(10)
 CLASSE_4                                           VARCHAR2(10)
 CLASSE_5                                           VARCHAR2(10)
 CLASSE_6                                           VARCHAR2(10)
 COTATION                                           VARCHAR2(50)
 CLASSEMENT                                         VARCHAR2(50)
 L_ESI_CTRL_BAL_GEST2                               VARCHAR2(100)
 ANNEE_ENTRETIEN                                    VARCHAR2(4)
 COMMENTAIRE_FR                                     VARCHAR2(3000)
 COMMENTAIRE_EN                                     VARCHAR2(3000)
 INFO_COMPLEMENTAIRE                                VARCHAR2(3000)

Note sure how you do it for postgresql, I just ran:

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'esi_itin_itineraire2'

and got

"code_itin";"bigint";
"nom";"character varying";250
"l_esi_itin_type_esi";"character varying";100
"l_esi_ctrl_sous_type_esi";"character varying";100
"numero_dossier";"character varying";20
"nom_proprietaire";"character varying";100
"l_esi_ctrl_proprietaire";"character varying";100
"commune_esi";"character varying";100
"numero_insee";"integer";
"canton_esi";"character varying";100
"communaute_de_communes";"character varying";100
"numero_siren_comcom";"character varying";100
"pays_esi";"character varying";100
"l_esi_ctrl_federation";"character varying";100
"l_esi_ctrl_etape_inscription";"character varying";100
"date_inscription_pdesi";"timestamp without time zone";
"date_passage_cdesi";"timestamp without time zone";
"l_esi_ctrl_niveau_inscr";"character varying";100
"notation_environnement";"numeric";
"l_esi_ctrl_avis_env";"character varying";100
"commentaire_environnement";"character varying";250
"date_avis_environnement";"timestamp without time zone";
"document_environnment";"character varying";250
"notation_sport";"numeric";
"l_esi_ctrl_avis_sport";"character varying";100
"commentaire_sport";"character varying";250
"date_avis_sport";"timestamp without time zone";
"document_sport";"character varying";250
"notation_tourisme";"numeric";
"l_esi_ctrl_avis_tourisme";"character varying";100
"commentaire_tourisme";"character varying";250
"date_ctrl_avis_tourisme";"timestamp without time zone";
"document_tourisme";"character varying";250
"notation_transport";"numeric";
"l_esi_ctrl_avis_transport";"character varying";100
"commentaire_transport";"character varying";250
"date_avis_transport";"timestamp without time zone";
"document_transport";"character varying";250
"notation_route";"numeric";
"l_esi_ctrl_avis_route";"character varying";100
"commentaire_route";"character varying";250
"date_avis_route";"timestamp without time zone";
"document_route";"character varying";250
"notation_finale";"numeric";
"longueur";"numeric";
"l_esi_itin_ctrl_vocation";"character varying";100
"l_esi_itin_ctrl_vocation_secnd";"character varying";100
"l_esi_itin_ctrl_label";"character varying";100
"l_esi_ctrl_mo";"character varying";100
"l_esi_itin_ctrl_prat_hiv";"character varying";100
"l_esi_ctrl_rand_mot";"character varying";100
"l_esi_ctrl_handicap_moteur";"character varying";5
"l_esi_ctrl_handicap_auditif";"character varying";5
"l_esi_ctrl_handicap_visuel";"character varying";5
"l_esi_ctrl_handicap_mental";"character varying";5
"commune_depart";"character varying";250
"description_depart";"character varying";250
"commune_arrivee";"character varying";250
"description_arrivee";"character varying";250
"l_esi_itin_ctrl_parcours";"character varying";100
"l_esi_ctrl_difficulte";"character varying";100
"temps_parcours";"character varying";50
"proximite_base_sport_nature";"character varying";250
"prox_etablissement_scolaire";"character varying";250
"l_esi_ctrl_acces_scolaire";"character varying";100
"evenement_sportif";"character varying";250
"denivelee";"integer";
"autoris_de_passage";"character varying";5
"l_esi_ctrl_balisage";"character varying";100
"l_esi_itin_ctrl_bal_type";"character varying";100
"l_esi_ctrl_bal_gest";"character varying";100
"l_esi_ctrl_bal_etat";"character varying";100
"subvention_dep";"character varying";100
"promotion";"character varying";100
"nom_topoguide";"character varying";100
"commentaire";"character varying";250
"l_esi_ctrl_nom_projet";"character varying";100
"apb";"character varying";10
"reserve_naturelle";"character varying";10
"pnr";"character varying";10
"natura2000";"character varying";10
"sage";"character varying";10
"ens";"character varying";10
"znieff1";"character varying";10
"znieff2";"character varying";10
"zico";"character varying";10
"zppaup";"character varying";10
"site_classe";"character varying";10
"site_inscrit";"character varying";10
"zpens";"character varying";10
"l_esi_ctrl_existence_esi";"character varying";50
"l_esi_ctrl_nature_esi";"character varying";50
"acces_esi";"character varying";250
"l_esi_ctrl_mod_exploit";"character varying";50
"l_esi_ctrl_periode_ouv";"character varying";200
"l_esi_ctrl_modalite_ouv";"character varying";50
"l_esi_ctrl_organisation_esi";"character varying";50
"l_esi_ctrl_modalite_acces";"character varying";50
"l_esi_ctrl_periode_freq";"character varying";50
"l_esi_ctrl_freq_tour";"character varying";50
"l_esi_ctrl_imp_tour";"character varying";50
"l_esi_ctrl_freq_sportive";"character varying";50
"l_esi_ctrl_imp_sportive";"character varying";50
"l_esi_ctrl_freq_sociale";"character varying";50
"l_esi_ctrl_imp_sociale";"character varying";50
"attrait_esi";"character varying";250
"l_esi_ctrl_attractivite";"character varying";50
"equipements_adaptes";"character varying";10
"appel_secours";"character varying";10
"acces_securise";"character varying";10
"desc_acces_secours";"character varying";250
"decriptif_securite";"character varying";250
"gestionnaire_esi";"character varying";250
"l_esi_ctrl_convention_esi";"character varying";100
"presence_stationnement";"character varying";10
"etat_aire_stationnement";"character varying";250
"contraintes_reglementaires";"character varying";10
"denivelee_plus";"integer";
"denivelee_moins";"integer";
"longueur_plus";"bigint";
"longueur_moins";"bigint";
"temps_plus";"character varying";20
"temps_moins";"character varying";20
"user_maintenance";"character varying";250
"date_maintenance";"timestamp without time zone";
"stationnement_conventionne";"character varying";200
"longueur_zero";"bigint";
"temps_zero";"bigint";
"description_itin";"character varying";254
"date_debut";"timestamp without time zone";
"date_fin";"timestamp without time zone";
"etat";"bigint";
"id_evenement";"bigint";
"geometrie";"text";
"temps_plus_hmn";"character varying";50
"temps_moins_hmn";"character varying";50
"temps_zero_hmn";"character varying";50
"temps_parcours_hmn";"character varying";50
"croquis1";"character varying";250
"croquis2";"character varying";250
"export_internet";"character varying";5
"classe_1";"character varying";10
"classe_2";"character varying";10
"classe_3";"character varying";10
"classe_4";"character varying";10
"classe_5";"character varying";10
"classe_6";"character varying";10
"cotation";"character varying";50
"classement";"character varying";50
"l_esi_ctrl_bal_gest2";"character varying";100
"annee_entretien";"character varying";4
"commentaire_fr";"character varying";3000
"commentaire_en";"character varying";3000
"info_complementaire";"character varying";3000

So MDSYS.SDO_GEOMETRY for Oracle and "text" in postgresql, using the IMPORT FOREIGN SCHEMA;

I tried your lines and it worked. I also tried creating the FOREIGN table with the type "timestamp(0) without time zone" instead of date but it also works.

For your last questions, I ran this query in sqlplus:

select count(1) from ESI_ITIN_ITINERAIRE2 where date_inscription_pdesi is null;

and got a count of 643, so the lines have null dates.

But it turns out that selectind a row which has a valid date value:

select date_inscription_pdesi from esi."esi_itin_itineraire2" where code_itin = 127;

Also brings out the same error
So the issue must be with date interpretation or something

Couple of question:

• Why is IMPORT FOREIGN SCHEMA typing the Oracle DATE fields as "timestamp without time zone" and note "date"?
• Could it be related to date format of my database ? How do I check that info in Oracle? My database is probably installed in French. When I run your example:

  INSERT INTO datetest VALUES (4, to_date('100-01-01 BC', 'YYYY-MM-DD BC'));
  It fails with an error in french:
  Il faut préciser BC/B.C. ou AD/A.D.

But there is no AD / BC in my database just dates like "30/11/02". So maybe there is a confusion between time and date? Like my dates don't have a time so extraction goes wrong?

I am trying to find a better way to reproduce this

[fnicollet]

Weird thing, if I run:

select to_char(date_inscription_pdesi, 'dd/MM/YYYY') from ESI.ESI_ITIN_ITINERAIRE2 where date_inscription_pdesi is not null;

I get dates with 0:

All of the lines are zeros

I am really not good at Oracle, so maybe I am doing something wrong but it is correctly displayed in SQLDeveloper:

[laurenz]

IMPORT FOREIGN SCHEMA maps Oracle DATE to "timestamp(0) without time zone" because Oracle dates have a time part, so this is the best mapping.
You can ALTER FOREIGN TABLE to change the type to "date" if your Oracle dates have zero time.

I guess that the problem you observe is due to the fact that the Oracle DATEs are NOT NULL, but look like 0000-00-00. PostgreSQL will correctly reject such a date.

To solve this it would be important to know what really is in these DATE columns. Try the following Oracle query:

SELECT dump(date_inscription_pdesi, 16) FROM esi_itin_itineraire2 WHERE code_itin = ...;

where the WHERE clause identifies one of the rows with 0000-00-00. That will help me figure out what is in those columns.

[fnicollet]

Again, thanks a lot for your help on this :)
Here is what I ran:

SQL> SELECT dump(date_inscription_pdesi, 16) FROM esi_itin_itineraire2 WHERE cod
e_itin =  127;

DUMP(DATE_INSCRIPTION_PDESI,16)
--------------------------------------------------------------------------------

Typ=12 Len=7: 64,64,0,0,1,1,1

Hopefully, it makes sense to you !

[laurenz]

Here is a good description of what the internal representation means.

In your case that should be: year 3636 before Christ, month 0, day 0 at midnight.
But month or day must be at least 1!

So I'd say you got corrupt data in your Oracle database. How did those strange values get there?
I don't think that oracle_fdw is to blame in this case.

[fnicollet]

Well I imported a dump from a customer's database into my own oracle installation, maybe something messed up in between.
Anyway, I think that you are right, oracle_fdw is probably not to blame here. Should this issue be closed then?

[laurenz]

Sure.
But I'd be interested in any information how these values got there.

[fnicollet]

I'll leave a comment if I ever find out.

And again, thanks a lot for your help/patience !