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.

  • 2
    From an SQL point of view this is true, MAX should return 1 row (possibly null). Can you repeat this behaviour with just SQL? – Lennart Jan 11 '16 at 11:19
  • Are you sure the "0 rows" refers to this query? – jcaron Jan 11 '16 at 13:06
  • Yes it's definitely this query. We have another similar query that just gets the current_timestamp rather than the max value from a table. Including all of those, we have run over 50,000 such queries over the last 4 months and only once did it return zero rows. – PhilHibbs Jan 19 '16 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.