Two parts - Union + Order by query issue - Why can't I use EXEC DIRECT all the time?

Hi,

I'm using a version of GridSQL updated from the CVS source tree around May 26th.

I am running into an issue with a query executing successfully in Postgres and not on GridSQL - it works fine with "exec direct on all" - I had a question - Why can't I just run the query using "EXEC DIRECT on ALL '....'" via JDBC from my program - all it seems to do is to add an extra "Node" column to each row in the result set. This works fine in my tests with simple queries.

Is there any inherent problem with this approach? If the query cannot be supported by GridSQL, I would like to use 'exec direct on all' for a particular scenario within my application where I know the data is distributed across all the nodes (and the required Dimension join tables are replicated on all nodes) - Any reason why I shouldn't do this? The query will be limited using the LIMIT clause so there is no reason for huge numbers of rows to be returned to GridSQL by each node.

The "EXEC DIRECT on all" option would allow me to escape having to worry about syntax dependencies on GridSQL - However, probably I will need to send each query as a String literal (without using parameters) - I don't seem to be able to specify JDBC parameters to a prepared statement when using EXEC DIRECT.

It would ideal of course if the query worked with GridSQL and I didn't have to worry about the Exec Direct part.

Incidentally the query in question is:

I am trying to use this query to work around a Postgres bug with handling order by + limit clauses when dealing with partitioned tables - (In this scenario both t2 and t3 are child tables of a parent table) - Similar issue to that faced by the person here: http://stackoverflow.com/questions/2236776/efficient-querying-of-multi-partition-postgres-table

Here is the exception shown while executing the above query in the GridSQL console log:

2010-07-07 16:06:17,600 - ERROR Catching throwable:
com.edb.gridsql.parser.core.ParseException: Encountered "ORDER BY" at line 1, column 56.
Was expecting one of:
    "UNION" ...
    <GROUP_BY_> ...
    "AND" ...
    "OR" ...
    "!" ...
    "HAVING" ...
    ")" ...
    "::" ...
    "+" ...
    "-" ...
    "||" ...
    "*" ...
    "/" ...
    "MOD" ...
    "DIV" ...
    "%" ...
    "&" ...
    "|" ...
    "#" ...
    "<<" ...
    ">>" ...
    "<<=" ...
    ">>=" ...
    "(+)" ...

at com.edb.gridsql.parser.core.CSQLParser.generateParseException(CSQLParser.java:38089)
        at com.edb.gridsql.parser.core.CSQLParser.jj_consume_token(CSQLParser.java:37927)
        at com.edb.gridsql.parser.core.CSQLParser.TableSpec(CSQLParser.java:22468)
        at com.edb.gridsql.parser.core.CSQLParser.FromTableSpec(CSQLParser.java:22603)
        at com.edb.gridsql.parser.core.CSQLParser.TableList(CSQLParser.java:22517)
        at com.edb.gridsql.parser.core.CSQLParser.FromClause(CSQLParser.java:22183)
        at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderAndSet(CSQLParser.java:12678)
        at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderAndSetWithParenthesis(CSQLParser.java:12566)
        at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrder(CSQLParser.java:12503)
        at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderWithParenthesis(CSQLParser.java:12483)
        at com.edb.gridsql.parser.core.CSQLParser.Select(CSQLParser.java:12446)
        at com.edb.gridsql.parser.core.CSQLParser.SelectWithParenthesis(CSQLParser.java:12430)
        at com.edb.gridsql.parser.core.CSQLParser.process(CSQLParser.java:2353)
        at com.edb.gridsql.parser.Parser.parse(Parser.java:611)
        at com.edb.gridsql.parser.Parser.parseStatement(Parser.java:85)
        at com.edb.gridsql.protocol.RequestAnalyzer.getExecutableRequest(RequestAnalyzer.java:149)
        at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:613)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:637)

My table structures:

Sorry, ORDER BY and LIMIT are currently not supported in subqueries, just in the top query. There is a large overlap of PostgreSQL and GridSQL SQL support, but not everything is supported.

Regards,

Mason