Re: Subqueries in CLSQL

Hi Bill,

> I have been working through Nick Levine's ILC2002 tutorial examples for
> CommonSQL. Most of them work fine in CLSQL; however, the following
> subqueries fail in CLSQL. Are subqueries a known area of non-compliance
> with CommonSQL's API?

I'm assuming that you are using MySQL which has a few quirks which are
causing these errors (see below). These will be documented in the clsql
manual at some point (I'm currently working on this) but for the time
being the file doc/TODO contains some notes on things we plan to document
including clsql extensions, idiosyncrasies of the different RDBMS
supported and differences from CommonSQL.

> (select ["table" LocalName] ["table" ScientificName]
>         :from '([SpeciesList "table"] [SpeciesList "join"])
>         :where [and [= ["table" ScientificName]
>                        ["join" ScientificName]]
>                     [not  [= ["table" SpeciesID]
>                              ["join" SpeciesID]]]]
>         :order-by '(["table" ScientificName]))

MySQL doesn't support string aliases unless the server is started in ansi
mode which (among other things) enable ANSI_QUOTES. The CLSQL test
:fdml/select/26 is based on the above example but is skipped for MySQL for
this reason. It runs fine on the other backends.

Also MySQL (prior to version 4.1 I think) doesn't support subqueries which
accounts for the remaining errors. The other supported RDBMSs do support
subqueries (although I think sqlite's subquery support is limited).

Cheers,
Marcus