mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-26 11:48:52 +03:00
114 lines
5.0 KiB
Plaintext
114 lines
5.0 KiB
Plaintext
# -------------------------------------------------------------- #
|
|
# Test case migrated from regression test suite: bug3442.sql
|
|
#
|
|
# Author: Daniel Lee, daniel.lee@mariadb.com
|
|
# -------------------------------------------------------------- #
|
|
#
|
|
--source ../include/have_columnstore.inc
|
|
#
|
|
USE tpch1;
|
|
#
|
|
--disable_warnings
|
|
drop table if exists optest;
|
|
--enable_warnings
|
|
|
|
create table optest (id int, c1 date, c2 decimal(4,2), c3 date) engine=columnstore;
|
|
insert into optest values (0, '2011-01-27', null, '1999-08-25'), (1, null, null, null), (2, '2001-01-05', 2.34, null), (3, null, null, '2001-05-03');
|
|
select * from optest where (year(c1) = 2011 or c2 = 2.34) or month(c3) = 1;
|
|
select * from optest where (year(c1) = 2011 or c2 = 2.34) and month(c1) = 1;
|
|
select * from optest where year(c1) = 2011 or (c2 = 2.4) or month(c3) = 8;
|
|
select * from optest where year(c1) = 2011 or (c2 is null) or month(c1) = 2;
|
|
select * from optest where c1 is null or (c2 is null) or year(c1) = 99;
|
|
select * from optest where year(ifnull(c1, c3)) > 2000 and c2 is not null;
|
|
select * from optest where year(ifnull(c1, c3)) < 2000 or c2 is null and c3 is not null;
|
|
select * from optest where ifnull(year(c1), 2011) > 2010 or c2 is null and c3 is not null;
|
|
select * from optest where ifnull(year(c1), 2011) > 2010 or (c2 is null and c3 is not null or c2 > 2);
|
|
select * from optest where (null or year(ifnull(c1, c3)) > 2000) and ( c3 is not null or null);
|
|
select * from optest where (null or year(ifnull(c1, c3)) > 2000) or ( (c3 is not null or null) and c3 is null);
|
|
select * from optest where (null or year(ifnull(c1, c3)) > 2000) or ( (c3 is not null or null) and null);
|
|
select * from optest where (null or year(ifnull(c1, c3)) > 2000 and isnull(c2)) or ( (c3 is not null or null) and null);
|
|
select * from optest where ((null or year(ifnull(c1, c3)) > 2000) and isnull(c2)) or ( (c3 is not null or null) and null);
|
|
select * from optest where (null or c2+1 between 0 and 4 or isnull(c2)) and ( c3 is not null or null);
|
|
|
|
--disable_warnings
|
|
drop table if exists bug3442b;
|
|
--enable_warnings
|
|
|
|
create table bug3442b (
|
|
idx int,
|
|
i1 int,
|
|
i2 int,
|
|
bi1 bigint,
|
|
bi2 bigint,
|
|
d1 double,
|
|
d2 double,
|
|
f1 float,
|
|
f2 float,
|
|
c1 char(8),
|
|
c2 char(8),
|
|
vc1 varchar(20),
|
|
vc2 varchar(20),
|
|
dt1 date,
|
|
dt2 date,
|
|
dtm1 datetime,
|
|
dtm2 datetime
|
|
)engine=columnstore;
|
|
|
|
insert into bug3442b values
|
|
(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, '2008-01-25', '2008-01-25', '2008-01-25 10:00', '2011-01-08 11:00'),
|
|
(2, null, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, null, '2009-01-25', '2009-01-25', '2009-01-25 10:00', '2019-01-25 11:00'),
|
|
(3, 3, null, 3, 3, 3, 3, 3, 3, 3, 3, null, 3, '2010-01-25', '2010-01-25', '2010-01-25 10:00', '2018-01-25 11:00'),
|
|
(4, 4, 4, null, 4, 4, 4, 4, 4, 4, null, 4, 4, null, '2011-01-25', '2011-01-25 10:00', '2017-01-25 11:00'),
|
|
(5, 5, 5, 5, null, 5, 5, 5, 5, null, 5, 5, 5, '2012-01-25', null, '2012-01-25 10:00', '2016-01-25 11:00'),
|
|
(6, 6, 6, 6, 6, null, 6, 6, 6, 6, 6, 6, 6, '2013-01-25', '2014-01-25', null, '2015-01-25 11:00'),
|
|
(7, 7, 7, 7, 7, 7, null, 7, 7, 7, 7, 7, 7, '2014-01-25', '2013-01-25', '2014-01-25 10:00', null),
|
|
(8, 8, 8, 8, 8, 8, 8, null, 8, 8, 8, 8, 8, '2015-01-25', '2012-01-25', '2013-01-25 10:00', '2015-01-25 11:00'),
|
|
(9, 9, 9, 9, 9, 9, 9, 9, null, 9, 9, 9, 9, '2016-01-25', '2011-01-25', '2012-01-25 10:00', '2016-01-25 11:00'),
|
|
(10, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
|
|
|
|
select 'q1', idx from bug3442b where (idx <=5 or idx is null) or (if(idx > 7, null, idx) = idx);
|
|
|
|
select 'q2', idx, bi1, dt1, dt2 from bug3442b
|
|
where (bi1 is null) or (year(dt1)<2013 or year(dt2)=2012 or dt1 is null) and (idx <= 9) and (year(dt2)<2020 or
|
|
year(dt2) is null);
|
|
|
|
select 'q3', idx, bi1, dt1, dt2 from bug3442b
|
|
where (bi1 is null) or (year(dt1)=2017 or year(dt2)=2012 or dt1 is null) and (idx <= 9) and (year(dt2)<2020 or
|
|
year(dt2) is null);
|
|
|
|
select 'q4', idx, f1, f2 from bug3442b where (f1 > 3) or (f2 < 7);
|
|
|
|
select 'q5', idx, vc1, vc2 from bug3442b where (vc1 > 1) or (vc2 > 1);
|
|
|
|
select 'q6', idx, dtm1, dtm2 from bug3442b where (year(dtm1)=2014 and month(dtm1)=1) or (year(dtm2)=2015 and
|
|
month(dtm2) <= 7);
|
|
|
|
select 'q7', idx, d1, d2, f1, f2 from bug3442b where (d1 <= d2 or f1 > f2 or f2 is null) and (idx >= 1 and (f1 < 8 or
|
|
f2 > 7));
|
|
|
|
select 'q8', idx, dtm1, dtm2 from bug3442b where hour(dtm1)=10 or hour(dtm2)=11;
|
|
|
|
select 'q9', idx from bug3442b where hour(dtm1)=10 or i1 > 2 or i2 < 5 or f1 > 6 or year(dtm2) < 2018 or vc1 < 9 or
|
|
bi2 < 4;
|
|
|
|
select 'q10', idx from bug3442b where hour(dtm1)=9 or i1 = 2 or i2 = 5 or f2 is null or f1 > 6 or year(dtm2)= 2012 or
|
|
vc1 = 9 or bi2 = 4;
|
|
|
|
--disable_warnings
|
|
drop table bug3442b;
|
|
drop table optest;
|
|
--enable_warnings
|
|
|
|
select count(*) from nation where n_nationkey < 3 OR NULL;
|
|
|
|
select count(*) from nation where n_nationkey > 0 and (n_nationkey < 3 OR NULL);
|
|
|
|
select count(*) from nation where n_nationkey > 0 and (n_nationkey < 3 OR n_nationkey is null);
|
|
|
|
select count(*) from nation where n_comment like '%ep%' and (n_comment not like '%lee%' OR null);
|
|
|
|
select count(*) from nation where n_nationkey > 0 union select count(*) from nation where n_nationkey < 3 OR NULL order by 1;
|
|
|
|
#
|
|
|