Raxx55 opened a new issue, #15566:
URL: https://github.com/apache/pinot/issues/15566
### **_The query contains JSON_EXTRACT_SCALAR in the SELECT clause, a LEFT
JOIN between two tables, and a column from the right table used in the WHERE
clause, which causes an error in Pinot._**
**Note:** I am working with **Pinot 1.2.0** version
### **Query:** Not working
select s.name, s.createdby, **json_extract_scalar(s.attributes,
'$.42fb8f5f-9a2f-4e06-aa28-0318df4e632d', 'STRING', 'null') AS
'42fb8f5f-9a2f-4e06-aa28-0318df4e632d'**
from student s **left join** class c on s.classId = c.id
where (s.professorId = '870e3f0d-25ac-45b6-bc42-841cdeda8b78') and **(c.id =
'0c9b1cea-5b98-4ac0-89dc-1ad914b5bf13')** limit 50000
### **Response:**
```
Error Code: 200
QueryExecutionError:
Unable to execute query plan for request: 1129558639000000402 on server:
my-pinot-release-server-0.my-pinot-release-server-headless.pinot.svc.cluster.local@{51314,18307},
ERROR: java.util.concurrent.ExecutionException: java.lang.RuntimeException:
Caught exception while submitting request: 1129558639000000402, stage: 3
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.submit(QueryServer.java:156)
at
org.apache.pinot.common.proto.PinotQueryWorkerGrpc$MethodHandlers.invoke(PinotQueryWorkerGrpc.java:284)
...
Caused by: java.lang.RuntimeException: Caught exception while submitting
request: 1129558639000000402, stage: 3
at
org.apache.pinot.query.service.server.QueryServer.lambda$submit$1(QueryServer.java:144)
at
java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
... 3 more
Caused by: java.util.concurrent.ExecutionException:
java.lang.IllegalStateException: Cannot find function with name:
JSON_EXTRACT_SCALAR
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.lambda$submit$1(QueryServer.java:141)
... 4 more
...
Caused by: java.lang.IllegalStateException: Cannot find function with name:
JSON_EXTRACT_SCALAR
at
org.apache.pinot.shaded.com.google.common.base.Preconditions.checkState(Preconditions.java:604)
at
org.apache.pinot.query.runtime.operator.operands.FunctionOperand.<init>(FunctionOperand.java:51)
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)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submit(QueryDispatcher.java:198)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submitAndReduce(QueryDispatcher.java:95)
org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:219)
org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:133)
```
### **Observation:**
1. ### **The same query without right tables column in where clause(here its
class id):** Working
select s.name, s.createdby, **json_extract_scalar(s.attributes,
'$.42fb8f5f-9a2f-4e06-aa28-0318df4e632d', 'STRING', 'null') AS
'42fb8f5f-9a2f-4e06-aa28-0318df4e632d'**
from student s **left join** class c on s.classId = c.id
where (s.professorId = '870e3f0d-25ac-45b6-bc42-841cdeda8b78') limit 50000
**Response:**
2. ### **The same query but with "join" instead of "left join":** Working
select s.name, s.createdby, **json_extract_scalar(s.attributes,
'$.42fb8f5f-9a2f-4e06-aa28-0318df4e632d', 'STRING', 'null') AS
'42fb8f5f-9a2f-4e06-aa28-0318df4e632d'**
from student s **join** class c on s.classId = c.id
where (s.professorId = '870e3f0d-25ac-45b6-bc42-841cdeda8b78') **and (c.id =
'0c9b1cea-5b98-4ac0-89dc-1ad914b5bf13')** limit 50000
**Response:**
### Few more details like schema, tables and actual data are as below,
**Student Schema:**
```
{
"schemaName": "student",
"primaryKeyColumns": [
"id"
],
"dimensionFieldSpecs": [
{
"name": "id",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "name",
"dataType": "STRING",
"maxLength": 256
},
{
"name": "professorId",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "classId",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "createdBy",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "updatedBy",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "attributes",
"dataType": "JSON"
}
],
"dateTimeFieldSpecs": [
{
"name": "creationTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS",
"defaultNullValue": 0
},
{
"name": "updationTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS",
"defaultNullValue": 0
}
]
}
```
**Student table:**
```
{
"tableName": "student",
"tableType": "REALTIME",
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"segmentsConfig": {
"schemaName": "student",
"timeColumnName": "updationTime",
"timeType": "MILLISECONDS",
"replication": "1",
"replicasPerPartition": "1"
},
"tableIndexConfig": {
"loadMode": "MMAP",
"invertedIndexColumns": [],
"createInvertedIndexDuringSegmentGeneration": false,
"rangeIndexColumns": [],
"sortedColumn": [],
"bloomFilterColumns": [],
"bloomFilterConfigs": null,
"noDictionaryColumns": [],
"onHeapDictionaryColumns": [],
"varLengthDictionaryColumns": [],
"enableDefaultStarTree": false,
"starTreeIndexConfigs": null,
"enableDynamicStarTreeCreation": false,
"segmentPartitionConfig": null,
"columnMinMaxValueGeneratorMode": null,
"aggregateMetrics": false,
"nullHandlingEnabled": false
},
"metadata": {},
"ingestionConfig": {
"filterConfig": null,
"transformConfigs": null,
"streamIngestionConfig": {
"streamConfigMaps": [
{
"streamType": "kafka",
"stream.kafka.topic.name": "student_entity_data",
"stream.kafka.broker.list": "{{kafka.broker.list}}",
"stream.kafka.consumer.type": "lowlevel",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest",
"stream.kafka.consumer.factory.class.name":
"org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.decoder.class.name":
"org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"stream.kafka.decoder.prop.format": "JSON",
"key.serializer":
"org.apache.kafka.common.serialization.ByteArraySerializer",
"value.serializer":
"org.apache.kafka.common.serialization.ByteArraySerializer",
"realtime.segment.flush.threshold.rows": "30000",
"realtime.segment.flush.threshold.time": "12h",
"topic.consumption.rate.limit": "6000"
}
]
}
},
"quota": {
"storage": null,
"maxQueriesPerSecond": null
},
"task": {
"taskTypeConfigsMap": {
}
},
"routing": {
"segmentPrunerTypes": null,
"instanceSelectorType": "strictReplicaGroup"
},
"query": {
"timeoutMs": null
},
"fieldConfigList": null,
"upsertConfig": {
"mode": "FULL",
"deleteRecordColumn": "deleted"
},
"tierConfigs": null
}
```
**Student table data:**
```
{
"id": "6f02ae8c-e7ca-4671-b759-5e18cb97c74f",
"name": "Alen",
"professorId": "870e3f0d-25ac-45b6-bc42-841cdeda8b78",
"classId": "0c9b1cea-5b98-4ac0-89dc-1ad914b5bf13",
"createdBy": "870e3f0d-25ac-45b6-bc42-841cdeda8b78",
"creationTime": 1740995630168,
"updatedBy": null,
"updationTime": null,
"attributes": {"42fb8f5f-9a2f-4e06-aa28-0318df4e632d":"Male",
"d30ef901-8acc-4872-887c-d048073fe5d4":5}
},
{
"id": "2743b65e-7ec3-4072-90db-1921fe5728d3",
"name": "Lana",
"professorId": "870e3f0d-25ac-45b6-bc42-841cdeda8b78",
"classId": "0c9b1cea-5b98-4ac0-89dc-1ad914b5bf13",
"createdBy": "870e3f0d-25ac-45b6-bc42-841cdeda8b78",
"creationTime": 1741064094982,
"updatedBy": "870e3f0d-25ac-45b6-bc42-841cdeda8b78",
"updationTime": 1741064099981,
"attributes": {"42fb8f5f-9a2f-4e06-aa28-0318df4e632d":"Female",
"d30ef901-8acc-4872-887c-d048073fe5d4":7}
},
{
"id": "cb18e2c6-6095-49c7-b991-70fc5c981a0e",
"name": "James",
"professorId": "dbac7d66-e443-473d-829c-43bb50a796f0",
"classId": "e2428f7d-565e-4b3b-9423-549a850a2ef5",
"creationTime": 1741253546022,
"createdBy": "dbac7d66-e443-473d-829c-43bb50a796f0",
"updatedBy": null,
"updationTime": null,
"attributes": {"42fb8f5f-9a2f-4e06-aa28-0318df4e632d":"Male",
"d30ef901-8acc-4872-887c-d048073fe5d4":3}
},
{
"id": "74eb710e-63c8-4524-943e-9586055fa840",
"name": "orry",
"professorId": "dbac7d66-e443-473d-829c-43bb50a796f0",
"classId": null,
"creationTime": 1741253946013,
"createdBy": "dbac7d66-e443-473d-829c-43bb50a796f0",
"updatedBy": null,
"updationTime": null,
"attributes": {"42fb8f5f-9a2f-4e06-aa28-0318df4e632d":"Female"}
}
```
**Class schema:**
```
{
"schemaName": "class",
"primaryKeyColumns": [
"id"
],
"dimensionFieldSpecs": [
{
"name": "id",
"dataType": "STRING",
"maxLength": 64
},
{
"name": "name",
"dataType": "STRING",
"maxLength": 256
},
{
"name": "displayOrder",
"dataType": "INT"
}
],
"metricFieldSpecs": [
],
"dateTimeFieldSpecs": [
{
"name": "dateTime",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS"
}
]
}
```
**Class table:**
```
{
"tableName": "class",
"tableType": "REALTIME",
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"segmentsConfig": {
"schemaName": "class",
"timeColumnName": "dateTime",
"timeType": "MILLISECONDS",
"replication": "1",
"replicasPerPartition": "1"
},
"tableIndexConfig": {
"loadMode": "MMAP",
"invertedIndexColumns": [],
"createInvertedIndexDuringSegmentGeneration": false,
"rangeIndexColumns": [],
"sortedColumn": [],
"bloomFilterColumns": [],
"bloomFilterConfigs": null,
"noDictionaryColumns": [],
"onHeapDictionaryColumns": [],
"varLengthDictionaryColumns": [],
"enableDefaultStarTree": false,
"starTreeIndexConfigs": null,
"enableDynamicStarTreeCreation": false,
"segmentPartitionConfig": null,
"columnMinMaxValueGeneratorMode": null,
"aggregateMetrics": false,
"nullHandlingEnabled": false
},
"metadata": {},
"ingestionConfig": {
"filterConfig": null,
"transformConfigs": null,
"streamIngestionConfig": {
"streamConfigMaps": [
{
"streamType": "kafka",
"stream.kafka.topic.name": "class_entity_data",
"stream.kafka.broker.list": "{{kafka.broker.list}}",
"stream.kafka.consumer.type": "lowlevel",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest",
"stream.kafka.consumer.factory.class.name":
"org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.decoder.class.name":
"org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"stream.kafka.decoder.prop.format": "JSON",
"key.serializer":
"org.apache.kafka.common.serialization.ByteArraySerializer",
"value.serializer":
"org.apache.kafka.common.serialization.ByteArraySerializer",
"realtime.segment.flush.threshold.rows": "30000",
"realtime.segment.flush.threshold.time": "12h",
"topic.consumption.rate.limit": "6000"
}
]
}
},
"quota": {
"storage": null,
"maxQueriesPerSecond": null
},
"task": {
"taskTypeConfigsMap": {
}
},
"routing": {
"segmentPrunerTypes": null,
"instanceSelectorType": "strictReplicaGroup"
},
"query": {
"timeoutMs": null
},
"fieldConfigList": null,
"upsertConfig": {
"mode": "FULL",
"deleteRecordColumn": "deleted"
},
"tierConfigs": null
}
```
**Class table data:**
```
{
"id": "0c9b1cea-5b98-4ac0-89dc-1ad914b5bf13",
"name": "class A",
"displayOrder": 10000,
"dateTime": 1741695499859
},
{
"id": "e2428f7d-565e-4b3b-9423-549a850a2ef5",
"name": "class B",
"displayOrder": 20000,
"dateTime": 1741695499869
},
{
"id": "5724f202-3cfb-41f5-894f-b44b0389878e",
"name": "class C",
"displayOrder": 30000,
"dateTime": 1741695499879
},
{
"id": "5ff9b60b-d012-4ba7-a02b-4f74dcbc79ac",
"name": "class D",
"displayOrder": 40000,
"dateTime": 1741695499889
},
{
"id": "9a9af0ec-334c-4212-b5ff-51390f909dde",
"name": "class E",
"displayOrder": 50000,
"dateTime": 1741695499899
}
```
--
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: commits-unsubscr...@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
