Bugs item #2658110, was opened at 2009-03-03 18:27
Message generated for change (Comment added) made by cornuz
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2658110&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL "stable"
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Roberto Cornacchia (cornuz)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: mitosis optimizer + insert into
Initial Comment:
So far I could not come up with a simple test that can reproduce this bug,
I will for now just describe it.
When mitosis optimizer is activated, the INSERT INTO in the query below reports
that 0 tuples were inserted.
However, the SELECT alone does return 752 tuples.
Many other INSERT INTO queries in the same session work correctly, I'm trying
to figure out what makes this special, but couldn't so far.
Here the active optimizers:
sql>select optimizer;
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| single_value
|
+==========================================================================================================================================================+
|
inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,constants,commonTerms,joinPath,deadcode,reduce,garbageCollector,dataflow,h
|
: istory,multiplex
:
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 tuple
And the query that fails:
INSERT INTO sec_references_doc (secID, docID)
SELECT secDict.secID, docDict.docID
FROM secDict, docDict, sec_string, doc_string
WHERE sec_string.attribute = 'docid'
AND doc_string.attribute = 'docid'
AND sec_string.value = doc_string.value
AND sec_string.secID = secDict.secID
AND doc_string.docID = docDict.docID
AND secDict.type = 'patcit'
GROUP BY secDict.secID, docDict.docID;
with:
sql>\d sec_references_doc
CREATE TABLE "sys"."sec_references_doc" (
"secid" int,
"docid" int,
"prob" double DEFAULT 1,
CONSTRAINT "sec_references_doc_docid_fkey" FOREIGN KEY ("docid")
REFERENCES "docdict" ("docid"),
CONSTRAINT "sec_references_doc_secid_fkey" FOREIGN KEY ("secid")
REFERENCES "secdict" ("secid")
);
sql>\d secdict
CREATE TABLE "sys"."secdict" (
"secid" int NOT NULL DEFAULT next value for "sys"."seq_4215",
"sec" varchar(255),
"type" varchar(255),
"prob" double DEFAULT 1,
CONSTRAINT "secdict_secid_pkey" PRIMARY KEY ("secid")
);
sql>\d docdict
CREATE TABLE "sys"."docdict" (
"docid" int NOT NULL DEFAULT next value for "sys"."seq_4205",
"doc" varchar(255),
"type" varchar(255),
"prob" double DEFAULT 1,
CONSTRAINT "docdict_docid_pkey" PRIMARY KEY ("docid")
);
sql>\d sec_string
CREATE TABLE "sys"."sec_string" (
"secid" int,
"attribute" varchar(255),
"value" varchar(255),
"prob" double DEFAULT 1,
CONSTRAINT "sec_string_secid_fkey" FOREIGN KEY ("secid") REFERENCES
"secdict" ("secid")
);
sql>\d doc_string
CREATE TABLE "sys"."doc_string" (
"docid" int,
"attribute" varchar(255),
"value" varchar(255),
"prob" double DEFAULT 1,
CONSTRAINT "doc_string_docid_fkey" FOREIGN KEY ("docid") REFERENCES
"docdict" ("docid")
);
----------------------------------------------------------------------
>Comment By: Roberto Cornacchia (cornuz)
Date: 2009-03-04 10:39
Message:
Hope this can help. I have run the same query with different optimizer
combinations.
Starting from the one relative to this bug report (default + mitosis),
then removing one optimizer at the time.
The query becomes correct (i.e. the INSERT INTO does insert tuples)
when 'aliases' is removed.
However, when trying the combination (default + mitosis - aliases), I hit
an assertion.
Details below.
-- incorrect (default + mitosis)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,constants,commonTerms,joinPath,deadcode,reduce,garbageCollector,dataflow,history,multiplex';
-- incorrect (garbageCollector removed)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex';
-- incorrect (joinPath removed)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,constants,commonTerms,deadcode,reduce,dataflow,history,multiplex';
-- incorrect (commonTerms removed)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,constants,deadcode,reduce,dataflow,history,multiplex';
-- incorrect (constants removed)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,deadcode,reduce,dataflow,history,multiplex';
-- incorrect (deadcode removed)
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,reduce,dataflow,history,multiplex';
-- incorrect (coercions removed)
set
optimizer='inline,remap,evaluate,costModel,emptySet,mitosis,aliases,mergetable,reduce,dataflow,history,multiplex';
-- incorrect (emptySet removed)
set
optimizer='inline,remap,evaluate,costModel,mitosis,aliases,mergetable,reduce,dataflow,history,multiplex';
-- CORRECT (aliases removed)
set
optimizer='inline,remap,evaluate,costModel,mitosis,mergetable,reduce,dataflow,history,multiplex';
-- ASSERTION (default + mitosis - aliases):
src/mal/mal_interpreter.mx:1077: DFLOWexecute: Assertion `queued || pc ==
limit' failed.
set
optimizer='inline,remap,evaluate,costModel,coercions,emptySet,mitosis,mergetable,constants,commonTerms,joinPath,deadcode,reduce,garbageCollector,dataflow,history,multiplex';
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2009-03-03 18:39
Message:
Indeed, you walk ahead of the core team. We are currently actively testing
the mitosis/mergetable/dataflow combination.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2658110&group_id=56967
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
