Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 326744

Summary:	Result not displayed when MySQL date type has '0000-00-00' in any record
Product:	[Tools] Data Tools	Reporter:	Drew Bertola <dbertola>
Component:	SQL Results View	Assignee:	dtp.sqldevtools-inbox <dtp.sqldevtools-inbox>
Status:	RESOLVED NOT_ECLIPSE	QA Contact:
Severity:	critical
Priority:	P3
Version:	1.8
Target Milestone:	1.8.2
Hardware:	Macintosh
OS:	Mac OS X - Carbon (unsup.)
Whiteboard:

Description Drew Bertola

2010-10-01 04:25:21 EDT

Build Identifier: 20100617-1415

DTP Enabler SDK and DTP Extender SDK versions 1.8.0 v.20100528
Eclipse for PHP Developers 20100617-1415
MySQL 5.1.0
JDBC 3.0.0


Reproducible: Always

Steps to Reproduce:
1. Table with date type and id or something else
2. several records, different dates, one or two with '0000-00-00'
3. select * from table (Gives me no result)
4. select id from table (Gives me all ids)
5. select date_field from table (Gives me no result)
6. update date_field set = null where = '0000-00-00' 
7. repeat selects and all results displayed.

Comment 1 Brian Fitzpatrick

2010-10-01 09:50:38 EDT

So based on your description, it seems you're having issues with executing queries using the SQL Results View?

Comment 2 Drew Bertola

2010-10-01 12:18:45 EDT

(In reply to comment #1)
> So based on your description, it seems you're having issues with executing
> queries using the SQL Results View?

Yes.

One other interesting fact to mention
If there is a date type and a record with '0000-00-00', I can use:

select concat(date_field) from table;

This will display the result.

Comment 3 Drew Bertola

2010-10-22 16:51:39 EDT

create table scratch.drew_test_dates (
  id int unsigned not null primary key auto_increment,
  test_date_str char(10),
  test_date date not null default '0000-00-00'
);

insert into scratch.drew_test_dates
    (test_date_str, test_date)
  values
    ('2010-01-01', '2010-01-01'),
    ('0000-00-00', '0000-00-00'),
    ('2010-10-22', '2010-10-22')
;

select * from scratch.drew_test_dates;
select id, test_date_str, test_date from scratch.drew_test_dates;
select id, test_date_str, concat(test_date) from scratch.drew_test_dates;

In PDT:
select * shows no rows returned in results window
select w/ explicit cols show no results...
select w/ explicit cols and date col wrapped in concat() shows expected results.

Connector/J is from 5.1.0 jar.
Eclipse Helios rel: 20100617-1415,
 PDTVersion: 1.8.0.v201005280400-7B7C77CcNBGKBaIdEbPQ
Build id: 1.7.2.v20100528-1022

Comment 4 Drew Bertola

2010-10-22 16:56:54 EDT

I bumped this back up to critical.  It's actually much more harmful to return erroneous data (no result, in this case) than it is to crash.

Case in point:

select * from patient_reported_drug_allergies;

(no result)

Point made?

Comment 5 Drew Bertola

2010-10-23 04:36:49 EDT

So, I found out that this is a connector/J issue.  When setting up optional properties for the connection, I can use:

zeroDateTimeBehavior=convertToNull (or =round or =exception).