Changeset: 0bec27aa6d8e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0bec27aa6d8e
Added Files:
sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql
sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.err
sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2015/Tests/All
Branch: Jul2015
Log Message:
fixed bug 3803, ie make sure we add only not yet added projection columns
diffs (truncated from 362 to 300 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -2788,6 +2788,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
} else if (f == sql_sel) { /* allways add left
side in case of selections phase */
if (!l->processed) { /* add all
expressions to the project */
l->exps = list_merge(l->exps,
rel_projections(sql, l->l, NULL, 1, 1), (fdup)NULL);
+ l->exps =
list_distinct(l->exps, (fcmp)exp_equal, (fdup)NULL);
set_processed(l);
}
if (!rel_find_exp(l, ls))
diff --git a/sql/test/BugTracker-2015/Tests/All
b/sql/test/BugTracker-2015/Tests/All
--- a/sql/test/BugTracker-2015/Tests/All
+++ b/sql/test/BugTracker-2015/Tests/All
@@ -54,3 +54,4 @@ THREADS>=3?useless_casts.Bug-3756
sum_interval.Bug-3785
nil_cast.Bug-3787
sql2pcre.Bug-3800
+ambiguous.Bug-3803
diff --git a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql
b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql
@@ -0,0 +1,59 @@
+CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER);
+INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104);
+INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105);
+INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,111,113);
+INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,118);
+INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,120,121);
+INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125);
+INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130);
+INSERT INTO t1(a,d,b,e,c) VALUES(138,136,139,135,137);
+
+SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN
444 ELSE 555 END, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN
333 ELSE 444 END, a+b*2+c*3+d*4, a+b*2+c*3, c, CASE WHEN c>(SELECT avg(c) FROM
t1) THEN a*2 ELSE b*10 END, abs(b-c) FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x
WHERE x.b<t1.b) OR b>c OR d NOT BETWEEN 110 AND 150 ORDER BY 4,1,5,2,6,3,7;
+-- ERROR = !SELECT: identifier 'c' ambiguous
+
+SELECT a, a+b*2+c*3+d*4+e*5, c-d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2
ELSE b*10 END, b-c, a+b*2 FROM t1 ORDER BY 6,2,4,5,3,1;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT a+b*2+c*3+d*4+e*5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN
a<b+3 THEN 333 ELSE 444 END, a, abs(b-c), a+b*2, d, CASE WHEN c>(SELECT avg(c)
FROM t1) THEN a*2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR a>b ORDER BY
4,5,3,7,1,6,2;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT a, e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, a-b
FROM t1 ORDER BY 2,4,3,1;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
abs(b-c), a+b*2+c*3+d*4+e*5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN
a<b+3 THEN 333 ELSE 444 END, d-e FROM t1 ORDER BY 1,6,2,3,5,4;
+-- ERROR = !SELECT: identifier 'd' ambiguous
+
+SELECT a+b*2+c*3+d*4+e*5, a, abs(a), a-b, d-e, CASE WHEN c>(SELECT avg(c) FROM
t1) THEN a*2 ELSE b*10 END FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE
x.b<t1.b) AND b>c ORDER BY 4,6,3,1,5,2;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE
444 END, a+b*2+c*3+d*4+e*5, a, CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d
THEN 333 WHEN e THEN 444 ELSE 555 END, CASE WHEN c>(SELECT avg(c) FROM t1)
THEN a*2 ELSE b*10 END, (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND
x.d<t1.d), d FROM t1 WHERE a>b AND (e>a AND e<b) ORDER BY 7,2,4,6,1,3,5;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT e, (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b), CASE WHEN c>(SELECT
avg(c) FROM t1) THEN a*2 ELSE b*10 END, CASE a+1 WHEN b THEN 111 WHEN c THEN
222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, a-b, (SELECT count(*) FROM
t1 AS x WHERE x.c>t1.c AND x.d<t1.d) FROM t1 WHERE a>b AND (c<=d-2 OR c>=d+2)
AND c>d ORDER BY 6,5,4,2,3,1;
+-- ERROR = !SELECT: identifier 'e' ambiguous
+
+SELECT b, a-b, c, abs(b-c), d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2
ELSE b*10 END, b-c FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
ORDER BY 1,6,4,5,2,7,3;
+-- ERROR = !SELECT: identifier 'b' ambiguous
+
+SELECT (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), b, a,
a+b*2+c*3+d*4+e*5, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE
555 END, a+b*2+c*3 FROM t1 WHERE a>b AND (e>c OR e<d) ORDER BY 3,7,2,5,6,4,1;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT c-d, a-b, b, b-c, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE
b*10 END, c, a+b*2 FROM t1 ORDER BY 1,5,4,3,2,6,7;
+-- ERROR = !SELECT: identifier 'b' ambiguous
+
+SELECT a+b*2+c*3+d*4, a, c-d, abs(b-c), b, CASE WHEN c>(SELECT avg(c) FROM t1)
THEN a*2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR (c<=d-2 OR c>=d+2) ORDER
BY 4,3,2,5,1,6;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT a, a+b*2+c*3+d*4+e*5, b, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2
ELSE b*10 END, e, a-b FROM t1 ORDER BY 1,4,5,3,6,2;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT d, d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
a+b*2, a+b*2+c*3+d*4+e*5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN
a<b+3 THEN 333 ELSE 444 END, a+b*2+c*3 FROM t1 ORDER BY 3,2,4,5,7,1,6;
+-- ERROR = !SELECT: identifier 'd' ambiguous
+
+SELECT a, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, (SELECT
count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), a+b*2+c*3+d*4, b FROM t1
WHERE c>d OR d>e ORDER BY 2,5,1,3,4;
+-- ERROR = !SELECT: identifier 'a' ambiguous
+
+SELECT c, (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), CASE
WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, a+b*2+c*3+d*4 FROM t1
WHERE b>c OR (e>c OR e<d) OR d NOT BETWEEN 110 AND 150 ORDER BY 3,2,1,4;
+-- ERROR = !SELECT: identifier 'c' ambiguous
+
+DROP TABLE t1;
diff --git a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.err
b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'ambiguous.Bug-3803` in directory 'sql/test/BugTracker-2015`
itself:
+
+
+# 17:26:39 >
+# 17:26:39 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39839" "--set"
"mapi_usock=/var/tmp/mtest-12556/.s.monetdb.39839" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2015"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 17:26:39 >
+
+# builtin opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 39839
+# cmdline opt mapi_usock = /var/tmp/mtest-12556/.s.monetdb.39839
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2015
+# cmdline opt mal_listing = 0
+# cmdline opt embedded_r = yes
+# cmdline opt gdk_debug = 536870922
+
+# 17:26:39 >
+# 17:26:39 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-12556" "--port=39839"
+# 17:26:39 >
+
+
+# 17:26:40 >
+# 17:26:40 > "Done."
+# 17:26:40 >
+
diff --git a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out
b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out
@@ -0,0 +1,232 @@
+stdout of test 'ambiguous.Bug-3803` in directory 'sql/test/BugTracker-2015`
itself:
+
+
+# 17:26:39 >
+# 17:26:39 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39839" "--set"
"mapi_usock=/var/tmp/mtest-12556/.s.monetdb.39839" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2015"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 17:26:39 >
+
+# MonetDB 5 server v11.21.2
+# This is an unreleased version
+# Serving database 'mTests_sql_test_BugTracker-2015', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit
integers dynamically linked
+# Found 7.333 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://localhost.nes.nl:39839/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-12556/.s.monetdb.39839
+# MonetDB/GIS module loaded
+# Start processing logs sql/sql_logs version 52200
+# Start reading the write-ahead log 'sql_logs/sql/log.37'
+# Finished reading the write-ahead log 'sql_logs/sql/log.37'
+# Finished processing logs sql/sql_logs
+# MonetDB/SQL module loaded
+# MonetDB/R module loaded
+
+Ready.
+
+# 17:26:39 >
+# 17:26:39 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-12556" "--port=39839"
+# 17:26:39 >
+
+#CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER);
+#INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104);
+[ 1 ]
+#INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105);
+[ 1 ]
+#INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,111,113);
+[ 1 ]
+#INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,118);
+[ 1 ]
+#INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,120,121);
+[ 1 ]
+#INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125);
+[ 1 ]
+#INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130);
+[ 1 ]
+#INSERT INTO t1(a,d,b,e,c) VALUES(138,136,139,135,137);
+[ 1 ]
+#SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN
444 ELSE 555 END, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN
333 ELSE 444 END, a+b*2+c*3+d*4, a+b*2+c*3, c, CASE WHEN c>(SELECT avg(c) FROM
t1) THEN a*2 ELSE b*10 END, abs(b-c) FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x
WHERE x.b<t1.b) OR b>c OR d NOT BETWEEN 110 AND 150 ORDER BY 4,1,5,2,6,3,7;
+% .L1, .L2, .L3, .L4, .t1, .L6, .L7 # table_name
+% L1, L2, L3, L4, c, L6, L7 # name
+% smallint, smallint, hugeint, hugeint, int, bigint,
bigint # type
+% 3, 3, 4, 3, 3, 4, 1 # length
+[ 555, 444, 1014, 610, 102, 1000, 2 ]
+[ 333, 333, 1067, 635, 106, 1050, 1 ]
+[ 111, 222, 1130, 674, 113, 1120, 1 ]
+[ 333, 222, 1172, 708, 119, 1180, 1 ]
+[ 333, 222, 1226, 738, 123, 242, 1 ]
+[ 333, 222, 1272, 760, 125, 254, 4 ]
+[ 444, 333, 1325, 793, 134, 262, 4 ]
+[ 111, 222, 1371, 827, 137, 276, 2 ]
+#SELECT a, a+b*2+c*3+d*4+e*5, c-d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN
a*2 ELSE b*10 END, b-c, a+b*2 FROM t1 ORDER BY 6,2,4,5,3,1;
+% sys.t1, sys.L1, sys.L2, sys.L4, sys.L5, sys.L6 # table_name
+% a, L1, L2, L4, L5, L6 # name
+% int, hugeint, bigint, bigint, bigint, hugeint # type
+% 3, 4, 2, 4, 2, 3 # length
+[ 104, 1529, 1, 1000, -2, 304 ]
+[ 107, 1612, -2, 1050, -1, 317 ]
+[ 111, 1680, -1, 1120, -1, 335 ]
+[ 115, 1757, 3, 1180, -1, 351 ]
+[ 121, 1826, 1, 242, 1, 369 ]
+[ 127, 1902, -3, 254, 4, 385 ]
+[ 131, 1985, 1, 262, -4, 391 ]
+[ 138, 2046, 1, 276, 2, 416 ]
+#SELECT a+b*2+c*3+d*4+e*5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN
a<b+3 THEN 333 ELSE 444 END, a, abs(b-c), a+b*2, d, CASE WHEN c>(SELECT avg(c)
FROM t1) THEN a*2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR a>b ORDER BY
4,5,3,7,1,6,2;
+% sys.L1, sys.L2, sys.t1, sys.L3, sys.L4, sys.t1, sys.L6 # table_name
+% L1, L2, a, L3, L4, d, L6 # name
+% hugeint, smallint, int, bigint, hugeint, int, bigint
# type
+% 4, 3, 3, 1, 3, 3, 4 # length
+[ 1612, 333, 107, 1, 317, 108, 1050 ]
+[ 1680, 222, 111, 1, 335, 114, 1120 ]
+[ 1826, 222, 121, 1, 369, 122, 242 ]
+[ 1529, 444, 104, 2, 304, 101, 1000 ]
+[ 2046, 222, 138, 2, 416, 136, 276 ]
+[ 1902, 222, 127, 4, 385, 128, 254 ]
+[ 1985, 333, 131, 4, 391, 133, 262 ]
+#SELECT a, e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, a-b
FROM t1 ORDER BY 2,4,3,1;
+% sys.t1, sys.t1, sys.L2, sys.L3 # table_name
+% a, e, L2, L3 # name
+% int, int, bigint, bigint # type
+% 3, 3, 4, 2 # length
+[ 104, 103, 1000, 4 ]
+[ 107, 109, 1050, 2 ]
+[ 111, 110, 1120, -1 ]
+[ 115, 117, 1180, -3 ]
+[ 121, 120, 242, -3 ]
+[ 127, 126, 254, -2 ]
+[ 131, 132, 262, 1 ]
+[ 138, 135, 276, -1 ]
+#SELECT d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
abs(b-c), a+b*2+c*3+d*4+e*5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN
a<b+3 THEN 333 ELSE 444 END, d-e FROM t1 ORDER BY 1,6,2,3,5,4;
+% sys.t1, sys.L2, sys.L3, sys.L4, sys.L5, sys.L6 # table_name
+% d, L2, L3, L4, L5, L6 # name
+% int, bigint, bigint, hugeint, smallint, bigint # type
+% 3, 4, 1, 4, 3, 2 # length
+[ 101, 1000, 2, 1529, 444, -2 ]
+[ 108, 1050, 1, 1612, 333, -1 ]
+[ 114, 1120, 1, 1680, 222, 4 ]
+[ 116, 1180, 1, 1757, 222, -1 ]
+[ 122, 242, 1, 1826, 222, 2 ]
+[ 128, 254, 4, 1902, 222, 2 ]
+[ 133, 262, 4, 1985, 333, 1 ]
+[ 136, 276, 2, 2046, 222, 1 ]
+#SELECT a+b*2+c*3+d*4+e*5, a, abs(a), a-b, d-e, CASE WHEN c>(SELECT avg(c)
FROM t1) THEN a*2 ELSE b*10 END FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x
WHERE x.b<t1.b) AND b>c ORDER BY 4,6,3,1,5,2;
+% sys.L1, sys.t1, sys.L2, sys.L3, sys.L4, sys.L6 # table_name
+% L1, a, L2, L3, L4, L6 # name
+% hugeint, int, int, bigint, bigint, bigint # type
+% 4, 3, 3, 2, 1, 3 # length
+[ 1826, 121, 121, -3, 2, 242 ]
+[ 1902, 127, 127, -2, 2, 254 ]
+[ 2046, 138, 138, -1, 1, 276 ]
+#SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE
444 END, a+b*2+c*3+d*4+e*5, a, CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d
THEN 333 WHEN e THEN 444 ELSE 555 END, CASE WHEN c>(SELECT avg(c) FROM t1)
THEN a*2 ELSE b*10 END, (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND
x.d<t1.d), d FROM t1 WHERE a>b AND (e>a AND e<b) ORDER BY 7,2,4,6,1,3,5;
+% .L1, .L2, .t1, .L3, .L5, .L7, .t1 # table_name
+% L1, L2, a, L3, L5, L7, d # name
+% smallint, hugeint, int, smallint, bigint, wrd, int #
type
+% 1, 1, 1, 1, 1, 1, 1 # length
+#SELECT e, (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b), CASE WHEN c>(SELECT
avg(c) FROM t1) THEN a*2 ELSE b*10 END, CASE a+1 WHEN b THEN 111 WHEN c THEN
222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, a-b, (SELECT count(*) FROM
t1 AS x WHERE x.c>t1.c AND x.d<t1.d) FROM t1 WHERE a>b AND (c<=d-2 OR c>=d+2)
AND c>d ORDER BY 6,5,4,2,3,1;
+% .t1, .L2, .L4, .L5, .L6, .L10 # table_name
+% e, L2, L4, L5, L6, L10 # name
+% int, wrd, bigint, smallint, bigint, wrd # type
+% 1, 1, 1, 1, 1, 1 # length
+#SELECT b, a-b, c, abs(b-c), d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2
ELSE b*10 END, b-c FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
ORDER BY 1,6,4,5,2,7,3;
+% sys.t1, sys.L1, sys.t1, sys.L2, sys.L3, sys.L5, sys.L6 # table_name
+% b, L1, c, L2, L3, L5, L6 # name
+% int, bigint, int, bigint, bigint, bigint, bigint # type
+% 3, 2, 3, 1, 2, 4, 2 # length
+[ 105, 2, 106, 1, -1, 1050, -1 ]
+[ 112, -1, 113, 1, 4, 1120, -1 ]
+[ 118, -3, 119, 1, -1, 1180, -1 ]
+[ 124, -3, 123, 1, 2, 242, 1 ]
+[ 129, -2, 125, 4, 2, 254, 4 ]
+[ 130, 1, 134, 4, 1, 262, -4 ]
+[ 139, -1, 137, 2, 1, 276, 2 ]
+#SELECT (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), b, a,
a+b*2+c*3+d*4+e*5, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE
555 END, a+b*2+c*3 FROM t1 WHERE a>b AND (e>c OR e<d) ORDER BY 3,7,2,5,6,4,1;
+% .L1, .t1, .t1, .L2, .L4, .L5, .L6 # table_name
+% L1, b, a, L2, L4, L5, L6 # name
+% wrd, int, int, hugeint, bigint, smallint, hugeint # type
+% 1, 3, 3, 4, 4, 3, 3 # length
+[ 0, 100, 104, 1529, 1000, 555, 610 ]
+[ 0, 105, 107, 1612, 1050, 333, 635 ]
+[ 0, 130, 131, 1985, 262, 444, 793 ]
+#SELECT c-d, a-b, b, b-c, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE
b*10 END, c, a+b*2 FROM t1 ORDER BY 1,5,4,3,2,6,7;
+% sys.L1, sys.L2, sys.t1, sys.L3, sys.L5, sys.t1, sys.L6 # table_name
+% L1, L2, b, L3, L5, c, L6 # name
+% bigint, bigint, int, bigint, bigint, int, hugeint # type
+% 2, 2, 3, 2, 4, 3, 3 # length
+[ -3, -2, 129, 4, 254, 125, 385 ]
+[ -2, 2, 105, -1, 1050, 106, 317 ]
+[ -1, -1, 112, -1, 1120, 113, 335 ]
+[ 1, -3, 124, 1, 242, 123, 369 ]
+[ 1, 1, 130, -4, 262, 134, 391 ]
+[ 1, -1, 139, 2, 276, 137, 416 ]
+[ 1, 4, 100, -2, 1000, 102, 304 ]
+[ 3, -3, 118, -1, 1180, 119, 351 ]
+#SELECT a+b*2+c*3+d*4, a, c-d, abs(b-c), b, CASE WHEN c>(SELECT avg(c) FROM
t1) THEN a*2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR (c<=d-2 OR c>=d+2)
ORDER BY 4,3,2,5,1,6;
+% sys.L1, sys.t1, sys.L2, sys.L3, sys.t1, sys.L5 # table_name
+% L1, a, L2, L3, b, L5 # name
+% hugeint, int, bigint, bigint, int, bigint # type
+% 4, 3, 2, 1, 3, 4 # length
+[ 1067, 107, -2, 1, 105, 1050 ]
+[ 1130, 111, -1, 1, 112, 1120 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
