Changeset: eb347d3c0321 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=eb347d3c0321 Modified Files: ChangeLog clients/odbc/driver/ODBCUtil.c sql/ChangeLog sql/server/sql_parser.y sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.sql sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.stable.out sql/test/pg_regress/Tests/date.sql sql/test/pg_regress/Tests/time.sql sql/test/pg_regress/Tests/time.stable.err sql/test/pg_regress/Tests/timestamp.sql sql/test/pg_regress/Tests/timestamp.stable.err sql/test/pg_regress/Tests/timestamptz.sql sql/test/pg_regress/Tests/timestamptz.stable.err sql/test/pg_regress/Tests/timetz.sql sql/test/pg_regress/Tests/timetz.stable.err Branch: default Log Message:
Addendum to changeset https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1523b559564f Added the words quarter and week to the non_reserved_word list to avoid upgrade problems for users which have objects named quarter or week. This allows me to undo some changes made in pg_regress tests where column quarter or function calls to week or quarter were made. Also extended test extract_quarter_week_from_date.Bug-3831.sql with tests of new scalar function: quarter(x). Also updated ODBC driver mapping for scalar function quarter(), which can now map to quarter() directly instead of the workaround using month(). Also moved ChangeLog message to the prooer sql/ChangeLog as this is related only to sql part. diffs (truncated from 417 to 300 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,11 +1,3 @@ # ChangeLog file for devel # This file is updated with Maddlog -* Thu Jul 20 2017 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> -- Added support for extracting the quarter (number between 1 and 4) - of a date (or timestamp or timestamp with timezone) in SQL: - EXTRACT ( QUARTER FROM my_date_expr ). - Added support for extracting the week (number between 1 and 53) - of a date (or timestamp or timestamp with timezone) in SQL: - EXTRACT ( WEEK FROM my_date_expr ). - diff --git a/clients/odbc/driver/ODBCUtil.c b/clients/odbc/driver/ODBCUtil.c --- a/clients/odbc/driver/ODBCUtil.c +++ b/clients/odbc/driver/ODBCUtil.c @@ -331,13 +331,13 @@ static struct scalars { {"dayofmonth", 1, "\"dayofmonth\"(\1)", }, {"dayofweek", 1, "\"dayofweek\"(\1)", }, {"dayofyear", 1, "\"dayofyear\"(\1)", }, - {"extract", 1, "\"extract\"(\1)", }, /* include "FROM" in argument */ + {"extract", 1, "\"extract\"(\1)", }, /* include "X FROM " in argument */ {"hour", 1, "\"hour\"(\1)", }, {"minute", 1, "\"minute\"(\1)", }, {"month", 1, "\"month\"(\1)", }, {"monthname", 1, NULL, }, {"now", 0, "\"now\"()", }, - {"quarter", 1, "((\"month\"(\1) - 1) / 3 + 1)", }, + {"quarter", 1, "\"quarter\"(\1)", }, {"second", 1, "\"second\"(\1)", }, {"timestampadd", 3, NULL, }, {"timestampdiff", 3, NULL, }, @@ -357,7 +357,7 @@ static struct convert { { "SQL_BIGINT", "bigint", }, { "SQL_BINARY", "binary large object", }, { "SQL_BIT", "boolean", }, - { "SQL_CHAR", "character large object", }, + { "SQL_CHAR", "character", }, { "SQL_DATE", "date", }, { "SQL_DECIMAL", "decimal(18,7)", }, { "SQL_DOUBLE", "double", }, @@ -389,7 +389,7 @@ static struct convert { { "SQL_VARCHAR", "character varying", }, { "SQL_WCHAR", "character", }, { "SQL_WLONGVARCHAR", "character large object", }, - { "SQL_WVARCHAR", "character large object", }, + { "SQL_WVARCHAR", "character varying", }, { NULL, NULL, }, /* sentinel */ }; diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -6,3 +6,13 @@ In principle, all errors should now include a SQLSTATE error code (see the SQL standard). +* Thu Jul 20 2017 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> +- Added support for extracting the quarter (number between 1 and 4) + of a date or a timestamp or a timestamp with timezone in SQL: + EXTRACT ( QUARTER FROM my_date_expr ). + Added support for extracting the week (number between 1 and 53) + of a date or a timestamp or a timestamp with timezone in SQL: + EXTRACT ( WEEK FROM my_date_expr ). + Added support for scalar functions: quarter(date_expr), + quarter(timestamp_expr) and quarter(timestamptz_expr). + diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y --- a/sql/server/sql_parser.y +++ b/sql/server/sql_parser.y @@ -5277,6 +5277,8 @@ non_reserved_word: | TIME { $$ = sa_strdup(SA, "time"); } | TIMESTAMP { $$ = sa_strdup(SA, "timestamp"); } | INTERVAL { $$ = sa_strdup(SA, "interval"); } +| QUARTER { $$ = sa_strdup(SA, "quarter"); } +| WEEK { $$ = sa_strdup(SA, "week"); } | IMPRINTS { $$ = sa_strdup(SA, "imprints"); } | PREPARE { $$ = sa_strdup(SA, "prepare"); } diff --git a/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql b/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql --- a/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql +++ b/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql @@ -16,37 +16,37 @@ CREATE TABLE "time" ( "quarter" SMALLINT NOT NULL ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (100, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 1, 0, 2009040101, 1, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (101, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 2, 0, 2009040102, 2, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (102, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 3, 0, 2009040103, 3, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (103, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 4, 0, 2009040104, 4, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (104, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 5, 0, 2009040105, 5, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (105, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 6, 0, 2009040106, 6, 'OFF', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (106, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 7, 0, 2009040107, 7, 'ON', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (107, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 8, 0, 2009040108, 8, 'ON', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (108, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 9, 0, 2009040109, 9, 'ON', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (109, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 10, 0, 2009040110, 10, 'ON', 2009, 4, '2009-04', 2 ); -INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) VALUES (110, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 11, 0, 2009040111, 11, 'ON', 2009, 4, '2009-04', 2 ); diff --git a/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out b/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out --- a/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out +++ b/sql/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out @@ -40,47 +40,47 @@ Ready. # "yyyymm" varchar(7) NOT NULL, # "quarter" SMALLINT NOT NULL #); -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (100, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 1, 0, 2009040101, 1, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (101, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 2, 0, 2009040102, 2, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (102, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 3, 0, 2009040103, 3, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (103, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 4, 0, 2009040104, 4, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (104, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 5, 0, 2009040105, 5, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (105, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 6, 0, 2009040106, 6, 'OFF', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (106, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 7, 0, 2009040107, 7, 'ON', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (107, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 8, 0, 2009040108, 8, 'ON', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (108, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 9, 0, 2009040109, 9, 'ON', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (109, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 10, 0, 2009040110, 10, 'ON', 2009, 4, '2009-04', 2 ); [ 1 ] -#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, "quarter") +#INSERT INTO time( data_id, header_id, data_item, opr_date, opr_hr, opr_min, yyyymmddhh, interval_num, svalue, years, months, yyyymm, quarter) # VALUES # (110, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 11, 0, 2009040111, 11, 'ON', 2009, 4, '2009-04', 2 ); [ 1 ] diff --git a/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.sql b/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.sql --- a/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.sql +++ b/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.sql @@ -18,5 +18,17 @@ select dt, extract(week from dt) as dt_w select ts, extract(week from ts) as ts_wk from my_dates order by dt; select tstz, extract(week from tstz) as tstz_wk from my_dates order by dt; +select dt, "year"(dt) as dt_yr from my_dates order by dt; +select ts, "year"(ts) as ts_yr from my_dates order by dt; +select tstz, "year"(tstz) as tstz_yr from my_dates order by dt; + +select dt, quarter(dt) as dt_qrfrom from my_dates order by dt; +select ts, quarter(ts) as ts_qrfrom from my_dates order by dt; +select tstz, quarter(tstz) as tstz_qr from my_dates order by dt; + +select dt, week(dt) as dt_wk from my_dates order by dt; +select ts, week(ts) as ts_wk from my_dates order by dt; +select tstz, week(tstz) as tstz_wk from my_dates order by dt; + drop table my_dates; diff --git a/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.stable.out b/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.stable.out --- a/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.stable.out +++ b/sql/test/BugTracker-2017/Tests/extract_quarter_week_from_date.Bug-3831.stable.out @@ -165,6 +165,87 @@ Ready. [ 2015-12-14 14:12:12.120000+02:00, 51 ] [ 2016-07-14 14:12:12.120000+02:00, 28 ] [ 2017-06-14 14:12:12.120000+02:00, 24 ] +#select dt, "year"(dt) as dt_yr from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% dt, dt_yr # name +% date, int # type +% 10, 4 # length +[ 2014-01-14, 2014 ] +[ 2015-12-14, 2015 ] +[ 2016-07-14, 2016 ] +[ 2017-06-14, 2017 ] +#select ts, "year"(ts) as ts_yr from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% ts, ts_yr # name +% timestamp, int # type +% 26, 4 # length +[ 2014-01-14 12:12:12.120000, 2014 ] +[ 2015-12-14 12:12:12.120000, 2015 ] +[ 2016-07-14 12:12:12.120000, 2016 ] +[ 2017-06-14 12:12:12.120000, 2017 ] +#select tstz, "year"(tstz) as tstz_yr from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% tstz, tstz_yr # name +% timestamptz, int # type +% 32, 4 # length +[ 2014-01-14 14:12:12.120000+02:00, 2014 ] +[ 2015-12-14 14:12:12.120000+02:00, 2015 ] +[ 2016-07-14 14:12:12.120000+02:00, 2016 ] +[ 2017-06-14 14:12:12.120000+02:00, 2017 ] +#select dt, quarter(dt) as dt_qrfrom from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% dt, dt_qrfrom # name +% date, int # type +% 10, 1 # length +[ 2014-01-14, 1 ] +[ 2015-12-14, 4 ] +[ 2016-07-14, 3 ] +[ 2017-06-14, 2 ] +#select ts, quarter(ts) as ts_qrfrom from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% ts, ts_qrfrom # name +% timestamp, int # type +% 26, 1 # length +[ 2014-01-14 12:12:12.120000, 1 ] +[ 2015-12-14 12:12:12.120000, 4 ] +[ 2016-07-14 12:12:12.120000, 3 ] +[ 2017-06-14 12:12:12.120000, 2 ] +#select tstz, quarter(tstz) as tstz_qr from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% tstz, tstz_qr # name +% timestamptz, int # type +% 32, 1 # length +[ 2014-01-14 14:12:12.120000+02:00, 1 ] +[ 2015-12-14 14:12:12.120000+02:00, 4 ] +[ 2016-07-14 14:12:12.120000+02:00, 3 ] +[ 2017-06-14 14:12:12.120000+02:00, 2 ] +#select dt, week(dt) as dt_wk from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% dt, dt_wk # name +% date, int # type +% 10, 2 # length +[ 2014-01-14, 3 ] +[ 2015-12-14, 51 ] +[ 2016-07-14, 28 ] +[ 2017-06-14, 24 ] +#select ts, week(ts) as ts_wk from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% ts, ts_wk # name +% timestamp, int # type +% 26, 2 # length +[ 2014-01-14 12:12:12.120000, 3 ] +[ 2015-12-14 12:12:12.120000, 51 ] +[ 2016-07-14 12:12:12.120000, 28 ] +[ 2017-06-14 12:12:12.120000, 24 ] +#select tstz, week(tstz) as tstz_wk from my_dates order by dt; +% sys.my_dates, sys.L3 # table_name +% tstz, tstz_wk # name +% timestamptz, int # type +% 32, 2 # length _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
