A bug when using GROUP BY in DSP 1.8.2

REST API
1

This seems to be a bug we have identified when using DSP version 1.8.2.
This can be tested when using the API Doc interface in the DSP.
I am connecting to a MySQL database.

When using the REST API to query a table, the FILTER does not recognize single quotations when there is a GROUP BY in the filter.

Examples:

The following script will throw an ERROR (as the string values are in single quotes and has Group By):
employee_code <>’’ and clockin >‘2014-12-31 18:30’ and clockout <=‘2015-01-04 18:30’ GROUP BY job_code

The following script will WORK (as the string values are in single quotes and NO Group By in the filter):
employee_code <>’’ and clockin >‘2014-12-31 18:30’ and clockout <=‘2015-01-04 18:30’

The following script will WORK (as the string values are in double quotes with Group By):
employee_code <>"" and job_code<>"" and clockin >“2014-12-31 18:30” and clockout <=“2015-01-04 18:30” GROUP BY job_code

I do NOT have this issue on DSP version 1.7.8.
In 1.7.8 the script in example #1 works.

2

Just so the community isn’t left hanging, @rajivperera discussed this directly with DreamFactory support on a call last week. We determined that the SQL GROUP BY option is not explicitly supported, but just happened to work because of how the SQL statement is concatenated by the DSP before being passed to the DB. In certain locations in the query string and with certain formatting, this happy coincidence didn’t work, resulting in the strange behavior reported.

Since it does seem useful to support GROUP BY in the future, we have added this to our development backlog.