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

              melody.wu Melody Wu
              david.zhang David Zhang (Inactive)
              Kiyoshi Lee Kiyoshi Lee
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                6 years, 32 weeks, 2 days ago

                Packages

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