Greenplum,HAWQ interval parser帶來的問題 - TPCH 測試注意啦

作者

digoal

日期

2016-10-11

標簽

Greenplum , PostgreSQL , interval , parser

---

背景

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 spec's 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 didn't 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 RNGselectl_returnflag,l_linestatus,sum(l_quantity) as sum_qty,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 fromlineitem wherel_shipdate <= date '1998-12-01' - interval '78' day group byl_returnflag,l_linestatus order byl_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_USEDOid typelem = PG_GETARG_OID(1); #endifint32 typmod = PG_GETARG_INT32(2);Interval *result;fsec_t fsec;struct pg_tm tt,*tm = &tt;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);elserange = 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 it's 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 = &tt;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,HAWQ interval parser带来的问题 - TPCH 测试注意啦的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。