Union query being treated as "not read-only"

[graham_mueller]

I'm making a request to the ShortQuery endpoint that contains a union, which is surrounded by parentheses to add a limit at the end, ala

Code: Select all

(select foo from table)
union all
(select bar from table)
limit 10
offset 0

The API returns the message "Query command must either be read-only or execute on a temporary table." This seems to be a mistake in the logic around the read-only test.

[graham_mueller]

A further example, the analyzer considers this a valid read-only statement.

Code: Select all

select foo from table
union all
(select bar from table)
limit 10
offset 0

[SLeon]

Because you mentioned ShortQuery, I am moving your thread to the API forums.

The API Queries PUT ShortQuery endpoint utilizes the same logic that the User Query window does in Open Dental. Do you receive a similar error message when running this query in the UI (with a user that only has read-only query permission)?

[graham_mueller]

Whoops, sorry - thought I had posted to API. The error I see does not occur when I post in the query interface in the Open Dental client. Here's a more specific example you could test with.

Code: Select all

(select * from patient where PatNum = 1)
union all
(select * from patient where PatNum = 2)

This returns the read-only error from the API, but executes fine in the desktop client.

[SLeon]

I was not able to duplicate the described behavior in the User Query window. I created a UserOD belonging to a UserGroup that does not contain the "Command Query" permission. This is the result of running your query:

UserQuery_NoCommandPermission.png (14.72 KiB) Viewed 6754 times

Regardless, I recommend removing extraneous comments and characters in queries prior to running them in the Open Dental UI or API endpoints.

[graham_mueller]

I don't really understand your reply; it looks like you got a different error based on a lack of permission? I'm attaching my query window, minus the actual rows since they're patient info. I'm running against an instance OD version 23.2.52.0 for this particular query.

[SLeon]

Good afternoon,

Your original post suggested there was an issue with the API's parsing of the queries. I replied that the API uses the exact method utilized by the User Query window in Open Dental. This is why I asked if you experienced the issue you described in the User Query Window with a user that only has read-only permissions. This troubleshooting step allows us to see if there is an issue with the API endpoint, the User Query window, or your query.

Next, you indicated that you were able to successfully run the query without issue in the User Query window. I tried this myself and was not able to run this query. I suspected that you were not logged in as a user that lacked the "Command Query" permission, and therefore Open Dental allowed your execution without issue.

I stepped through the read-only query screening logic and saw that the extraneous leading parenthesis was the culprit. You likely expected this, because in your second post you share a successful query with the extraneous parentheses removed. My reply was merely to confirm this behavior, explicitly identifying the leading parenthesis as the culprit, and not the UNION keyword that the title of this thread implies. Therefore my recommendation was the removal of extraneous characters to avoid the issue.

[graham_mueller]

You are correct that this problem is perhaps not limited to union, and in fact running even a single (select 1) statement in the parentheses has the same behavior. I could retitle to "parenthetical query being treated as not read-only" if you'd prefer. There's evidently some difference between running it in OD vs via the API - it works in OD and the API returns "Query command must either be read-only or execute on a temporary table."

I stepped through the read-only query screening logic and saw that the extraneous leading parenthesis was the culprit.

To me, this is a bug in the query screening logic, and is the reason I opened this issue. Sure - I have a work around for it, but the code is incorrectly flagging a valid query as non-read-only. Perhaps the code around that validation is too complex to make the change worthwhile, but it's something I stumbled into so I figured I'd provide you a test case.

[graham_mueller]

Unfortunately after playing a bit more with my query, I've realized that I actually can't work around this for the query that I'm hoping to use, which is effectively as follows.

Code: Select all

(
select ...
  from commlog cl
  join patient p on cl.PatNum = p.PatNum
 where cl.Mode_ = 5
   and cl.CommLogNum in (a set of ids)
 limit 10
offset 0
)
 union all
(
select ...
  from commlog cl
  join patient p on cl.PatNum = p.PatNum
 where cl.Mode_ = 5
   and cl.CommLogNum not in (a set of ids)
 order by cl.CommDateTime desc
 limit 10
offset 0
)

The goal of this query is to load a paged set of text messages, where <list of ids> is stored in another system as "new / unread," and we want those brought to the top. It appears that earlier versions (5.5) of MySQL allow the top bit of the query to be without parenthesis and include a limit/offset, but newer versions (10.5) do not.

[SLeon]

That is an interesting difference between MySQL version 5.5 and 10.5. You can easily get around this by SELECTing from your original query.

Code: Select all

SELECT subquery1.* FROM [subquery] as subquery1
UNION ALL
SELECT subquery2.* FROM [subquery] as subquery2

Edited to add:

In response to your other post, I agree that the query parsing logic is erroneously flagging your "(SELECT ..." query as not read-only. This is a limitation of the Open Dental's parsing logic.

This is not extremely important for your use case, but the reason I keep bringing up the User Query window is that the API specifically calls the centralized query parsing code as a user without the "Command Query" userpermission (aka can only run read-only queries). When developers have questions about what constitutes a read-only query for the API, we suggest they run it in the User Query window with a a user without the "Command Query" userpermission. The idea being, if it works there it will also work via the API, because the underlying code is the same. I consider this valuable, and hoped my previous post conveyed this, but undoubtably some developers might interpret the workflow as "guess and check with extra steps".

To programmatically resolve this issue, Open Dental would need to rewrite its parsing logic to somehow intelligently remove leading extraneous brackets/braces/parentheses and their paired character later in the string. Not a simple undertaking, especially considering it only hiccups on characters at the start of the query, but the issue has been noted. I hope the code-section above, and the idea of SELECTing from, is helpful.