Hello,
I'd like to report what looks like a bug. I'm trying to join 2 datasets,
in particular, I'd like to display the data provenance associated with some
InterPro entry Those 2 pieces of information are stored in 2 different
virtual marts (both based on a postgresql connexion).
The (simplified) source schema is for InterPro:
* IprEntry*
iprEntryWID (PK)
name
This table corresponds to virtual mart "*IprEntry*".
For data provenance, I have 2 tables:
* WID2DataFile*
recordWID (PK) (FK -> IprEntry.iprEntryWID)
dataFileWID (PK) (FK -> DataFile.dataFileWID)
* DataFile*
dataFileWID (PK)
filename
Those 2 table are gather in the target schema and part of the "Database"
virtual mart.
The link between the 2 virtual marts was properly created. I created a
simple access point to test the join (note that *IprEntryWID* and *recordWID
* should be equal in the resulting table):
iprEntryWID, name, recordWID
When displayed, it shows an error ("Error has occurred"). The log is more
informative and reveals that something is definitely wrong with the
underlying SQL queries. 2 queries are generated:
SELECT "biomart"."interpro"."IprEntry"."iprEntryWID"
FROM "biomart"."interpro"."IprEntry"
WHERE "biomart"."interpro"."IprEntry"."iprEntryWID" = '219810'
The usefulness of this query is arguable, but at least it is correct. The
second SQL query is:
SELECT "fungi"."provenance"."WID2DataFile"."recordWID",
"fungi"."provenance"."WID2DataFile"."recordWID"
FROM "fungi"."provenance"."Database"
First, the query doesn't seem very optimized (*recordWID* is requested
twice). More importantly, as suggested by the SELECT clause, recordWID is
part of the *WID2DataFile* table. Yet, the FROM clause is *
"fungi"."provenance"."Database"*, which seems to be based on the name of
the virtual mart, not the name of the actual underlying table. This
generates an exception (org.postgresql.util.PSQLException: ERROR: missing
FROM-clause entry for table "WID2DataFile").
Hope this helps reproduce and fix the bug.
Best,
Thomas
Thomas Triplet, Ph.D.
http://www.thomastriplet.net
Centre for Structural and Functional Genomics
Concordia University
7141 West Sherbrooke St
Montreal QC H4B 1R6
_______________________________________________
Users mailing list
[email protected]
https://lists.biomart.org/mailman/listinfo/users
