Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦

背景

interval是用来表达时间间隔的数据类型，比如1年，或者1分钟，或者1天零多少小时分钟等。

postgres=# select interval 100 year 2 month 1 day 1:00:01.11; 

 interval 

------------------------------------

 100 years 2 mons 1 day 01:00:01.11

(1 row)

interval可以与时间，日期类型加减。

postgres=# select now()+interval 100 year 2 month 1 day 1:00:01.11; 

 ?column? 

-------------------------------

 2116-12-12 20:06:48.391422+08

(1 row)

interval的用法可参考

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

interval parser不同版本的差异

1. PostgreSQL 8.3以及以前的版本不能解释放在单引号外面的单位

$psql -h 127.0.0.1 -p 35432 -U digoal postgres

psql (8.3.23)

Type "help" for help.

postgres=# select now(), now()+interval 1 year, now()+interval 1 year;

 now | ?column? | ?column? 

-------------------------------+-------------------------------+-------------------------------

 2016-10-11 19:02:46.881375+08 | 2017-10-11 19:02:46.881375+08 | 2016-10-11 19:02:46.881375+08

(1 row)

postgres=# select interval 100 year; 

 interval 

----------

 00:00:00

(1 row)

2. 8.4以及以后的版本则支持放在外面的单位的写法。

psql (9.4.9)

Type "help" for help.


postgres=# select now(), now()+interval 1 year, now()+interval 1 year;

 now | ?column? | ?column? 

-------------------------------+-------------------------------+-------------------------------

 2016-10-11 19:08:29.365853+08 | 2017-10-11 19:08:29.365853+08 | 2017-10-11 19:08:29.365853+08

(1 row)

postgres=# select interval 100 year; 

 interval 

-----------

 100 years

(1 row)

postgres=# select interval 100 hour; 

 interval 

-----------

 100:00:00

(1 row)

patch在这里
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=70530c808bf8eaba2a41a28c9dc7b96dcc3b6c51

Adjust the parser to accept the typename syntax INTERVAL ... SECOND(n)

and the literal syntax INTERVAL string ... SECOND(n), as required by the

SQL standard. Our old syntax put (n) directly after INTERVAL, which was

a mistake, but will still be accepted for backward compatibility as well

as symmetry with the TIMESTAMP cases.

Change intervaltypmodout to show it in the specs way, too. (This could

potentially affect clients, if there are any that analyze the typmod of an

INTERVAL in any detail.)

Also fix interval input to handle min:sec.frac properly; I had overlooked

this case in my previous patch.

Document the use of the interval fields qualifier, which up to now we had

never mentioned in the docs. (I think the omission was intentional because

it didnt work per spec; but it does now, or at least close enough to be

credible.)

tpch的QUERY造句

tpch的dbgen产生的query用的是带单位的写法，导致没有出现加减。

涉及的SQL如下

10.explain.sql: and o_orderdate date 1993-04-01 + interval 3 month

12.explain.sql: and l_receiptdate date 1995-01-01 + interval 1 year

14.explain.sql: and l_shipdate date 1995-08-01 + interval 1 month

15.explain.sql: and l_shipdate date 1997-03-01 + interval 3 month

1.explain.sql: l_shipdate = date 1998-12-01 - interval 78 day

20.explain.sql: and l_shipdate date 1994-01-01 + interval 1 year

4.explain.sql: and o_orderdate date 1995-03-01 + interval 3 month

5.explain.sql: and o_orderdate date 1997-01-01 + interval 1 year

6.explain.sql: and l_shipdate date 1997-01-01 + interval 1 year

例子

-- using 1474112033 as a seed to the RNG


 sum(l_extendedprice) as sum_base_price,

 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

 avg(l_quantity) as avg_qty,

 avg(l_extendedprice) as avg_price,

 avg(l_discount) as avg_disc,

 count(*) as count_order

 lineitem

where

 l_shipdate = date 1998-12-01 - interval 78 day

group by

 l_returnflag,

 l_linestatus

order by

 l_returnflag,

 l_linestatus

LIMIT 1;

这些SQL直接影响了TPCH的测试结果。

请务必修正query后再执行。

interval 的 io函数

src/backend/utils/adt/timestamp.c

/* interval_in()

 * Convert a string to internal form.

 * External format(s):

 * Uses the generic date/time parsing and decoding routines.

Datum

interval_in(PG_FUNCTION_ARGS)

 char *str = PG_GETARG_CSTRING(0);

#ifdef NOT_USED

 Oid typelem = PG_GETARG_OID(1);

#endif

 int32 typmod = PG_GETARG_INT32(2);

 Interval *result;

 fsec_t fsec;

 struct pg_tm tt,

 *tm = 

 int dtype;

 int nf;

 int range;

 int dterr;

 char *field[MAXDATEFIELDS];

 int ftype[MAXDATEFIELDS];

 char workbuf[256];

 tm- tm_year = 0;

 tm- tm_mon = 0;

 tm- tm_mday = 0;

 tm- tm_hour = 0;

 tm- tm_min = 0;

 tm- tm_sec = 0;

 fsec = 0;

 if (typmod = 0)

 range = INTERVAL_RANGE(typmod);

 else

 range = INTERVAL_FULL_RANGE;

 dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,

 ftype, MAXDATEFIELDS, nf);

 if (dterr == 0)

 dterr = DecodeInterval(field, ftype, nf, range,

 dtype, tm, fsec);

 /* if those functions think its a bad format, try ISO8601 style */

 if (dterr == DTERR_BAD_FORMAT)

 dterr = DecodeISO8601Interval(str,

 dtype, tm, fsec);

 if (dterr != 0)

 if (dterr == DTERR_FIELD_OVERFLOW)

 dterr = DTERR_INTERVAL_OVERFLOW;

 DateTimeParseError(dterr, str, "interval");

 result = (Interval *) palloc(sizeof(Interval));

 switch (dtype)

 case DTK_DELTA:

 if (tm2interval(tm, fsec, result) != 0)

 ereport(ERROR,

 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

 errmsg("interval out of range")));

 break;

 case DTK_INVALID:

 ereport(ERROR,

 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

 errmsg("date/time value \"%s\" is no longer supported", str)));

 break;

 default:

 elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",

 dtype, str);

 AdjustIntervalForTypmod(result, typmod);

 PG_RETURN_INTERVAL_P(result);

/* interval_out()

 * Convert a time span to external form.

Datum

interval_out(PG_FUNCTION_ARGS)

 Interval *span = PG_GETARG_INTERVAL_P(0);

 char *result;

 struct pg_tm tt,

 *tm = 

 fsec_t fsec;

 char buf[MAXDATELEN + 1];

 if (interval2tm(*span, tm, fsec) != 0)

 elog(ERROR, "could not convert interval to tm");

 EncodeInterval(tm, fsec, IntervalStyle, buf);

 result = pstrdup(buf);

 PG_RETURN_CSTRING(result);

Count

Greenplum TPC-H测试 请参考如下 http://www.tpc.org/information/current_specifications.asp https://github.com/digoal/pg_tpch 下载tpch TPC-H V2.17.1 pdf Download D
区块链交易所搭建开发_平台_测试_系统智能合约ATOM代示例 Cosmos（ATOM）没有传统意义上的原生智能合约，因为它没有自己的图灵完备编程语言。 相反，Cosmos 使用区块链间通信 (IBC) 协议来允许不同区块链之间的通信和价值转移。 这允许开发人员构建跨越多个区块链的去中心化应用程序。