[RTFACT-12088] Using Oracle DB the limit feature of AQL does not work Created: 08/Aug/16  Updated: 14/Nov/17  Resolved: 09/Aug/16

Status: Resolved
Project: Artifactory Binary Repository
Component/s: AQL, Database
Affects Version/s: 4.7.5, 4.8.3, 4.11.0
Fix Version/s: 4.11.1

Type: Bug Priority: High
Reporter: Daniel Augustine Assignee: Yinon Avraham
Resolution: Fixed Votes: 1
Labels: None

Assigned QA: Mor Iluz (Inactive)

 Description   

When using Oracle DB the "limit" feature of AQL does not work.

Steps to reproduce this:

1. Create an instance of Artifactory that uses Oracle DB
2. Make sure you have some artifacts on there
3. Run the following simple AQL query:

curl -i -u<user>:<password> -XPOST http://<server-url>/artifactory/api/search/aql -d "items.find().limit(2)"

It will not limit it to 2, but will return all results. Enabling debug logging shows the following query being run:

select * from( select rownum rnum, inner_query.* from ( select distinct n.repo as itemRepo,n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated,n.modified as itemModified,n.updated as itemUpdated,n.created_by as itemCreatedBy,n.modified_by as itemModifiedBy,n.node_type as itemType,n.bin_length as itemSize from nodes n left outer join node_props np100 on np100.node_id = n.node_id where ( np100.prop_key like 'docker.manifest' and n.node_type = 1) and(n.repo != 'auto-trashcan' or n.repo is null) ) inner_query where ROWNUM <= 9223372036854775807)where rnum > 0 '

Notice the ROWNUM <= part, this value is incorrect, despite the value Artifactory shows at the end of the AQL results:

"range" : {
"start_pos" : 0,
"end_pos" : 635,
"total" : 635,
"limit" : 2
}
}



 Comments   
Comment by Yinon Avraham [ 09/Aug/16 ]

Fixed.
Please test also on SQL-Server.

Comment by Taylor Jones [ 09/Aug/16 ]

Can this be backported to 4.8, please?

Generated at Wed Apr 08 09:13:15 UTC 2020 using Jira 8.5.3#805003-sha1:b4933e02eaff29a49114274fe59e1f99d9d963d7.