Validate a large query in Queries runs the full statement and causes OOM

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Validating a query in Administration - Queries executes the administrator's SQL in full and holds the entire result set in JVM heap, so a query returning millions of rows causes OutOfMemoryError.

STEPS TO REPRODUCE:

1. In Administration - Queries, create a query with code z_test
2. Set the SQL text to a statement returning several million rows with single-token column aliases. 
   PostgreSQL: select g as col1, repeat('x',200) as col2 from generate_series(1,5000000) g
   Oracle: select level as col1, rpad('x',200,'x') as col2 from dual connect by level <= 5000000
   MSSQL: select a.object_id as col1, replicate('x',200) as col2 from sys.all_objects a cross join sys.all_objects b
3. Click Validate
4. Monitor the JVM heap on the app service.

Expected Results: Validation is successful as it does not run the full statement

Actual Results: The statement runs in full, every row is loaded into heap, and the request thread exhausts the heap, error in app-system is java.lang.OutOfMemoryError: Java heap space.

Environment

Clarity 16.4.2, 16.4.3

Cause

DE211327

Resolution

Fixed in Clarity 16.5.0

Workaround:

• Add to statement inside the SQL text, FETCH FIRST 20 ROWS ONLY on Oracle, LIMIT 20 on PostgreSQL, SELECT TOP 20 on MSSQL