Possible Amazon Aurora Incompatible Datetime conversion issue

[dgilbert]

Using Pomelo.EntityFrameworkCore.MySql Entity Framework ORM and MySQL Connector and it generates the following query:

SELECT `o`.`UserCallHistoryId`, `o`.`CallDateTime`, `o`.`CallDurationInSeconds`, `o`.`CallHistoryId`, `o`.`DispositionCallHistoryUserCallDispositionId`, `o`.`HasCallRecording`, `o`.`HasVoicemail`, `o`.`IsInbound`, `o`.`IsInternal`, `o`.`OtherPartyAttendantAccountAutoAttendantId`, `o`.`OtherPartyForwardingNumberAccountForwardingNumberId`, `o`.`OtherPartyHostedNumberAccountHostedNumberId`, `o`.`OtherPartyLocationLocationId`, `o`.`OtherPartyPhoneNumber`, `o`.`OtherPartyUserAccountUserId`, `o`.`OtherPartyUserGroupAccountUserGroupId`, `o`.`UserAccountUserId`, `o.CallHistory`.`CallHistoryId`, `o.CallHistory`.`AccountId`, `o.CallHistory`.`CallCompleteDateTime`, `o.CallHistory`.`CallDateTime`, `o.CallHistory`.`CallHistoryDestinationId`, `o.CallHistory`.`CallHistoryOriginationId`, `o.CallHistory`.`HasCallRecording`, `o.CallHistory`.`HasVoicemail`, `o.CallHistory`.`RawDurationInSeconds`, `o.User`.`AccountUserId`, `o.User`.`AccountId`, `c`.`CallHistoryId`, `c`.`AccountId`, `c`.`CallCompleteDateTime`, `c`.`CallDateTime`, `c`.`CallHistoryDestinationId`, `c`.`CallHistoryOriginationId`, `c`.`HasCallRecording`, `c`.`HasVoicemail`, `c`.`RawDurationInSeconds`, `a`.`AccountAutoAttendantId`, `a`.`AccountId`, `a0`.`AccountHostedNumberId`, `a0`.`AccountId`, `a0`.`HostedPhoneNumber`, `a1`.`AccountUserId`, `a1`.`AccountId`, `a2`.`AccountUserGroupId`, `a2`.`AccountId`, `a2`.`AccountUserGroupType`, `a3`.`AccountForwardingNumberId`, `a3`.`AccountId`
FROM `UserCallHistory` AS `o`
LEFT JOIN `CallHistory` AS `o.CallHistory` ON `o`.`CallHistoryId` = `o.CallHistory`.`CallHistoryId`
LEFT JOIN `AccountUser` AS `o.User` ON `o`.`UserAccountUserId` = `o.User`.`AccountUserId`
LEFT JOIN `CallHistory` AS `c` ON `o`.`CallHistoryId` = `c`.`CallHistoryId`
LEFT JOIN `AccountAutoAttendant` AS `a` ON `o`.`OtherPartyAttendantAccountAutoAttendantId` = `a`.`AccountAutoAttendantId`
LEFT JOIN `AccountHostedNumber` AS `a0` ON `o`.`OtherPartyHostedNumberAccountHostedNumberId` = `a0`.`AccountHostedNumberId`
LEFT JOIN `AccountUser` AS `a1` ON `o`.`OtherPartyUserAccountUserId` = `a1`.`AccountUserId`
LEFT JOIN `AccountUserGroup` AS `a2` ON `o`.`OtherPartyUserGroupAccountUserGroupId` = `a2`.`AccountUserGroupId`
LEFT JOIN `AccountForwardingNumber` AS `a3` ON `o`.`OtherPartyForwardingNumberAccountForwardingNumberId` = `a3`.`AccountForwardingNumberId`
WHERE (`o.User`.`AccountUserId` = '53ed425c-7ddd-4c0d-ba63-fdb81e265bf4') AND (`o.CallHistory`.`CallCompleteDateTime` > timestamp '2017-10-18 15:18:15.000000')
ORDER BY `o`.`CallHistoryId`, `o`.`UserAccountUserId`;

This query does not return any results when, in fact, that is data in the table that matches the criteria. If I update the following line from timestamp '2017-10-18 15:18:15.000000' to timestamp('2017-10-18 15:18:15.000000') the query returns the correct results. Any ideas?

[bgrainger]

The function is documented as TIMESTAMP(expr). MySQL Server 5.7 accepts either > timestamp '2017-10-18 15:18:15.000000' or > timestamp('2017-10-18 15:18:15.000000'), but Amazon Aurora may be more strict and only accept the documented format.

[bgrainger]

TIMESTAMP 'str' is a literal, documented here.

[bgrainger]

The two syntaxes seem to be fairly equivalent, so if one works with Aurora and the other doesn't, we should switch to the working one.

[bgrainger]

I'm not able to reproduce a problem with timestamp 'YYYY-MM-DD ...' on Amazon RDS. Are you using Aurora MySQL 5.6.10a?

[bgrainger]

I would think that if Aurora didn't recognise the timestamp 'YYYY-MM-DD' syntax it would return a "you have an error in your SQL syntax" error, rather than return no rows. Is it possible that something else is going on to cause 0 rows to be returned?

[dgilbert]

I believe it is a combination of the number of joins and that timestamp syntax. When I remove any one of those joins the query works as expected with 'timestamp' literal syntax. However the query works 100% of the time when I used the timestamp function syntax. I am also opening a ticket the the AWS folks to get their thoughts.

[bgrainger]

Thanks for the update. Does sound like an Aurora bug, but makes sense to get this merged as an immediate workaround.

[bgrainger]

Fixed by #365 in 0.29.1.