There is a difference in behaviour between Adobe ColdFusion and Lucee when processing a query with SELECT/DELETE when using <cfqueryparam list="true" ...>.
In particular, if the specified list is empty then Adobe ColdFusion casts the list to an empty string and returns ALL rows; whereas in Lucee the empty list is cast to NULL and it returns zero rows. I verified this with SQL Profiler.
The same behavior can be seen when directly running the query on MSSQL Management Studio using empty string versus NULL.
The expected behaviour of doing a SELECT/DELETE ... WHERE Column NOT IN () is that all rows would be selected/deleted.
Modifying Lucee to follow this pattern would also remove the need to put <cfif> statements around the relevant WHERE clause to guard against this scenario.
I don’t understand how this TryCF is supposed to work without a valid datasource.
I have tested real queries against real databases: MS SQL, MySQL, and PostgreSQL. A query with IN () throws a parser error in all three.
How are you suggesting to handle this? Why not throw an error?
IMO it is the developer’s responsibility to check that the list is not empty prior to executing such a query.
When the code in that TryCF is run on my machine using Adobe ColdFusion with a real datasource it produces a query result with all the table numbers in the query.
Whilst I am not disagreeing with your point of view, we discovered this whilst porting some code from ACF to Lucee.
This ticket is highlighting the difference between the ACF behaviour and Lucee behaviour.
My expectation was that Lucee would behave the same as ACF.
of course, I understand the difference between the ACF and Lucee implementations of this case.
ACF treats the empty list as empty string while Lucee treats the empty list as null.
When you use NOT IN (:empty_list) then ACF runsNOT IN('') returning ALL rows, while Lucee runs NOT IN(null) returning NO rows.
Now consider using IN (:empty_list)- ACF runs IN ('') returning NO rows and Lucee runs IN (null) returning NO rows.
NOT IN (:empty_list)
So Lucee is consistent here, while ACF has a bug.
Also consider the difference between a TEXT list (e.g. VARCHAR) and a numeric one. In a TEXT list the empty list is ('') but in the numeric case it is () which throws a parsing error in all of the DBMSs that I tested.
To repeat my opinion - the combination of list=true and empty value should throw an exception rather than add the buggy ACF implementation.
I appreciate the straight-forward explanation.
If all the DBMSs and Lucee behave the same way - that is pretty hard to argue against.