Hi, I have added a new multivalued string field in...
# troubleshooting
b
Hi, I have added a new multivalued string field in pinot table. It is getting ingested as expected and I am able to query it using Pinot Query Web Interface. But when I try the same query using a Java/Python client it is throwing following exception.
Copy code
QueryPlanningError:\n'
            'Error composing query plan for: SELECT * from '
            'ace_watchtower_agents_agent_kpi_events WHERE manager_ids = '
            "'GU2406261237361289228350'\n"
            'LIMIT 10001\n'
            'org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:138)\n'
            'org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:158)\n'
            'org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:133)\n'
            'org.apache.pinot.broker.requesthandler.BrokerRequestHandlerDelegate.handleRequest(BrokerRequestHandlerDelegate.java:86)\n'
            'Failed to convert query to relational expression:\n'
            'SELECT `ace_watchtower_agents_agent_kpi_events`.`event_name`, '
            '`ace_watchtower_agents_agent_kpi_events`.`event_timestamp`, '
            '`ace_watchtower_agents_agent_kpi_events`.`executioner_id`, '
            '`ace_watchtower_agents_agent_kpi_events`.`ingestion_timestamp`, '
            '`ace_watchtower_agents_agent_kpi_events`.`manager_ids`, '
            '`ace_watchtower_agents_agent_kpi_events`.`unique_key`\n'
            'FROM `default`.`ace_watchtower_agents_agent_kpi_events` AS '
            '`ace_watchtower_agents_agent_kpi_events`\n'
            'WHERE `ace_watchtower_agents_agent_kpi_events`.`manager_ids` = '
            "CAST('GU2406261237361289228350' AS VARCHAR CHARACTER SET `UTF-8` "
            'ARRAY)\n'
            'FETCH NEXT 10001 ROWS ONLY\n'
            'org.apache.pinot.query.QueryEnvironment.toRelation(QueryEnvironment.java:259)\n'
            'org.apache.pinot.query.QueryEnvironment.compileQuery(QueryEnvironment.java:235)\n'
            'org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:129)\n'
            'org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:158)\n'
            'while converting '
            '`ace_watchtower_agents_agent_kpi_events`.`manager_ids` = '
            "CAST('GU2406261237361289228350' AS VARCHAR CHARACTER SET `UTF-8` "
            'ARRAY)\n'
            'org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)\n'
            'org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:61)\n'
            'org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5838)\n'
            'org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4985)\n'
            'null\n'
            'jdk.internal.reflect.GeneratedMethodAccessor403.invoke(Unknown '
            'Source)\n'
            'java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n'
            'java.base/java.lang.reflect.Method.invoke(Method.java:566)\n'
            'org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)\n'
            "Was not expecting value 'ARRAY' for enumeration "
            "'org.apache.calcite.sql.type.SqlTypeName' in this context\n"
            'org.apache.calcite.util.Util.unexpected(Util.java:1928)\n'
            'org.apache.calcite.rex.RexLiteral.valueMatchesType(RexLiteral.java:397)\n'
            'org.apache.calcite.rex.RexBuilder.canRemoveCastFromLiteral(RexBuilder.java:693)\n'
            'org.apache.calcite.rex.RexBuilder.makeCast(RexBuilder.java:604)\n'}
SELECT * from ace_watchtower_agents_agent_kpi_events WHERE manager_ids = 'GU2406261237361289228350'
this query is working fine using Pinot Query tab
@Mayank @Jackie @Xiang Fu Please help
Schema:
Copy code
{
  "schemaName": "ace_watchtower_agents_agent_kpi_events",
  "enableColumnBasedNullHandling": true,
  "dimensionFieldSpecs": [
    {
      "name": "executioner_id",
      "dataType": "STRING",
      "notNull": true
    },
    {
      "name": "event_name",
      "dataType": "STRING",
      "notNull": true
    },
    {
      "name": "manager_ids",
      "dataType": "STRING",
      "singleValueField": false,
      "notNull": false
    },
    {
      "name": "unique_key",
      "dataType": "STRING",
      "notNull": true
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "event_timestamp",
      "dataType": "LONG",
      "notNull": false,
      "format": "1:MILLISECONDS:EPOCH",
      "granularity": "1:MILLISECONDS"
    },
    {
      "name": "ingestion_timestamp",
      "dataType": "LONG",
      "notNull": false,
      "format": "1:MILLISECONDS:EPOCH",
      "granularity": "1:MILLISECONDS"
    }
  ]
}
Screenshot 2025-08-18 at 8.52.04 PM.png
Screenshot 2025-08-18 at 8.52.37 PM.png
Let me know if more info is required
x
can you try to escape the single quote for
'GU2406261237361289228350'
b
Copy code
'From line 1, column 73 to line 1, column 96: Column '
            "'GU2406261237361289228350' not found in any table\n"
same where filter is working for other string columns.. I think this has something to do with manager_ids multivalued column
x
this means you double quoted
GU2406261237361289228350
which makes the sql parser to recognize
GU2406261237361289228350
as a column name
you should use single quote
to make it literal
b
i am using only single quotes. The same query is working with
Screenshot 2025-08-18 at 9.31.43 PM.png
this is not working with useMultistageEngine=true
there is something wrong with singleValued=false i.e. multiple valued string
x
I think you may need to escape in java or python client
b
I think, It is not about escaping anything. It is working with useMultistageEngine=false
x
which pinot version are you running
b
1.2.0
x
hmm, then
SET useMultistageEngine=true;
should work
b
not true, it works when it is false
x
what if you run
SET useMultistageEngine=true; SELECT * from ace_watchtower_agents_agent_kpi_events WHERE manager_ids = 'GU2406261237361289228350'
b
same error..
x
ah
i know the reason
b
Screenshot 2025-08-18 at 9.49.24 PM.png
Copy code
WHERE arrayToMv(manager_ids) IN ('GU2406261237361289228350')
b
Unsupported function: arraytomv not found
Screenshot 2025-08-18 at 9.55.53 PM.png
on superset this works
but on pinot console:
Screenshot 2025-08-18 at 9.56.42 PM.png
x
arraytomv
is for multiStageEngine only
b
yeah, it worked with multiStageEngine=true
so it should be either multiStageEngine=true with arraytomv or multiStageEngine=false without arraytomv
x
right, because multiStageEngine treat the column
manager_ids
as an array but on the right side, it's just a value not array
so need this kind of type conversion
multi-stage engine is strong-typed than single stage engine
b
okay, I will proceed this way, Will come back if there is any performance/indexing related issues for multivalued field.
Thanks a lot @Xiang Fu for helping me
One more quick question: how indexing works in this case.. will it create dict, inverted index for each value in array ?
x
both will use inverted index if exist
b
okay, thanks