Bug 59200 - ODBC - incorrect display with mysql zero timestamp values using Actual Technologies ODBC driver
Summary: ODBC - incorrect display with mysql zero timestamp values using Actual Techno...
Status: CLOSED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Database (show other bugs)
Version: 4.1.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) Mac OS X (All)
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-01-10 16:02 UTC by Alex Thurgood
Modified: 2014-09-17 18:12 UTC (History)
2 users (show)

See Also:
i915 platform:
i915 features:

Attachments

Description Alex Thurgood 2013-01-10 16:02:00 UTC 
Connecting to a locally hosted mysql database via the Actual Technologies ODBC driver for Mac OSX.

The table contains NULL timestamps values, which mysql represents as :

SQL> select * from action_type;
+----------------+-----------------------------------------+---------------+--------------------+
| type           | comments                                | action_type_id| chg_date           |
+----------------+-----------------------------------------+---------------+--------------------+
| A.94(3) EPC    | Normal examiner office action           | 1             | 0000-00-00 00:00:00|
| R.71(3) EPC    | Intention to grant communication        | 2             | 0000-00-00 00:00:00|
| R.51(6) EPC    | Notice of grant communication           | 3             | 0000-00-00 00:00:00|
+----------------+-----------------------------------------+---------------+--------------------+
SQLRowCount returns 3
3 rows fetched

However, the same table in LO master build :
Version 4.1.0.0.alpha0+ (Build ID: 989863d849b1e703e78afc413088c3ae5109313)
TinderBox: MacOSX-Intel@1-built_no-moz_on_10.6.8, Branch:master, Time: 2013-01-06_10:52:34

shows the following :

02/01/1 00:00
02/01/1 00:00
02/01/1 00:00

The format of the date column is supposed to be in the default locale of the machine, i.e. French format of DDMMYYYY hh:mm, yet this this is displayed incorrectly on screen as above.



Alex
Comment 1 Alex Thurgood 2013-01-10 16:05:16 UTC 
@Putting Lionel + Julien on CC
Comment 2 Alex Thurgood 2013-01-10 16:12:35 UTC 
There are two points here :

- the visual representation of the NULL timestamp value on screen, i.e. the fact that the year is only represented as a single digit ;

- the apparent 1 day date miscalculation compared to the default reference date of LO which I was led to believe was/is 18/01/1901.


Alex
Comment 3 Alex Thurgood 2013-01-10 16:25:31 UTC 
Sample table definition :
CREATE TABLE `action_type` (
  `type` varchar(15) NOT NULL DEFAULT '',
  `comments` varchar(40) DEFAULT NULL,
  `action_type_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `chg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`action_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 


Alex
Comment 4 Lionel Elie Mamane 2013-01-14 09:30:52 UTC 
In MySQL, "0000-00-00 00:00:00" is not a NULL value, but a zero timestamp. NULL and "0000-00-00 00:00:00" are distinct values. "0000-00-00 00:00:00" is a MySQL-specific idiosyncrasy, an "anomalous" value which does *not* conform to (from memory) the SQL standard, nor ODBC:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms712480(v=vs.85).aspx
and entries "SQL_TYPE_DATE/TIME/TIMESTAMP" in
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710150(v=vs.85).aspx


The MySQL ODBC Connector (the driver provided by MySQL) has an option "convert zero date/timestamp to NULL" so as not to leak these anomalous values to ODBC applications; if the column is "NOT NULL", then it may confuse applications in other ways :-| I haven't tested how LibreOffice reacts to this (NULL in a non-NULLable column).


This being said, if we can change something in LibreOffice so that it reacts more gracefully to such anomalous values, then it would be good, so let's keep this bug open to track this future improvement.


To use applications that are not specifically designed for MySQL (such as LibreOffice...), I'd recommend running MySQL with NO_ZERO_DATE and NO_ZERO_IN_DATE options activated.
Comment 5 Alex Thurgood 2013-01-14 10:17:00 UTC 
(In reply to comment #4)
> In MySQL, "0000-00-00 00:00:00" is not a NULL value, but a zero timestamp.
> NULL and "0000-00-00 00:00:00" are distinct values. "0000-00-00 00:00:00" is
> a MySQL-specific idiosyncrasy, an "anomalous" value which does *not* conform
> to (from memory) the SQL standard, nor ODBC:
>

Yes, I've always had misgivings about the way MySQL considered invalid dates...

 
> The MySQL ODBC Connector (the driver provided by MySQL) has an option
> "convert zero date/timestamp to NULL" so as not to leak these anomalous
> values to ODBC applications; if the column is "NOT NULL", then it may
> confuse applications in other ways :-| I haven't tested how LibreOffice
> reacts to this (NULL in a non-NULLable column).
> 

I will have a look to see if the Actual Technologies has such a parameter somewhere.

Alex
Comment 6 Alex Thurgood 2014-09-17 18:12:29 UTC 
This now works for me in latest 4312 prod release

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.