(Bug?)MySQL: DATE NULL / SQL Editor: TINYINT

[ezod]

Hi,

first of all thank you for this great software and keep up the good work!

I think I found two MySQL relted bugs to discuss

1.)
The "Data" view and also the "SQL Editor" view shows a "DATE" field with the value '0000-00-00' as [NULL] wich is IMHO incorrect. '0000-00-00' is a value so it isn't NULL. Same behavior by exporting the resultset.

2.)
The result view of the "SQL Editor" shows a "TINYINT" field as "true/false". TINYINT itself has a range from -127 to +127, so it isn't a boolean

(Version 2.0.6)

Greetz

Jan

[Serge]

Hello!

1. Weird NULL date values is a special MySQL "feature". Often used in MyISAM tables as it's simpler than handling special NULL values (in PHP like languages). Refer this article - http://dev.mysql.com/doc/refman/5.0/en/ ... types.html
Yep, it's MySQL world
2. Thats strange. Generally TINYINT (1 byte) columns are handled as integers. There is one special case - if actual data type name is "bool" or "boolean" then it treated as boolean. But if it is not your case - could you send DDL for such table?

Thanks!

[ezod]

[ezod]

Argh - sorry. TINYINT(1) = Boolean, so DBeaver is correct

But the problem with the NULL values for the date resists

[Serge]

Actually 000-00-00 IS a NULL, but it represented in a special MySQL format. I believe that UI client should represent such values as NULL.
Anyway, you may change bahavior by setting advanced connection property "zeroDateTimeBehavior" to something else than "convertToNull". But I''m afraid it won't help much because 000-00-00 isn't a valid date and can't be displayed as date.

Thanks

[ezod]

[Serge]

Agree. It is not a naturall NULL, it is some new synthetic thing invented by MySQL. Unfortunatelly there is no portable way to correctly represent it (as date 0000-00-00 is not valid in terms of time). JDBC and ODBC drivers simply converts it to NULL.
If you need to see it as not NULL the only workaround I can suggest is to set zeroDateTimeBehavior parater to "round". Then such values will be represeted as date value "0001-01-01".

If you can suggest any other solution - please tell

[ezod]

[amosmoss]

Hi,

I ran into the tinyint(1) issue too. I didn't really understand what's the solution from this thread.

Thanks, Amos.

[Serge]

Hi,

It seems to be a bug in MySQL JDBC driver. It treats TINYINT(1) as BIT data type. And BIT is generally a boolean type.
Check this bug - http://bugs.mysql.com/bug.php?id=16854
Unfortunatelly Connector/J team doesn't think that it is a bug. TINYINT(1) == BOOLEAN by spec and I'm afraid I can't do anything with that...

BTW use of TINYINT(1) doesn't make much sense because TINYINT is always a byte. (1) just means you need only first decimal digit of it.

If you have suggestions or possible workaround on mind - please tell me..