/*
* This file is used to test the function of ExecVecResult()
*/
----
--- Create Table and Insert Data
----
create schema vector_result_engine;
set current_schema=vector_result_engine;
create table vector_result_engine.ROW_RESULT_TABLE_01
(
col_int int
,col_bint bigint
,col_serial int
,col_char char(25)
,col_vchar varchar(35)
,col_text text
,col_num numeric(10,4)
,col_decimal decimal
,col_float float
,col_date date
,col_time time
,col_timetz timetz
,col_interval interval
,col_tinterval tinterval
);
create table vector_result_engine.VECTOR_RESULT_TABLE_01
(
col_int int
,col_bint bigint
,col_serial int
,col_char char(25)
,col_vchar varchar(35)
,col_text text
,col_num numeric(10,4)
,col_decimal decimal
,col_float float
,col_date date
,col_time time
,col_timetz timetz
,col_interval interval
,col_tinterval tinterval
)with(orientation=column);
copy row_result_table_01 from '@abs_srcdir@/data/vec_result.data' DELIMITER as ',' NULL as '';
insert into vector_result_table_01 select * from row_result_table_01;
CREATE TABLE vector_result_engine.VECTOR_RESULT_TABLE_02(
a1 character varying(1000),
a2 integer,
a3 character varying(1000),
a4 integer,
a5 character varying(1000),
a6 integer,
a7 character varying(1000),
a8 integer,
a9 character varying(1000),
a10 integer
)
WITH (orientation=column)
PARTITION BY RANGE (a2)
(
PARTITION p1 VALUES LESS THAN (1),
PARTITION p50001 VALUES LESS THAN (50001)
);
create table vector_result_engine.VECTOR_RESULT_TABLE_03
(
a int
,b varchar(23)
)with(orientation=column);
create table vector_result_engine.VECTOR_RESULT_TABLE_04
(
a int
,b text
)with(orientation=column);
insert into VECTOR_RESULT_TABLE_03 values(1,'tianjian');
create table vector_result_engine.ROW_RESULT_TABLE_05
(
c_int integer
,c_smallint smallint
,c_bigint bigint
,c_decimal decimal
,c_numeric numeric
,c_real real
,c_double double precision
,c_serial int
,c_bigserial bigint
,c_money money
,c_character_varying character varying(1123)
,c_varchar varchar(16678)
,c_char char(14675)
,c_text text
,c_bytea bytea
,c_timestamp_without timestamp without time zone
,c_timestamp_with timestamp with time zone
,c_boolean boolean
,c_cidr cidr
,c_inet inet
,c_bit bit(20)
,c_bit_varying bit varying(20)
,c_oid oid
,c_character character(10)
,c_interval interval
,c_date date
,c_time_without time without time zone
,c_time_with time with time zone
,c_binary_integer binary_integer
,c_binary_double binary_double
,c_dec dec(18,9)
,c_numeric_1 numeric(19,9)
,c_varchar2 varchar2
);
create table vector_result_engine.VECTOR_RESULT_TABLE_05 with(orientation=column) as select * from vector_result_engine.ROW_RESULT_TABLE_05;
analyze vector_result_table_01;
analyze vector_result_table_02;
analyze vector_result_table_03;
analyze vector_result_table_04;
----
--- case 1: Basic Case
----
explain (verbose on, costs off) select col_serial from vector_result_table_01 where current_date>'2015-02-14' order by 1 limit 10;
QUERY PLAN
------------------------------------------------------------------------------
Row Adapter
Output: col_serial
-> Vector Limit
Output: col_serial
-> Vector Sort
Output: col_serial
Sort Key: vector_result_table_01.col_serial
-> CStore Scan on vector_result_engine.vector_result_table_01
Output: col_serial
(9 rows)
select col_serial from vector_result_table_01 where current_date>'2015-02-14' order by 1 limit 10;
col_serial
------------
10
20
30
40
50
60
70
80
90
100
(10 rows)
select col_time+'00:00:20' from vector_result_table_01 where current_date < '2010-02-14' order by 1 limit 5;
?column?
----------
(0 rows)
select 'aa' from vector_result_table_01 where current_date > '2015-02-14' and col_timetz = '08:12:36+08' limit 5;
?column?
----------
aa
aa
aa
aa
aa
(5 rows)
select ctid, * from vector_result_table_01 where col_text is NULL;
ctid | col_int | col_bint | col_serial | col_char | col_vchar | col_text | col_num | col_decimal | col_float | col_date | col_time | col_timetz | col_interval | col_tinterval
------+---------+----------+------------+----------+-----------+----------+---------+-------------+-----------+----------+----------+------------+--------------+---------------
(0 rows)
select col_timetz, ctid from vector_result_table_01 where current_date > '2015-02-14' and col_int < 5 order by 1, 2;
col_timetz | ctid
-------------+-------------
11:20:22+06 | (1001,868)
11:20:22+06 | (1001,7474)
11:20:22+06 | (1001,8295)
11:20:22+06 | (1001,8296)
(4 rows)
select 2 from vector_result_engine.vector_result_table_01 where col_int > 500 limit 10;
?column?
----------
2
2
2
2
2
2
2
2
2
2
(10 rows)
select count(*) from vector_result_engine.vector_result_table_01 t1 where exists (select t2.col_int from vector_result_engine.vector_result_table_01 t2 where col_int < 500);
count
-------
10000
(1 row)
\o vec_result_json.o
explain (analyze on, format json)select count(*) from vector_result_engine.vector_result_table_01 t1 where exists (select t2.col_int from vector_result_engine.vector_result_table_01 t2 where col_int < 500);
\o
\! rm vec_result_json.o
set enable_vector_engine=false;
select count(*) from vector_result_engine.vector_result_table_01 t1 where exists (select t2.col_int from vector_result_engine.vector_result_table_01 t2 where col_int < 10);
count
-------
10000
(1 row)
reset enable_vector_engine;
----
--- case 2: With NULL
----
INSERT INTO vector_result_engine.row_result_table_01 VALUES(25, 252, 2525, NULL,'result_bb','result_MPPDBSZ',0.222,5.67,6.789,'2015-09-02',NULL,'08:12:36+08','1 day 11:24:56',NULL);
INSERT INTO vector_result_engine.row_result_table_01 VALUES(NULL,NULL,212525,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
delete from vector_result_table_01;
insert into vector_result_table_01 select * from row_result_table_01;
select *,ctid from vector_result_table_01 where col_vchar is NULL order by 1, 2, 3;
col_int | col_bint | col_serial | col_char | col_vchar | col_text | col_num | col_decimal | col_float | col_date | col_time | col_timetz | col_interval | col_tinterval | ctid
---------+----------+------------+----------+-----------+----------+---------+-------------+-----------+----------+----------+------------+--------------+---------------+--------------
| | 212525 | | | | | | | | | | | | (1002,10002)
(1 row)
select col_date from vector_result_table_01 where current_date>'2015-02-14' and col_char is NULL order by 1;
col_date
--------------------------
Wed Sep 02 00:00:00 2015
(2 rows)
----
--- case 3: Function Case
----
CREATE FUNCTION vec_result_func(int, bigint) RETURNS bigint
AS 'select count(*) from vector_result_table_01 where col_int<$1 and col_bint<$2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
select * from vec_result_func(5,500);
vec_result_func
-----------------
4
(1 row)
drop function vec_result_func;
CREATE FUNCTION vec_result_func(int, bigint) RETURNS bigint
AS 'select count(*) from vector_result_table_01 where col_int<$1'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;;
select * from vec_result_func(5,500);
vec_result_func
-----------------
4
(1 row)
drop function vec_result_func;
----
--- case 4: With Partition
----
SELECT a1, a2 FROM vector_result_table_02 WHERE a9='da' AND a9=' l' AND current_date>'2015-02-14' ORDER BY a1, a2;
a1 | a2
----+----
(0 rows)
----
--- case 5: coerce transformation
----
insert into vector_result_table_04 select * from vector_result_table_03;
select * from vector_result_table_03;
a | b
---+----------
1 | tianjian
(1 row)
select * from vector_result_table_04;
a | b
---+----------
1 | tianjian
(1 row)
----
--- case 6: Test Vtimstamp_part
----
explain (costs off, verbose on) select distinct date_trunc('microsecon',col_date), date_trunc('millisecon',col_date) from vector_result_table_01 order by 1, 2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
Output: (date_trunc('microsecon'::text, col_date)), (date_trunc('millisecon'::text, col_date))
-> Vector Sort
Output: (date_trunc('microsecon'::text, col_date)), (date_trunc('millisecon'::text, col_date))
Sort Key: (date_trunc('microsecon'::text, vector_result_table_01.col_date)), (date_trunc('millisecon'::text, vector_result_table_01.col_date))
-> Vector Sonic Hash Aggregate
Output: (date_trunc('microsecon'::text, col_date)), (date_trunc('millisecon'::text, col_date))
Group By Key: date_trunc('microsecon'::text, vector_result_table_01.col_date), date_trunc('millisecon'::text, vector_result_table_01.col_date)
-> CStore Scan on vector_result_engine.vector_result_table_01
Output: date_trunc('microsecon'::text, col_date), date_trunc('millisecon'::text, col_date)
(10 rows)
select distinct date_trunc('microsecon',col_date), date_trunc('millisecon',col_date) from vector_result_table_01 order by 1, 2;
date_trunc | date_trunc
--------------------------+--------------------------
Wed Dec 24 00:00:00 1986 | Wed Dec 24 00:00:00 1986
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
Tue Jun 02 00:00:00 2015 | Tue Jun 02 00:00:00 2015
Wed Sep 02 00:00:00 2015 | Wed Sep 02 00:00:00 2015
|
(5 rows)
--select distinct date_part('seconds',col_timetz), date_part('min',col_timetz), date_part('hours',col_timetz) from vector_result_table_01 order by 1,2,3;
select distinct date_trunc('months',col_date), date_trunc('qtr',col_date), date_trunc('days',col_date) from vector_result_table_01 order by 1,2 ,3;
date_trunc | date_trunc | date_trunc
--------------------------+--------------------------+--------------------------
Mon Dec 01 00:00:00 1986 | Wed Oct 01 00:00:00 1986 | Wed Dec 24 00:00:00 1986
Sat Jun 01 00:00:00 1996 | Mon Apr 01 00:00:00 1996 | Sat Jun 08 00:00:00 1996
Mon Jun 01 00:00:00 2015 | Wed Apr 01 00:00:00 2015 | Tue Jun 02 00:00:00 2015
Tue Sep 01 00:00:00 2015 | Wed Jul 01 00:00:00 2015 | Wed Sep 02 00:00:00 2015
| |
(5 rows)
select distinct date_trunc('decades',col_date), date_trunc('weeks',col_date), date_trunc('years',col_date) from vector_result_table_01 order by 1,2,3;
date_trunc | date_trunc | date_trunc
--------------------------+--------------------------+--------------------------
Tue Jan 01 00:00:00 1980 | Mon Dec 22 00:00:00 1986 | Wed Jan 01 00:00:00 1986
Mon Jan 01 00:00:00 1990 | Mon Jun 03 00:00:00 1996 | Mon Jan 01 00:00:00 1996
Fri Jan 01 00:00:00 2010 | Mon Jun 01 00:00:00 2015 | Thu Jan 01 00:00:00 2015
Fri Jan 01 00:00:00 2010 | Mon Aug 31 00:00:00 2015 | Thu Jan 01 00:00:00 2015
| |
(5 rows)
select distinct date_trunc('millennia',col_date),date_trunc('centuries',col_date) from vector_result_table_01 order by 1,2;
date_trunc | date_trunc
--------------------------+--------------------------
Thu Jan 01 00:00:00 1001 | Tue Jan 01 00:00:00 1901
Mon Jan 01 00:00:00 2001 | Mon Jan 01 00:00:00 2001
|
(3 rows)
select distinct date_trunc('hours',col_time), date_trunc('minute',col_interval) from vector_result_table_01 order by 1, 2;
date_trunc | date_trunc
------------+---------------------------
@ 2 hours | @ 4 days 13 hours 24 mins
@ 8 hours | @ 1 day 11 hours 24 mins
@ 11 hours | @ 2 days 13 hours 24 mins
| @ 1 day 11 hours 24 mins
|
(5 rows)
--select distinct date_part('timezone_h',col_timetz) from vector_result_table_01 order by 1;
--select distinct date_part('timezone_m',col_timetz) from vector_result_table_01 order by 1;
--select distinct date_part('timezone',col_timetz) from vector_result_table_01 order by 1;
select date_trunc('microsecon',col_date), date_trunc('millisecon',col_date) from vector_result_table_01 where col_num > 1998 and col_float < 2015 order by 1,2;
date_trunc | date_trunc
--------------------------+--------------------------
Wed Dec 24 00:00:00 1986 | Wed Dec 24 00:00:00 1986
Wed Dec 24 00:00:00 1986 | Wed Dec 24 00:00:00 1986
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
Sat Jun 08 10:11:15 1996 | Sat Jun 08 10:11:15 1996
(7 rows)
--select distinct date_part('timezone_h',col_timetz) from vector_result_table_01 where col_char > 'aa' and col_timetz < '11:20:22+06' order by 1;
----
--- Test table_skewness function
----
create table test(id int);
--查询集群使用率
--select table_skewness('test');
drop table test;
---test vtextne
create table promo_type
(
promo_type_name varchar(30) null ,
promo_type_id number(18,10) not null
)with (orientation=column);
INSERT INTO PROMO_TYPE VALUES (NULL, 1);
INSERT INTO PROMO_TYPE VALUES ('B' , 2);
INSERT INTO PROMO_TYPE VALUES (' ' , 3);
INSERT INTO PROMO_TYPE VALUES (' D' , 4);
INSERT INTO PROMO_TYPE VALUES (NULL, 5);
INSERT INTO PROMO_TYPE VALUES ('F' , 6);
INSERT INTO PROMO_TYPE VALUES ('G ' , 7);
SELECT 1
FROM promo_type t1 INNER JOIN promo_type t2
ON NOT EXISTS
(SELECT prt.promo_type_id Column_008,
(CASE
WHEN ((CASE
WHEN (((CASE
WHEN (84 < 58) THEN ('w')
END)) NOT IN ((CASE WHEN 84 < 58 THEN ('w') END),'X')) THEN ('c')
END) BETWEEN 48 AND 33) THEN 9
END) Column_009,
GROUPING(Column_008) Column_011,
GROUPING(Column_009) Column_012
FROM promo_type prt
GROUP BY GROUPING SETS(Column_008, Column_009));
?column?
----------
(0 rows)
drop table if exists region cascade;
NOTICE: table "region" does not exist, skipping
create table region
(
region_cd varchar(50) not null ,
region_name varchar(100) not null ,
division_cd varchar(50) not null ,
REGION_MGR_ASSOCIATE_ID number(18,9)
)with (orientation=column);
drop table if exists job_classification cascade;
NOTICE: table "job_classification" does not exist, skipping
create table job_classification
(
job_classification_cd varchar(50) not null ,
job_classification_desc varchar(250) null
)with (orientation=column);
drop table if exists location_type cascade;
NOTICE: table "location_type" does not exist, skipping
create table location_type
(
location_type_cd varchar(50) not null ,
location_type_desc varchar(250) not null
)with (orientation=column);
explain (costs off, verbose on) WITH WITH_345 AS MATERIALIZED (SELECT Table_037.location_type_desc Column_024
FROM (SELECT 1 Column_017
FROM region Table_010 RIGHT OUTER JOIN job_classification Table_030 ON FALSE) Table_036,
location_type Table_037 )
SELECT WITH_345.Column_024 Column_026
FROM WITH_345
GROUP BY ROLLUP(Column_026);
QUERY PLAN
--------------------------------------------------------------------------------------------
GroupAggregate
Output: with_345.column_024
Group By Key: with_345.column_024
Group By Key: ()
CTE with_345
-> Row Adapter
Output: table_037.location_type_desc
-> Vector Nest Loop
Output: table_037.location_type_desc
-> Vector Nest Loop Left Join
Output: ('Dummy')
Join Filter: false
-> CStore Scan on vector_result_engine.job_classification table_030
Output: 'Dummy'
-> Vector Adapter
Output: (NULL::integer)
-> Result
Output: NULL::integer
One-Time Filter: false
-> Vector Materialize
Output: table_037.location_type_desc
-> CStore Scan on vector_result_engine.location_type table_037
Output: table_037.location_type_desc
-> Sort
Output: with_345.column_024
Sort Key: with_345.column_024
-> CTE Scan on with_345
Output: with_345.column_024
(28 rows)
explain (costs off, verbose on) WITH WITH_345 AS (SELECT Table_037.location_type_desc Column_024
FROM (SELECT 1 Column_017
FROM region Table_010 RIGHT OUTER JOIN job_classification Table_030 ON FALSE) Table_036,
location_type Table_037 )
SELECT WITH_345.Column_024 Column_026
FROM WITH_345
GROUP BY ROLLUP(Column_026);
QUERY PLAN
------------------------------------------------------------------------------------------------
Row Adapter
Output: table_037.location_type_desc
-> Vector Sort Aggregate
Output: table_037.location_type_desc
Group By Key: table_037.location_type_desc
Group By Key: ()
-> Vector Sort
Output: table_037.location_type_desc
Sort Key: table_037.location_type_desc
-> Vector Nest Loop
Output: table_037.location_type_desc
-> Vector Nest Loop Left Join
Output: ('Dummy')
Join Filter: false
-> CStore Scan on vector_result_engine.job_classification table_030
Output: 'Dummy'
-> Vector Adapter
Output: (NULL::integer)
-> Result
Output: NULL::integer
One-Time Filter: false
-> Vector Materialize
Output: table_037.location_type_desc
-> CStore Scan on vector_result_engine.location_type table_037
Output: table_037.location_type_desc
(25 rows)
----
--- Clean Table and Resource
----
drop schema vector_result_engine cascade;
NOTICE: drop cascades to 11 other objects
DETAIL: drop cascades to table row_result_table_01
drop cascades to table vector_result_table_01
drop cascades to table vector_result_table_02
drop cascades to table vector_result_table_03
drop cascades to table vector_result_table_04
drop cascades to table row_result_table_05
drop cascades to table vector_result_table_05
drop cascades to table promo_type
drop cascades to table region
drop cascades to table job_classification
drop cascades to table location_type
