Bugs item #2658110, was opened at 2009-03-03 18:27
Message generated for change (Settings changed) made by stmane
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: Roberto Cornacchia (cornuz)
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: Stefan Manegold (stmane)
Date: 2009-08-11 11:39
Message:
Roberto,
could you please provide some sample data that used to give incorrect
result with your query and different optimizer settings?
Thanks!
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2009-08-07 09:42
Message:
I guess by all changes this got fixed, but I cannot test this as there is
no example data available. Could you add an example query which shows the
problem
----------------------------------------------------------------------
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
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
