[#MCOL-963] self join cte queries from tpcds alternately fail with parsing error and succeed but with incorrect results

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.

[MCOL-963] self join cte queries from tpcds alternately fail with parsing error and succeed but with incorrect results Created: 2017-10-09 Updated: 2017-11-20 Resolved: 2017-11-20
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	None
Affects Version/s:	1.1.0
Fix Version/s:	1.1.1, 1.1.2