Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-67752

PostgreSQL fails when doing an union with NULL dates

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              melody.wu Melody Wu
              Reporter:
              david.zhang David Zhang (Inactive)
              Participants of an Issue:
              Recent user:
              Yvonne Han
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                5 years, 31 weeks, 3 days ago

                  Packages

                  Version Package
                  7.0.0 DXP FP8
                  7.0.0 DXP SP2
                  7.0.3 CE GA4
                  7.1.X
                  Master