MySQL Bugs: #95754: DateTime error

Bug #95754	DateTime error
Submitted:	12 Jun 2019 12:34	Modified:	14 Jun 2019 12:45
Reporter:	Денис Ткаченко	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.16	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: '%-%-% %'

How to repeat:
SELECT * FROM database WHERE date LIKE "%-%-% %";

Hi Mr. Tkacenko,

Thank you for your bug report.

However, this is not a bug. Simply, the value that you are searching for is not a regular Datetime constant in MySQL.

You can read more about that data type in the subchapter 11.3 in our Reference Manual. In the same manual, you can find functions that can be used to search for the range of dates that you are looking for.

Not a bug.

Thank's!

But in previous versions it worked correctly. And many scripts work in this form. 

For example (php-code):

------->

if (!$filters['notfinished']) $where .= "and O.order_created='1' ";
if ($filters['year']!=0) $year = $filters['year']; else $year="%";
if ($filters['month']!=0) $month = $filters['month']; else $month="%";
if ($filters['day']!=0) $day = $filters['day']; else $day="%";
$where .= " and O.order_date like '".$year."-".$month."-".$day." %'";

...

$query = "SELECT COUNT(O.order_id) FROM `#__jshopping_orders` as O where 1 ".$where;

<-------

It turns out that backward compatibility is lost.

Sorry for my English, he is very bad :)

Hi,

There is no software for which a full backward guarantee is preserved. That is why we have new versions, like 5.6, than 5.7 and now 8.0. We introduce new features, become more compliant with a standard and deprecate features that are incompatible, problematic or non-standard.

Your English is not a problem ...

Good day,

Well, I am also for strict typing and standardization. True, against the abrupt cancellation of support for the old code.

But, if you switched to strict typing of datetime type, then why does the following construct work correctly?

Example:

---->

SELECT * FROM database WHERE datetime LIKE "201%-0%-1% 0%";

<----

Why it is correctly? :)

It turns out the typing is not strict and a regular expression is applied to it. Only if is the empty it does not work. This is bug!

Sorry, but your typing is not working correctly. This will cause even more confusion with the old code, some of which will work, but other not works.

Hi,

The answer is simple. Because that constant is keeping in line with strict typing. Character '%' means a wildcard in SQL, unlike other ones supported before, which are not compliant with latest SQL standards.

Hi,

Now I understand. Thank you.