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

Subquery using IN with VIEW returns incorrect results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 1.0.0
    • Icebox
    • MariaDB Server
    • None
    • 2016-19

    Description

      test001 working_tpch1/view/view.sql, the query marked "VIEW used in subquery" at line 29.

      This is the sequence:

      create or replace view v1 as (select sub1.c1 s1c1, sub1.c2 s1c2, sub2.c1 s2c1, sub2.c2 s2c2 from sub1 left join sub2 on sub1.c1 = sub2.c1);
      create or replace view v2 as (select sub3.c1 s3c1, sub4.c1 s4c1, sub3.c2 s3c2, sub4.c2 s4c2 from sub3 right join sub4 on sub3.c1 = sub4.c1);
      select * from v2 where s3c1 in (select s1c1 from v1) order by 1,2,3,4;
      

      The result set is all of the rows from v2, regardless of the contents of s1c1 in v1.

      This issue does not manifest if equivalent tables are used, rather than views.
      That is, if t1 and t2 are tables containing the same rows as the views (created by 'create table t1 as select * from v1', maybe), then

      select * from t2 where s3c1 in (select s1c1 from t1) order by 1,2,3,4;
      

      filters the data correctly.

      Attachments

        Issue Links

          Activity

            People

              LinuxJedi Andrew Hutchings (Inactive)
              David.Hall David Hall (Inactive)
              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.