|
Next executed Query is returning wrong results after Error reception from some previous executed Query
Expected : The Error(s) received from some Query does not affect any Next executed Queries
How to Repeat:
1. Run Query A --it's was returned Error
2. Run some other Query B-- the returned results are wrong and not corresponding to the expected ; If run Query B before Query A , it's rerunning results correctly
1. Run Query A
MariaDB [tpcds_1]> with cross_items as
|
-> (select i_item_sk ss_item_sk
|
-> from item,
|
-> (select iss.i_brand_id brand_id
|
-> ,iss.i_class_id class_id
|
-> ,iss.i_category_id category_id
|
-> from store_sales
|
-> ,item iss
|
-> ,date_dim d1
|
-> where ss_item_sk = iss.i_item_sk
|
-> and ss_sold_date_sk = d1.d_date_sk
|
-> and d1.d_year between 1999 AND 1999 + 2
|
-> intersect
|
-> select ics.i_brand_id
|
-> ,ics.i_class_id
|
-> ,ics.i_category_id
|
-> from catalog_sales
|
-> ,item ics
|
-> ,date_dim d2
|
-> where cs_item_sk = ics.i_item_sk
|
-> and cs_sold_date_sk = d2.d_date_sk
|
-> and d2.d_year between 1999 AND 1999 + 2
|
-> intersect
|
-> select iws.i_brand_id
|
-> ,iws.i_class_id
|
-> ,iws.i_category_id
|
-> from web_sales
|
-> ,item iws
|
-> ,date_dim d3
|
-> where ws_item_sk = iws.i_item_sk
|
-> and ws_sold_date_sk = d3.d_date_sk
|
-> and d3.d_year between 1999 AND 1999 + 2) x
|
-> where i_brand_id = brand_id
|
-> and i_class_id = class_id
|
-> and i_category_id = category_id
|
-> ),
|
-> avg_sales as
|
-> (select avg(quantity*list_price) average_sales
|
-> from (select ss_quantity quantity
|
-> ,ss_list_price list_price
|
-> from store_sales
|
-> ,date_dim
|
-> where ss_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2
|
-> union all
|
-> select cs_quantity quantity
|
-> ,cs_list_price list_price
|
-> from catalog_sales
|
-> ,date_dim
|
-> where cs_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2
|
-> union all
|
-> select ws_quantity quantity
|
-> ,ws_list_price list_price
|
-> from web_sales
|
-> ,date_dim
|
-> where ws_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2) x)
|
-> select * from
|
-> (select 'store' channel, i_brand_id,i_class_id,i_category_id
|
-> ,sum(ss_quantity*ss_list_price) sales, count(*) number_sales
|
-> from store_sales
|
-> ,item
|
-> ,date_dim
|
-> where ss_item_sk in (select ss_item_sk from cross_items)
|
-> and ss_item_sk = i_item_sk
|
-> and ss_sold_date_sk = d_date_sk
|
-> and d_week_seq = (select d_week_seq
|
-> from date_dim
|
-> where d_year = 1999 + 1
|
-> and d_moy = 12
|
-> and d_dom = 11)
|
-> group by i_brand_id,i_class_id,i_category_id
|
-> having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) this_year,
|
-> (select 'store' channel, i_brand_id,i_class_id
|
-> ,i_category_id, sum(ss_quantity*ss_list_price) sales, count(*) number_sales
|
-> from store_sales
|
-> ,item
|
-> ,date_dim
|
-> where ss_item_sk in (select ss_item_sk from cross_items)
|
-> and ss_item_sk = i_item_sk
|
-> and ss_sold_date_sk = d_date_sk
|
-> and d_week_seq = (select d_week_seq
|
-> from date_dim
|
-> where d_year = 1999
|
-> and d_moy = 12
|
-> and d_dom = 11)
|
-> group by i_brand_id,i_class_id,i_category_id
|
-> having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) last_year
|
-> where this_year.i_brand_id= last_year.i_brand_id
|
-> and this_year.i_class_id = last_year.i_class_id
|
-> and this_year.i_category_id = last_year.i_category_id
|
-> order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id
|
-> limit 100;
|
ERROR 1052 (23000): Column 'channel' in order clause is ambiguous
|
|
MariaDB [tpcds_1]> with cross_items as
|
-> (select i_item_sk ss_item_sk
|
-> from item,
|
-> (select iss.i_brand_id brand_id
|
-> ,iss.i_class_id class_id
|
-> ,iss.i_category_id category_id
|
-> from store_sales
|
-> ,item iss
|
-> ,date_dim d1
|
-> where ss_item_sk = iss.i_item_sk
|
-> and ss_sold_date_sk = d1.d_date_sk
|
-> and d1.d_year between 1999 AND 1999 + 2
|
-> intersect
|
-> select ics.i_brand_id
|
-> ,ics.i_class_id
|
-> ,ics.i_category_id
|
-> from catalog_sales
|
-> ,item ics
|
-> ,date_dim d2
|
-> where cs_item_sk = ics.i_item_sk
|
-> and cs_sold_date_sk = d2.d_date_sk
|
-> and d2.d_year between 1999 AND 1999 + 2
|
-> intersect
|
-> select iws.i_brand_id
|
-> ,iws.i_class_id
|
-> ,iws.i_category_id
|
-> from web_sales
|
-> ,item iws
|
-> ,date_dim d3
|
-> where ws_item_sk = iws.i_item_sk
|
-> and ws_sold_date_sk = d3.d_date_sk
|
-> and d3.d_year between 1999 AND 1999 + 2) x
|
-> where i_brand_id = brand_id
|
-> and i_class_id = class_id
|
-> and i_category_id = category_id
|
-> ),
|
-> avg_sales as
|
-> (select avg(quantity*list_price) average_sales
|
-> from (select ss_quantity quantity
|
-> ,ss_list_price list_price
|
-> from store_sales
|
-> ,date_dim
|
-> where ss_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2
|
-> union all
|
-> select cs_quantity quantity
|
-> ,cs_list_price list_price
|
-> from catalog_sales
|
-> ,date_dim
|
-> where cs_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2
|
-> union all
|
-> select ws_quantity quantity
|
-> ,ws_list_price list_price
|
-> from web_sales
|
-> ,date_dim
|
-> where ws_sold_date_sk = d_date_sk
|
-> and d_year between 1999 and 1999 + 2) x)
|
-> select * from
|
-> (select 'store' channel, i_brand_id,i_class_id,i_category_id
|
-> ,sum(ss_quantity*ss_list_price) sales, count(*) number_sales
|
-> from store_sales
|
-> ,item
|
-> ,date_dim
|
-> where ss_item_sk in (select ss_item_sk from cross_items)
|
-> and ss_item_sk = i_item_sk
|
-> and ss_sold_date_sk = d_date_sk
|
-> and d_week_seq = (select d_week_seq
|
-> from date_dim
|
-> where d_year = 1999 + 1
|
-> and d_moy = 12
|
-> and d_dom = 11)
|
-> group by i_brand_id,i_class_id,i_category_id
|
-> having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) this_year,
|
-> (select 'store' channel, i_brand_id,i_class_id
|
-> ,i_category_id, sum(ss_quantity*ss_list_price) sales, count(*) number_sales
|
-> from store_sales
|
-> ,item
|
-> ,date_dim
|
-> where ss_item_sk in (select ss_item_sk from cross_items)
|
-> and ss_item_sk = i_item_sk
|
-> and ss_sold_date_sk = d_date_sk
|
-> and d_week_seq = (select d_week_seq
|
-> from date_dim
|
-> where d_year = 1999
|
-> and d_moy = 12
|
-> and d_dom = 11)
|
-> group by i_brand_id,i_class_id,i_category_id
|
-> having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) last_year
|
-> where this_year.i_brand_id= last_year.i_brand_id
|
-> and this_year.i_class_id = last_year.i_class_id
|
-> and this_year.i_category_id = last_year.i_category_id
|
-> order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id
|
-> limit 100;
|
ERROR 1052 (23000): Column 'channel' in order clause is ambiguous
|
MariaDB [tpcds_1]>
|
MariaDB [tpcds_1]> with customer_total_return as
|
-> (select sr_customer_sk as ctr_customer_sk
|
-> ,sr_store_sk as ctr_store_sk
|
-> ,sum(SR_RETURN_AMT) as ctr_total_return
|
-> from store_returns
|
-> ,date_dim
|
-> where sr_returned_date_sk = d_date_sk
|
-> and d_year =2000
|
-> group by sr_customer_sk
|
-> ,sr_store_sk)
|
-> select c_customer_id
|
-> from customer_total_return ctr1
|
-> ,store
|
-> ,customer
|
-> where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
|
-> from customer_total_return ctr2
|
-> where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
|
-> and s_store_sk = ctr1.ctr_store_sk
|
-> and s_state = 'TN'
|
-> and ctr1.ctr_customer_sk = c_customer_sk
|
-> order by c_customer_id
|
-> limit 100;
|
+---------------+
|
| c_customer_id |
|
+---------------+
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
|
~~
|
|
store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
| store |
|
+---------------+
|
734 rows in set (0.004 sec)
|
|
Actual expected results from Query B
MariaDB [tpcds_1]> with customer_total_return as
|
-> (select sr_customer_sk as ctr_customer_sk
|
-> ,sr_store_sk as ctr_store_sk
|
-> ,sum(SR_RETURN_AMT) as ctr_total_return
|
-> from store_returns
|
-> ,date_dim
|
-> where sr_returned_date_sk = d_date_sk
|
-> and d_year =2000
|
-> group by sr_customer_sk
|
-> ,sr_store_sk)
|
-> select c_customer_id
|
-> from customer_total_return ctr1
|
-> ,store
|
-> ,customer
|
-> where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
|
-> from customer_total_return ctr2
|
-> where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
|
-> and s_store_sk = ctr1.ctr_store_sk
|
-> and s_state = 'TN'
|
-> and ctr1.ctr_customer_sk = c_customer_sk
|
-> order by c_customer_id
|
-> limit 100;
|
+------------------+
|
| c_customer_id |
|
+------------------+
|
| AAAAAAAAAAABBAAA |
|
| AAAAAAAAAAADBAAA |
|
| AAAAAAAAAAADBAAA |
|
| AAAAAAAAAAAKAAAA |
|
| AAAAAAAAAABDAAAA |
|
| AAAAAAAAAABHBAAA |
|
| AAAAAAAAAABLAAAA |
|
| AAAAAAAAAABMAAAA |
|
| AAAAAAAAAACHAAAA |
|
| AAAAAAAAAACMAAAA |
|
| AAAAAAAAAADDAAAA |
|
| AAAAAAAAAADGAAAA |
|
| AAAAAAAAAADGBAAA |
|
| AAAAAAAAAADGBAAA |
|
| AAAAAAAAAADPAAAA |
|
| AAAAAAAAAAEBAAAA |
|
| AAAAAAAAAAEFBAAA |
|
| AAAAAAAAAAEGBAAA |
|
| AAAAAAAAAAEIAAAA |
|
| AAAAAAAAAAEMAAAA |
|
| AAAAAAAAAAFAAAAA |
|
| AAAAAAAAAAFPAAAA |
|
| AAAAAAAAAAGGBAAA |
|
| AAAAAAAAAAGHBAAA |
|
| AAAAAAAAAAGJAAAA |
|
| AAAAAAAAAAGMAAAA |
|
| AAAAAAAAAAHEBAAA |
|
| AAAAAAAAAAHFBAAA |
|
| AAAAAAAAAAIEBAAA |
|
| AAAAAAAAAAJGBAAA |
|
| AAAAAAAAAAJHBAAA |
|
| AAAAAAAAAAKCAAAA |
|
| AAAAAAAAAAKCAAAA |
|
| AAAAAAAAAAKJAAAA |
|
| AAAAAAAAAAKMAAAA |
|
| AAAAAAAAAAKMAAAA |
|
| AAAAAAAAAALAAAAA |
|
| AAAAAAAAAALABAAA |
|
| AAAAAAAAAALGAAAA |
|
| AAAAAAAAAALHBAAA |
|
| AAAAAAAAAALJAAAA |
|
| AAAAAAAAAANHAAAA |
|
| AAAAAAAAAANHBAAA |
|
| AAAAAAAAAANJAAAA |
|
| AAAAAAAAAANMAAAA |
|
| AAAAAAAAAANMAAAA |
|
| AAAAAAAAAANNAAAA |
|
| AAAAAAAAAAOBBAAA |
|
| AAAAAAAAAAODBAAA |
|
| AAAAAAAAAAOLAAAA |
|
| AAAAAAAAAAPGBAAA |
|
| AAAAAAAAABAAAAAA |
|
| AAAAAAAAABAEAAAA |
|
| AAAAAAAAABAEBAAA |
|
| AAAAAAAAABAFBAAA |
|
| AAAAAAAAABAIAAAA |
|
| AAAAAAAAABAOAAAA |
|
| AAAAAAAAABBDBAAA |
|
| AAAAAAAAABCFAAAA |
|
| AAAAAAAAABCHBAAA |
|
| AAAAAAAAABDHAAAA |
|
| AAAAAAAAABENAAAA |
|
| AAAAAAAAABFEBAAA |
|
| AAAAAAAAABFGAAAA |
|
| AAAAAAAAABFMAAAA |
|
| AAAAAAAAABFPAAAA |
|
| AAAAAAAAABGFAAAA |
|
| AAAAAAAAABGFBAAA |
|
| AAAAAAAAABGJAAAA |
|
| AAAAAAAAABIBBAAA |
|
| AAAAAAAAABICBAAA |
|
| AAAAAAAAABIIAAAA |
|
| AAAAAAAAABJNAAAA |
|
| AAAAAAAAABKGBAAA |
|
| AAAAAAAAABLOAAAA |
|
| AAAAAAAAABLPAAAA |
|
| AAAAAAAAABMABAAA |
|
| AAAAAAAAABMPAAAA |
|
| AAAAAAAAABNAAAAA |
|
| AAAAAAAAABNCBAAA |
|
| AAAAAAAAABNEBAAA |
|
| AAAAAAAAABNLAAAA |
|
| AAAAAAAAABNOAAAA |
|
| AAAAAAAAABNPAAAA |
|
| AAAAAAAAABOAAAAA |
|
| AAAAAAAAABOFBAAA |
|
| AAAAAAAAABOOAAAA |
|
| AAAAAAAAABOPAAAA |
|
| AAAAAAAAABPEAAAA |
|
| AAAAAAAAACADAAAA |
|
| AAAAAAAAACAFAAAA |
|
| AAAAAAAAACAFAAAA |
|
| AAAAAAAAACAHBAAA |
|
| AAAAAAAAACAJAAAA |
|
| AAAAAAAAACBDAAAA |
|
| AAAAAAAAACBDAAAA |
|
| AAAAAAAAACBEBAAA |
|
| AAAAAAAAACBNAAAA |
|
| AAAAAAAAACBPAAAA |
|
| AAAAAAAAACCHAAAA |
|
+------------------+
|
100 rows in set (0.182 sec)
|
|
|