testdata/workloads/functional-query/queries/QueryTest/kudu-scan-node.test

====
---- QUERY
# Make sure LIMIT is enforced.
select * from functional_kudu.dimtbl order by id limit 1;
---- RESULTS
1001,'Name1',94611
---- TYPES
BIGINT, STRING, INT
====
---- QUERY
# Make sure that we can list the columns to be scanned in any order, that predicates
# work and that we can have predicates on columns not referenced elsewhere.
select zip, id from functional_kudu.dimtbl where id >= 1000 and 1002 >= id and
94611 = zip and 'Name1' = name order by id;
---- RESULTS
94611,1001
---- TYPES
INT, BIGINT
====
---- QUERY
# Regression test for IMPALA-2740, a NULL value from a previously filtered row would
# carry over into the next unfiltered row (the result below would incorrectly be 2,NULL).
CREATE TABLE impala_2740 (key INT PRIMARY KEY, value INT)
  PARTITION BY HASH (key) PARTITIONS 3 STORED AS KUDU;
INSERT INTO impala_2740 VALUES (1, NULL), (2, -2);
SELECT * FROM impala_2740 WHERE key != 1;
---- RESULTS
2,-2
---- TYPES
INT, INT
====
---- QUERY
# Regression test for IMPALA-2635, the Kudu scanner hangs waiting for data from scanner
# threads that are never started. The union and both scans land in the same fragment which
# is run on all impalads. However, for the t1 table there is only as single scan range,
# so two of the scan instances get empty scan ranges.
CREATE TABLE impala_2635_t1 (id BIGINT PRIMARY KEY, name STRING)
  PARTITION BY HASH (id) PARTITIONS 3 STORED AS KUDU;
CREATE TABLE impala_2635_t2 (id BIGINT PRIMARY KEY, name STRING)
  PARTITION BY HASH(id) PARTITIONS 16 STORED AS KUDU;
INSERT INTO impala_2635_t1 VALUES (0, 'Foo');
INSERT INTO impala_2635_t2 VALUES (1, 'Blah');
SELECT * FROM impala_2635_t1 UNION ALL SELECT * FROM impala_2635_t2;
---- RESULTS
0,'Foo'
1,'Blah'
---- TYPES
BIGINT, STRING
====
---- QUERY
# IMPALA-4408: Test Kudu scans where all materialized slots are non-nullable.
select count(int_col) from functional_kudu.tinyinttable
---- RESULTS
10
---- TYPES
BIGINT
====
---- QUERY
# IMPALA-4859: Test Kudu IS NULL/IS NOT NULL pushdown
select count(*) from functional_kudu.alltypesagg where id < 10 and float_col is null;
---- RESULTS
2
---- TYPES
BIGINT
====
---- QUERY
select count(*) from functional_kudu.alltypesagg where id < 10 and float_col is not null;
---- RESULTS
9
---- TYPES
BIGINT
====
---- QUERY
# alltypes.id is primary key/not nullable, verify IS NOT NULL/IS NULL pushdown works
select count(*) from functional_kudu.alltypes where id is not null;
---- RESULTS
7300
---- TYPES
BIGINT
====
---- QUERY
select count(*) from functional_kudu.alltypes where id is null;
---- RESULTS
0
---- TYPES
BIGINT
====
---- QUERY
# Push down TIMESTAMP binary predicates
select id, timestamp_col from functional_kudu.alltypes where
timestamp_col <= cast('2009-01-01 00:08:00.28' as timestamp) and
timestamp_col >= cast('2009-01-01 00:04:00.6' as timestamp)
order by id;
---- RESULTS
4,2009-01-01 00:04:00.600000000
5,2009-01-01 00:05:00.100000000
6,2009-01-01 00:06:00.150000000
7,2009-01-01 00:07:00.210000000
8,2009-01-01 00:08:00.280000000
---- TYPES
INT, TIMESTAMP
====
---- QUERY
# Out-of-range TIMESTAMP predicate (evaluates to NULL)
select id, timestamp_col from functional_kudu.alltypes where
timestamp_col > cast('1000-01-01 00:00:00.00' as timestamp)
---- RESULTS
---- TYPES
INT, TIMESTAMP
====
---- QUERY
select id, timestamp_col from functional_kudu.alltypes where
timestamp_col < cast('2009-01-01 00:08:00.28' as timestamp) and
timestamp_col > cast('2009-01-01 00:04:00.6' as timestamp)
order by id;
---- RESULTS
5,2009-01-01 00:05:00.100000000
6,2009-01-01 00:06:00.150000000
7,2009-01-01 00:07:00.210000000
---- TYPES
INT, TIMESTAMP
====
---- QUERY
select id, timestamp_col from functional_kudu.alltypes where
timestamp_col = cast('2009-01-01 00:08:00.28' as timestamp);
---- RESULTS
8,2009-01-01 00:08:00.280000000
---- TYPES
INT, TIMESTAMP
====
---- QUERY
# Push down TIMESTAMP IN list predicates
select id, timestamp_col from functional_kudu.alltypes where
timestamp_col in (cast('2010-03-01 00:00:00' as timestamp),
                  cast('2010-03-01 00:01:00' as timestamp))
order by id;
---- RESULTS
4240,2010-03-01 00:00:00
4241,2010-03-01 00:01:00
---- TYPES
INT, TIMESTAMP
====
---- QUERY
# Push down VARCHAR predicates
CREATE TABLE kudu_varchar_pred (key INT PRIMARY KEY, varchar_col VARCHAR(10))
  PARTITION BY HASH (key) PARTITIONS 4 STORED AS KUDU;
INSERT INTO kudu_varchar_pred VALUES
  (1, cast('a' as VARCHAR(10))),
  (2, cast('b' as VARCHAR(10))),
  (3, cast('m' as VARCHAR(10))),
  (4, cast('y' as VARCHAR(10))),
  (5, cast('z' as VARCHAR(10))),
  (6, NULL);
select key, varchar_col from kudu_varchar_pred where
varchar_col >= cast('b' as VARCHAR(10)) and
varchar_col <= cast('y' as VARCHAR(10))
order by key;
---- RESULTS
2,'b'
3,'m'
4,'y'
---- TYPES
INT, STRING
====
---- QUERY
# Regression test for IMPALA-6187. Make sure count(*) queries with partition columns only
# won't miss conjuncts evaluation. 'id' is the partition column here.
select count(*) from functional_kudu.alltypes where rand() + id < 0.0;
---- RESULTS
0
---- TYPES
BIGINT
====
---- QUERY
# Create Kudu table with non unique primary key
create table non_unique_key_scan_tbl1 non unique primary key (id)
partition by range (id)
(partition value = 0, partition value = 1,
 partition value = 2, partition value = 3,
 partition value = 4, partition value = 5,
 partition value = 6, partition value = 7)
stored as kudu
as select id, int_col from functional.alltypestiny;
---- RESULTS
'Inserted 8 row(s)'
====
---- QUERY
# auto-incrementing column is not shown for SELECT *
select * from non_unique_key_scan_tbl1 order by id asc;
---- RESULTS
0,0
1,1
2,0
3,1
4,0
5,1
6,0
7,1
---- TYPES
INT,INT
====
---- QUERY
# auto-incrementing column is shown when the column is specified in SELECT statement
select id, int_col, auto_incrementing_id from non_unique_key_scan_tbl1 order by id asc,
auto_incrementing_id desc;
---- RESULTS
0,0,1
1,1,1
2,0,1
3,1,1
4,0,1
5,1,1
6,0,1
7,1,1
---- TYPES
INT,INT,BIGINT
====
---- QUERY
# Query with auto-incrementing column in where clause
select id, int_col, auto_incrementing_id from non_unique_key_scan_tbl1
where auto_incrementing_id = 1 and id < 3
group by id, int_col, auto_incrementing_id;
---- RESULTS
0,0,1
1,1,1
2,0,1
---- TYPES
INT,INT,BIGINT
====
---- QUERY
# Create unpartitioned Kudu table with non unique primary key.
create table non_unique_key_scan_tbl2 non unique primary key (id)
stored as kudu
as select id, int_col from functional.alltypestiny order by id asc limit 100;
---- RESULTS
'Inserted 8 row(s)'
====
---- QUERY
# Query with auto-incrementing column in ORDER BY.
# All rows are added to one tablet-server so auto_incrementing_id shows insertion order.
select id, int_col, auto_incrementing_id from non_unique_key_scan_tbl2
order by auto_incrementing_id asc;
---- RESULTS
0,0,1
1,1,2
2,0,3
3,1,4
4,0,5
5,1,6
6,0,7
7,1,8
---- TYPES
INT,INT,BIGINT
====