Bugs item #2658110, was opened at 2009-03-03 18:27
Message generated for change (Tracker Item Submitted) made by Item Submitter
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")
);
----------------------------------------------------------------------
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
