QoQ Only Matching First OR in WHERE Clause

dev
1

I have a QoQ to filter results from query based on several WHERE criteria:

<cfquery dbtype="query" name="filterData">
	SELECT branch, build, product, nfs, unc 
	FROM myData
	WHERE branch LIKE 'cm_ggsg%' OR 
           branch LIKE 'cm_[0-9]%[0-9]' OR
		   branch LIKE 'cm_[0-9]%[0-9]a' OR
		   branch LIKE 'pcd_[0-9]%[0-9]' OR
		   branch LIKE 'cuc_[0-9]%[0-9]' OR
		   branch LIKE 'cer_[0-9]%[0-9]' OR
		   branch LIKE 'cer_[0-9]%[0-9]_respin'
	ORDER BY build DESC
</cfquery>

When I dump the results, only the data matching the first WHERE criteria is returned (branch LIKE ‘cm_ggsg%’). All of the other WHERE criteria seems to be ignored. The complete data set is returned if I run the query in ACF. Is this known / expected behavior and any workaround?

Lucee (Gelert) Os 5.3.8.206 (CFML Version 2016,0,03,300357)

Jason

2

I don’t think Lucee’s native QoQ supports regex, or whatever that is. Does that work on Adobe? Can you put in a ticket to add support for it?

3

Ahh, that is what was breaking it. It does work in ACF, but I’m not sure how common it would be to use syntax like that. Let me see if I can simplify it.

Jason

4

Actually, I’m wrong-- Lucee does build a regex out of your LIKE statement.

Perhaps it’s just buggy. Can you build a standalone example of what works on ACF. Something that creates a simple result set and then queries it so I can look into it. If it’s truly an ACF compat, then it deserves a ticket. I’m just not sure exactly what values you’re matching against with cm_[0-9]%[0-9], etc.

5

Ok, so putting a bit of debugging in, I can see a couple things

  • The code supports an escape character, but Lucee’s implementation doesn’t actually use it, making it impossible to actually escape any special chars. This doesn’t apply to your example, just something I noticed. The default escape char is usually \ but ideally, Lucee’s QoQ SQL parser should support the ESCAPE 'escape_character' syntax.
  • The regex ONLY supports % and _ (which map to .* and . respectivley

That means your [0-9] bits are just being used as literal strings. The actual regex being used for

column LIKE 'cm_[0-9]%[0-9]'

is

cm.\[0-9\].*\[0-9\]

Making Lucee support the additional stuff isn’t probably too hard, but I need some hard specs on exactly what should be supported. One troubling thing for me is the square brackets don’t seem to be standard SQL according to W3Schools

I do see it covered in SQL Server specific docs

MySQL does NOT support this in the LIKE operator:

Neither does Postgres’ LIKE operator

Nor Oracle
https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions016.htm

So square bracket char sets seem to mostly be a SQL Server thing that Adobe also added. Now, the fact that Adobe supports them may be enough of a reason to add support in Lucee (for compat) and it looks like it would be pretty easy-- but this does have the potential to be backwards incompatible for any existing code currently using a [ literal in their LIKE match which would need escaped if we add support.

Go ahead and enter a ticket to support SQL Server and Adobe’s [] char set as well as the ESCAPE 'escape_character' keyword. Another idea would be to add support for full regex matching in Lucee’s QoQ, which is already something both MySQL and Postres do-- they just have another operator other than LIKE for that.

6

Thanks for the detailed analysis!! Two questions:

  1. Do you still need an example of what works on ACF?
  2. I am very new to Lucee and I have no idea how to enter a ticket. Is there a link with the steps?

Jason

7

The issue tracker for lucee: https://luceeserver.atlassian.net
And create a ticket under Lucee Development(LDEV)

8

This is what I came up with:

employees = queryNew( 'name,foo', 'varchar,varchar',[
	['Brad','cm_4test5'],
	['Luis','yeah']
]);

actual = QueryExecute(
	sql = "SELECT * from employees WHERE foo LIKE 'cm_[0-9]%[0-9]'",
	options = { dbtype: 'query' }
);

writedump( actual );

If that matches what you were doing, we can just roll with it.

I can enter it for you.

https://luceeserver.atlassian.net/browse/LDEV-3826

I’ve also entered a ticket for my idea about adding generic regex support like most DB’s have

https://luceeserver.atlassian.net/browse/LDEV-3827

9

Thanks Brad! That matches what I am trying to accomplish.

10

@jrwill74 I have implemented support for [] char sets in Lucee’s native QoQ LIKE clause with this pul request that includes a bunch of new tests

It does have some backwards compat potential which I noted on the ticket. I sent to the pull to the branch that 5.3.9 is happening on, and you may be able to convince @Zackster to merge it if you send him a bunch of beer, but at the moment he’s eyeballing it as an enhancement for 6.0 instead due to the potential for backwards compat. Alternatively, you can also build the code on my branch and drop in a custom lco file on your server if you want.

11

I like beer, but I’m not putting my self forward for SCOTUS!

As I wrote on the ticket, REGEXP_LIKE() seems like a good approach without backwards compat issues

https://luceeserver.atlassian.net/browse/LDEV-3827?focusedCommentId=50282

12

@Zackster I think you may have confused the two tickets in this thread:

This ticket was for the immediate ask on this thread to support the Adobe ColdFusion and MSSQL behavior of having bracketed char sets in your LIKE clause. That is the ticket I sent the pull for.
https://luceeserver.atlassian.net/browse/LDEV-3826

The other ticket was for a new behavior that wouldn’t affect how LIKE works but provided a new function for raw Regex matches.
https://luceeserver.atlassian.net/browse/LDEV-3827

The second ticket doesn’t really avoid anything in regards to the first ticket. It’s just a separate, but related feature.