Postgres - metadata - ad hoc reporting issues

[dibleya]

Hi All

Have a problem with the generated MQL statements generated with the MQL query editor and the ad hoc reporting tool on the Pentaho server.

To explain:
An SQL query like this works:

SELECT
"BT_ORDERS"."Customer" AS "COL0"
FROM
"Processing_Orders" "BT_PROCESSING_ORDERS"
but this does not:

SELECT
"BT_ORDERS"."Customer" AS "COL0"
FROM
"Processing_Orders" "BT_PROCESSING_ORDERS"
ORDER BY
COL0

The error reported on MQL query editor is:
ERROR: column "col0" does not exist
and the error on the server is:
AdhocWebService.ERROR_0012
and
Could not execute InMemorySolution
org.postgresql.util.PSQLException: ERROR: column "col0" does not exist...

I think that the problem here is that the MQL generator does not quote the COL0 alias which upsets postgres. From experimenting you can make the second query work by either quoting the COL0 in the ORDER BY or un-quoting the COL0 in the AS statement.
Unfortunatelythis makes it imposable to do any sorting and grouping of the data.
The connection I am using is JNDI, I have the "quote all" enabled.

Has any one else experienced this problem with postgres?

Many thanks in advance
AJ

[dibleya]

It seems that in Postgres if you create tables that have mixed or upper case names, these tables will have to be quoted in all queries, even the aliases. In other words you have to quote everything!. But if tables are created if only lower case names no quotes are needed at all.

So since I can not modify the database names (as too many applications depend on it), I created views and column aliases all in lower case and hey presto metadata editor and the ad-hoc report tool have no problems on these views.
You will have to turn off "quote all in database".

I lot of work but at least it works.
Cheers
Aj

[wgorman]

Another potential solution is to use the quote all identifiers option in the advanced section of the database connection dialog, or the force all identifiers to upper case. Give those a shot and see if they work.

Will

[dibleya]

Thanks for the reply;

I have tried your suggestion but unfortunately I still have the same issue. Using force upper case and lower case I can not import tables as the Postgres complains that it cannot find the table. The same happens when I use the combination of forcing case and quote all in database.

If I use quote all without forcing case, I can import tables but any query that groups or sorts data will fail, as the query does not quote the column aliases on the ORDER by or SORT by. So it seems that the quote all does not in this case, quote all.

Cheers

[wgorman]

Hi AJ,

I confirmed that this is a bug, and also created a JIRA case here:
http://jira.pentaho.com/browse/PMD-528

Feel free to vote and follow the issue.

Will