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

self join cte queries from tpcds alternately fail with parsing error and succeed but with incorrect results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 1.1.0
    • 1.1.1, 1.1.2
    • None
    • None
    • 2017-21, 2017-22, 2017-23

    Description

      query47 and query57 in tpcds both are complex queries using 2 level CTE, self join, and window functions. For example query47:

      #QUERY_47
      with v1 as(
       select i_category, i_brand,
              s_store_name, s_company_name,
              d_year, d_moy,
              sum(ss_sales_price) sum_sales,
              avg(sum(ss_sales_price)) over
                (partition by i_category, i_brand,
                           s_store_name, s_company_name, d_year)
                avg_monthly_sales,
              rank() over
                (partition by i_category, i_brand,
                           s_store_name, s_company_name
                 order by d_year, d_moy) rn
       from item, store_sales, date_dim, store
       where ss_item_sk = i_item_sk and
             ss_sold_date_sk = d_date_sk and
             ss_store_sk = s_store_sk and
             (
               d_year = 2000 or
               ( d_year = 2000-1 and d_moy =12) or
               ( d_year = 2000+1 and d_moy =1)
             )
       group by i_category, i_brand,
                s_store_name, s_company_name,
                d_year, d_moy),
       v2 as(
       select v1_base.i_category
              ,v1_base.d_year, v1_base.d_moy
              ,v1_base.avg_monthly_sales
              ,v1_base.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
       from v1 v1_base, v1 v1_lag, v1 v1_lead
       where v1_base.i_category = v1_lag.i_category and
             v1_base.i_category = v1_lead.i_category and
             v1_base.i_brand = v1_lag.i_brand and
             v1_base.i_brand = v1_lead.i_brand and
             v1_base.s_store_name = v1_lag.s_store_name and
             v1_base.s_store_name = v1_lead.s_store_name and
             v1_base.s_company_name = v1_lag.s_company_name and
             v1_base.s_company_name = v1_lead.s_company_name and
             v1_base.rn = v1_lag.rn + 1 and
             v1_base.rn = v1_lead.rn - 1)
        select  *
       from v2
       where  d_year = 2000 and    
              avg_monthly_sales > 0 and
              case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
       order by sum_sales - avg_monthly_sales, 3
       limit 100;
      

      will fail in one execution with:
      ERROR 1146 (42S02) at line 2 in file: 'query47.sql': Table 'tpcds_1.v1' doesn't exist

      but succeed the next and so on. I tried to come up with a simpler example wasn't able to so will need more debugging to see what is going on.

      Attachments

        Issue Links

          Activity

            People

              dleeyh Daniel Lee (Inactive)
              dthompson David Thompson (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.