PostgreSQL fails when doing an union with NULL dates

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: Master
• Fix Version/s: 7.0.0 DXP FP8, 7.0.0 DXP SP2, 7.0.3 CE GA4, 7.1.X, Master
• Component/s: Message Boards, ~ [Archived] Collaboration
• Labels:
  • QA-CT
  • liferay-digital-enterprise-70-sp2
  • liferay-fixpack-de-8-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Fix Priority: 4
• Sprint: Padparadschah 10/25-11/8
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/44218

Given two tables, one with a date field, the other without one, trying to create a union where the result set of the select with no date returns NULL results in an error.

create table A (id LONG primary key not null, date_ DATE null);
create table B (id LONG primary key not null);

(select date_ from A) union all (select NULL from B)

Replacing the query above with the following executes successfully:

(select date_ from A) union all (select CAST(NULL AS TIMESTAMP) from B)

To fix the problem we have introduced a new placeholder [$NULL_DATE$] that can be used in queries where Postgres incorrectly infers the type of the field.