Re: [dba-dev] unexpected behavior with HSQL embedded DB case when and coalese commands

[Andrew Jensen]

One latst piece of information before I let this go for the moment. Some of the examples of when you must use Column names, and when you MUST have Escape Processing OFF is dependent not only on theHSQL statement but on WHICH data type is passed in. The date to string functions are this way. For example DAYOFTHEMONTH With the test table T1(ID IDENTITY, DT DATE,TI TIME,TS TIMESTAMP) INSERT INTO T1 (ID, DT, TI, TS) VALUES (NULL, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP); Issue this with EscapeProcessing OFF and the result is incorrect, NULL value returned. SELECT DAYOFMONTH(DT)  FROM T1 Turn EscapeProcessing ON and the result is correct with a column named "_expression_ 1" However, change the selected column from DATE to TIMESTAMP SELECT DAYOFMONTH(TS)  FROM T1 andt EscapeProcessing OFF  - result is incorrect, NULL value returned. Turn EscapeProcessing ON - ERROR in parsing statemtnt - you MUST explicitly name the column in the resultset. I'll go ahead an make compendium of the results as I plow thru all the tests supplies with the HSQL 1.8.0 distribution before I get back to you on this again. Andrew Andrew Jensen wrote: Hallo Frank No issue for now then. Perhaps it is more fitting in user faq document.anyway. Andrew Frank Schönheit - Sun Microsystems Germany wrote: Hi Andrew. Change the select statement to SELECT CASE DATA WHEN 'xxxx' THEN 'X' WHEN 'yyyy' THEN 'Y' WHEN 'zzzz' THEN 'Z' ELSE 'NOTFOUND' END AS "DATA" FROM TESTCASE It still requires that EscapeProcessing = false. The HSQLDB engine is capable of more things that OOo's cross-DB parser, and SELECT CASE is not part of the latter. With the change (explicity setting the return columns name ) it works correctly using a RowSet object via the API, and as a View also. Still, is this an issue I should enter? Hmm, not sure without looking deeper into it. Might be possible that we can make OOo tolerant enough to also properly execute the AS-less query, so fel free to submit an issue (though it would probably be P4), so we can investigate. Thanks & Ciao Frank