-----------------------------------------------------------display pushdown predicate feature-----------------------------------------------
create schema dfs_predicate_schema;
set current_schema = dfs_predicate_schema;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (type 'hdfs', address '@hdfshostname@:@hdfsport@',hdfscfgpath '@hdfscfgpath@');
--1. explain predicate pushdown use case
set cstore_insert_mode = main;
--prepare, the a and b column could be pushed down, the t column can not be pushed down
drop table if exists dfs_predicate_01;
create table dfs_predicate_01(a1 int, a2 int, p1 tinyint, p2 int2, p3 int4, p4 int8, non_p SMALLDATETIME)
tablespace hdfs_ts
distribute by hash(a1, a2);
--partition by values (p1, p2, p3, p4);
insert into dfs_predicate_01 values(1, 2, 3, 4, 5, 6, '2001-04-11 04:05:01');
insert into dfs_predicate_01 values(2, 3, 4, 5, 6, 7, '2002-04-12 04:05:02');
insert into dfs_predicate_01 values(3, 4, 5, 6, 7, 8, '2003-04-13 04:05:03');
insert into dfs_predicate_01 values(4, 5, 6, 7, 8, 9, '2004-04-14 04:05:04');
insert into dfs_predicate_01 values(5, 6, 7, 8, 9, 10, '2005-04-15 04:05:05');
drop table if exists temp_predicate_01;
create table temp_predicate_01 (a1 int, a2 int, p1 tinyint, p2 int2, p3 int4, p4 int8)
tablespace hdfs_ts
distribute by hash(a1, a2);
insert into temp_predicate_01 select a1, a2, p1, p2, p3, p4 from dfs_predicate_01;
create foreign table hdfs_predicate_01(a1 int, a2 int, p1 tinyint, p2 int2, p3 int4, p4 int8)
server hdfs_server
options(format 'orc', foldername '/@hdfsstoreplus@/dfs_init_004/tablespace_secondary/regression/dfs_predicate_schema.temp_predicate_01')
distribute by roundrobin;
drop table if exists normal_01;
create table normal_01 (a1 int, a2 int, p1 tinyint, p2 int2, p3 int4, p4 int8, non_p SMALLDATETIME);
insert into normal_01 select * from dfs_predicate_01;
drop table if exists dfs_predicate_02;
create table dfs_predicate_02( a int, p5 date, p6 decimal(5, 2), p7 bpchar(10), p8 char, non_p SMALLDATETIME)
tablespace hdfs_ts;
--partition by values (p5, p6, p7, p8);
insert into dfs_predicate_02 values(1, '12-10-2016', 2.1, '3.1', 'a', '2001-10-11 16:53:00');
insert into dfs_predicate_02 values(2, '12-11-2016', 3.1, '4.1', 'b', '2001-10-12 16:53:00');
insert into dfs_predicate_02 values(3, '12-12-2016', 4.1, '5.1', 'c', '2001-10-13 16:53:00');
insert into dfs_predicate_02 values(4, '12-13-2016', 5.1, '6.1', 'd', '2001-10-14 16:53:00');
insert into dfs_predicate_02 values(5, '12-14-2016', 6.1, '7.1', 'e', '2001-10-15 16:53:00');
drop table if exists temp_predicate_02;
create table temp_predicate_02 ( a int, p5 date, p6 decimal(5, 2), p7 bpchar(10), p8 char)
tablespace hdfs_ts;
insert into temp_predicate_02 select a, p5, p6, p7, p8 from dfs_predicate_02;
create foreign table hdfs_predicate_02( a int, p5 date, p6 decimal(5, 2), p7 bpchar(10), p8 char)
server hdfs_server
options(format 'orc', foldername '/@hdfsstoreplus@/dfs_init_004/tablespace_secondary/regression/dfs_predicate_schema.temp_predicate_02')
distribute by roundrobin;
drop table if exists normal_02;
create table normal_02( a int, p5 date, p6 decimal(5, 2), p7 bpchar(10), p8 char, non_p SMALLDATETIME);
insert into normal_02 select * from dfs_predicate_02;
drop table if exists dfs_predicate_03;
create table dfs_predicate_03(a int, p9 varchar(10), b clob, p11 text, p12 timestamp, non_p SMALLDATETIME)
tablespace hdfs_ts
partition by values (p9, p11, p12);
insert into dfs_predicate_03 values(1, '12-10-2016', '2.1', '3.1', '2003-04-10 04:05:06', '2001-10-11 16:53:01');
insert into dfs_predicate_03 values(2, '12-11-2016', '3.1', '4.1', '2003-04-11 04:05:06', '2001-10-12 16:53:02');
insert into dfs_predicate_03 values(3, '12-12-2016', '4.1', '5.1', '2003-04-12 04:05:06', '2001-10-13 16:53:03');
insert into dfs_predicate_03 values(4, '12-13-2016', '5.1', '6.1', '2003-04-13 04:05:06', '2001-10-14 16:53:04');
insert into dfs_predicate_03 values(5, '12-14-2016', '6.1', '7.1', '2003-04-14 04:05:06', '2001-10-15 16:53:05');
drop table if exists temp_predicate_03;
create table temp_predicate_03 (a int, p9 varchar(10), b clob, p11 text, p12 timestamp)
tablespace hdfs_ts;
insert into temp_predicate_03 select a , p9, b, p11, p12 from dfs_predicate_03;
create foreign table hdfs_predicate_03(a int, p9 varchar(10), b clob, p11 text, p12 timestamp)
server hdfs_server
options(format 'orc', foldername '/@hdfsstoreplus@/dfs_init_004/tablespace_secondary/regression/dfs_predicate_schema.temp_predicate_03')
distribute by roundrobin;
drop table if exists normal_03;
create table normal_03(a int, p9 varchar(10), b clob, p11 text, p12 timestamp, non_p SMALLDATETIME);
insert into normal_03 select * from dfs_predicate_03;
select * from dfs_predicate_01 order by 1;
select * from dfs_predicate_02 order by 1;
select * from dfs_predicate_03 order by 1;
CREATE OR REPLACE FUNCTION predicate_clause_test(
TestName VARCHAR,
nor_tbl VARCHAR,
dfs_tbl VARCHAR,
predicate_clause VARCHAR
) RETURNS TEXT
AS $$
DECLARE
count_result INTEGER;
BEGIN
EXECUTE
'
select count(*)
from ( select * from '|| nor_tbl ||' where '|| predicate_clause ||'
MINUS ALL
select * from '|| dfs_tbl ||' where '|| predicate_clause ||'
)
'
INTO count_result;
return TestName || ' return code:' || count_result;
END;
$$ LANGUAGE plpgsql;
---------test HDFS table
----------------------------check explain
--The first category, check explain
--------------------------------------------------
set explain_perf_mode=normal;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from dfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=pretty;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from dfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=summary;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from dfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=run;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from dfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from dfs_predicate_03 where p9 = '12-10-2016' and a = 1;
----------------------------test HDFS foreign table
set explain_perf_mode=normal;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from hdfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=pretty;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from hdfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=summary;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from hdfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and a = 1;
--------------------------------------------------
set explain_perf_mode=run;
--use case1: pushdown predicate
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where a1 = 1 and a2 = 2;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint and p2 = 5 and p3 = 6 and p4 = 7 and a1 = 1 and a2 = 2 ;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where b is null;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_02 where p5 > '12-10-2016' and p6 = 2.1 and p7 = '3.1' and p8 = 'a' and a =1;
explain (verbose, costs off) select count(*) from hdfs_predicate_01 where a1 = 2 and a2 = 3;
--use case non-pushdown predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_01 where p1 = 4::tinyint or p2 = 5 and p3 = 6 and p4 = 7;
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and p11 = '3.1' and p12='2003-04-10 04:05:06';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p11 = '3.1';
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and b = '2.1';
--use case mixed predicate clause
explain (verbose, costs off, nodes off) select count(*) from hdfs_predicate_03 where p9 = '12-10-2016' and a = 1;
----------------------------result check
--------------------------------------------------pushdown predicate
select predicate_clause_test('Test predicate clause(p1 = 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 = 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 < 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 < 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 > 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 > 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 <= 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 <= 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 >= 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 >= 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 <> 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 <> 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 != 4::tinyint):', 'normal_01', 'dfs_predicate_01', 'p1 != 4::tinyint');
select predicate_clause_test('Test predicate clause(p1 is null):', 'normal_01', 'dfs_predicate_01', 'p1 is null');
select predicate_clause_test('Test predicate clause(p1 is not null):', 'normal_01', 'dfs_predicate_01', 'p1 is not null');
select * from dfs_predicate_01 where p1 is null;
select * from dfs_predicate_01 where p1 > 100::tinyint;
select * from dfs_predicate_01 where p1 <-10::tinyint;
select predicate_clause_test('Test predicate clause(p2 = 4):', 'normal_01', 'dfs_predicate_01', 'p2 = 4');
select predicate_clause_test('Test predicate clause(p2 < 4):', 'normal_01', 'dfs_predicate_01', 'p2 < 4');
select predicate_clause_test('Test predicate clause(p2 > 4):', 'normal_01', 'dfs_predicate_01', 'p2 > 4');
select predicate_clause_test('Test predicate clause(p2 <= 4):', 'normal_01', 'dfs_predicate_01', 'p2 <= 4');
select predicate_clause_test('Test predicate clause(p2 >= 4):', 'normal_01', 'dfs_predicate_01', 'p2 >= 4');
select predicate_clause_test('Test predicate clause(p2 <> 4):', 'normal_01', 'dfs_predicate_01', 'p2 <> 4');
select predicate_clause_test('Test predicate clause(p2 != 4):', 'normal_01', 'dfs_predicate_01', 'p2 != 4');
select predicate_clause_test('Test predicate clause(p2 is null):', 'normal_01', 'dfs_predicate_01', 'p2 is null');
select predicate_clause_test('Test predicate clause(p2 is not null):', 'normal_01', 'dfs_predicate_01', 'p2 is not null');
select * from dfs_predicate_01 where p2 is null;
select * from dfs_predicate_01 where p2 > 100::tinyint;
select * from dfs_predicate_01 where p2 <-10::tinyint;
select predicate_clause_test('Test predicate clause(p3 = 6):', 'normal_01', 'dfs_predicate_01', 'p3 = 6');
select predicate_clause_test('Test predicate clause(p3 < 6):', 'normal_01', 'dfs_predicate_01', 'p3 < 6');
select predicate_clause_test('Test predicate clause(p3 > 6):', 'normal_01', 'dfs_predicate_01', 'p3 > 6');
select predicate_clause_test('Test predicate clause(p3 <= 6):', 'normal_01', 'dfs_predicate_01', 'p3 <= 6');
select predicate_clause_test('Test predicate clause(p3 >= 6):', 'normal_01', 'dfs_predicate_01', 'p3 >= 6');
select predicate_clause_test('Test predicate clause(p3 <> 6):', 'normal_01', 'dfs_predicate_01', 'p3 <> 6');
select predicate_clause_test('Test predicate clause(p3 != 6):', 'normal_01', 'dfs_predicate_01', 'p3 != 6');
select predicate_clause_test('Test predicate clause(p3 is null):', 'normal_01', 'dfs_predicate_01', 'p3 is null');
select predicate_clause_test('Test predicate clause(p3 is not null):', 'normal_01', 'dfs_predicate_01', 'p3 is not null');
select * from dfs_predicate_01 where p3 is null;
select * from dfs_predicate_01 where p3 > 100::tinyint;
select * from dfs_predicate_01 where p3 <-10::tinyint;
select predicate_clause_test('Test predicate clause(p4 = 7):', 'normal_01', 'dfs_predicate_01', 'p4 = 7');
select predicate_clause_test('Test predicate clause(p4 < 7):', 'normal_01', 'dfs_predicate_01', 'p4 < 7');
select predicate_clause_test('Test predicate clause(p4 > 7):', 'normal_01', 'dfs_predicate_01', 'p4 > 7');
select predicate_clause_test('Test predicate clause(p4 <= 7):', 'normal_01', 'dfs_predicate_01', 'p4 <= 7');
select predicate_clause_test('Test predicate clause(p4 >= 7):', 'normal_01', 'dfs_predicate_01', 'p4 >= 7');
select predicate_clause_test('Test predicate clause(p4 <> 7):', 'normal_01', 'dfs_predicate_01', 'p4 <> 7');
select predicate_clause_test('Test predicate clause(p4 != 7):', 'normal_01', 'dfs_predicate_01', 'p4 != 7');
select predicate_clause_test('Test predicate clause(p4 is null):', 'normal_01', 'dfs_predicate_01', 'p4 is null');
select predicate_clause_test('Test predicate clause(p4 is not null):', 'normal_01', 'dfs_predicate_01', 'p4 is not null');
select * from dfs_predicate_01 where p4 is null;
select * from dfs_predicate_01 where p4 > 100::tinyint;
select * from dfs_predicate_01 where p4 <-10::tinyint;
--test p5
select count(*) from ( select * from normal_02 where p5 = '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 = '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 < '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 < '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 > '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 > '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 <= '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 <= '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 >= '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 >= '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 <> '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 <> '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 != '12-10-2016' MINUS ALL select * from dfs_predicate_02 where p5 != '12-10-2016' );
select count(*) from ( select * from normal_02 where p5 is null MINUS ALL select * from dfs_predicate_02 where p5 is null );
select count(*) from ( select * from normal_02 where p5 is not null MINUS ALL select * from dfs_predicate_02 where p5 is not null );
select * from dfs_predicate_02 where p5 is null;
select * from dfs_predicate_02 where p5 > '12-15-2016';
select * from dfs_predicate_02 where p5 < '12-9-2016';
--test p6 deciaml type
select predicate_clause_test('Test predicate clause(p6 = 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 = 3.1');
select predicate_clause_test('Test predicate clause(p6 < 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 < 3.1');
select predicate_clause_test('Test predicate clause(p6 > 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 > 3.1');
select predicate_clause_test('Test predicate clause(p6 <= 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 <= 3.1');
select predicate_clause_test('Test predicate clause(p6 >= 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 >= 3.1');
select predicate_clause_test('Test predicate clause(p6 <> 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 <> 3.1');
select predicate_clause_test('Test predicate clause(p6 != 3.1):', 'normal_02', 'dfs_predicate_02', 'p6 != 3.1');
select predicate_clause_test('Test predicate clause(p6 is null):', 'normal_02', 'dfs_predicate_02', 'p6 is null');
select predicate_clause_test('Test predicate clause(p6 is not null):', 'normal_02', 'dfs_predicate_02', 'p6 is not null');
select * from dfs_predicate_02 where p6 is not null order by 1;
select * from dfs_predicate_02 where p6 < '2.1' ;
select * from dfs_predicate_02 where p6 > '6.1' ;
--test p7 bpchar
select count(*) from ( select * from normal_02 where p7 = '5.1' MINUS ALL select * from dfs_predicate_02 where p7 = '5.1' );
select count(*) from ( select * from normal_02 where p7 < '5.1' MINUS ALL select * from dfs_predicate_02 where p7 < '5.1' );
select count(*) from ( select * from normal_02 where p7 > '5.1' MINUS ALL select * from dfs_predicate_02 where p7 > '5.1' );
select count(*) from ( select * from normal_02 where p7 <= '5.1' MINUS ALL select * from dfs_predicate_02 where p7 <= '5.1' );
select count(*) from ( select * from normal_02 where p7 >= '5.1' MINUS ALL select * from dfs_predicate_02 where p7 >= '5.1' );
select count(*) from ( select * from normal_02 where p7 <> '5.1' MINUS ALL select * from dfs_predicate_02 where p7 <> '5.1' );
select count(*) from ( select * from normal_02 where p7 != '5.1' MINUS ALL select * from dfs_predicate_02 where p7 != '5.1' );
select count(*) from ( select * from normal_02 where p7 is null MINUS ALL select * from dfs_predicate_02 where p7 is null );
select count(*) from ( select * from normal_02 where p7 is not null MINUS ALL select * from dfs_predicate_02 where p7 is not null );
select * from dfs_predicate_02 where p7 is null;
select * from dfs_predicate_02 where p7 < '3.1' ;
select * from dfs_predicate_02 where p7 > '7.1' ;
--test p8
select count(*) from ( select * from normal_02 where p8 = 'c' MINUS ALL select * from dfs_predicate_02 where p8 = 'c' );
select count(*) from ( select * from normal_02 where p8 < 'c' MINUS ALL select * from dfs_predicate_02 where p8 < 'c' );
select count(*) from ( select * from normal_02 where p8 > 'c' MINUS ALL select * from dfs_predicate_02 where p8 > 'c' );
select count(*) from ( select * from normal_02 where p8 <= 'c' MINUS ALL select * from dfs_predicate_02 where p8 <= 'c' );
select count(*) from ( select * from normal_02 where p8 >= 'c' MINUS ALL select * from dfs_predicate_02 where p8 >= 'c' );
select count(*) from ( select * from normal_02 where p8 <> 'c' MINUS ALL select * from dfs_predicate_02 where p8 <> 'c' );
select count(*) from ( select * from normal_02 where p8 != 'c' MINUS ALL select * from dfs_predicate_02 where p8 != 'c' );
select count(*) from ( select * from normal_02 where p8 is null MINUS ALL select * from dfs_predicate_02 where p8 is null );
select count(*) from ( select * from normal_02 where p8 is not null MINUS ALL select * from dfs_predicate_02 where p8 is not null );
select * from dfs_predicate_02 where p8 is null;
select * from dfs_predicate_02 where p8 < 'a' ;
select * from dfs_predicate_02 where p8 > 'e' ;
--currently, do not push down the parition column clause
--test p9 varchar
select count(*) from ( select p9 from normal_03 where p9 = '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 = '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 < '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 < '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 > '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 > '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 <= '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 <= '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 >= '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 >= '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 <> '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 <> '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 != '12-12-2016' MINUS ALL select p9 from dfs_predicate_03 where p9 != '12-12-2016' );
select count(*) from ( select p9 from normal_03 where p9 is null MINUS ALL select p9 from dfs_predicate_03 where p9 is null );
select count(*) from ( select p9 from normal_03 where p9 is not null MINUS ALL select p9 from dfs_predicate_03 where p9 is not null );
select * from dfs_predicate_03 where p9 is null;
select p9 from dfs_predicate_03 where p9 < '12-10-2016' ;
select p9 from dfs_predicate_03 where p9 > '12-16-2016' ;
-- test p11 text type
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 = '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 = '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 < '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 < '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 > '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 > '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 <= '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 <= '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 >= '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 >= '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 <> '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 <> '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 != '5.1' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 != '5.1' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 is null MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 is null );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p11 is not null MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 is not null );
select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 < '3.1' ;
select a, p9, p11, p12, non_p from dfs_predicate_03 where p11 > '7.1' ;
-- test p12 timestamp type
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 = '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 = '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 < '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 < '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 > '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 > '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 <= '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 <= '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 >= '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 >= '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 <> '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 <> '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 != '2003-04-13 04:05:06' MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 != '2003-04-13 04:05:06' );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 is null MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 is null );
select count(*) from ( select a, p9, p11, p12, non_p from normal_03 where p12 is not null MINUS ALL select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 is not null );
select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 is null;
select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 < '2003-04-10 04:05:06' ;
select a, p9, p11, p12, non_p from dfs_predicate_03 where p12 > '2003-04-14 04:05:06' ;
CREATE FOREIGN TABLE customer_address_ext
(
ca_address_sk integer ,
ca_address_id char(16) ,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20)
)
SERVER gsmpp_server
OPTIONS(location 'gsfs://10.185.180.195:9990/tpcds100x/customer_address.dat',
FORMAT 'TEXT' ,
DELIMITER '|',
encoding 'utf8',
mode 'Normal'
)
with customer_address_err
;
explain (verbose, costs off, nodes off) select count(*) from customer_address_ext;
drop table predicate_dfs1;
create table predicate_dfs1(a int, b decimal(1,1), c decimal(18,0), d decimal(18,18), e decimal(25,23), f decimal(25,25)) tablespace hdfs_ts;
set cstore_insert_mode='main';
insert into predicate_dfs1 values(1, 0, 0, 0, 0, 0);
insert into predicate_dfs1 values(1, 0.1, -1, 0.2, 3, -0.5);
explain (verbose, costs off, nodes off) select * from predicate_dfs1 where b <> 0;
select * from predicate_dfs1 where b <> 0;
explain (verbose, costs off, nodes off) select * from predicate_dfs1 where c = 0.0;
select * from predicate_dfs1 where c = 0.0;
explain (verbose, costs off, nodes off) select * from predicate_dfs1 where d = .0;
select * from predicate_dfs1 where d = .0;
explain (verbose, costs off, nodes off) select * from predicate_dfs1 where e != 0.0;
select * from predicate_dfs1 where e != 0.0;
explain (verbose, costs off, nodes off) select * from predicate_dfs1 where f != .0;
select * from predicate_dfs1 where f != .0;
drop table predicate_dfs1;
reset cstore_insert_mode;
create table predicate_dfs2(a int, b char(2), c varchar(2), d char, e varchar) tablespace hdfs_ts;
set cstore_insert_mode='main';
insert into predicate_dfs2 values(1, '12', '12', '1', '12');
select * from predicate_dfs2 where b > '1' and c > '1' and d > '0' and e > '1';
select * from predicate_dfs2 where b = '12' and c = '12' and d = '1' and e = '12';
select * from predicate_dfs2 where b < '123' and c < '123' and d < '123' and e < '123';
drop table predicate_dfs2;
reset cstore_insert_mode;
--predicate is self-inconsistent
set cstore_insert_mode=auto;
create table item_inventory_plan ( location_id number(35,0) not null , item_id number(20,5) not null )
tablespace hdfs_ts distribute by hash(item_id);
create table location ( location_id number(18,0) not null , district_cd varchar(50) null, location_mgr_associate_id number(18,18) null )
tablespace hdfs_ts distribute by hash(location_id);
INSERT INTO ITEM_INVENTORY_PLAN VALUES (1, 0.12);
INSERT INTO ITEM_INVENTORY_PLAN VALUES (1, 0.12);
INSERT INTO LOCATION VALUES (2, 'CHINA', 0.1238790);
INSERT INTO LOCATION VALUES (2, 'CHINA' , 0.2328787);
INSERT INTO LOCATION VALUES (5, 'NICAR' , -0.7887879 );
analyze ITEM_INVENTORY_PLAN;
analyze LOCATION;
explain SELECT dt.Column_005
FROM (
SELECT (loc_1.location_id + loc_1.location_mgr_associate_id) Column_005
FROM (SELECT loc.location_id
FROM item_inventory_plan iip
INNER JOIN location loc
ON iip.LOCATION_ID = loc.LOCATION_ID
AND iip.location_id >= 13
AND loc.location_id <= 8
AND iip.item_id >= '13.99000') dt1,
location loc_1
GROUP BY 1) dt;
drop table item_inventory_plan;
drop table location;
reset cstore_insert_mode;
--cross type test
set cstore_insert_mode=main;
create table t1(a char(6)) tablespace hdfs_ts;
create table t2(a char(8)) tablespace hdfs_ts;
create table t3(a varchar(6)) tablespace hdfs_ts;
create table t4(a varchar(8)) tablespace hdfs_ts;
create table t5(a varchar) tablespace hdfs_ts;
create table t6(a text) tablespace hdfs_ts;
create table t7(a int) tablespace hdfs_ts;
create table t8(a int2) tablespace hdfs_ts;
create table t9(a int8) tablespace hdfs_ts;
create table t10(b int, a float4) tablespace hdfs_ts;
create table t11(b int, a float8) tablespace hdfs_ts;
insert into t1 values('15');
insert into t2 values('15');
insert into t3 values('15');
insert into t4 values('15');
insert into t5 values('15');
insert into t6 values('15');
insert into t7 values(15);
insert into t8 values(15);
insert into t9 values(15);
insert into t10 values(1, 15);
insert into t11 values(1, 15);
analyze t1;
analyze t2;
analyze t3;
analyze t4;
analyze t5;
analyze t6;
analyze t7;
analyze t8;
analyze t9;
analyze t10;
analyze t11;
explain (verbose, costs off, nodes off) select * from t1,t2 where t1.a=t2.a;
select * from t1,t2 where t1.a=t2.a;
explain (verbose, costs off, nodes off) select * from t1 where a = (select a from t2);
select * from t1 where a = (select a from t2);
explain (verbose, costs off, nodes off) select * from t3 where a = (select a from t2);
select * from t3 where a = (select a from t2);
explain (verbose, costs off, nodes off) select * from t1 where a = (select a from t3);
select * from t1 where a = (select a from t3);
explain (verbose, costs off, nodes off) select * from t1 where a = (select a from t4);
select * from t1 where a = (select a from t4);
explain (verbose, costs off, nodes off) select * from t3 where a = (select a from t4);
select * from t3 where a = (select a from t4);
explain (verbose, costs off, nodes off) select * from t1 where a = (select a from t5);
select * from t1 where a = (select a from t5);
explain (verbose, costs off, nodes off) select * from t1 where a = (select a from t6);
select * from t1 where a = (select a from t6);
explain (verbose, costs off, nodes off) select * from t1 where a='15'::char(8);
select * from t1 where a='15'::char(8);
explain (verbose, costs off, nodes off) select * from t1 where a='15'::char(4);
select * from t1 where a='15'::char(4);
explain (verbose, costs off, nodes off) select * from t1 where a='15';
select * from t1 where a='15';
explain (verbose, costs off, nodes off) select * from t1 where a='15'::varchar(8);
select * from t1 where a='15'::varchar(8);
explain (verbose, costs off, nodes off) select * from t1 where a='15'::varchar(4);
select * from t1 where a='15'::varchar(4);
explain (verbose, costs off, nodes off) select * from t3 where a='15'::varchar(4);
select * from t3 where a='15'::varchar(4);
explain (verbose, costs off, nodes off) select * from t3 where a='15'::varchar(8);
select * from t3 where a='15'::varchar(8);
explain (verbose, costs off, nodes off) select * from t3 where a='15';
select * from t3 where a='15';
explain (verbose, costs off, nodes off) select * from t3 where a='15'::char(4);
select * from t3 where a='15'::char(4);
explain (verbose, costs off, nodes off) select * from t3 where a='15'::char(8);
select * from t3 where a='15'::char(8);
explain (verbose, costs off, nodes off) select * from t3 where a='15'::char;
select * from t3 where a='15'::char;
explain (verbose, costs off, nodes off) select * from t3 where a='15'::bpchar;
select * from t3 where a='15'::bpchar;
explain (verbose, costs off, nodes off) select * from t5 where a='15';
select * from t5 where a='15';
explain (verbose, costs off, nodes off) select * from t5 where a='15'::char(4);
select * from t5 where a='15'::char(4);
explain (verbose, costs off, nodes off) select * from t5 where a='15'::char(8);
select * from t5 where a='15'::char(8);
explain (verbose, costs off, nodes off) select * from t5 where a='15'::char;
select * from t5 where a='15'::char;
explain (verbose, costs off, nodes off) select * from t5 where a='15'::bpchar;
select * from t5 where a='15'::bpchar;
explain (verbose, costs off, nodes off) select * from t6 where a='15';
select * from t6 where a='15';
explain (verbose, costs off, nodes off) select * from t6 where a='15'::char(4);
select * from t6 where a='15'::char(4);
explain (verbose, costs off, nodes off) select * from t6 where a='15'::char(8);
select * from t6 where a='15'::char(8);
explain (verbose, costs off, nodes off) select * from t6 where a='15'::char;
select * from t6 where a='15'::char;
explain (verbose, costs off, nodes off) select * from t6 where a='15'::bpchar;
select * from t6 where a='15'::bpchar;
explain (verbose, costs off, nodes off) select * from t7 where a = 15::int2;
select * from t7 where a = 15::int2;
explain (verbose, costs off, nodes off) select * from t7 where a = 15::int8;
select * from t7 where a = 15::int8;
explain (verbose, costs off, nodes off) select * from t7 where a = 15;
select * from t7 where a = 15;
explain (verbose, costs off, nodes off) select * from t8 where a = 15::int4;
select * from t8 where a = 15::int4;
explain (verbose, costs off, nodes off) select * from t8 where a = 15::int8;
select * from t8 where a = 15::int8;
explain (verbose, costs off, nodes off) select * from t8 where a = 15;
select * from t8 where a = 15;
explain (verbose, costs off, nodes off) select * from t9 where a = 15::int4;
select * from t9 where a = 15::int4;
explain (verbose, costs off, nodes off) select * from t9 where a = 15::int2;
select * from t9 where a = 15::int2;
explain (verbose, costs off, nodes off) select * from t9 where a = 15;
select * from t9 where a = 15;
explain (verbose, costs off, nodes off) select * from t10 where a = 15::float8;
select * from t10 where a = 15::float8;
explain (verbose, costs off, nodes off) select * from t10 where a = 15;
select * from t10 where a = 15;
explain (verbose, costs off, nodes off) select * from t11 where a = 15::float4;
select * from t11 where a = 15::float4;
explain (verbose, costs off, nodes off) select * from t11 where a = 15;
select * from t11 where a = 15;
reset cstore_insert_mode;
drop foreign table customer_address_ext;
drop schema dfs_predicate_schema cascade;
drop server hdfs_server cascade;