Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23799

CREATE .. SELECT wrong result on join versioned table

    XMLWordPrintable

    Details

      Description

      When creating a temporary table with the following statements, the behavior is as expected:

      -- standard tables yield the expected result
      create table x (id Int);
      create table x_p (elementId Int, pkey varchar(20), pvalue varchar(20));
       
      insert into x values (1), (2), (3);
      insert into x_p values (1, 'gender', 'male');
      insert into x_p values (2, 'gender', 'female');
      insert into x_p values (3, 'gender', 'male');
       
      create temporary table tmp1
          select xgender.pvalue as gender, xtitle.pvalue as title
          from x
              left join x_p as xgender on x.id = xgender.elementId and xgender.pkey = 'gender'
              left join x_p as xtitle on x.id = xtitle.elementId and xtitle.pkey = 'title';
       
      select * from tmp1;
      

      The statements above result in the expected temporary table:

       
      gender | title
      -------+------
      male   | NULL
      female | NULL
      male   | NULL
      

      But issuing the same statement for bi-temporal tables, wich are similar in respect to the data, result in an empty set:

      create or replace table t (
          id INT,
          date_1 DATE,
          date_2 DATE,
          row_start TIMESTAMP(6) AS ROW START INVISIBLE,
          row_end TIMESTAMP(6) AS ROW END INVISIBLE,
          PERIOD FOR application_time(date_1, date_2),
          PERIOD FOR system_time(row_start, row_end))
          WITH SYSTEM VERSIONING;
       
      create or replace table t_p (
          elementId INT,
          pkey VARCHAR(20),
          pvalue VARCHAR(20),
          date_1 DATE,
          date_2 DATE,
          row_start TIMESTAMP(6) AS ROW START INVISIBLE,
          row_end TIMESTAMP(6) AS ROW END INVISIBLE,
          PERIOD FOR application_time(date_1, date_2),
          PERIOD FOR system_time(row_start, row_end))
          WITH SYSTEM VERSIONING;
       
      insert into t values (1, DATE('1000-01-01'), DATE('9999-01-01')), (2, DATE('1000-01-01'), DATE('9999-01-01')), (3,  DATE('1000-01-01'), DATE('9999-01-01'));
      insert into t_p values (1, 'gender', 'female', DATE('1000-01-01'), DATE('9999-01-01'));
      insert into t_p values (2, 'gender', 'male', DATE('1000-01-01'), DATE('9999-01-01'));
      insert into t_p values (3, 'gender', 'female', DATE('1000-01-01'), DATE('9999-01-01'));
       
      create temporary table tmp2
          select tgender.pvalue as gender, ttitle.pvalue as title
          from t
              left join t_p as tgender on t.id = tgender.elementId and tgender.pkey = 'gender'
              left join t_p as ttitle on t.id = ttitle.elementId and ttitle.pkey = 'title';
       
      select * from tmp2;
      
      

      I expect that the two queries yield the same result since they have nothing to do with the temporality of the tables.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              Rascat Lucas Schons
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: