MySQL Forums
Forum List  »  General

Re: Error when writing using date aggregate
Posted by: Judah Wright
Date: April 28, 2020 02:46PM

> > Data truncation: Incorrect datetime value:
> '0000-00-00 00:00:00'
> Indicates zero dates (not null dates) have been
> inserted.

Running the following I get:
SELECT MIN(created_at) FROM a; # '2019-08-09 20:37:18'
SELECT COUNT(*) FROM a WHERE created_at IS NULL; # 0
SELECT COUNT(*) FROM a WHERE created_at = 0; # 0

> > ...FROM table1 a
> > RIGHT JOIN table2 s2 on a.session_uuid =
> s2.uuid
> > WHERE s2.created_at IS NULL...
> This outer joins from s2 to a, ie it sees all rows
> from s2 and only matching rows from a, so any
> s2.created nulls it sees are s2 column values. Is
> that what you intended?

There are two tables involved, lets call them "s" and "a".
Table "s" has many millions of rows. Each record can have around 20-300 related "a" values. Our application recently had an update that would mistakenly insert new "s" values with null dates. This isn't the end of the world because we can calculate these values from the "a" table. The `MIN(created_at)` and the `MAX(updated_at)`, respectively.

Because of the size of the "a" table, we don't want to run these aggregate numbers for the whole thing since it could take days. We only want to query against the records that belong to a "s" value that has the null date fields.
So here we are "select ... from a right join s on ..." so we are only keeping "a" values where we have an invalid "s" value.

So yes, its intentional. It might be incorrect, but it was intentional.

Options: ReplyQuote

Written By
Re: Error when writing using date aggregate
April 28, 2020 02:46PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.