Issue 115165 - Base's SQL parser doesn't recognize NULL in function argument list anymore
Summary: Base's SQL parser doesn't recognize NULL in function argument list anymore
Alias: None
Product: Base
Classification: Application
Component: MySQL Connector/OOo (show other issues)
Version: OOO330m9
Hardware: Unknown All
: P3 Trivial with 1 vote (vote)
Target Milestone: OOo 3.3
Assignee: marc.neumann
QA Contact: issues@dba
Keywords: oooqa, regression
Depends on:
Blocks: 111112
  Show dependency tree
Reported: 2010-10-21 03:11 UTC by jgsylvester
Modified: 2010-10-31 13:41 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Blood Sugars.odb (401.27 KB, application/vnd.sun.xml.base)
2010-10-21 08:18 UTC, jgsylvester
no flags Details
Correct.pgn screenshot of good results from query. (251.59 KB, image/png)
2010-10-21 10:43 UTC, jgsylvester
no flags Details
Incorrect.pgn - results from query when NULL is replaced by 0 (253.76 KB, image/png)
2010-10-21 10:47 UTC, jgsylvester
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jgsylvester 2010-10-21 03:11:54 UTC
I upgraded to OpenOffice.og 3.3.0 Release Candidate 1 today 10/20/2010. In
verifying my existing databases worked correctly (or as expected), SQL queries
involving the "CASEWHEN" construct no longer worked. I would get a SQL syntax
error. I removed 3.3, and reinstalled 3.2.1, and the queries worked correctly.
In installing OOO 3.3.0, I removed OOO 3.2.1 completely.
Comment 1 r4zoli 2010-10-21 07:09:31 UTC
Could you add an example file to this issue?
Comment 2 Frank Schönheit 2010-10-21 07:46:53 UTC
cannot reproduce using something like
  SELECT "n", casewhen( mod( "n", 2 ) = 1, 1, 0 ) AS "odd" FROM "table"
This works fine in OOo 3.2.1 and OOO330.m11.

So, the queries which cause you problems are obviously more complex than this :).

Care to attach a sample document?
Comment 3 jgsylvester 2010-10-21 08:16:29 UTC
I eill attach my database Blood Sugars. There are a lot of queries, most look
complex. Check out any of the queries labeled "NEW" or "NEWER". I could not get
a simple casewhen to work.
Comment 4 jgsylvester 2010-10-21 08:18:14 UTC
Created attachment 72125 [details]
Blood Sugars.odb
Comment 5 jgsylvester 2010-10-21 08:43:17 UTC
It seems that my queries that are failing are due to NULL not being valid and/or
not recognized in the CASEWHEN. I changed the NULL to 0 and the query "NEW
Averages By Month" ran corrrectly.
Comment 6 Frank Schönheit 2010-10-21 08:59:08 UTC
confirming - it seems NULL is not recognized anymore by our parser.

Adding "regression" keyword, assigning to developer.

I don't think we'd get 3.3 blocker approval for this, the more since there is a
workaround, and the fix would probably be somewhat risky. Targeting to 3.4 thus.
Comment 7 jgsylvester 2010-10-21 10:13:06 UTC
Just for the record, replacing NULL with 0 in my queries allows the queries to
run without parse error, but the results are not equivalent. What is the
Comment 8 Frank Schönheit 2010-10-21 10:25:21 UTC
oh, sorry - I just noticed that even in 3.2, NULL effectively delivers "0", and
without looking deeper into it, it seemed to me that replacing NULL with 0 in
the SQL statement in fact *did* deliver the same results. So, I thought that
this is the workaround - sorry for not checking in detail.

Which query is it which doesn't deliver the same results, and how does that look
like in detail?
Comment 9 jgsylvester 2010-10-21 10:41:19 UTC
I am attaching a screenshot of what the query "NEW Averages By Month" should
look like. Then I will send a screenshot of what it looks like with NULL being
replaced as 0.
Comment 10 jgsylvester 2010-10-21 10:43:27 UTC
Created attachment 72128 [details]
Correct.pgn screenshot of good results from query.
Comment 11 jgsylvester 2010-10-21 10:46:29 UTC
Here is screenshot of query results when NULL is replaced by 0.
Comment 12 jgsylvester 2010-10-21 10:47:46 UTC
Created attachment 72129 [details]
Incorrect.pgn - results from query when NULL is replaced by 0
Comment 13 Frank Schönheit 2010-10-21 10:52:09 UTC
Okay, I withdraw my statement 'bout the workaround :(
Comment 14 ocke.janssen 2010-10-21 11:50:56 UTC
I found a work around which you could use. You have to replace the casewhen()
function call by the SQL syntax or case when like

SELECT MONTH( "DATE" ) AS "Month", AVG( CASE when "MEAL" = '1' then "BG" else
NULL end) AS "Breakfast", AVG( CASE when "MEAL" = '2' then "BG" else NULL end )
AS "Lunch", AVG( CASE WHEN  "MEAL" = '3' then "BG" else NULL end ) AS "Dinner",
AVG( CASE WHEN "MEAL" = '4' then "BG" else NULL end ) AS "Bed", AVG( "BG" * 1.0
) AS "Daily Average" FROM "BG" WHERE YEAR( "DATE" ) =
:Enter_Year_Desired_as_YYYY GROUP BY MONTH( "DATE" )

But it is still an issue to allow NULL as function parameter.
Comment 15 jgsylvester 2010-10-21 12:01:40 UTC
Are you sure this works? I copied your workaround into the database, and I got a
syntax error just as before. I am currently working back on OOo 3.2.1 on Ubuntu.
I can try this query on Windows which is at OOo 3.3.0. 
Comment 16 jgsylvester 2010-10-21 12:13:07 UTC
I verified your workaround does indeed work in OOo 3.3.0, but not OOo 3.2.1. I
am not sure why.
Comment 17 ocke.janssen 2010-10-21 12:16:54 UTC
case when is implemented in 3.3 only.
Comment 18 Frank Schönheit 2010-10-22 08:59:31 UTC
adjusting summary
Comment 19 mdxonefour 2010-10-22 11:31:14 UTC
adjusting target
Comment 20 ocke.janssen 2010-10-25 07:57:52 UTC
Fixed in cws dba33l
Comment 21 ocke.janssen 2010-10-26 10:50:28 UTC
Please verify. Thanks.
Comment 22 marc.neumann 2010-10-26 12:46:32 UTC
verified in cws dba33l
Comment 23 r4zoli 2010-10-31 13:41:06 UTC
Checked in OOo 3.3 RC3 (OOO330m13), OK.