Changeset: 81fd9cd9aaa5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=81fd9cd9aaa5
Modified Files:
sql/backends/monet5/sql_statement.c
sql/server/rel_optimizer.c
sql/server/sql_atom.c
sql/server/sql_atom.h
sql/server/sql_semantic.c
sql/test/SQLancer/Tests/sqlancer03.sql
sql/test/SQLancer/Tests/sqlancer03.stable.out
sql/test/pg_regress/Tests/timestamp.stable.out
sql/test/pg_regress/Tests/timestamptz.stable.out
Branch: oscar
Log Message:
Merged with Jun2020
diffs (truncated from 447 to 300 lines):
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
@@ -1501,6 +1501,8 @@ stmt_uselect(backend *be, stmt *op1, stm
q = pushArgument(mb, q, l);
if (sub && !op1->cand)
q = pushArgument(mb, q, sub->nr);
+ else
+ sub = NULL;
q = pushArgument(mb, q, r);
q = pushArgument(mb, q, r);
q = pushBit(mb, q, TRUE);
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -1740,6 +1740,11 @@ rel_simplify_count_fk_join(mvc *sql, sql
if (oce->l) /* we only handle COUNT(*) */
return r;
+ /* primary side must be a full table */
+ if ((fk_left && (!is_left(r->op) && !is_full(r->op)) &&
!is_basetable(rr->op)) ||
+ (!fk_left && (!is_right(r->op) && !is_full(r->op)) &&
!is_basetable(rl->op)))
+ return r;
+
if (fk_left && is_join(rl->op) && !rel_is_ref(rl)) {
rl = rel_simplify_count_fk_join(sql, rl, gexps, changes);
r->l = rl;
@@ -1748,10 +1753,6 @@ rel_simplify_count_fk_join(mvc *sql, sql
rr = rel_simplify_count_fk_join(sql, rr, gexps, changes);
r->r = rr;
}
- /* primary side must be a full table */
- if ((fk_left && (!is_left(r->op) && !is_full(r->op)) &&
!is_basetable(rr->op)) ||
- (!fk_left && (!is_right(r->op) && !is_full(r->op)) &&
!is_basetable(rl->op)))
- return r;
(*changes)++;
/* rewrite, ie remove pkey side */
diff --git a/sql/server/sql_atom.c b/sql/server/sql_atom.c
--- a/sql/server/sql_atom.c
+++ b/sql/server/sql_atom.c
@@ -429,7 +429,7 @@ atom2string(sql_allocator *sa, atom *a)
}
char *
-atom2sql(atom *a)
+atom2sql(atom *a, int timezone)
{
sql_class ec = a->tpe.type->eclass;
char buf[BUFSIZ];
@@ -567,26 +567,101 @@ atom2sql(atom *a)
sprintf(buf, "%f", a->data.val.fval);
break;
case EC_TIME:
+ case EC_TIME_TZ:
case EC_DATE:
case EC_TIMESTAMP:
- if (a->data.vtype == TYPE_str) {
- char *val1 = sql_escape_str(a->tpe.type->sqlname),
*val2 = sql_escape_str(a->data.val.sval), *res;
+ case EC_TIMESTAMP_TZ: {
+ char val1[64], sbuf[64], *val2 = sbuf, *res;
+ size_t len = sizeof(sbuf);
- if (!val1 || !val2) {
- c_delete(val1);
- c_delete(val2);
- return NULL;
+ switch (ec) {
+ case EC_TIME:
+ case EC_TIME_TZ:
+ case EC_TIMESTAMP:
+ case EC_TIMESTAMP_TZ: {
+ char *n = stpcpy(val1, (ec == EC_TIME || ec ==
EC_TIME_TZ) ? "TIME" : "TIMESTAMP");
+ if (a->tpe.digits) {
+ char str[16];
+ sprintf(str, "%u", a->tpe.digits);
+ n = stpcpy(stpcpy(stpcpy(n, " ("), str), ")");
}
+ if (ec == EC_TIME_TZ || ec == EC_TIMESTAMP_TZ)
+ stpcpy(n, " WITH TIME ZONE");
+ } break;
+ case EC_DATE:
+ strcpy(val1, "DATE");
+ break;
+ default:
+ assert(0);
+ }
+
+ switch (ec) {
+ case EC_TIME:
+ case EC_TIME_TZ: {
+ daytime dt = a->data.val.lval;
+ int digits = a->tpe.digits ? a->tpe.digits - 1 : 0;
+ char *s = val2;
+ ssize_t lens;
+
+ if (ec == EC_TIME_TZ)
+ dt = daytime_add_usec_modulo(dt, timezone *
1000);
+ if ((lens = daytime_precision_tostr(&s, &len, dt,
digits, true)) < 0)
+ assert(0);
+
+ if (ec == EC_TIME_TZ) {
+ lng timezone_hours = llabs(timezone / 60000);
+ char *end = sbuf + sizeof(sbuf) - 1;
- if ((res = NEW_ARRAY(char, strlen(val1) + strlen(val2)
+ 4)))
- stpcpy(stpcpy(stpcpy(stpcpy(res, val1)," '"),
val2), "'");
- c_delete(val1);
- c_delete(val2);
- return res;
- } else {
- snprintf(buf, BUFSIZ, "atom2sql(TYPE_%d) not
implemented", a->data.vtype);
+ s += lens;
+ snprintf(s, end - s, "%c%02d:%02d", (timezone
>= 0) ? '+' : '-', (int) (timezone_hours / 60), (int) (timezone_hours % 60));
+ }
+ } break;
+ case EC_DATE: {
+ date dt = a->data.val.ival;
+ if (date_tostr(&val2, &len, &dt, false) < 0)
+ assert(0);
+ } break;
+ case EC_TIMESTAMP:
+ case EC_TIMESTAMP_TZ: {
+ timestamp ts = a->data.val.lval;
+ int digits = a->tpe.digits ? a->tpe.digits - 1 : 0;
+ char *s = val2;
+ size_t nlen;
+ ssize_t lens;
+ date days;
+ daytime usecs;
+
+ if (ec == EC_TIMESTAMP_TZ)
+ ts = timestamp_add_usec(ts, timezone * 1000);
+ days = timestamp_date(ts);
+ if ((lens = date_tostr(&s, &len, &days, true)) < 0)
+ assert(0);
+
+ s += lens;
+ *s++ = ' ';
+ nlen = len - lens - 1;
+ assert(nlen < len);
+
+ usecs = timestamp_daytime(ts);
+ if ((lens = daytime_precision_tostr(&s, &nlen, usecs,
digits, true)) < 0)
+ assert(0);
+
+ if (ec == EC_TIMESTAMP_TZ) {
+ lng timezone_hours = llabs(timezone / 60000);
+ char *end = sbuf + sizeof(sbuf) - 1;
+
+ s += lens;
+ snprintf(s, end - s, "%c%02d:%02d", (timezone
>= 0) ? '+' : '-', (int) (timezone_hours / 60), (int) (timezone_hours % 60));
+ }
+ } break;
+ default:
+ assert(0);
}
- break;
+
+ if ((res = NEW_ARRAY(char, strlen(val1) + strlen(val2) + 4)))
+ stpcpy(stpcpy(stpcpy(stpcpy(res, val1)," '"), val2),
"'");
+ return res;
+ } break;
default:
snprintf(buf, BUFSIZ, "atom2sql(TYPE_%d) not implemented",
a->data.vtype);
}
diff --git a/sql/server/sql_atom.h b/sql/server/sql_atom.h
--- a/sql/server/sql_atom.h
+++ b/sql/server/sql_atom.h
@@ -49,7 +49,7 @@ extern atom *atom_dup( sql_allocator *sa
extern int atom_cast(sql_allocator *sa, atom *a, sql_subtype *tp);
extern char *atom2string(sql_allocator *sa, atom *a);
-extern char *atom2sql(atom *a);
+extern char *atom2sql(atom *a, int timezone);
extern sql_subtype *atom_type(atom *a);
extern void atom_set_type(atom *a, sql_subtype *t);
diff --git a/sql/server/sql_semantic.c b/sql/server/sql_semantic.c
--- a/sql/server/sql_semantic.c
+++ b/sql/server/sql_semantic.c
@@ -431,7 +431,7 @@ symbol2string(mvc *sql, symbol *se, int
case SQL_ATOM:{
AtomNode *an = (AtomNode *) se;
if (an && an->a)
- return atom2sql(an->a);
+ return atom2sql(an->a, sql->timezone);
else
return _STRDUP("NULL");
}
@@ -453,7 +453,7 @@ symbol2string(mvc *sql, symbol *se, int
assert(l->h->type != type_lng);
if (dlist_length(l) == 1 && l->h->type == type_int) {
atom *a = sql_bind_arg(sql, l->h->data.i_val);
- return atom2sql(a);
+ return atom2sql(a, sql->timezone);
} else if (expression && dlist_length(l) == 1 && l->h->type ==
type_string) {
/* when compiling an expression, a column of a table
might be present in the symbol, so we need this case */
const char *op = l->h->data.sval;
diff --git a/sql/test/SQLancer/Tests/sqlancer03.sql
b/sql/test/SQLancer/Tests/sqlancer03.sql
--- a/sql/test/SQLancer/Tests/sqlancer03.sql
+++ b/sql/test/SQLancer/Tests/sqlancer03.sql
@@ -153,6 +153,67 @@ SELECT 1 FROM t0 WHERE NULL IN (DATE '19
SELECT ALL DATE '1970-01-01', CAST(COUNT(ALL TIMESTAMP '1970-01-01 01:06:52')
AS INT) FROM t0 WHERE (NULL) NOT IN (DATE '1970-01-01') GROUP BY ((CAST(0.6 AS
STRING))||(0.75)), INTERVAL '-13' SECOND;
ROLLBACK;
+START TRANSACTION;
+create table t0 (a time default time '10:15:32', b date default date
'2010-01-31', c timestamp default timestamp '1980-11-04 08:49:25');
+insert into t0;
+insert into t0 values (default, default, default);
+select a,b,c from t0;
+ROLLBACK;
+
+START TRANSACTION;
+CREATE TABLE "sys"."t0" (
+ "c0" TIME,
+ "c1" CHARACTER LARGE OBJECT NOT NULL,
+ CONSTRAINT "t0_c1_pkey" PRIMARY KEY ("c1"),
+ CONSTRAINT "t0_c1_unique" UNIQUE ("c1")
+);
+COPY 14 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+19:29:38 ""
+05:51:00 "#B"
+NULL ".n"
+NULL "0.0"
+20:29:28 "-601098762"
+20:59:23 ")TD)Dnö"
+22:09:19 "aaJ즳#B!sJNG㡩"
+09:10:09 "W"
+23:43:33 "k"
+15:43:27 "-0.0"
+20:02:19 "0.7126186870446843"
+05:23:53 "1498390845"
+00:17:15 "{"
+06:51:52 "41230238"
+
+SELECT t0.c0 FROM t0 WHERE ((t0.c0)<>(t0.c0));
+ --empty
+SELECT t0.c0 FROM t0 WHERE NOT (((t0.c0)<>(t0.c0)));
+ --12 tuples (all rows except ones with c0 null)
+SELECT t0.c0 FROM t0 WHERE (((t0.c0)<>(t0.c0))) IS NULL;
+ --NULL
+ --NULL
+ROLLBACK;
+
+START TRANSACTION; -- Bug 6926
+CREATE TABLE t0(c0 TIME, c1 VARCHAR(1000) DEFAULT (upper(CAST((('')||(DATE
'1970-01-19')) AS VARCHAR(152)))) PRIMARY KEY UNIQUE NULL);
+INSERT INTO t0(c0, c1) VALUES(TIME '19:29:38', '');
+INSERT INTO t0(c1, c0) VALUES(CAST(TIME '06:50:34' AS VARCHAR(256)), TIME
'01:39:55');
+INSERT INTO t0(c1, c0) VALUES(CAST(0.6 AS VARCHAR(411)), TIME '22:07:22'),
('#B', TIME '05:51:00'), (((((CAST('434' AS VARCHAR(849)))||(0.4)))||(TIME
'04:05:28')), TIME '06:50:34');
+INSERT INTO t0(c1) VALUES(CAST(INTERVAL '42' SECOND AS VARCHAR(102)));
+INSERT INTO t0(c0, c1) VALUES(TIME '16:29:13', 'J'), (TIME '05:57:44',
'49䄎y_}');
+INSERT INTO t0(c1, c0) VALUES('2', TIME '07:15:50'), (CAST(TIME '16:29:13' AS
VARCHAR(1000)), TIME '03:00:56');
+INSERT INTO t0(c1) VALUES('O큑LSGAM||Z赐'), ('.n');
+INSERT INTO t0(c1, c0) VALUES('c蠷웵', TIME '04:59:13');
+INSERT INTO t0(c1) VALUES('0.0');
+INSERT INTO t0(c0, c1) VALUES(TIME '20:29:28', '-343'), (TIME '01:19:51',
'j'), (TIME '11:37:23', ((((((((CAST(DATE '1970-01-23' AS
VARCHAR(849)))||(INTERVAL '23' MONTH)))||(INTERVAL '-535'
SECOND)))||(lower((('鮥*Qß4')||(DATE '1970-01-01'))))))||(0.4)));
+INSERT INTO t0(c0, c1) VALUES(TIME '02:38:14', lower(CAST(INTERVAL '342'
SECOND AS VARCHAR(1000)))), (TIME '23:43:33', '64');
+DELETE FROM t0 WHERE ((((1 IS NOT NULL)AND(FALSE)))OR(((CAST(0.3 AS
VARCHAR(836)))<(upper(t0.c1)))));
+INSERT INTO t0(c0, c1) VALUES(TIME '20:59:23', ')TD)Dnö'), (TIME '22:09:19',
'aaJ즳#B!sJNG㡩');
+INSERT INTO t0(c0, c1) VALUES(TIME '20:02:19', CAST(0.1 AS VARCHAR(927)));
+INSERT INTO t0(c0, c1) VALUES(TIME '00:17:15', '{'), (TIME '06:51:52',
CAST(4.1230238E7 AS VARCHAR(459)));
+SELECT ALL t0.c0 FROM t0 WHERE (((t0.c0)<>(t0.c0))) IS NULL;
+ --NULL
+ --NULL
+ROLLBACK;
+
START TRANSACTION; -- Bug 6924
CREATE TABLE "sys"."t0" ("a" INTEGER, "b" INTEGER NOT NULL, CONSTRAINT
"t0_a_b_unique" UNIQUE ("a","b"));
--This copy into must succeed
diff --git a/sql/test/SQLancer/Tests/sqlancer03.stable.out
b/sql/test/SQLancer/Tests/sqlancer03.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer03.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer03.stable.out
@@ -43,8 +43,8 @@ stdout of test 'sqlancer03` in directory
#CREATE TABLE "sys"."t0" ("c0" BOOLEAN NOT NULL,"c1" BIGINT,CONSTRAINT
"t0_c0_pkey" PRIMARY KEY ("c0"),CONSTRAINT "t0_c0_unique" UNIQUE ("c0"));
#create view v0(c0, c1, c2) as (select all 2.020551048E9, 0.16688174,
0.3732000026221729 from t0 where t0.c0) with check option;
#SELECT sql_min(sql_max(NULL, ''), '') FROM v0 LEFT OUTER JOIN t0 ON true;
-% .%12 # table_name
-% %12 # name
+% .%14 # table_name
+% %14 # name
% char # type
% 0 # length
#SELECT sql_min(sql_max(NULL, ''), '');
@@ -55,8 +55,8 @@ stdout of test 'sqlancer03` in directory
[ NULL ]
#SELECT ALL length(upper(MIN(ALL CAST(((trim(CAST(r'' AS STRING(659)),
CAST(r'o3%+i]抔DCöf▟nßOpNbybಜ7' AS STRING)))||(sql_min(sql_max(NULL, r''),
splitpart(r'x', r',7+.', t0.c1)))) AS STRING(151))))), 0.4179268710155164
#FROM v0 LEFT OUTER JOIN t0 ON NOT (t0.c0) WHERE t0.c0 GROUP BY 0.3584962,
CAST(t0.c1 AS STRING(601)), t0.c1;
-% .%24, .%25 # table_name
-% %24, %25 # name
+% .%31, .%32 # table_name
+% %31, %32 # name
% int, decimal # type
% 1, 19 # length
#ROLLBACK;
@@ -258,6 +258,113 @@ stdout of test 'sqlancer03` in directory
% date, int # type
% 10, 1 # length
#ROLLBACK;
+#START TRANSACTION;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
