Changeset: 9ed6a92391ac for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9ed6a92391ac Modified Files: Branch: jacqueline Log Message:
Merged from default diffs (truncated from 522 to 300 lines): diff --git a/clients/mapiclient/mclient.1 b/clients/mapiclient/mclient.1 --- a/clients/mapiclient/mclient.1 +++ b/clients/mapiclient/mclient.1 @@ -368,5 +368,27 @@ Enable auto commit mode. .TP \fB\ea\fP Disable auto commit mode. +.SH EXAMPLES +Efficiently import data from a CSV (comma-separated values) file into +a table. The file must be readable by the server. $file is the +absolute path name of the file, $table is the name of the table, $db +is the name of the database. +.PP +mclient -d $db -s "COPY INTO $table FROM '$file' USING DELIMITERS ',','\e\en','\e"'" +.PP +Efficiently import data from a CSV file into a table when the file is +to be read by mclient (e.g. the server has no access to the file). +$file is the (absolute or relative) path name of the file, $table is +the name of the table, $db is the name of the database. +.PP +mclient -d $db -s "COPY INTO $table FROM STDIN USING DELIMITERS ',','\e\en','\e"'" - < $file +.PP +Note that in this latter case, if a count of records is supplied, it +should be at least as large as the number of records actually present +in the CSV file. This, because otherwise the remainder of the file +will be interpreted as SQL queries. +.PP +See http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto +for more information about the COPY INTO query. .SH SEE ALSO .IR msqldump (1) diff --git a/monetdb5/optimizer/Tests/roberto1.stable.out b/monetdb5/optimizer/Tests/roberto1.stable.out --- a/monetdb5/optimizer/Tests/roberto1.stable.out +++ b/monetdb5/optimizer/Tests/roberto1.stable.out @@ -20,7 +20,7 @@ barrier go := true; bat.setPersistent(a,false); transaction.commit(); # alter the values - a:bat[:oid,:dbl] := batcalc.+(a,1); + a:bat[:oid,:dbl] := batcalc.+(a,1:dbl); # store it to disk with the same name bat.setName(a,"P_bat"); bat.setPersistent(a,true); @@ -39,7 +39,7 @@ barrier go := true; a:bat[:oid,:dbl] := bbp.bind("P_bat");# 5 a:bat[:oid,:dbl] := CMDbbpbind(_7:str) {G} bat.setPersistent(a,false); # 6 _8:void := BKCpersists(a:bat[:oid,:dbl], _9:bit) {G} transaction.commit(); # 7 _10:bit := TRNglobal_commit() - a:bat[:oid,:dbl] := batcalc.+(a,1);# 8 a:bat[:oid,:dbl] := CMDbatADDcst_dbl_dbl_dbl(a:bat[:oid,:dbl], _12:dbl) {G} + a:bat[:oid,:dbl] := batcalc.+(a,1:dbl);# 8 a:bat[:oid,:dbl] := CMDbatADDcst_dbl_dbl_dbl(a:bat[:oid,:dbl], _12:dbl) {G} # store it to disk with the same name bat.setName(a,"P_bat"); # 10 _14:void := BKCsetName(a:bat[:oid,:dbl], _7:str) {G} bat.setPersistent(a,true); # 11 _15:void := BKCpersists(a:bat[:oid,:dbl], _4:bit) {G} diff --git a/monetdb5/optimizer/Tests/tst4820.stable.out b/monetdb5/optimizer/Tests/tst4820.stable.out --- a/monetdb5/optimizer/Tests/tst4820.stable.out +++ b/monetdb5/optimizer/Tests/tst4820.stable.out @@ -20,7 +20,7 @@ barrier go := true; bat.setPersistent(a,false); transaction.commit(); # alter the values - a:bat[:oid,:dbl] := batcalc.+(a,1); + a:bat[:oid,:dbl] := batcalc.+(a,1:dbl); # store it to disk with the same name bat.setName(a,"P_bat"); bat.setPersistent(a,true); @@ -40,7 +40,7 @@ barrier go := true; a:bat[:oid,:dbl] := bbp.bind("P_bat");# 5 a:bat[:oid,:dbl] := CMDbbpbind(_7:str) {G} bat.setPersistent(a,false); # 6 _8:void := BKCpersists(a:bat[:oid,:dbl], _9:bit) {G} transaction.commit(); # 7 _10:bit := TRNglobal_commit() - a:bat[:oid,:dbl] := batcalc.+(a,1);# 8 a:bat[:oid,:dbl] := CMDbatADDcst_dbl_dbl_dbl(a:bat[:oid,:dbl], _12:dbl) {G} + a:bat[:oid,:dbl] := batcalc.+(a,1:dbl);# 8 a:bat[:oid,:dbl] := CMDbatADDcst_dbl_dbl_dbl(a:bat[:oid,:dbl], _12:dbl) {G} # store it to disk with the same name bat.setName(a,"P_bat"); # 10 _14:void := BKCsetName(a:bat[:oid,:dbl], _7:str) {G} bat.setPersistent(a,true); # 11 _15:void := BKCpersists(a:bat[:oid,:dbl], _4:bit) {G} diff --git a/sql/backends/monet5/Tests/optimizers.sql b/sql/backends/monet5/Tests/optimizers.sql --- a/sql/backends/monet5/Tests/optimizers.sql +++ b/sql/backends/monet5/Tests/optimizers.sql @@ -39,4 +39,20 @@ set optimizer='optimizer.costModel();opt set optimizer='optimizer.costModel();optimizer.coercions();optimizer.emptySet();optimizer.accessmode();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.garbageCollector();'; +set optimizer='optimizer.inline();'; + +set optimizer='optimizer.inline();optimizer.multiplex();'; + +set optimizer='optimizer.inline();optimizer.multiplex();optimizer.deadcode();'; + +set optimizer='optimizer.inline();optimizer.deadcode();optimizer.multiplex();'; + +set optimizer='optimizer.deadcode();optimizer.inline();optimizer.multiplex();optimizer.garbageCollector();'; + +set optimizer='optimizer.inline();optimizer.deadcode();optimizer.garbageCollector();optimizer.multiplex();'; + +set optimizer='optimizer.inline();optimizer.multiplex();optimizer.deadcode();optimizer.garbageCollector();'; + +set optimizer='optimizer.inline();optimizer.deadcode();optimizer.multiplex();optimizer.garbageCollector();'; + select 'done'; diff --git a/sql/backends/monet5/Tests/optimizers.stable.err b/sql/backends/monet5/Tests/optimizers.stable.err --- a/sql/backends/monet5/Tests/optimizers.stable.err +++ b/sql/backends/monet5/Tests/optimizers.stable.err @@ -41,43 +41,60 @@ QUERY = set optimizer=''; ERROR = !Syntax error detected.See documentation for details MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.costModel();optimizer.coercions();optimizer.emptySet();optimizer.accessmode();optimizer.aliases();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.coercions();optimizer.emptySet();optimizer.accessmode();optimizer.aliases();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.emptySet();optimizer.accessmode();optimizer.aliases();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.accessmode();optimizer.aliases();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.aliases();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.garbageCollector();'; -ERROR = !SQLException:optimizer:Optimizer 'garbageCollector' pipeline does not exist, use default pipe instead +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.costModel();optimizer.coercions();optimizer.emptySet();optimizer.accessmode();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.reduce();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first MAPI = monetdb@sofia:38256 QUERY = set optimizer='optimizer.costModel();optimizer.coercions();optimizer.emptySet();optimizer.accessmode();optimizer.commonTerms();optimizer.accumulators();optimizer.joinPath();optimizer.deadcode();optimizer.garbageCollector();'; -ERROR = !SET OPTIMIZER: 'inline' should be the first +ERROR = !'inline' should be the first +QUERY = set optimizer='optimizer.inline();'; +ERROR = !'multiplex' should be used +MAPI = monetdb@rome:38178 +QUERY = set optimizer='optimizer.inline();optimizer.multiplex();'; +ERROR = !'deadcode' should be used at least once +MAPI = monetdb@rome:38178 +QUERY = set optimizer='optimizer.inline();optimizer.multiplex();optimizer.deadcode();'; +ERROR = !'garbageCollector' should be used as the last one +MAPI = monetdb@rome:38178 +QUERY = set optimizer='optimizer.inline();optimizer.deadcode();optimizer.multiplex();'; +ERROR = !'garbageCollector' should be used as the last one +MAPI = monetdb@rome:38178 +QUERY = set optimizer='optimizer.deadcode();optimizer.inline();optimizer.multiplex();optimizer.garbageCollector();'; +ERROR = !'inline' should be the first +MAPI = monetdb@rome:38178 +QUERY = set optimizer='optimizer.inline();optimizer.deadcode();optimizer.garbageCollector();optimizer.multiplex();'; +ERROR = !'garbageCollector' should be used as the last one # 03:16:17 > # 03:16:17 > "Done." diff --git a/sql/server/rel_bin.c b/sql/server/rel_bin.c --- a/sql/server/rel_bin.c +++ b/sql/server/rel_bin.c @@ -2191,6 +2191,7 @@ rel2bin_predicate(mvc *sql) static stmt * rel2bin_hash_lookup( mvc *sql, sql_rel *rel, stmt *sub, sql_idx *i, node *en ) { + node *n; sql_subtype *it = sql_bind_localtype("int"); sql_subtype *wrd = sql_bind_localtype("wrd"); stmt *h = NULL; @@ -2199,8 +2200,8 @@ rel2bin_hash_lookup( mvc *sql, sql_rel * sql_exp *l = e->l; stmt *idx = bin_find_column(sql->sa, sub, l->l, sa_strconcat(sql->sa, "%", i->base.name)); - /* TODO should be in key order! */ - for( en = rel->exps->h; en; en = en->next ) { + /* should be in key order! */ + for( en = rel->exps->h, n = i->columns->h; en && n; en = en->next, n = n->next ) { sql_exp *e = en->data; stmt *s = NULL; @@ -2229,7 +2230,7 @@ rel2bin_select( mvc *sql, sql_rel *rel, { list *l; node *en, *n; - stmt *sub = NULL, *sel = NULL; + stmt *sub = NULL, *sel = NULL, *s = NULL; stmt *predicate = NULL; if (!rel->exps) { @@ -2259,26 +2260,26 @@ rel2bin_select( mvc *sql, sql_rel *rel, if ((p=find_prop(e->p, PROP_HASHIDX)) != NULL) { sql_idx *i = p->value; - sel = rel2bin_hash_lookup(sql, rel, sub, i, en); + s = rel2bin_hash_lookup(sql, rel, sub, i, en); } } - if (!sel) { - sel = stmt_relselect_init(sql->sa); - for( en = rel->exps->h; en; en = en->next ) { - /*stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, sel);*/ - stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, NULL); - - if (!s) { - assert(0); - return NULL; - } - if (s->nrcols == 0){ - if (!predicate) - predicate = rel2bin_predicate(sql); - predicate = stmt_select(sql->sa, predicate, s, cmp_equal); - } else { - stmt_relselect_fill(sel, s); - } + sel = stmt_relselect_init(sql->sa); + if (s) + stmt_relselect_fill(sel, s); + for( en = rel->exps->h; en; en = en->next ) { + /*stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, sel);*/ + stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, NULL); + + if (!s) { + assert(0); + return NULL; + } + if (s->nrcols == 0){ + if (!predicate) + predicate = rel2bin_predicate(sql); + predicate = stmt_select(sql->sa, predicate, s, cmp_equal); + } else { + stmt_relselect_fill(sel, s); } } diff --git a/sql/test/BugTracker-2012/Tests/All b/sql/test/BugTracker-2012/Tests/All --- a/sql/test/BugTracker-2012/Tests/All +++ b/sql/test/BugTracker-2012/Tests/All @@ -18,3 +18,4 @@ correlated_groupby_in_selection.Bug-3011 multicolumn_join.Bug-2998 simple_select.Bug-2988 interval_timestamp.Bug-3002 +hash_select.Bug-2947 diff --git a/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.sql b/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.sql @@ -0,0 +1,32 @@ +START TRANSACTION; +SET SCHEMA "sys"; +CREATE TABLE "sys"."c__has_a" ( + "owner_table" VARCHAR(32672), + "owner_id" BIGINT, + "relation_name" VARCHAR(32672), + "property_table" VARCHAR(32672), + "property_id" BIGINT, + "property_class" VARCHAR(32672) +); +CREATE INDEX "c__has_a_property_index" ON "sys"."c__has_a" ("property_table", "property_id"); +COPY 1 RECORDS INTO "sys"."c__has_a" FROM stdin USING DELIMITERS '\t','\n','"'; +NULL NULL NULL "SIMPLE" 4 "simpleclass" +COMMIT; + +select * from c__has_a where owner_table is null and property_table='SIMPLE' and property_id = 4; + +DROP table c__has_a; + + + +CREATE TABLE FOO ( + "a" VARCHAR(8), + "b" VARCHAR(8), + "c" BIGINT); +CREATE INDEX FOO_INDEX ON FOO ("b","c"); + +insert into foo (b,c)values('foo',3); + +select * from foo where a is null and b='foo' and c=3; + +DROP TABLE FOO; diff --git a/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.stable.err b/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2012/Tests/hash_select.Bug-2947.stable.err @@ -0,0 +1,37 @@ _______________________________________________ Checkin-list mailing list [email protected] http://mail.monetdb.org/mailman/listinfo/checkin-list
