Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5049

OR Criteria in JOINS does not work

    XMLWordPrintable

Details

    Description

      The case is related to Tableau or other ORMs where users have no control over the SQL being generated by the tools.

      The example code:

      Scenario 1:

      CREATE TABLE t3(id INT) ENGINE=COLUMNSTORE;
      CREATE TABLE t4(id INT) ENGINE=COLUMNSTORE;
       
      SELECT *
      FROM t3
      LEFT JOIN
      (
        SELECT t4.*
        FROM t4
      ) t4 ON (t3.id = t4.id OR t3.id IS NULL);
       
      "Internal Error: MCS-1000: 't3' and 'sub-query' are not joined".
      

      Scenario 2:

      MariaDB [testdb]> select * from t3 left join t4 on t3.id = t4.id or t3.id is null;
      ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.
       
      MariaDB [testdb]> select * from t3 join t4 on t3.id = t4.id or t3.id is null;
      ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.
       
      MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id or t3.id is null;
      ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.
      

      But if we change the above to use the `AND` operator for the second condition, it works well. With the `OR` condition, these fail. Using InnoDB, there is no such issue, and similarly, if we turn off the SQL Handler, it works as well, which is not really something that is recommended.

      Same tables using `AND` condition in the joining criteria.

      MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id and t3.id = 1;
      Empty set (0.018 sec)
       
      MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id and t3.id is null;
      Empty set (0.024 sec)
      

      The queries are out of the user's control as these are generated by Tableau and similar tools.

      Attachments

        Issue Links

          Activity

            People

              maxmether Max Mether
              Faisal Faisal Saeed (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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