[MCOL-44] tpch22.sql returns empty set Created: 2016-05-06  Updated: 2016-06-15  Resolved: 2016-05-31

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: None
Fix Version/s: 1.0.0

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

Ref: working_tpch1_compareLogOnly/fnJoin/tpch22.sql.log

This statement returns an empty set. should return as below

select  cntrycode,  count(*) as numcust,  sum(c_acctbal) as totacctbal 
from  (   select    substr(c_phone, 1, 2) as cntrycode,    c_acctbal   
	from    customer   
	where    substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')    
	and c_acctbal > (     select      avg(c_acctbal)     
			from      customer     
			where      c_acctbal > 0.00      
			and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17'))
 
	and not exists (     select      *     
			from      orders     
			where      from_unixtime(o_custkey) = from_unixtime(c_custkey)    )  
	) custsale 
group by  cntrycode 
order by  cntrycode;

Should return:

+-----------+---------+------------+
 
| cntrycode | numcust | totacctbal |
 
+-----------+---------+------------+
 
| 13        |     888 | 6737713.99 |
 
| 17        |     861 | 6460573.72 |
 
| 18        |     964 | 7236687.40 |
 
| 23        |     892 | 6701457.95 |
 
| 29        |     948 | 7158866.63 |
 
| 30        |     909 | 6808436.13 |
 
| 31        |     922 | 6806670.18 |
 
+-----------+---------+------------+
 
7 rows in set (5.12 sec)
 
 
 
mysql>



 Comments   
Comment by David Hall (Inactive) [ 2016-05-24 ]

This issue has been fixed.

Comment by David Hall (Inactive) [ 2016-05-24 ]

For QA

Comment by Dipti Joshi (Inactive) [ 2016-05-31 ]

dleeyh Has this been verified ? If it has and now working then please close this item

Comment by Daniel Lee (Inactive) [ 2016-05-31 ]

Build verified:

getsoftwareinfo Tue May 31 10:30:28 2016

Name : mariadb-columnstore-platform Relocations: (not relocatable)
Version : 1.0 Vendor: MariaDB Corporation Ab
Release : 0 Build Date: Fri 27 May 2016 04:07:53 PM EDT
Install Date: Mon 30 May 2016 11:32:15 AM EDT Build Host: srvbuilder

MariaDB [mytest]> select cntrycode, count as numcust, sum(c_acctbal) as totacctbal
-> from ( select substr(c_phone, 1, 2) as cntrycode, c_acctbal
-> from customer
-> where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
-> and c_acctbal > ( select avg(c_acctbal)
-> from customer
-> where c_acctbal > 0.00
-> and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17'))
-> and not exists ( select *
-> from orders
-> where from_unixtime(o_custkey) = from_unixtime(c_custkey) )
-> ) custsale
-> group by cntrycode
-> order by cntrycode;
----------------------------

cntrycode numcust totacctbal

----------------------------

13 888 6737713.99
17 861 6460573.72
18 964 7236687.40
23 892 6701457.95
29 948 7158866.63
30 909 6808436.13
31 922 6806670.18

----------------------------
7 rows in set (21.19 sec)

Generated at Thu Feb 08 02:18:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.