Changeset: 715c9a5ee107 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=715c9a5ee107 Removed Files: sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out.int128 sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.stable.out.int128 sql/test/BugTracker-2015/Tests/nil_cast.Bug-3787.stable.out.int128 Modified Files: sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.sql sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.stable.out sql/test/BugTracker-2015/Tests/incorrect_result_type.Bug-3687.sql sql/test/BugTracker-2015/Tests/incorrect_result_type.Bug-3687.stable.out sql/test/BugTracker-2015/Tests/incorrect_result_type.Bug-3687.stable.out.int128 sql/test/BugTracker-2015/Tests/nil_cast.Bug-3787.sql sql/test/BugTracker-2015/Tests/nil_cast.Bug-3787.stable.out Branch: default Log Message:
Eliminated *.int128 output files (by adding cast(... as bigint)) Also extended test: incorrect_result_type.Bug-3687 diffs (truncated from 1023 to 300 lines): diff --git a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql --- a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql +++ b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.sql @@ -8,25 +8,25 @@ INSERT INTO t1(a,d,b,e,c) VALUES(127,128 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; +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, cast(a+b*2+c*3+d*4 as bigint), cast(a+b*2+c*3 as bigint), 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; +SELECT a, cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +SELECT cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +SELECT d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, abs(b-c), cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +SELECT cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, cast(a+b*2+c*3+d*4+e*5 as bigint), 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; @@ -35,25 +35,25 @@ SELECT e, (SELECT count(*) FROM t1 AS x 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; +SELECT (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), b, a, cast(a+b*2+c*3+d*4+e*5 as bigint), 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, cast(a+b*2+c*3 as bigint) 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; +SELECT cast(a+b*2+c*3+d*4 as bigint), 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; +SELECT a, cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +SELECT d, d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, a+b*2, cast(a+b*2+c*3+d*4+e*5 as bigint), CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, cast(a+b*2+c*3 as bigint) 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; +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), cast(a+b*2+c*3+d*4 as bigint), 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; +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, cast(a+b*2+c*3+d*4 as bigint) 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.out b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out --- a/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out +++ b/sql/test/BugTracker-2015/Tests/ambiguous.Bug-3803.stable.out @@ -30,199 +30,231 @@ stdout of test 'ambiguous.Bug-3803` in d #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 ] +[ 1 ] #INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105); -[ 1 ] +[ 1 ] #INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,111,113); -[ 1 ] +[ 1 ] #INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,118); -[ 1 ] +[ 1 ] #INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,120,121); -[ 1 ] +[ 1 ] #INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125); -[ 1 ] +[ 1 ] #INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130); -[ 1 ] +[ 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; -% .L16, .L20, .L22, .L24, .t1, .L33, .L35 # table_name -% L16, L20, L22, L24, c, L33, L35 # name -% smallint, smallint, bigint, bigint, 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.L3, sys.L5, sys.L13, sys.L15, sys.L17 # table_name -% a, L3, L5, L13, L15, L17 # name -% int, bigint, bigint, bigint, bigint, bigint # 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.L2, sys.L4, sys.t1, sys.L7, sys.L11, sys.t1, sys.L20 # table_name -% L2, L4, a, L7, L11, d, L20 # name -% bigint, smallint, int, bigint, bigint, 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 ] +[ 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, cast(a+b*2+c*3+d*4 as bigint), cast(a+b*2+c*3 as bigint), 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; +% ., ., ., ., .t1, ., . # table_name +# .L16, .L20, .L22, .L24, .t1, .L33, .L35 # table_name +% L, L, L, L, c, L, L # name +# L16, L20, L22, L24, c, L33, L35 # name +% smallint, smallint, bigint, bigint, 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, cast(a+b*2+c*3+d*4+e*5 as bigint), 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., sys., sys., sys., sys. # table_name +# sys.t1, sys.L3, sys.L5, sys.L13, sys.L15, sys.L17 # table_name +% a, L, L, L, L, L # name +# a, L3, L5, L13, L15, L17 # name +% int, bigint, bigint, bigint, bigint, bigint # 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 cast(a+b*2+c*3+d*4+e*5 as bigint), 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., sys., sys.t1, sys., sys., sys.t1, sys. # table_name +# sys.L2, sys.L4, sys.t1, sys.L7, sys.L11, sys.t1, sys.L20 # table_name +% L, L, a, L, L, d, L # name +# L2, L4, a, L7, L11, d, L20 # name +% bigint, smallint, int, bigint, bigint, 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.L10, sys.L12 # table_name -% a, e, L10, L12 # 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.L7, sys.L11, sys.L13, sys.L15, sys.L17 # table_name -% d, L7, L11, L13, L15, L17 # name -% int, bigint, bigint, bigint, 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.L4, sys.t1, sys.L7, sys.L11, sys.L13, sys.L21 # table_name -% L4, a, L7, L11, L13, L21 # name -% bigint, 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; -% sys.L2, sys.L4, sys.t1, sys.L7, sys.L15, .L21, sys.t1 # table_name -% L2, L4, a, L7, L15, L21, d # name -% smallint, bigint, int, smallint, bigint, bigint, int # type -% 1, 1, 1, 1, 1, 1, 1 # length +% sys.t1, sys.t1, sys., sys. # table_name +# sys.t1, sys.t1, sys.L10, sys.L12 # table_name +% a, e, L, L # name +# a, e, L10, L12 # 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), cast(a+b*2+c*3+d*4+e*5 as bigint), 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., sys., sys., sys., sys. # table_name +# sys.t1, sys.L7, sys.L11, sys.L13, sys.L15, sys.L17 # table_name +% d, L, L, L, L, L # name +# d, L7, L11, L13, L15, L17 # name +% int, bigint, bigint, bigint, 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 cast(a+b*2+c*3+d*4+e*5 as bigint), 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., sys.t1, sys., sys., sys., sys. # table_name +# sys.L4, sys.t1, sys.L7, sys.L11, sys.L13, sys.L21 # table_name +% L, a, L, L, L, L # name +# L4, a, L7, L11, L13, L21 # name +% bigint, 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, cast(a+b*2+c*3+d*4+e*5 as bigint), 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; +% sys., sys., sys.t1, sys., sys., ., sys.t1 # table_name +# sys.L2, sys.L4, sys.t1, sys.L7, sys.L15, .L21, sys.t1 # table_name +% L, L, a, L, L, L, d # name +# L2, L4, a, L7, L15, L21, d # name +% smallint, bigint, int, smallint, bigint, bigint, 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; -% sys.t1, .L5, sys.L14, sys.L16, sys.L20, .L24 # table_name -% e, L5, L14, L16, L20, L24 # name -% int, bigint, bigint, smallint, bigint, bigint # type -% 1, 1, 1, 1, 1, 1 # length +% sys.t1, ., sys., sys., sys., . # table_name +# sys.t1, .L5, sys.L14, sys.L16, sys.L20, .L24 # table_name +% e, L, L, L, L, L # name +# e, L5, L14, L16, L20, L24 # name +% int, bigint, bigint, smallint, bigint, bigint # 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.L5, sys.t1, sys.L10, sys.L12, sys.L20, sys.L22 # table_name -% b, L5, c, L10, L12, L20, L22 # 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; -% .L4, sys.t1, sys.t1, sys.L10, sys.L17, sys.L21, sys.L23 # table_name -% L4, b, a, L10, L17, L21, L23 # name -% bigint, int, int, bigint, bigint, smallint, bigint # 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 ] +% sys.t1, sys., sys.t1, sys., sys., sys., sys. # table_name +# sys.t1, sys.L5, sys.t1, sys.L10, sys.L12, sys.L20, sys.L22 # table_name +% b, L, c, L, L, L, L # name +# b, L5, c, L10, L12, L20, L22 # name +% int, bigint, int, bigint, bigint, bigint, bigint # type _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
