[OPEN-ILS-DEV] open-ils.cstore.json_query and HAVING clauses

[Jeff Godin]

Greetings-

First off, a huge thank you to Scott and whoever else was involved in
the production of "JSON Queries: A Tutorial", found in
docs/TechRef/JSONTutorial.xml

I think I've found a bug in cstore when using GROUP BY with a HAVING
clause. After a discussion with Mike Rylander, this might be less of a
bug and more a case of "incomplete support for HAVING clauses".

Using an example from the tutorial:

	{
	    "select": {
	        "aou": [
	            "parent_ou", {
	                "column":"id",
	                "transform":"count",
	                "alias":"id_count",
	                "aggregate":"true"
	            }
	        ]
	    },
	    "from":"aou",
	    "having": {
	        "id": {
	            ">" : {
	                "transform":"count",
	                "value":6
	            }
	        }
	    }
	}

I execute this in srfsh like so:

srfsh# request open-ils.cstore open-ils.cstore.json_query.atomic {
"select": { "aou": [ "parent_ou", { "column":"id",
"transform":"count", "alias":"id_count", "aggregate":"true" } ] },
"from":"aou", "having": { "id": { ">" : { "transform":"count",
"value":1 } } } }

And the result is:

Received Exception:
Name: osrfMethodException
Status: Severe query error -- see error log for more details
Status: 500
Received Exception:
Name: osrfMethodException
Status: An unknown server error occurred
Status: 404
------------------------------------
Request Completed Successfully
Request Time in seconds: 0.000476
------------------------------------

Checking logs, it appears that the GROUP BY and HAVING clauses are
reversed in the generated SQL:

open-ils.cstore 2009-06-25 09:34:03 [ERR :4398:oils_cstore.c:4213:]
open-ils.cstore: Error with query [SELECT  "aou".parent_ou AS
"parent_ou", count("aou".id ) AS "id_count" FROM actor.org_unit AS
"aou"  HAVING count("aou".id ) >  1  GROUP BY  1;]

I looked at oils_cstore.c and it seems like things are being placed
into the buffer in the correct order, but it's very possible that I'm
reading this wrong, or missing something:

        string = buffer_release(group_buf);

        if ( *string && ( aggregate_found || (flags & SELECT_DISTINCT) ) ) {
                OSRF_BUFFER_ADD( sql_buf, " GROUP BY " );
                OSRF_BUFFER_ADD( sql_buf, string );
        }

        free(string);

        if( having_buf ) {
                string = buffer_release(having_buf);

                if ( *string ) {
                        OSRF_BUFFER_ADD( sql_buf, " HAVING " );
                        OSRF_BUFFER_ADD( sql_buf, string );
                }

                free(string);
        }

If I take the generated SQL and manually re-order the GROUP BY and
HAVING clauses, the query works.

Anyone with in-depth knowledge of open-ils.cstore care to investigate
and let me know if this is a simple fix?

Thanks!

-jeff