Bug 59200

Summary:	ODBC - incorrect display with mysql zero timestamp values using Actual Technologies ODBC driver
Product:	LibreOffice	Reporter:	Alex Thurgood <iplaw67>
Component:	Database	Assignee:	Not Assigned <libreoffice-bugs>
Status:	CLOSED WORKSFORME	QA Contact:
Severity:	normal
Priority:	medium	CC:	lionel, serval2412
Version:	4.1.0.0.alpha0+ Master
Hardware:	x86-64 (AMD64)
OS:	Mac OS X (All)
Whiteboard:
i915 platform:		i915 features:

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.