Hello Thomas,
The PostGreSQL optimisation is definitely needed for v0.8 and there is a
indeed some way to go. I can see the bug and how the table name is
reading the internal name of database. Just in case, i am missing
something, did you create the whole schema from scratch using
martconfigurator with no hooks/hacks anywhere ? I wonder if you quickly
emulate the same scenario without any detailed alterations to any
preferred names to schema in MartConfigurator, it reproduces the same bug ?
thanks
Syed
On 13/01/2012 17:06, Thomas TRIPLET wrote:
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
