# -------------------------------------------------------------- #
# Test case migrated from regression test suite: bug3314.sql
#
# Author: Daniel Lee, daniel.lee@mariadb.com
# -------------------------------------------------------------- #
#
--source ../include/have_columnstore.inc
#
USE tpch1;
#
--disable_warnings
drop table if exists paa;
drop table if exists dates;
drop table if exists t;
--enable_warnings

create table paa (
    p_id int,
    a_id int,
    date_id int
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

insert into paa values
    (1, 10, 100),
    (2, 20, 100);

create table dates (
    date_id int,
    dates date
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

insert into dates values
    (100, '2010-09-01');

create table t (
    p_id int,
    a_id int,
    date_id int,
    amount int
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

insert into t values
    (2, 20, 100, 99999);

select p.p_id,
  p.a_id,
  ifnull(`new_sales_amount`,0),
  ifnull(`new_sales_amount_ok`,0),
  p.dates as 'Date'
from (select aaa.p_id, aaa.a_id, dof.date_id, dof.dates
  from (select paa.p_id, paa.a_id, 1 as xxx
    from paa
    ) aaa
    join (select date_id, 1 as xxx, dates from dates) dof on (dof.xxx=aaa.xxx)
 ) p
left outer join (select t.date_id, t.p_id, a_id,
      IF(t.amount = 99999, amount, 0) / 10000 as 'new_sales_amount',
      IF(t.amount = 99999, amount, 0) as 'new_sales_amount_ok'
   from t
) tx
   on (p.p_id = tx.p_id
       and p.a_id = tx.a_id
       and p.date_id = tx.date_id)
order by 1, 2, 3;

--disable_warnings
drop table if exists paa;
drop table if exists dates;
drop table if exists t;
--enable_warnings
#