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

System-Versioned Tables inccorect joins

    XMLWordPrintable

Details

    Description

      CREATE TABLE `t1` (
        `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `x` VARCHAR(10),
        PRIMARY KEY (`id`)
      )WITH SYSTEM VERSIONING; 
       
      CREATE TABLE `t2` (
        `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `t1_id` INT(10) UNSIGNED NOT NULL,
        `x` VARCHAR(10),
        PRIMARY KEY (`id`),
        KEY `t1_id_foreign` (`t1_id`),
        CONSTRAINT `t1_id_foreign` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) 
      )WITH SYSTEM VERSIONING;
       
       
      INSERT INTO t1 SET  `x` = 'a';
      UPDATE t1 SET X = 'b' WHERE ID = 1;
       
      INSERT INTO t2 SET X = 'b',t1_id=1;
       
      SELECT t1.*,t2.* FROM t1 LEFT JOIN t2 ON t1.`id` = t2.`t1_id` ;
      +----+------+------+-------+------+
      | id | X    | id   | t1_id | X    |
      +----+------+------+-------+------+
      |  1 | b    |    1 |     1 | b    |
      |  1 | a    | NULL |  NULL | NULL |
      +----+------+------+-------+------+
      2 ROWS IN SET (0.001 sec)
       
      expected 
       
      SELECT t1.*,t2.* FROM t1 LEFT JOIN t2 ON t1.`id` = t2.`t1_id` ;
      +----+------+------+-------+------+
      | id | X    | id   | t1_id | X    |
      +----+------+------+-------+------+
      |  1 | b    |    1 |     1 | b    |
      +----+------+------+-------+------+
      1 ROWS IN SET (0.001 sec)
      
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            giko giko
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.