Lucee QoQ Error with Empty Recordset

David_R · December 27, 2021, 9:50am

Hi to all, we are experience an error with Query of Query where the QoQ read from an empty database Query.
The error repported is

"java.lang.ArrayIndexOutOfBoundsException;java.lang.ArrayIndexOutOfBoundsException;lucee.runtime.exp.NativeException: java.lang.ArrayIndexOutOfBoundsException
Caused by: java.lang.ArrayIndexOutOfBoundsException

Try to better explain the problem.
Do a database query MYDBQUERY = Select A,B,C from tableA
Do a QoQ to MYDBQUERY : Select A from MYDBQUERY
Lucee report the above java error.

We noticed this error to Lucee Version 5.3.8.206
The same code used to Lucee 5.3.7.47 don’t have any error.

For now, to solve the problem we do an AddRow to MYDBQUERY when it return an empty query. This cause the QoQ don’t go to java error. This is only a temporarely solution.

We noticed some changes to QoQ features during an year of Lucee Upgrades. Some query as SELECT with GROUP have restrictions that in prevision version don’t generate java issue. Is it normal?

Please help, we can’t imagine a QoQ alternative in our software because we don’t use it only to order data but an intensive use of QoQ. Thanks

cfmitrah · December 27, 2021, 10:33am

@David_R Can you please share the full exception stacktrace here if possible?

David_R · December 27, 2021, 12:08pm

@cfmitrah the only error reported by exception.log is below
“ERROR”,“http-nio-8888-exec-18”,“12/22/2021”,“11:46:39”,“”,"java.lang.ArrayIndexOutOfBoundsException;java.lang.ArrayIndexOutOfBoundsException;lucee.runtime.exp.NativeException: java.lang.ArrayIndexOutOfBoundsException
Caused by: java.lang.ArrayIndexOutOfBoundsException

To find the QoQ error I had to analyse all the code to undestrand where is the error, lucee don’t report a row error in this exeption

If you make some test, please, don’t use the QueryNew tag to create a Query. The error is on QoQ to a Database Query. Our database query was made to MSSQL Server table and the datasource is registered to Lucee Admin

Thanks

cfmitrah · December 27, 2021, 2:07pm

okay @David_R, please give some more details on the issue. Like
are you using cfquery or queryExecute? Did you use any params?
if possible share some test code to reproduce the issue.

bdw429s · December 27, 2021, 6:46pm

@David_R That isn’t enough to go on really. If there’s an exception in your application, it has to be bubbling up to your app’s error handling or the Application.cfc’s onError() method. Ensure you are logging all errors. Also, check all of Lucee’s other log files. There are dedicated exception and database error logs which may have the full stack trace that will lead you to the specific query in your code. Once you’ve found the full stack trace and query in question that is erroring, then create a reproducible case and we’ll look into it.

There were improvements made to QoQ this year, but I can’t really answer your specific question about as it’s not clear what you’re talking about. Please provide a specific runnable example of the behavior you have a question about and we can help address that specific use case.

David_R · January 4, 2022, 9:13pm

Hi Brad,
it was very hard to understand where is the problem and how to reproduce it.
Now I have found a code to get this issue. I’ll try to explain it.

We noticed this error to Lucee Version 5.3.8.206
The same code used to Lucee 5.3.7.47 doesn’t get any error.

We start a cfquery to database (database is MS Sql Server). The query gets some records from a View like the code below :

<cfquery name="qRooms" datasource="#application.database#">
SELECT 
 pri
 ,pax
,tariff
,tariff_pri
,tariff_gg
,tariff_des
,contract
,sist_cod
,rates_price
,rates_cost
FROM View_Rooms
WHERE sist_cod = <cfqueryparam value="AESPECIAL" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

The query runs correctly and gives us some records.
Below is the QoQ code that gets the Error: java.lang.ArrayIndexOutOfBoundsException;java.lang.ArrayIndexOutOfBoundsException;lucee.runtime.exp.NativeException: java.lang.ArrayIndexOutOfBoundsException
Caused by: java.lang.ArrayIndexOutOfBoundsException

<cfquery name="qSist" dbtype="query">
SELECT distinct
sist_cod
,pax
,tariff
,tariff_pri
,tariff_gg
,tariff_des
,contract
,pax
,pri
FROM qRooms
ORDER BY tariff_pri, pax, pri    
</cfquery>

There was a programming error in QoQ because, we are reading twice the same field “PAX”

For this QoQ we don’t get any error with previous version of Lucee, so in the past we haven’t noticed these double fields in the SELECT statement.

PS
Another type of error in this new Lucee Version is that QoQ can’t do an Order By for a fields that isn’t present in the SELECT statement. In many cases this is a problem for a flow of codes. But this is another issue we have found.

Thank you very much.
Davide

bdw429s · January 4, 2022, 9:30pm

Thanks for the additional information on this. Since you’ve been able to reproduce it, can you please include the full stack trace of the array out of bounds exception?

This is not true. In fact, Lucee’s test case has a query doing exactly that.

github.com

lucee/Lucee/blob/6.0/test/tickets/LDEV3042.cfc#L105


      
          				
          			it( 'Can order by columns not in select' , function() {				
          				actual = QueryExecute(
          					sql = "SELECT department from employees ORDER BY department, isActive desc, name, email",
          					options = { dbtype: 'query' }
          				);
          				expect( actual.recordcount ).toBe( 15 );
          				expect( actual.department[1] ).toBe( 'Acounting' );
          				expect( actual.department[15] ).toBe( 'IT' );
          			});
          				
          			it( 'Can have extra whitespace in group by and order by clauses' , function() {
          				actual = QueryExecute(
          					sql = "SELECT department from employees group       by department ORDER       BY department",
          					options = { dbtype: 'query' }
          				);
          				expect( actual.recordcount ).toBe( 4 );
          				expect( actual.department[1] ).toBe( 'Acounting' );
          				expect( actual.department[4] ).toBe( 'IT' );
          			});

Here is a complete runnable example that shows you can order by columns not in the select list:

And here is a complete runnable example showing selecting the same column twice in the same QoQ that works as well without error:

Now you do the same, but with the version that’s erroring for you so we can both be talking about the same thing

cfmitrah · January 5, 2022, 7:30am

@David_R and @bdw429s I can reproduce the ArrayIndexOutOfBoundsException issue on the lucee latest version also.
Here is the example to reproduce

github.com

lucee/Lucee/blob/5.3/core/src/main/java/lucee/runtime/db/QoQ.java#L180


      
          	 */
          	private static void order( PageContext pc, Query target, Expression[] columns, boolean isUnion, SQL sql) throws PageException {
          		Expression col;
          		// Looping backwards over columns so they order correctly
          		for (int i = columns.length - 1; i >= 0; i--) {
          			col = columns[i];
          			if (!isUnion) {
          				Integer ordinalIndex;
          				if (col instanceof Literal ) {
          					if( col instanceof ValueNumber && ( ordinalIndex = Caster.toInteger( ((Literal)col).getValue(), null ) ) != null && ordinalIndex > 0 && ordinalIndex <= target.getColumnNames().length ) {
          						// Sort the column referenced by the ordinal position
          						target.sort(target.getColumnNames()[ ordinalIndex-1 ], col.isDirectionBackward() ? Query.ORDER_DESC : Query.ORDER_ASC);
          					} else {
          						// All other non-integer literals are invalid.
          						throw new IllegalQoQException("ORDER BY item [" + col.toString(true) + "] in position " + (i+1) + " cannot be a literal value unless it is an integer matching a select column's ordinal position.", null, sql, null);
          					}
          				} else {
          					// order by ? -- ignore this as well
          					if (col instanceof Column && ((Column) col).getColumn().equals(paramKey)) continue;
          
          
					// Lookup column in query based on the index stored in the order by expression

Stacktrace
lucee.runtime.exp.NativeException: 8
 	at lucee.runtime.db.QoQ.order(QoQ.java:180)
 	at lucee.runtime.db.QoQ.execute(QoQ.java:132)
 	at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:258)
 	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 test.testcases.test_cfm$cf$2.call(/test/testcases/test.cfm:132)
 	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)

cfmitrah · January 5, 2022, 11:47am

I’ve created a ticket for this issue [LDEV-3801] - Lucee.

David_R · January 5, 2022, 12:25pm

The Order By Error can be reproduced using the SELECT DISTINT in QoQ. Below is an example

<cfquery datasource="#dsn#"    name="qViewTest">
        select top 20
            autore_id
            ,id
            ,idsomm
            ,datains
            ,ogg
            ,autore
            --,AUTORE
            ,AUTORE_NOME
            ,idsomm
            --,ID
            ,AUTORE_ID
        from    VIEW_TEST
        order by autore desc, id desc
</cfquery>

<cfquery name="QoQViewTest" dbtype="query">
    select distinct
            autore_id
            ,id
            ,idsomm
            ,ogg
            ,ID
            ,autore
            ,AUTORE
            ,AUTORE_NOME
            ,idsomm
            ,AUTORE_ID
    from    qViewTest
    where
            AUTORE_NOME like <cfqueryparam value="%aless%" cfsqltype="CF_SQL_CHAR">
    order by  autore desc, idsomm desc, datains
</cfquery>

You can see the correct version changing the
order by autore desc, idsomm desc, datains
to
order by autore desc, idsomm desc
deleting the datains from order by statement

For this error I receive the Java StackTrace. For the other case, the error with two fields I can’t receive the Stacktrace fo now. If I can see it I’ll post.

lucee.runtime.exp.DatabaseException: L’argomento in ORDER BY deve essere nella lista di SELECT DISTINCT: org.hsqldb.Expression@41ef28fe in statement [select distinct
autore_id
,id
,idsomm

,ogg
,ID
,autore
,AUTORE
,AUTORE_NOME
,idsomm
,AUTORE_ID

from qViewTest
where
AUTORE_NOME like ‘%aless%’

order by autore desc, idsomm desc, datains]
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.(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 testdb_cfm$cf$5fh.call(/testdb.cfm:43)
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:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:679)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1839)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Unknown Source)

bdw429s · January 5, 2022, 8:02pm

@David_R You’re not understanding what a self-contained runnable example is. I can’t take your code and just run it as it depends on externalities you haven’t included. The trycf.com examples that @cfmitrah and I included are proper repro cases which anyone can just run directly.

http://sscce.org/

@cfmitrah Thanks for helping pull out the examples. I’ll take a look at fixing them.

cfmitrah · January 10, 2022, 2:28pm

@bdw429s While checking this I got NPE in QoQ using ORDER BY with commented line. I created a separate ticket for the issue [LDEV-3806] - Lucee.

bdw429s · January 10, 2022, 8:31pm

@cfmitrah To my knowledge, there is no comment support in QoQ’s parser. Adobe CF doesn’t even support this, so it would be a feature request. I’ve never used SQL comments in QoQ’s because they never worked. So what you basically have is invalid SQL