Changeset: 809ced83d8c4 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=809ced83d8c4 Modified Files: sql/ChangeLog sql/backends/monet5/sql.mx sql/test/BugTracker-2010/Tests/All sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.sql sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.err sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.out Branch: default Log Message:
sequences: refuse starting/restarting with NULL, bug #2648 Using sub-queries, the parser cannot "see" that the restart value is actually NULL. Implement the runtime checks to ensure that we cannot set restart value for sequences to NULL. diffs (227 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 +* Mon Jan 24 2011 Fabian Groffen <[email protected]> +- Fix bug #2648, do not allow restarting a sequence with NULL via the + result of a sub-query. + * Wed Dec 15 2010 Niels Nes <[email protected]> - Allow clients to release prepared handles using Xrelease commands diff --git a/sql/backends/monet5/sql.mx b/sql/backends/monet5/sql.mx --- a/sql/backends/monet5/sql.mx +++ b/sql/backends/monet5/sql.mx @@ -1992,7 +1992,7 @@ return msg; if (STORE_READONLY(active_store_type)) - return sql_message("schema statements cannot be executed on a readonly database."); + return sql_message("SQLException:sql.catalog: schema statements cannot be executed on a readonly database."); switch (type) { case DDL_CREATE_SEQ: @@ -2006,7 +2006,10 @@ sql_sequence *s = *(sql_sequence**) getArgReference(stk, pci, 3); if (getArgType(mb, pci, 4) == TYPE_lng) val = getArgReference(stk, pci, 4); - msg = alter_seq(sql, sname, s, val); + if (val == NULL || *val == lng_nil) + msg = sql_message("ALTER SEQUENCE: cannot (re)start with NULL"); + else + msg = alter_seq(sql, sname, s, val); } break; case DDL_DROP_SEQ: { str name = *(str*) getArgReference(stk, pci, 3); @@ -2358,6 +2361,8 @@ (void) cntxt; if (msg) return msg; + if (*start == lng_nil) + throw(SQL, "sql.restart", "cannot (re)start with NULL"); s = mvc_bind_schema(m, *sname); if (s) { sql_sequence *seq = find_sql_sequence(s, *seqname); @@ -2367,7 +2372,7 @@ return MAL_SUCCEED; } } - throw(SQL, "sql.restart", "error"); + throw(SQL, "sql.restart", "sequence %s not found", *sname); } diff --git a/sql/test/BugTracker-2010/Tests/All b/sql/test/BugTracker-2010/Tests/All --- a/sql/test/BugTracker-2010/Tests/All +++ b/sql/test/BugTracker-2010/Tests/All @@ -85,3 +85,4 @@ call_function_without_warning.Bug-2752 vacuum with-between.Bug-2732 +sequence-restart-with-null.Bug-2648 diff --git a/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.sql b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.sql @@ -0,0 +1,13 @@ +create sequence testseq_2648 as integer start with 1; +create table testtbl_2648 (id integer); + +select next value for testseq_2648; + +alter sequence testseq_2648 restart with null no cycle; + +alter sequence testseq_2648 restart with (select max(id) from testtbl_2648); + +select next value for testseq_2648; + +drop table testtbl_2648; +drop sequence testseq_2648; diff --git a/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.err b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.err @@ -0,0 +1,87 @@ +stderr of test 'sequence-restart-with-null` in directory 'test/BugTracker-2010` itself: + + +# 20:16:54 > +# 20:16:54 > mserver5 "--config=/ufs/fabian/scratch/monetdb/current/program-x86_64/etc/monetdb5.conf" --debug=10 --set gdk_nr_threads=0 --set "monet_mod_path=/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/lib:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/bin" --set "gdk_dbfarm=/net/volund.ins.cwi.nl/export/scratch0/fabian/vtmp/mtest-current-volund.ins.cwi.nl/sql/dbfarm" --set mapi_open=true --set xrpc_open=true --set mapi_port=33129 --set xrpc_port=47994 --set monet_prompt= --trace --forcemito --set mal_listing=2 "--dbname=mTests_test_BugTracker-2010" --set mal_listing=0 ; echo ; echo Over.. +# 20:16:54 > + +# builtin opt gdk_arch = 64bitx86_64-pc-linux-gnu +# builtin opt gdk_version = 11.0.0 +# builtin opt prefix = /ufs/fabian/scratch/monetdb/current/program-x86_64 +# 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 = /ufs/fabian/scratch/monetdb/current/program-x86_64/lib/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/fabian/scratch/monetdb/current/program-x86_64 +# config opt config = /ufs/fabian/scratch/monetdb/current/program-x86_64/etc/monetdb5.conf +# config opt prefix = /ufs/fabian/scratch/monetdb/current/program-x86_64 +# config opt exec_prefix = ${prefix} +# config opt gdk_dbfarm = ${prefix}/var/monetdb5/dbfarm +# config opt monet_mod_path = /ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/lib:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/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,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector +# config opt sidcrack_pipe = inline,remap,evaluate,costModel,coercions,emptySet,aliases,sidcrack,deadcode,commonTerms,joinPath,reorder,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,deadcode,octopus,reduce,dataflow,history,multiplex,garbageCollector +# config opt tarantula_pipe = inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,constants,commonTerms,joinPath,tarantula,deadcode,reduce,dataflow,history,multiplex,garbageCollector +# config opt mapreduce_pipe = inline,remap,evaluate,costModel,coercions,emptySet,aliases,mapreduce,mergetable,deadcode,commonTerms,joinPath,reorder,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/fabian/scratch/monetdb/current/program-x86_64/etc/monetdb5.conf +# cmdline opt gdk_nr_threads = 0 +# cmdline opt monet_mod_path = /ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/lib:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/bin +# cmdline opt gdk_dbfarm = /net/volund.ins.cwi.nl/export/scratch0/fabian/vtmp/mtest-current-volund.ins.cwi.nl/sql/dbfarm +# cmdline opt mapi_open = true +# cmdline opt xrpc_open = true +# cmdline opt mapi_port = 33129 +# cmdline opt xrpc_port = 47994 +# cmdline opt monet_prompt = +# cmdline opt mal_listing = 2 +# cmdline opt gdk_dbname = mTests_test_BugTracker-2010 +# cmdline opt mal_listing = 0 +#warning: please don't forget to set your vault key! +#(see /ufs/fabian/scratch/monetdb/current/program-x86_64/etc/monetdb5.conf) + +# 20:16:54 > +# 20:16:54 > mclient -lsql -ftest -i -e --host=volund --port=33129 +# 20:16:54 > + +MAPI = monetdb@volund:38496 +QUERY = alter sequence testseq_2648 restart with null no cycle; +ERROR = !syntax error, unexpected sqlNULL, expecting sqlINT or '(' in: "alter sequence testseq_2648 restart with null" +MAPI = monetdb@volund:38496 +QUERY = alter sequence testseq_2648 restart with (select max(id) from testtbl_2648); +ERROR = !SQLException:sql.catalog:ALTER SEQUENCE: cannot (re)start with NULL + +# 20:16:54 > +# 20:16:54 > Done. +# 20:16:54 > + diff --git a/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.out b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2010/Tests/sequence-restart-with-null.Bug-2648.stable.out @@ -0,0 +1,48 @@ +stdout of test 'sequence-restart-with-null` in directory 'test/BugTracker-2010` itself: + + +# 20:16:54 > +# 20:16:54 > mserver5 "--config=/ufs/fabian/scratch/monetdb/current/program-x86_64/etc/monetdb5.conf" --debug=10 --set gdk_nr_threads=0 --set "monet_mod_path=/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/lib:/ufs/fabian/scratch/monetdb/current/program-x86_64/lib/monetdb5/bin" --set "gdk_dbfarm=/net/volund.ins.cwi.nl/export/scratch0/fabian/vtmp/mtest-current-volund.ins.cwi.nl/sql/dbfarm" --set mapi_open=true --set xrpc_open=true --set mapi_port=33129 --set xrpc_port=47994 --set monet_prompt= --trace --forcemito --set mal_listing=2 "--dbname=mTests_test_BugTracker-2010" --set mal_listing=0 ; echo ; echo Over.. +# 20:16:54 > + +# MonetDB 5 server v11.0.0 +# Release current-hg +# Serving database 'mTests_test_BugTracker-2010', using 2 threads +# Compiled for x86_64-pc-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-2011 MonetDB B.V., all rights reserved +# Visit http://monetdb.cwi.nl/ for further information +# MonetDB/SQL module v11.0.0 loaded +# MonetDB/GIS module v11.0.0 loaded + +Ready. +# SQL catalog created, loading sql scripts once + +Over.. + +# 20:20:11 > +# 20:20:11 > mclient -lsql -ftest -i -e --host=volund --port=34184 +# 20:20:11 > + +#create sequence testseq_2648 as integer start with 1; +#create table testtbl_2648 (id integer); +#select next value for testseq_2648; +% . # table_name +% next_value_for_sys # name +% bigint # type +% 1 # length +[ 1 ] +#alter sequence testseq_2648 restart with (select max(id) from testtbl_2648); +#select next value for testseq_2648; +% . # table_name +% next_value_for_sys # name +% bigint # type +% 1 # length +[ 2 ] +#drop table testtbl_2648; + +# 20:16:54 > +# 20:16:54 > Done. +# 20:16:54 > + _______________________________________________ Checkin-list mailing list [email protected] http://mail.monetdb.org/mailman/listinfo/checkin-list
