[
https://issues.apache.org/jira/browse/CONNECTORS-110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13042655#comment-13042655
]
Karl Wright commented on CONNECTORS-110:
----------------------------------------
HSQLDB is now also in roughly the same situation, although I've gotten a rough
outline of a way to make this work involving temporary tables. This is as
follows:
SELECT * FROM (SELECT DISTINCT customerid FROM invoice) AS i_one,
LATERAL ( SELECT id, total FROM invoice WHERE customerid =
i_one.customerid ORDER BY total DESC LIMIT 1) AS i_two
... where "invoice" would be a temporary table created on the fly, as follows:
DECLARE LOCAL TEMPORARY TABLE T AS (SELECT statement) [ON COMMIT {
PRESERVE | DELETE } ROWS]
For example:
DECLARE LOCAL TEMPORARY TABLE invoice AS (SELECT * FROM whatever) ON COMMIT
DELETE ROWS WITH DATA
then perform the kind of query I suggested.
The issue is that this does not fit in a our single-query abstraction metaphor
at all. Maybe a (different but identically named) stored procedure could be
generated on all three databases that would do the trick. Alternatively, all
databases could go the temporary table route, but then PostgreSQL would be
unnecessarily crippled.
> Max activity and Max bandwidth reports don't work properly under Derby
> ----------------------------------------------------------------------
>
> Key: CONNECTORS-110
> URL: https://issues.apache.org/jira/browse/CONNECTORS-110
> Project: ManifoldCF
> Issue Type: Bug
> Components: Framework crawler agent
> Reporter: Karl Wright
>
> The reason for the failure is because the queries used are doing the
> Postgresql DISTINCT ON (xxx) syntax, which Derby does not support.
> Unfortunately, there does not seem to be a way in Derby at present to do
> anything similar to DISTINCT ON (xxx), and the queries really can't be done
> without that.
> One option is to introduce a getCapabilities() method into the database
> implementation, which would allow ACF to query the database capabilities
> before even presenting the report in the navigation menu in the UI. Another
> alternative is to do a sizable chunk of resultset processing within ACF,
> which would require not only the DISTINCT ON() implementation, but also the
> enclosing sort and limit stuff. It's the latter that would be most
> challenging, because of the difficulties with i18n etc.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
