[MDEV-8911] Date comparaison to string failed with '=' and ISO DATE - Jira

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.20, 10.0, 10.1
Fix Version/s: 10.0, 10.1
Component/s: Optimizer
Labels:
- wrong_result
Environment:
linux, ubuntu trusty docker, mariadb 10.0.20-MariaDB-1~trusty-wsrep-log

Date comparison fail on the datetime fields only on strict comparisons

Table format
+------------------+--------------+------+-----+---------+-------+
\| Field \| Type \| Null \| Key \| Default \| Extra \|
+------------------+--------------+------+-----+---------+-------+
\| processingdate \| datetime \| YES \| \| NULL \| \|
+------------------+--------------+------+-----+---------+-------+

Table content
select processingdate from rbs_order_doc_shipment ;
+---------------------+
\| processingdate \|
+---------------------+
\| NULL \|
\| 2015-10-06 15:24:47 \|
\| NULL \|
\| 2015-10-06 15:27:38 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-07 06:04:30 \|
+---------------------+

Query on error
select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
Empty set (0.00 sec)

But those works:

select processingdate from rbs_order_doc_shipment where processingdate <= '2015-10-06T19:14:52+0000' ;

+---------------------+

| processingdate      |

+---------------------+

| 2015-10-06 15:24:47 |

| 2015-10-06 15:27:38 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

+---------------------+

select processingdate from rbs_order_doc_shipment where processingdate >= '2015-10-06T19:14:52+0000'   limit 10;

+---------------------+

| processingdate      |

+---------------------+

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-07 06:04:30 |

+---------------------+

blocks

MDEV-4912 Add a plugin to field types (column types)