Problem with placeholders in complex union

[ray1729]

I'm getting this exception when trying to execute a query against MySQL:

SQLException:
   Message: No value specified for parameter 6
   SQLState: 07001
   Error Code: 0

This is triggered by the query:

(defn fetch-gene-by-external-name
  [name]
  (let [base-query (-> (table :gene)
                       (select (where (= :gene.is_current 1)))
                       (join (table :gene_stable_id) :gene_id)
                       (join (table :seq_region)
                             (where (= :gene.seq_region_id :seq_region.seq_region_id)))
                       (join (table :coord_system)
                             (where (and (= :coord_system.coord_system_id :seq_region.coord_system_id)
                                         (= :coord_system.species_id *species-id*))))
                       (join (table :object_xref)
                             (where (and (= :object_xref.ensembl_id :gene.gene_id)
                                         (= :object_xref.ensembl_object_type "Gene"))))
                       (join (table :xref)
                             (where (= :xref.xref_id :object_xref.xref_id))))
        query1 (-> base-query
                   (select (where (or (= :xref.dbprimary_acc name)
                                      (= :xref.display_label name))))
                   (project [:gene.*, :gene_stable_id.stable_id])
                   (distinct))
        query2 (-> base-query
                   (join (table :external_synonym)
                         (where (= :external_synonym.xref_id :object_xref.xref_id)))
                   (select (where (= :external_synonym.synonym name)))
                   (project [:gene.*, :gene_stable_id.stable_id])
                   (distinct))]
    (union query1 query2)))

I'm not sure if this is a bug or due to my (mis)use of union - if the latter, please point me in the right direction. The complete code is here: https://github.com/ray1729/Bio-Ensembl/blob/master/src/ray1729/bio/ensembl/core.clj. It's querying a public database so you should be able to reproduce the problem.

[LauJensen]

Please include the generated SQL in your issue report

[ray1729]

This is the generated SQL:

(SELECT DISTINCT gene.*,gene_stable_id.stable_id 
 FROM gene 
 JOIN gene_stable_id USING(gene_id) 
 JOIN seq_region ON (gene.seq_region_id = seq_region.seq_region_id) 
 JOIN coord_system ON ((coord_system.coord_system_id = seq_region.coord_system_id) AND (coord_system.species_id = 1)) 
 JOIN object_xref ON ((object_xref.ensembl_id = gene.gene_id) AND (object_xref.ensembl_object_type = Gene)) 
 JOIN xref ON (xref.xref_id = object_xref.xref_id) 
 JOIN external_synonym ON (external_synonym.xref_id = object_xref.xref_id) 
 WHERE (gene.is_current = 1) AND (external_synonym.synonym = Cbx1)) 
UNION 
(SELECT DISTINCT gene.*,gene_stable_id.stable_id 
 FROM gene 
 JOIN gene_stable_id USING(gene_id) 
 JOIN seq_region ON (gene.seq_region_id = seq_region.seq_region_id) 
 JOIN coord_system ON ((coord_system.coord_system_id = seq_region.coord_system_id) AND (coord_system.species_id = 1)) 
 JOIN object_xref ON ((object_xref.ensembl_id = gene.gene_id) AND (object_xref.ensembl_object_type = ?)) 
 JOIN xref ON (xref.xref_id = object_xref.xref_id) 
 WHERE (gene.is_current = ?) AND ((xref.dbprimary_acc = ?) OR (xref.display_label = ?)))

[ray1729]

Hi,

Just wondering if you had any insights into this problem, or if there's another forum where I might get some help?

Ray.

[LauJensen]

Hi,

Its a complex issue and Im struggeling to find out whats wrong with the generated SQL statement. I'll let you know once I know.

In the meantime you could try either posting on the Clojure Google Group or visit #clojureql on irc.freenode.net, but Im afraid that Im your best bet right now and unfortunately Im a bit busy :(

Lau

[ray1729]

Hi Lau,

Thank you for looking into the problem. I can put this project on hold for a while - really it was just an excuse to play with clojureql, which looks like a really nice way to interact with relational databases. Do let me know if I can do anything to help, but I still consider myself a beginner so debugging such complex problems is probably beyond my reach.

Ray.

[ariel123]

Hi Lau,

I'm facing a similar problem with the placeholders using union, refer to issue #93 with sql example. Is there a work around this bug?

Ariel

[bendlas]

Duplicates of this bug are #91 and #93

[LauJensen]

Fixed in HEAD