--
-- ### View condition checks
--

create table t1 ( a string(10), b datetime);

insert into t1 values ( 'alpha', date('%d%m%Y %H%M%S', '01012009 120000'));
insert into t1 values ( 'beta', date('%d%m%Y %H%M%S', '01012010 120000'));
insert into t1 values ( 'alpha', date('%d%m%Y %H%M%S', '04012011 120000'));
insert into t1 values ( 'alpha', date('%d%m%Y %H%M%S', '06012011 120000'));
insert into t1 values ( 'alpha', date('%d%m%Y %H%M%S', '10012011 120000'));
insert into t1 values ( 'beta', date('%d%m%Y %H%M%S', '01012010 120000'));
insert into t1 values ( 'beta', date('%d%m%Y %H%M%S', '01012010 120000'));

create view tv1 as select a as a, date2str(b, '%Y') as year from t1;

select * from tv1 where a = 'alpha' and year = '2009';

select * from tv1 where year <= '2010';
select * from tv1 where year < '2020';

select year, count(*) from tv1 group by year;
select year, count(*) from tv1 where a = 'alpha' group by year;

drop view tv1;
drop table t1; 