http://coldfusion.com logo
Join Slack
Powered by
Seeing a breaking difference between Lucee 5.2.9 a...
# lucee
g
Seeing a breaking difference between Lucee 5.2.9 and 5.3.8. In the Query of Queries if the list you pass in to a param is empty in 5.2.9 it returns an empty query resultset, in 5.3.8, it throws an error. @bdw429s iirc you made some QoQ changes in this, it this change on purpose or a bug? Ill put a some test code on this thread.
Copy code
<cfscript>
	q = QueryNew("num");
    numbers = [1,2,3,4,5,6,7,8,9,10]
	for (num in numbers) {
		r = QueryAddRow(q);
		QuerySetCell(q,"num", num, r);
	}
</cfscript>
<cfset list = [] >
<cfdump var="#q#">
<cfquery name="q2" dbtype="query">
    select * from q
    where
    num IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#arrayToList(list)#" list="true" />)
</cfquery>
<cfdump var="#q2#">
Throws
Copy code
Unexpected token: [ in statement [select * from q
where
num IN (]
box start cfengine=lucee@5.3.8
with box start cfengine=lucee@5.2.9, just returns an empty result set
b
Did you try 5.3.9?
I don't think I touched that logic, but @zackster may know of a related ticket
I'd guess the error is actually coming from HSQLDB
g
i did try 5.3.9 latest
same problem
and yes from HSQLDB
b
Interesting. I am curious why the native QoQ isn't handling it, but I rarely use list params these days and I'm not entirely sure what the expected behavior should be under the hood. An empty list would basically create the following SQL
Copy code
SELECT *
FROM table
WHERE col in ()
which wouldn't even be valid SQL
I'm curious what Lucee did in the past that allowed that to work
g
bad news is just a regular = throws the same problem
<cfquery name=“q2” dbtype=“query”> select * from q where num = <cfqueryparam cfsqltype=“cf_sql_integer” value=“#val#“/> </cfquery>
val = “”
b
It throws this exact error?
Copy code
Unexpected token: [ in statement [select * from q
where
num IN (]
g
yep
b
That seems rather unlikely since you removed the 
in
from your example entirely
Either that or you're editing the wrong page or have trusted cache enabled or something
g
oh second one that error is
b
Also, the stack trace would be helpful here
g
Copy code
Unexpected token: [ in statement [select * from q
where
num = ]
Copy code
lucee.runtime.exp.DatabaseException: Unexpected token: [ in statement [select * from q
  where
  num = ]
  at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
  at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
  at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)
  at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:320)
  at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:287)
  at lucee.runtime.type.QueryImpl.<init>(QueryImpl.java:235)
  at lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:358)
  at lucee.runtime.db.HSQLDBHandler._execute(HSQLDBHandler.java:319)
  at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:307)
  at lucee.runtime.tag.Query.executeQoQ(Query.java:1110)
  at lucee.runtime.tag.Query._doEndTag(Query.java:680)
  at lucee.runtime.tag.Query.doEndTag(Query.java:565)
  at qoqintest_cfm$cf$9.call(/qoqInTest.cfm:16)
  at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034)
  at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926)
  at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:65)
  at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:45)
  at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460)
  at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450)
  at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421)
  at lucee.runtime.engine.Request.exe(Request.java:45)
  at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179)
  at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125)
  at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
  at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
  at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
  at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
  at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:47)
  at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
  at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
  at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:64)
  at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
  at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)
  at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
  at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
  at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
  at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
  at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
  at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
  at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
  at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
  at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
  at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
  at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
  at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
  at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
  at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
  at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
  at io.undertow.server.Connectors.executeRootHandler(Connectors.java:336)
  at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  at java.lang.Thread.run(Thread.java:750)
b
Makes sense-- that WOULD be invalid SQL
I don't ever really use cfquery param with QoQ, but I don't think it works the same was as a normal query, where the DB actually gets SQL like this
Copy code
DECLARE @val as INT
select *
from table
where col = @val
I would guess QoQ just concatenates the values right into the SQL and runs it
g
right, its the version difference that is confounding, mainly an undocumented difference for the upgrade path
b
Though, I am a little surprised
Copy code
<cfqueryparam cfsqltype="cf_sql_integer" value="#val#"/>
didn't throw a type validation error if 
val
was an empty string since you specified 
cf_sql_integer
5.2.9 was a loooooong time ago. There have been many many releases since then 🙂
You can go through them all and find the exact point where it stopped working, lol
g
right, varchar doesn’t error
haha
(in the equals case)
bottom line, you agree the new behavior throwing an error is the correct way to handle it?
and not a bug
b
To be honest, I don't know. I'd want to track down the ticket which caused the change and read the background of why and look at the unit tests to understand what happened
If Adobe doesn't error, that at least makes it a compat issue which is worth entering a ticket over (if none already exist in the backlog)
As tempting as it is to dig into the source code and figure out what it's doing, I don't have the time for that today.
From my calculations there's only been 773 builds of Lucee between 5.2.9 stable and the latest 5.3.9 snapshot. You'd better get to testing! 😆
g
latest adobe also errors
Comparison exception while executing IN. The value ‘’ cannot be converted to a number.
b
Wow, interesting.
I half expected Adobe to work
g
lol me too
b
Well, wait-- is that the version where you passed an empty string explicitly or an empty LIST?
I wonder consider those different tests
g
i did the list on adobe
b
I would think QoQ should be able to handle an empty list int he same manner as you'd get with
Copy code
select * 
from foo 
where col in ( select col from bar where 1 = 0 )
that for example, wouldn't error, it just wouldn't return anything
Conceptually, an empty list would be the same thing to me
g
right, 🤷
b
I'd say put in a ticket with your code sample above and note it doesn't work in either engine. When I get time, I can look into making it work for Lucee's native QoQ.
But I'd need time to dig into it and see how it works
if you need this working, like ASAP, you can engage me via Ortus support and I can fix it now and give you a patch file 😉
Otherwise, you're at the mercy of my free time 😆
g
im not convinced its a bug, especially since adobe throws, so imho the current way it works in lucee if “more correct”
and the old way it worked in lucee is not correct and should have thrown ha
b
I don't put a ton of stock in Adobe's QoQ implementation being the most correct way 🙂
This feels more like an edge case they never considered. if I fix it in Lucee, I'd also put in a ticket for Adobe to fix theirs as well.
I've put in a lot of QoQ ticket for Adobe this last year, inviting them to make the same improvements that I've been making to Lucee's QoQ
I also think this separate find is worth a ticket
Copy code
<cfquery name="q2" dbtype="query">
        select num, <cfqueryparam cfsqltype="cf_sql_integer" value="test" > as foo
        from q
    </cfquery>
where passing a string to a cfqueryparam with a type of integer throws an odd error.
Passing a number like 
42
instead of 
test
does work there
m
I would think the proper handling would be on the users code side by adding something like this... null="#ArrayLen(list) eq 0#" It still doesn't work in ACF, but it does solve the edge case for the potential of an empty array..
b
I have a feeling the native QoQ is throwing a type error and kicking it over to HSQLDB which is falling on its face
g
@Michael Schmidt thats actually how i fixed it
z
num screams reserved word to me
b
Hmm, null is not the same as an empty array
g
lol its a contrived example
b
Unless you're expecting it to generate this
Copy code
select *
from table
where col in (null)
which I suppose would work
z
other side effects
just validate ya input.
b
That doesn't make it not a Lucee bug 🙂
g
its not a reserved word in either sql or cf
b
Lucee shouldn't fall on its face and bleed all over the carpet because there's a string in a numberic query param
m
how would you exepct it to react in non Query of Query but as a database i would expect
Copy code
select num from table where col in ()
would fail
b
I'm actually rather convinced that Lucee's QoQ hsqldb implementation isn't even setup to pass parameterized values to be honest.
z
it does
b
Based on the error message, I think the actual literal SQL that HSQLDB received was
Copy code
select num, [CF_SQL_INTEGER] as foo from q
which is why it's talking about a 
[
char
g
^^ thats what i think happened
b
how would you exepct it to react in non Query of Query but as a database i would expect
@Michael Schmidt I answered that above. https://cfml.slack.com/archives/C06TA0A9W/p1649431127487969?thread_ts=1649422898.443549&amp;cid=C06TA0A9W
Sure, when you're TYPING out a SQL statement, you would be dumb to type an actual literal empty set 
()
. But in this case, the developer is saying, all the values in this data struture, which if empty, the engine should be able to deal with properly just like a sub select would.
In this case, it's Lucee creating the bad SQL, not the developer.
@gratzc I created a ticket for the secondary error https://luceeserver.atlassian.net/browse/LDEV-3945
Thinking further on the empty list issue-- one of the wrinkles there is that just because a string is empty, it doesn't necessarily mean the list is empty. it could be a list with a single item, which is an empty string. 🤔
Perhaps that's being pedantic, but in reality, this is a total valid SQL statement
Copy code
select *
from table
where column in ('')
m
So i could see that maybe this scenario is where the issue is if it is a sqltype that isn't a string type and list and empty then it should have a special handling
👍 1
g
lol, for this i was just using an empty array converted to a list
b
Right, but the point is when an array has no items we can know that for sure. But a list always has ambiguity around being "empty" or not.
Now, that said
Copy code
CommandBox> #listlen ""
will give you zero
g
fyi a regular query, not QoQ, it doesn’t throw an error
b
I'm curious what SQL is generated in that case
@Michael Schmidt I agree it seems plausible that if I tell Lucee I have a list of numerics, and it gets an empty string, it could reasonably assume the list is empty. I'm not sure if we could make the same assumption for strings. Does cfqueryparam accept an actual array?
According to the docs, yes!
z
yes and then it may create ?,?,?
b
m
Copy code
select *, '1' as t, <cfqueryparam cfsqltype="cf_sql_integer" value="1"> as q from q
    where
    num IN (<cfqueryparam cfsqltype="cf_sql_integer"  value="#list#" />)
And it does work lol
b
What is the contents of your 
list
variable in that case? Also, you didn't specify 
list=true
m
not necessary with passing in an array
i just took his precode
Copy code
<cfset list = [] >
👍 1
b
You didn't show us that part of your code 🙂
g
Copy code
<cfset list = [] >
<cfquery name="q2">
    select * from users
    where
    userID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#arrayToList(list)#" list="true" />)
</cfquery>
<cfdump var="#q2#">
for a regular query, it doesn’t throw and returns an empty result set, the complied SQL for ^^ is
Copy code
select * from users
where
userID IN ([CF_SQL_INTEGER])
just like QoQ, but no error
b
@gratzc Where are you getting the SQL from?
You can't necessarily just look at what shows up when you dump the query. That's Lucee's representation of it, not what was sent via JDBC to the actual DB itself
in SQL Server, for example, you'd need to run a trace on the DB to see the actual query coming across the wire.
Though, at this point, that is more for curiosity sake and only tangentially applies to what QoQ does under the hood.
g
good point
b
@gratzc Did you catch that passing the array directly to the cfqueryparam works?
🎉 1
I think this existing ticket describes your original issue, though it's not related to QoQ directly. https://luceeserver.atlassian.net/browse/LDEV-3207
There's some good comments on that last ticket explaining what's happening, however I fundamentally disagree with Igal's diagnosis of what the right behavior is.
I hadn't considered the 
NOT IN
scenario, in which passing an empty set as 
null
no longer works since null is never equal or unequal to anything!
i.e. any comparison operation with null will return false, so 
not in ( null )
gives the opposite behavior desired.
@gratzc @Michael Schmidt I left a comment on that last ticket with some more thoughts on the matter.
Basically, I think Lucee can make empty sets work for 
IN
and 
NOT IN
but only when an empty array is used to denote an empty set, and with some DB-specific SQL hackery.
a
Pretty sure in SQL, one needs to have at least one value in the list, for the form 
IN (value,...)
. An empty string is a zero-length list (as Brad observed), which should not be valid. 
IN(subquery)
is a different function, so a zero-row subquery is not analogous to a zero-lenth list-of-values. Think of the two like 
f(String s)
and 
f(Integer i)
in Java: different implementations, and the sense of what constitutes "empty" or "valid" differs from the two. Using that metaphor, 
IN ()
is not the same "method signature" as 
IN (value,...)
, and the no-param version does not exist. Therefore: error. This is legit. Trying to find if ANSI SQL has anything specific to say.
b
Pretty much, yep. Empty sets of constant values aren't allowed in SQL, so CF engines have resorted to sticking a placeholder value in there to approximate the same behavior as an empty sub select.
✅ 2
3 Views
Open in Slack
PreviousNext
Powered by