0

I have migrated a MySQL 5.5 database over to AWS Aurora and have both databases working as expected with one exception. A set of stored procedures that utilise COALESCE and IFNULL in the WHERE clause are not behaving as expected for DATETIME parameters / columns.

The definition of the stored procedure in both MySQL 5.5 and Aurora is:

CREATE DEFINER=`TesterAcct`@`%` PROCEDURE `usp_LookupTest`(
    IN paramEmail VARCHAR(200),
    IN paramCreateDate DATETIME
)
BEGIN

SELECT
    UserID as UserID,
    Email as Email,
    CreateDate as CreateDate
FROM Users      
WHERE
COALESCE(IFNULL(Email,''),'') = COALESCE(paramEmail,IFNULL(Email,''))
AND COALESCE(IFNULL(CreateDate,''),'') =  COALESCE(paramCreateDate,IFNULL(CreateDate,''))
;
END

The call being used to test the stored procedure is:

call usp_LookupTest('pcg@none.com',null)

None of the columns for the record being queried are NULL and both database instances (5.5 and Aurora) have exactly the same data and objects

In MySQL 5.5 the record returns as expected and in Aurora an empty dataset is returned. I know it is the CreateDate clause causing the issue because the call in Aurora does return the record when that line is commented out.

I don't believe this SQL syntax is anything non-standard and have not found any documentation on Aurora handling COALESCE or IFNULL differently.

Where should I start looking next?

fingerprints
  • 2,751
  • 1
  • 25
  • 45
Patrick Greene
  • 1
  • 1
  • I wonder what converting `''` to a date does in Aurora. – Gordon Linoff Apr 15 '17 at 22:57
  • Tried replacing that with a placeholder date (1/1/1950) instead of '' to see if there was any weirdness there related to conversions but it didn't change the results. The CreateDate in the record is '2016-05-22 16:43:38' so the the IFNULL check on that column should resolve to a date anyway and all other records in the table have a CreateDate as well – Patrick Greene Apr 15 '17 at 23:13

0 Answers0