Raxx55 opened a new issue, #16495:
URL: https://github.com/apache/pinot/issues/16495
### **The query contains a LEFT JOIN between two tables, with filters on
both the left and right tables, including a JSON_EXTRACT_SCALAR condition on
the left table and column filters from the right table (workitemcustomfield).
When the WHERE clause includes multiple filter conditions combined using AND,
the query works fine. However, when the same conditions are grouped using OR
logic or nested under a single AND, Pinot fails to execute the query.**
**Note**: I am working with **Pinot 1.3.0** version
### **Query: Not working**
SELECT wt.workItemIdentifier,wt.name FROM workitem wt LEFT JOIN
workitemcustomfield wc ON wt.id = wc.workItemId
WHERE ( wt.projectId = '8aaa0bd3-7df0-4e76-bfa7-78a5654db9ee' )
AND (
(
wc.customFieldId = '1a6df50e-a987-4d68-a7cf-3334f8c3ecb0' AND wc.value
= '74d3d23a-bffa-4410-808a-0c80acf003f5'
)
**OR (
json_extract_scalar(
customAttributes,
'$.d8b5bddd-e21b-4980-8fb9-d72ce74d07f7',
'STRING',
'null'
) != 'null'
AND json_extract_scalar(
customAttributes,
'$.d8b5bddd-e21b-4980-8fb9-d72ce74d07f7',
'STRING',
'null'
) = '06f19d05-b253-4483-ae1a-7842f138573b'
)**
) ORDER BY wt.creationTime desc LIMIT 50000
### **Response:**
```
Error Code: 200
QueryExecutionError:
Unable to execute query plan for request: 529680001000000009 on server:
172.27.0.6@{37095,34963}, ERROR: java.util.concurrent.ExecutionException:
java.lang.RuntimeException: Caught exception while submitting request:
529680001000000009, stage: 2
at
java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:396)
at
java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2096)
at
org.apache.pinot.query.service.server.QueryServer.forEachStage(QueryServer.java:307)
at
org.apache.pinot.query.service.server.QueryServer.submit(QueryServer.java:147)
...
Caused by: java.lang.RuntimeException: Caught exception while submitting
request: 529680001000000009, stage: 2
at
org.apache.pinot.query.service.server.QueryServer.submitStage(QueryServer.java:270)
at
org.apache.pinot.query.service.server.QueryServer.lambda$forEachStage$5(QueryServer.java:300)
at
java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
... 3 more
...
Caused by: java.util.concurrent.ExecutionException:
java.lang.IllegalArgumentException: Unsupported function: JSONEXTRACTSCALAR
at
java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:396)
at
java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2096)
at
org.apache.pinot.query.service.server.QueryServer.submitStage(QueryServer.java:262)
... 5 more
...
Caused by: java.lang.IllegalArgumentException: Unsupported function:
JSONEXTRACTSCALAR
at
org.apache.pinot.query.runtime.operator.operands.FunctionOperand.<init>(FunctionOperand.java:74)
at
org.apache.pinot.query.runtime.operator.operands.TransformOperandFactory.getTransformOperand(TransformOperandFactory.java:81)
at
org.apache.pinot.query.runtime.operator.operands.TransformOperandFactory.getTransformOperand(TransformOperandFactory.java:33)
at
org.apache.pinot.query.runtime.operator.operands.FilterOperand$Predicate.<init>(FilterOperand.java:196)
org.apache.pinot.query.service.dispatch.QueryDispatcher.lambda$submit$1(QueryDispatcher.java:188)
org.apache.pinot.query.service.dispatch.QueryDispatcher.execute(QueryDispatcher.java:248)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submit(QueryDispatcher.java:185)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submitAndReduce(QueryDispatcher.java:132)
```
### **Observation:**
### **The same query working but with "AND" condition**
SELECT wt.workItemIdentifier,wt.name FROM workitem wt LEFT JOIN
workitemcustomfield wc ON wt.id = wc.workItemId
WHERE ( wt.projectId = '8aaa0bd3-7df0-4e76-bfa7-78a5654db9ee' )
AND (
(
wc.customFieldId = '1a6df50e-a987-4d68-a7cf-3334f8c3ecb0' AND wc.value
= '74d3d23a-bffa-4410-808a-0c80acf003f5'
)
**AND (
json_extract_scalar(
customAttributes,
'$.d8b5bddd-e21b-4980-8fb9-d72ce74d07f7',
'STRING',
'null'
) != 'null'
AND json_extract_scalar(
customAttributes,
'$.d8b5bddd-e21b-4980-8fb9-d72ce74d07f7',
'STRING',
'null'
) = '06f19d05-b253-4483-ae1a-7842f138573b'
)**
) ORDER BY wt.creationTime desc LIMIT 50000
**Response:**
<img width="1062" height="170" alt="Image"
src="https://github.com/user-attachments/assets/ecb4a024-8aaf-4441-81df-59695dcda310";
/>
### **Few more details like schema structure are as below,**
**Workitem Schema:**
```
{
"schemaName": "student",
"primaryKeyColumns": [
"id"
],
"dimensionFieldSpecs": [
{
"name": "id",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "name",
"dataType": "STRING",
"maxLength": 256
},
{
"name": "workItemIdentifier",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "projectId",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "customAttributes",
"dataType": "JSON"
},
{
"name": "deleted",
"dataType": "BOOLEAN"
}
],
"dateTimeFieldSpecs": [
{
"name": "creationTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS",
"defaultNullValue": 0
},
{
"name": "dbEventTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS",
"defaultNullValue": 0
}
]
}
```
**Workitemcustomfield schema:**
```
{
"schemaName": "workitemcustomfield",
"primaryKeyColumns": [
"id"
],
"dimensionFieldSpecs": [
{
"name": "id",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "workItemId",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "customFieldId",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "value",
"dataType": "STRING",
"maxLength": 2048
},
{
"name": "deleted",
"dataType": "BOOLEAN"
}
],
"dateTimeFieldSpecs": [
{
"name": "dbEventTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS"
}
]
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
