TIME data is clipping. i.e. does not support MySQL documented '838:59:59' with Table Joins (Advanced Report Engine)

TIME data is clipping. i.e. does not support MySQL documented '838:59:59' with Table Joins (Advanced Report Engine)

I've been informed one of our databases has been migrated to "Advanced Reports Engine"

I've noticed strange behavior:


According to MySQL:

MySQL retrieves and displays  TIME  values in  'HH:MM:SS'  format (or  'HHH:MM:SS'  format for large hours values).  TIME  values may range from  '-838:59:59'  to  '838:59:59' .



Secnario 1: No Tables - On Advanced Report Engine ( Works as expected)

Create a new Query Table with the following SQL
    1. SELECT
    2.  SEC_TO_TIME(86399) as column1,
    3.  SEC_TO_TIME(86401) as column2,
    4.          TIME_FORMAT(SEC_TO_TIME(86401), '%H:%m:%s ') as "column3",

    6.  TIME_FORMAT(SEC_TO_TIME(1553463), '%h ') as "column4",
    7.  TIME_FORMAT(SEC_TO_TIME(1553463), ' %H ') as column5
                No "FROM" clause...

                This works as expected...

                column1  --> 23:59:59 
                column2 --> 00:00:01
                column3 --> 24:00:01
                column4 --> 11
                column5 -->  431


                Secnario 1: JOIN -  On Advanced Report Engine ( Changed Behavior)


                HOWEVER, if you add a JOIN Clause it does not work...

                1. SELECT
                2.  SEC_TO_TIME(86399) as column1,
                3.  SEC_TO_TIME(86401) as column2,
                4.          TIME_FORMAT(SEC_TO_TIME(86401), '%H:%m:%s ') as "column3",

                6.  TIME_FORMAT(SEC_TO_TIME(1553463), '%h ') as "column4",
                7.  TIME_FORMAT(SEC_TO_TIME(1553463), ' %H ') as column5
                8. FROM  "Athlete-AE-Events" aee 
                9. JOIN "Athlete" ath ON ath."DrupalId"  = aee."AthleteId"  

                You can pick ANY two tables ... you can see I am no selecting any table columns... so there is something happening with just the JOIN.

                In this case
                column1  --> 23:59:59
                column2 --> 00:00:01
                column3 --> <blank>
                column4 --> 11
                column5 -->  23


                ISSUE:
                The main issue is that the inclusion of a JOIN clause is changing the behavior of TIME_FORMAT() and/or SEC_TO_TIME()


                Regular Report Engine

                We have another database that was not upgraded to Advanced Report Engine.

                The existence of the JOIN clause does not change the behavior of the SQL Functions.  Clearly something is 'fragile' with the query optimizer on the Advanced Report Engine.

                Cheers,
                Tyler