We stumbled upon a bug in database activity mod_data.

The problem:

• If a field of type "Date" is created in a database activity, the entries are saved as a Unix timestamp in a string (type text for pgsql) in the database (mdl_data_content).
• If an entry is created with a value earlier than "Fri Dec 13 1901 20:45:52 GMT+0000", a value smaller than -2147483648 is saved.
• If I display the entries and sort them according to the “Date” field, the following query is made in the database:

SELECT DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, r.groupid, r.dataid, u.picture, u.firstname, u.lastname, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.imagealt, u.email, CAST(s.content AS INT) AS sortorder FROM mdl_data_content c, mdl_data_records r, mdl_user u, mdl_data_content s WHERE c.recordid = r.id AND r.dataid = $1 AND r.userid = u.id AND s.fieldid = $2 AND s.recordid = r.id ORDER BY sortorder ASC , r.id ASC LIMIT 10;

• The query fails and throws an error:
  Default exception handler: Error reading database Debug: ERROR: Value "-2208988800" is out of range for type integer
• The problem is here
  CAST(s.content AS INT) AS sortorder
  because the database tries to squeeze the value into an integer.

When I run the query directly on the database I get the same error.
If I change the "CAST(s.content as INT)" to a "CAST(s.content as BIGINT)" the query works.

We were able to trace the error back to the implementation of the “sql_cast_char2int” function.

For example the implementation for postgresql is:

return ' CAST(' . $fieldname . ' AS INT) ';

as are the implementations for other database systems all as INT.