Changeset: 09ab06b80eca for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=09ab06b80eca
Modified Files:
sql/src/test/BugTracker-2010/Tests/All
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.err
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out
Branch: Oct2010
Log Message:
test for bug 2694: works w/ assertions disabled, assertion fails otherwise
Added the provided test for bug 2694 "Join statement at select kill database".
With assertions disabled, the test appears to work fine.
With assertions enabled, the "left join" variant still works fine,
but the "join" variant triggers an assertion failure:
"
sql/src/server/rel_optimizer.mx:2410: rel_push_join_down: Assertion `re' failed.
"
Cetero censeo
a test should be added (when ever possible)
before closing a bug report.
diffs (truncated from 308 to 300 lines):
diff -r c40733f0a35a -r 09ab06b80eca sql/src/test/BugTracker-2010/Tests/All
--- a/sql/src/test/BugTracker-2010/Tests/All Fri Oct 29 13:27:10 2010 +0200
+++ b/sql/src/test/BugTracker-2010/Tests/All Sat Oct 30 10:56:14 2010 +0200
@@ -69,3 +69,4 @@
new-readonly-db.Bug-2695
Mbedded-crash.Bug-2701
distinct-order-by-limit.Bug-2691
+join_problem.Bug-2694
diff -r c40733f0a35a -r 09ab06b80eca
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.sql Sat Oct
30 10:56:14 2010 +0200
@@ -0,0 +1,71 @@
+start transaction;
+
+CREATE TABLE "time" (
+ "data_id" BIGINT NOT NULL,
+ "header_id" BIGINT NOT NULL,
+ "data_item" VARCHAR(24) NOT NULL,
+ "opr_date" DATE NOT NULL,
+ "opr_hr" INT NOT NULL,
+ "opr_min" INT NOT NULL,
+ "yyyymmddhh" BIGINT NOT NULL,
+ "interval_num" INT NOT NULL,
+ "svalue" VARCHAR(10) NOT NULL,
+ "years" SMALLINT NOT NULL,
+ "months" SMALLINT NOT NULL,
+ "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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ 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)
+ VALUES
+ (110, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 11, 0, 2009040111, 11,
'ON', 2009, 4, '2009-04', 2 );
+
+select t1.opr_date, t1.opr_hr, t1.svalue, t1.yyyymmddhh - t2.avg_yyyymmddhh
+from time t1
+left join -- works
+(select extract(year from opr_date) as y, extract(month from opr_date) as m,
svalue, avg(yyyymmddhh) as avg_yyyymmddhh from time group by y, m, svalue) as t2
+on extract(year from t1.opr_date) = t2.y
+and extract(month from t1.opr_date) = t2.m
+and t1.svalue = t2.svalue
+order by t1.opr_hr;
+
+select t1.opr_date, t1.opr_hr, t1.svalue, t1.yyyymmddhh - t2.avg_yyyymmddhh
+from time t1
+join -- crashes (assertion fails)
+(select extract(year from opr_date) as y, extract(month from opr_date) as m,
svalue, avg(yyyymmddhh) as avg_yyyymmddhh from time group by y, m, svalue) as t2
+on extract(year from t1.opr_date) = t2.y
+and extract(month from t1.opr_date) = t2.m
+and t1.svalue = t2.svalue
+order by t1.opr_hr;
+
+rollback;
diff -r c40733f0a35a -r 09ab06b80eca
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.err
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.err
Sat Oct 30 10:56:14 2010 +0200
@@ -0,0 +1,79 @@
+stderr of test 'join_problem.Bug-2694` in directory 'src/test/BugTracker-2010`
itself:
+
+
+# 10:39:58 >
+# 10:39:58 > mserver5
"--config=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf"
--debug=10 --set gdk_nr_threads=0 --set
"monet_mod_path=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin"
--set
"gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm"
--set mapi_open=true --set xrpc_open=true --set mapi_port=35126 --set
xrpc_port=47300 --set monet_prompt= --trace --forcemito --set mal_listing=2
"--dbname=mTests_src_test_BugTracker-2010" --set mal_listing=0 ;
echo ; echo Over..
+# 10:39:58 >
+
+# builtin opt gdk_arch = 64bitx86_64-unknown-linux-gnu
+# builtin opt gdk_version = 1.40.0
+# builtin opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# builtin opt exec_prefix = ${prefix}
+# builtin opt gdk_dbname = demo
+# builtin opt gdk_dbfarm = ${prefix}/var/MonetDB/dbfarm
+# builtin opt gdk_debug = 0
+# builtin opt gdk_alloc_map = no
+# builtin opt gdk_vmtrim = yes
+# builtin opt monet_admin = adm
+# builtin opt monet_prompt = >
+# builtin opt monet_welcome = yes
+# builtin opt monet_mod_path = ${exec_prefix}/lib64/MonetDB
+# builtin opt monet_daemon = no
+# builtin opt host = localhost
+# builtin opt mapi_port = 50000
+# builtin opt mapi_clients = 2
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt default_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# builtin opt minimal_pipe = inline,remap,deadcode,multiplex,garbageCollector
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# builtin opt standoff_ns =
+# builtin opt standoff_start = start
+# builtin opt standoff_end = end
+# config opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# config opt config = ${prefix}/etc/monetdb5.conf
+# config opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# config opt exec_prefix = ${prefix}
+# config opt gdk_dbfarm = ${prefix}/var/MonetDB5/dbfarm
+# config opt monet_mod_path =
${exec_prefix}/lib64/MonetDB5:${exec_prefix}/lib64/MonetDB5/lib:${exec_prefix}/lib64/MonetDB5/bin
+# config opt mero_pidfile = ${prefix}/var/run/MonetDB/merovingian.pid
+# config opt mero_controlport = 50001
+# config opt no_mitosis_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt sequential_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,history,multiplex,garbageCollector
+# config opt nov2009_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt replication_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector
+# config opt accumulator_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,accumulators,dataflow,history,multiplex,garbageCollector
+# config opt recycler_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector
+# config opt cracker_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,selcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt sidcrack_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,sidcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt datacell_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,datacell,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt octopus_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,constants,commonTerms,joinPath,octopus,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt datacyclotron_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,datacyclotron,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector
+# config opt derive_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,derivePath,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt dictionary_pipe =
inline,remap,dictionary,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt compression_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,compression,dataflow,history,multiplex,garbageCollector
+# cmdline opt config =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt monet_mod_path =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin
+# cmdline opt gdk_dbfarm =
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm
+# cmdline opt mapi_open = true
+# cmdline opt xrpc_open = true
+# cmdline opt mapi_port = 35126
+# cmdline opt xrpc_port = 47300
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_src_test_BugTracker-2010
+# cmdline opt mal_listing = 0
+#warning: please don't forget to set your vault key!
+#(see
/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf)
+
+# 10:39:58 >
+# 10:39:58 > mclient -lsql -ftest -i -e --host=rig --port=35126
+# 10:39:58 >
+
+
+# 10:39:58 >
+# 10:39:58 > Done.
+# 10:39:58 >
+
diff -r c40733f0a35a -r 09ab06b80eca
sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/BugTracker-2010/Tests/join_problem.Bug-2694.stable.out
Sat Oct 30 10:56:14 2010 +0200
@@ -0,0 +1,138 @@
+stdout of test 'join_problem.Bug-2694` in directory 'src/test/BugTracker-2010`
itself:
+
+
+# 10:39:58 >
+# 10:39:58 > mserver5
"--config=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf"
--debug=10 --set gdk_nr_threads=0 --set
"monet_mod_path=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin"
--set
"gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/HG/Oct2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm"
--set mapi_open=true --set xrpc_open=true --set mapi_port=35126 --set
xrpc_port=47300 --set monet_prompt= --trace --forcemito --set mal_listing=2
"--dbname=mTests_src_test_BugTracker-2010" --set mal_listing=0 ;
echo ; echo Over..
+# 10:39:58 >
+
+# MonetDB server v5.22.0, based on kernel v1.40.1
+# Not released
+# Serving database 'mTests_src_test_BugTracker-2010', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 7.750 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved
+# Visit http://monetdb.cwi.nl/ for further information
+# Listening for connection requests on mapi:monetdb://rig.ins.cwi.nl:35126/
+# MonetDB/SQL module v2.40.0 loaded
+
+Ready.
+# SQL catalog created, loading sql scripts once
+
+Over..
+
+# 10:39:58 >
+# 10:39:58 > mclient -lsql -ftest -i -e --host=rig --port=35126
+# 10:39:58 >
+
+#start transaction;
+#CREATE TABLE "time" (
+# "data_id" BIGINT NOT NULL,
+# "header_id" BIGINT NOT NULL,
+# "data_item" VARCHAR(24) NOT NULL,
+# "opr_date" DATE NOT NULL,
+# "opr_hr" INT NOT NULL,
+# "opr_min" INT NOT NULL,
+# "yyyymmddhh" BIGINT NOT NULL,
+# "interval_num" INT NOT NULL,
+# "svalue" VARCHAR(10) NOT NULL,
+# "years" SMALLINT NOT NULL,
+# "months" SMALLINT NOT NULL,
+# "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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# 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)
+# VALUES
+# (110, 14, 'ATL_PEAK_ON_OFF_FLG', '2009-04-01', 11, 0, 2009040111, 11,
'ON', 2009, 4, '2009-04', 2 );
+[ 1 ]
+#select t1.opr_date, t1.opr_hr, t1.svalue, t1.yyyymmddhh - t2.avg_yyyymmddhh
+#from time t1
+#left join -- works
+#(select extract(year from opr_date) as y, extract(month from opr_date) as m,
svalue, avg(yyyymmddhh) as avg_yyyymmddhh from time group by y, m, svalue) as t2
+#on extract(year from t1.opr_date) = t2.y
+#and extract(month from t1.opr_date) = t2.m
+#and t1.svalue = t2.svalue
+#order by t1.opr_hr;
+% .t1, .t1, .t1, . # table_name
+% opr_date, opr_hr, svalue, sql_sub_yyyymmddhh # name
+% date, int, varchar, double # type
+% 10, 2, 3, 22 # length
+[ 2009-04-01, 1, "OFF", -2.5 ]
+[ 2009-04-01, 2, "OFF", -1.5 ]
+[ 2009-04-01, 3, "OFF", -0.5 ]
+[ 2009-04-01, 4, "OFF", 0.5 ]
+[ 2009-04-01, 5, "OFF", 1.5 ]
+[ 2009-04-01, 6, "OFF", 2.5 ]
+[ 2009-04-01, 7, "ON", -2 ]
+[ 2009-04-01, 8, "ON", -1 ]
+[ 2009-04-01, 9, "ON", 0 ]
+[ 2009-04-01, 10, "ON", 1 ]
+[ 2009-04-01, 11, "ON", 2 ]
+#select t1.opr_date, t1.opr_hr, t1.svalue, t1.yyyymmddhh - t2.avg_yyyymmddhh
+#from time t1
+#join -- crashes (assertion fails)
+#(select extract(year from opr_date) as y, extract(month from opr_date) as m,
svalue, avg(yyyymmddhh) as avg_yyyymmddhh from time group by y, m, svalue) as t2
+#on extract(year from t1.opr_date) = t2.y
+#and extract(month from t1.opr_date) = t2.m
+#and t1.svalue = t2.svalue
+#order by t1.opr_hr;
+% sys.t1, sys.t1, sys.t1, sys. # table_name
+% opr_date, opr_hr, svalue, sql_sub_yyyymmddhh # name
+% date, int, varchar, double # type
+% 10, 2, 3, 22 # length
+[ 2009-04-01, 1, "OFF", -2.5 ]
+[ 2009-04-01, 2, "OFF", -1.5 ]
+[ 2009-04-01, 3, "OFF", -0.5 ]
+[ 2009-04-01, 4, "OFF", 0.5 ]
+[ 2009-04-01, 5, "OFF", 1.5 ]
+[ 2009-04-01, 6, "OFF", 2.5 ]
+[ 2009-04-01, 7, "ON", -2 ]
+[ 2009-04-01, 8, "ON", -1 ]
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
