I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even if the table were emtpy, which it was not, "select max" should return a single row with a null value.
SELECT COALESCE(MAX(load_dt AT TIME ZONE 'UTC'),'1901-01-01 00:00:00'::timestamp) FROM schema.tablename
The process is running in WhereScape on MS SQL Server, connecting to Greenplum using ODBC and loading the result into a SQL Server table.
We have a lot of ODBC failures, which the system detects and recovers from, including this step where we have seen ODBC connection failures that were correctly detected and the job failed. This time, no error, just "0 rows" in the job log.