Changeset: 5d0a187c6cca for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5d0a187c6cca Modified Files: sql/ChangeLog sql/backends/monet5/rel_bin.c sql/backends/monet5/sql_statement.c sql/backends/monet5/sql_statement.h sql/include/sql_relation.h sql/server/rel_dump.c sql/server/sql_parser.y sql/server/sql_scan.c Branch: default Log Message:
Implemented NULLS FIRST and NULLS LAST. diffs (274 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -1,6 +1,10 @@ # ChangeLog file for sql # This file is updated with Maddlog +* Thu Nov 8 2018 Sjoerd Mullender <[email protected]> +- Imlemented the NULLS FIRST and NULLS LAST option to ORDER BY. The + default is NULLS FIRST for ASC(ending) and NULLS LAST for DESC(ending). + * Thu Oct 18 2018 Pedro Ferreira <[email protected]> - Added possibility to rename SQL schemas, tables and columns with ALTER statements: diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c --- a/sql/backends/monet5/rel_bin.c +++ b/sql/backends/monet5/rel_bin.c @@ -2612,9 +2612,9 @@ rel2bin_project(backend *be, sql_rel *re /* handle constants */ orderbycolstmt = column(be, orderbycolstmt); if (!limit) { /* topn based on a single column */ - limit = stmt_limit(be, orderbycolstmt, NULL, NULL, stmt_atom_lng(be, 0), l, distinct, is_ascending(orderbycole), last, 1); + limit = stmt_limit(be, orderbycolstmt, NULL, NULL, stmt_atom_lng(be, 0), l, distinct, is_ascending(orderbycole), nulls_last(orderbycole), last, 1); } else { /* topn based on 2 columns */ - limit = stmt_limit(be, orderbycolstmt, lpiv, lgid, stmt_atom_lng(be, 0), l, distinct, is_ascending(orderbycole), last, 1); + limit = stmt_limit(be, orderbycolstmt, lpiv, lgid, stmt_atom_lng(be, 0), l, distinct, is_ascending(orderbycole), nulls_last(orderbycole), last, 1); } if (!limit) return NULL; @@ -2670,9 +2670,9 @@ rel2bin_project(backend *be, sql_rel *re break; } if (orderby_ids) - orderby = stmt_reorder(be, orderbycolstmt, is_ascending(orderbycole), orderby_ids, orderby_grp); + orderby = stmt_reorder(be, orderbycolstmt, is_ascending(orderbycole), nulls_last(orderbycole), orderby_ids, orderby_grp); else - orderby = stmt_order(be, orderbycolstmt, is_ascending(orderbycole)); + orderby = stmt_order(be, orderbycolstmt, is_ascending(orderbycole), nulls_last(orderbycole)); orderby_ids = stmt_result(be, orderby, 1); orderby_grp = stmt_result(be, orderby, 2); } @@ -2904,7 +2904,7 @@ rel2bin_topn(backend *be, sql_rel *rel, o = stmt_atom_lng(be, 0); sc = column(be, sc); - limit = stmt_limit(be, stmt_alias(be, sc, tname, cname), NULL, NULL, o, l, 0,0,0,0); + limit = stmt_limit(be, stmt_alias(be, sc, tname, cname), NULL, NULL, o, l, 0,0,0,0,0); for ( ; n; n = n->next) { stmt *sc = n->data; @@ -3168,9 +3168,9 @@ insert_check_ukey(backend *be, list *ins stmt *cs = list_fetch(inserts, c->c->colnr); if (orderby_grp) - orderby = stmt_reorder(be, cs, 1, orderby_ids, orderby_grp); + orderby = stmt_reorder(be, cs, 1, 0, orderby_ids, orderby_grp); else - orderby = stmt_order(be, cs, 1); + orderby = stmt_order(be, cs, 1, 0); orderby_ids = stmt_result(be, orderby, 1); orderby_grp = stmt_result(be, orderby, 2); } diff --git a/sql/backends/monet5/sql_statement.c b/sql/backends/monet5/sql_statement.c --- a/sql/backends/monet5/sql_statement.c +++ b/sql/backends/monet5/sql_statement.c @@ -1021,7 +1021,7 @@ stmt_result(backend *be, stmt *s, int nr /* limit maybe atom nil */ stmt * -stmt_limit(backend *be, stmt *col, stmt *piv, stmt *gid, stmt *offset, stmt *limit, int distinct, int dir, int last, int order) +stmt_limit(backend *be, stmt *col, stmt *piv, stmt *gid, stmt *offset, stmt *limit, int distinct, int dir, int nullslast, int last, int order) { MalBlkPtr mb = be->mb; InstrPtr q = NULL; @@ -1076,8 +1076,8 @@ stmt_limit(backend *be, stmt *col, stmt if (g) q = pushArgument(mb, q, g); q = pushArgument(mb, q, topn); - q = pushBit(mb, q, dir != 0); - q = pushBit(mb, q, dir == 0); /* nilslast */ + q = pushBit(mb, q, dir); + q = pushBit(mb, q, nullslast); q = pushBit(mb, q, distinct != 0); if (q == NULL) @@ -1187,9 +1187,8 @@ stmt_sample(backend *be, stmt *s, stmt * stmt * -stmt_order(backend *be, stmt *s, int direction) +stmt_order(backend *be, stmt *s, int direction, int nullslast) { - int reverse = (direction <= 0); MalBlkPtr mb = be->mb; InstrPtr q = NULL; @@ -1200,8 +1199,8 @@ stmt_order(backend *be, stmt *s, int dir q = pushReturn(mb, q, newTmpVariable(mb, TYPE_any)); q = pushReturn(mb, q, newTmpVariable(mb, TYPE_any)); q = pushArgument(mb, q, s->nr); - q = pushBit(mb, q, reverse); - q = pushBit(mb, q, reverse); /* nilslast */ + q = pushBit(mb, q, !direction); + q = pushBit(mb, q, nullslast); q = pushBit(mb, q, FALSE); if (q == NULL) return NULL; @@ -1226,9 +1225,8 @@ stmt_order(backend *be, stmt *s, int dir } stmt * -stmt_reorder(backend *be, stmt *s, int direction, stmt *orderby_ids, stmt *orderby_grp) +stmt_reorder(backend *be, stmt *s, int direction, int nullslast, stmt *orderby_ids, stmt *orderby_grp) { - int reverse = (direction <= 0); MalBlkPtr mb = be->mb; InstrPtr q = NULL; @@ -1241,8 +1239,8 @@ stmt_reorder(backend *be, stmt *s, int d q = pushArgument(mb, q, s->nr); q = pushArgument(mb, q, orderby_ids->nr); q = pushArgument(mb, q, orderby_grp->nr); - q = pushBit(mb, q, reverse); - q = pushBit(mb, q, reverse); /* nilslast */ + q = pushBit(mb, q, !direction); + q = pushBit(mb, q, nullslast); q = pushBit(mb, q, FALSE); if (q == NULL) return NULL; diff --git a/sql/backends/monet5/sql_statement.h b/sql/backends/monet5/sql_statement.h --- a/sql/backends/monet5/sql_statement.h +++ b/sql/backends/monet5/sql_statement.h @@ -209,10 +209,10 @@ extern stmt *stmt_result(backend *be, st * last: intermediate step or last step * order: is order important or not (firstn vs slice) */ -extern stmt *stmt_limit(backend *sa, stmt *c, stmt *piv, stmt *gid, stmt *offset, stmt *limit, int distinct, int dir, int last, int order); +extern stmt *stmt_limit(backend *sa, stmt *c, stmt *piv, stmt *gid, stmt *offset, stmt *limit, int distinct, int dir, int nullslast, int last, int order); extern stmt *stmt_sample(backend *be, stmt *s, stmt *sample, stmt *seed); -extern stmt *stmt_order(backend *be, stmt *s, int direction); -extern stmt *stmt_reorder(backend *be, stmt *s, int direction, stmt *orderby_ids, stmt *orderby_grp); +extern stmt *stmt_order(backend *be, stmt *s, int direction, int nullslast); +extern stmt *stmt_reorder(backend *be, stmt *s, int direction, int nullslast, stmt *orderby_ids, stmt *orderby_grp); extern stmt *stmt_convert(backend *sa, stmt *v, sql_subtype *from, sql_subtype *to); extern stmt *stmt_unop(backend *be, stmt *op1, sql_subfunc *op); diff --git a/sql/include/sql_relation.h b/sql/include/sql_relation.h --- a/sql/include/sql_relation.h +++ b/sql/include/sql_relation.h @@ -36,7 +36,7 @@ typedef struct expression { void *r; void *f; /* func's and aggr's */ /* e_cmp may have have 2 arguments */ - int flag; /* EXP_DISTINCT, NO_NIL, ASCENDING, cmp types */ + int flag; /* EXP_DISTINCT, NO_NIL, ASCENDING, NULLS_LAST, cmp types */ unsigned char card; /* card (0 truth value!) (1 atoms) @@ -67,6 +67,7 @@ typedef struct expression { #define ANTISEL 32 #define HAS_NO_NIL 64 #define EXP_INTERN 128 +#define NULLS_LAST 256 #define UPD_COMP 1 #define UPD_LOCKED 2 @@ -266,8 +267,10 @@ typedef enum operator_type { #define is_ascending(e) \ ((e->flag&ASCENDING)==ASCENDING) +#define nulls_last(e) \ + ((e->flag&NULLS_LAST)==NULLS_LAST) #define set_direction(e, dir) \ - e->flag |= (dir?ASCENDING:0) + e->flag |= ((dir&1)?ASCENDING:0) | ((dir&2)?NULLS_LAST:0) #define is_anti(e) \ ((e->flag&ANTISEL)==ANTISEL) diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c --- a/sql/server/rel_dump.c +++ b/sql/server/rel_dump.c @@ -1032,9 +1032,9 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re } /* [ ASC ] */ if (strncmp(r+*pos, "ASC", strlen("ASC")) == 0) { - (*pos)+= (int) strlen("NOT"); + (*pos)+= (int) strlen("ASC"); skipWS(r, pos); - set_direction(exp, ASCENDING); + set_direction(exp, 1); } /* [ NOT ] NULL */ if (strncmp(r+*pos, "NOT", strlen("NOT")) == 0) { 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 @@ -527,6 +527,7 @@ int yydebug=1; opt_grant_for opt_asc_desc + opt_nulls_first_last tz %right <sval> STRING @@ -609,7 +610,7 @@ SQLCODE SQLERROR UNDER WHENEVER %token TEMP TEMPORARY STREAM MERGE REMOTE REPLICA %token<sval> ASC DESC AUTHORIZATION -%token CHECK CONSTRAINT CREATE COMMENT +%token CHECK CONSTRAINT CREATE COMMENT NULLS FIRST LAST %token TYPE PROCEDURE FUNCTION sqlLOADER AGGREGATE RETURNS EXTERNAL sqlNAME DECLARE %token CALL LANGUAGE %token ANALYZE MINMAX SQL_EXPLAIN SQL_PLAN SQL_DEBUG SQL_TRACE PREP PREPARE EXEC EXECUTE @@ -3662,10 +3663,10 @@ sort_specification_list: ; ordering_spec: - scalar_exp opt_asc_desc + scalar_exp opt_asc_desc opt_nulls_first_last { dlist *l = L(); append_symbol(l, $1); - append_int(l, $2); + append_int(l, $2 | (($3 == -1 ? !$2 : $3) << 1)); $$ = _symbol_create_list(SQL_COLUMN, l ); } ; @@ -3676,6 +3677,12 @@ opt_asc_desc: | DESC { $$ = FALSE; } ; +opt_nulls_first_last: + /* empty */ { $$ = -1; } + | NULLS LAST { $$ = TRUE; } + | NULLS FIRST { $$ = FALSE; } + ; + predicate: comparison_predicate | between_predicate @@ -5656,6 +5663,9 @@ non_reserved_word: | COMMENT { $$ = sa_strdup(SA, "comment"); } | CLIENT { $$ = sa_strdup(SA, "client"); } | SERVER { $$ = sa_strdup(SA, "server"); } +| NULLS { $$ = sa_strdup(SA, "nulls"); } +| LAST { $$ = sa_strdup(SA, "last"); } +| FIRST { $$ = sa_strdup(SA, "first"); } ; name_commalist: diff --git a/sql/server/sql_scan.c b/sql/server/sql_scan.c --- a/sql/server/sql_scan.c +++ b/sql/server/sql_scan.c @@ -196,6 +196,7 @@ scanner_init_keywords(void) failed += keywords_insert("ACTION", ACTION); failed += keywords_insert("CASCADE", CASCADE); failed += keywords_insert("RESTRICT", RESTRICT); + failed += keywords_insert("FIRST", FIRST); failed += keywords_insert("GLOBAL", GLOBAL); failed += keywords_insert("GROUP", sqlGROUP); failed += keywords_insert("HAVING", HAVING); @@ -213,11 +214,13 @@ scanner_init_keywords(void) failed += keywords_insert("LIMIT", LIMIT); failed += keywords_insert("SAMPLE", SAMPLE); failed += keywords_insert("SEED", SEED); + failed += keywords_insert("LAST", LAST); failed += keywords_insert("LOCAL", LOCAL); failed += keywords_insert("LOCKED", LOCKED); failed += keywords_insert("NATURAL", NATURAL); failed += keywords_insert("NOT", NOT); failed += keywords_insert("NULL", sqlNULL); + failed += keywords_insert("NULLS", NULLS); failed += keywords_insert("OFFSET", OFFSET); failed += keywords_insert("ON", ON); failed += keywords_insert("OPTIONS", OPTIONS); _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
