Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
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>
|