LogoLogo
release-1.2.0
release-1.2.0
  • Introduction
  • Basics
    • Concepts
      • Pinot storage model
      • Architecture
      • Components
        • Cluster
          • Tenant
          • Server
          • Controller
          • Broker
          • Minion
        • Table
          • Segment
            • Deep Store
            • Segment threshold
            • Segment retention
          • Schema
          • Time boundary
        • Pinot Data Explorer
    • Getting Started
      • Running Pinot locally
      • Running Pinot in Docker
      • Quick Start Examples
      • Running in Kubernetes
      • Running on public clouds
        • Running on Azure
        • Running on GCP
        • Running on AWS
      • Create and update a table configuration
      • Batch import example
      • Stream ingestion example
      • HDFS as Deep Storage
      • Troubleshooting Pinot
      • Frequently Asked Questions (FAQs)
        • General
        • Pinot On Kubernetes FAQ
        • Ingestion FAQ
        • Query FAQ
        • Operations FAQ
    • Import Data
      • From Query Console
      • Batch Ingestion
        • Spark
        • Flink
        • Hadoop
        • Backfill Data
        • Dimension table
      • Stream ingestion
        • Ingest streaming data from Apache Kafka
        • Ingest streaming data from Amazon Kinesis
        • Ingest streaming data from Apache Pulsar
        • Configure indexes
      • Stream ingestion with Upsert
      • Segment compaction on upserts
      • Stream ingestion with Dedup
      • Stream ingestion with CLP
      • File Systems
        • Amazon S3
        • Azure Data Lake Storage
        • HDFS
        • Google Cloud Storage
      • Input formats
        • Complex Type (Array, Map) Handling
        • Ingest records with dynamic schemas
      • Reload a table segment
      • Upload a table segment
    • Indexing
      • Bloom filter
      • Dictionary index
      • Forward index
      • FST index
      • Geospatial
      • Inverted index
      • JSON index
      • Native text index
      • Range index
      • Star-tree index
      • Text search support
      • Timestamp index
    • Release notes
      • 1.1.0
      • 1.0.0
      • 0.12.1
      • 0.12.0
      • 0.11.0
      • 0.10.0
      • 0.9.3
      • 0.9.2
      • 0.9.1
      • 0.9.0
      • 0.8.0
      • 0.7.1
      • 0.6.0
      • 0.5.0
      • 0.4.0
      • 0.3.0
      • 0.2.0
      • 0.1.0
    • Recipes
      • Connect to Streamlit
      • Connect to Dash
      • Visualize data with Redash
      • GitHub Events Stream
  • For Users
    • Query
      • Querying Pinot
      • Query Syntax
        • Aggregation Functions
        • Cardinality Estimation
        • Explain Plan (Single-Stage)
        • Explain Plan (Multi-Stage)
        • Filtering with IdSet
        • GapFill Function For Time-Series Dataset
        • Grouping Algorithm
        • JOINs
        • Lookup UDF Join
        • Querying JSON data
        • Transformation Functions
        • Window aggregate
        • Funnel Analysis
      • Query Options
      • Multi stage query
        • Operator Types
          • Aggregate
          • Filter
          • Join
          • Intersect
          • Leaf
          • Literal
          • Mailbox receive
          • Mailbox send
          • Minus
          • Sort or limit
          • Transform
          • Union
          • Window
        • Understanding Stages
        • Explain
        • Stats
      • User-Defined Functions (UDFs)
    • APIs
      • Broker Query API
        • Query Response Format
      • Controller Admin API
      • Controller API Reference
    • External Clients
      • JDBC
      • Java
      • Python
      • Golang
    • Tutorials
      • Use OSS as Deep Storage for Pinot
      • Ingest Parquet Files from S3 Using Spark
      • Creating Pinot Segments
      • Use S3 as Deep Storage for Pinot
      • Use S3 and Pinot in Docker
      • Batch Data Ingestion In Practice
      • Schema Evolution
  • For Developers
    • Basics
      • Extending Pinot
        • Writing Custom Aggregation Function
        • Segment Fetchers
      • Contribution Guidelines
      • Code Setup
      • Code Modules and Organization
      • Dependency Management
      • Update documentation
    • Advanced
      • Data Ingestion Overview
      • Ingestion Aggregations
      • Ingestion Transformations
      • Null value support
      • Use the multi-stage query engine (v2)
      • Troubleshoot issues with the multi-stage query engine (v2)
      • Advanced Pinot Setup
    • Plugins
      • Write Custom Plugins
        • Input Format Plugin
        • Filesystem Plugin
        • Batch Segment Fetcher Plugin
        • Stream Ingestion Plugin
    • Design Documents
      • Segment Writer API
  • For Operators
    • Deployment and Monitoring
      • Set up cluster
      • Server Startup Status Checkers
      • Set up table
      • Set up ingestion
      • Decoupling Controller from the Data Path
      • Segment Assignment
      • Instance Assignment
      • Rebalance
        • Rebalance Servers
        • Rebalance Brokers
        • Rebalance Tenant
      • Separating data storage by age
        • Using multiple tenants
        • Using multiple directories
      • Pinot managed Offline flows
      • Minion merge rollup task
      • Consistent Push and Rollback
      • Access Control
      • Monitoring
      • Tuning
        • Real-time
        • Routing
        • Query Routing using Adaptive Server Selection
        • Query Scheduling
      • Upgrading Pinot with confidence
      • Managing Logs
      • OOM Protection Using Automatic Query Killing
    • Command-Line Interface (CLI)
    • Configuration Recommendation Engine
    • Tutorials
      • Authentication
        • Basic auth access control
        • ZkBasicAuthAccessControl
      • Configuring TLS/SSL
      • Build Docker Images
      • Running Pinot in Production
      • Kubernetes Deployment
      • Amazon EKS (Kafka)
      • Amazon MSK (Kafka)
      • Monitor Pinot using Prometheus and Grafana
      • Performance Optimization Configurations
  • Configuration Reference
    • Cluster
    • Controller
    • Broker
    • Server
    • Table
    • Ingestion
    • Schema
    • Ingestion Job Spec
    • Monitoring Metrics
    • Functions
      • ABS
      • ADD
      • ago
      • EXPR_MIN / EXPR_MAX
      • arrayConcatDouble
      • arrayConcatFloat
      • arrayConcatInt
      • arrayConcatLong
      • arrayConcatString
      • arrayContainsInt
      • arrayContainsString
      • arrayDistinctInt
      • arrayDistinctString
      • arrayIndexOfInt
      • arrayIndexOfString
      • ARRAYLENGTH
      • arrayRemoveInt
      • arrayRemoveString
      • arrayReverseInt
      • arrayReverseString
      • arraySliceInt
      • arraySliceString
      • arraySortInt
      • arraySortString
      • arrayUnionInt
      • arrayUnionString
      • AVGMV
      • Base64
      • caseWhen
      • ceil
      • CHR
      • codepoint
      • concat
      • count
      • COUNTMV
      • COVAR_POP
      • COVAR_SAMP
      • day
      • dayOfWeek
      • dayOfYear
      • DISTINCT
      • DISTINCTAVG
      • DISTINCTAVGMV
      • DISTINCTCOUNT
      • DISTINCTCOUNTBITMAP
      • DISTINCTCOUNTHLLMV
      • DISTINCTCOUNTHLL
      • DISTINCTCOUNTBITMAPMV
      • DISTINCTCOUNTMV
      • DISTINCTCOUNTRAWHLL
      • DISTINCTCOUNTRAWHLLMV
      • DISTINCTCOUNTRAWTHETASKETCH
      • DISTINCTCOUNTTHETASKETCH
      • DISTINCTSUM
      • DISTINCTSUMMV
      • DIV
      • DATETIMECONVERT
      • DATETRUNC
      • exp
      • FIRSTWITHTIME
      • FLOOR
      • FrequentLongsSketch
      • FrequentStringsSketch
      • FromDateTime
      • FromEpoch
      • FromEpochBucket
      • FUNNELCOUNT
      • FunnelCompleteCount
      • FunnelMaxStep
      • FunnelMatchStep
      • Histogram
      • hour
      • isSubnetOf
      • JSONFORMAT
      • JSONPATH
      • JSONPATHARRAY
      • JSONPATHARRAYDEFAULTEMPTY
      • JSONPATHDOUBLE
      • JSONPATHLONG
      • JSONPATHSTRING
      • jsonextractkey
      • jsonextractscalar
      • LAG
      • LASTWITHTIME
      • LEAD
      • length
      • ln
      • lower
      • lpad
      • ltrim
      • max
      • MAXMV
      • MD5
      • millisecond
      • min
      • minmaxrange
      • MINMAXRANGEMV
      • MINMV
      • minute
      • MOD
      • mode
      • month
      • mult
      • now
      • percentile
      • percentileest
      • percentileestmv
      • percentilemv
      • percentiletdigest
      • percentiletdigestmv
      • percentilekll
      • percentilerawkll
      • percentilekllmv
      • percentilerawkllmv
      • quarter
      • regexpExtract
      • regexpReplace
      • remove
      • replace
      • reverse
      • round
      • ROW_NUMBER
      • rpad
      • rtrim
      • second
      • SEGMENTPARTITIONEDDISTINCTCOUNT
      • sha
      • sha256
      • sha512
      • sqrt
      • startswith
      • ST_AsBinary
      • ST_AsText
      • ST_Contains
      • ST_Distance
      • ST_GeogFromText
      • ST_GeogFromWKB
      • ST_GeometryType
      • ST_GeomFromText
      • ST_GeomFromWKB
      • STPOINT
      • ST_Polygon
      • strpos
      • ST_Union
      • SUB
      • substr
      • sum
      • summv
      • TIMECONVERT
      • timezoneHour
      • timezoneMinute
      • ToDateTime
      • ToEpoch
      • ToEpochBucket
      • ToEpochRounded
      • TOJSONMAPSTR
      • toGeometry
      • toSphericalGeography
      • trim
      • upper
      • Url
      • UTF8
      • VALUEIN
      • week
      • year
      • yearOfWeek
      • Extract
    • Plugin Reference
      • Stream Ingestion Connectors
      • VAR_POP
      • VAR_SAMP
      • STDDEV_POP
      • STDDEV_SAMP
    • Dynamic Environment
  • Reference
    • Single-stage query engine (v1)
    • Multi-stage query engine (v2)
    • Troubleshooting
      • Troubleshoot issues with the multi-stage query engine (v2)
      • Troubleshoot issues with ZooKeeper znodes
  • RESOURCES
    • Community
    • Team
    • Blogs
    • Presentations
    • Videos
  • Integrations
    • Tableau
    • Trino
    • ThirdEye
    • Superset
    • Presto
    • Spark-Pinot Connector
  • Contributing
    • Contribute Pinot documentation
    • Style guide
Powered by GitBook
On this page
  • Limitations of the multi-stage query engine
  • Support for multi-value columns is limited
  • Schema and other prefixes are not supported
  • Modifying query behavior based on the cluster config is not supported
  • Ambiguous reference to a projected column in statement clauses
  • Tightened restriction on function naming
  • Tightened restriction on function signature and type matching
  • Default names for projections with function calls
  • Table names and column names are case sensitive
  • Arbitrary number of arguments isn't supported
  • NULL function support
  • Custom transform function support
  • Custom aggregate function support
  • Different type names
  • Varbinary literals
  • Troubleshoot errors
  • Semantic/runtime errors
  • Timeout errors

Was this helpful?

Edit on GitHub
Export as PDF
  1. Reference
  2. Troubleshooting

Troubleshoot issues with the multi-stage query engine (v2)

Troubleshoot issues with the multi-stage query engine (v2).

PreviousTroubleshootingNextTroubleshoot issues with ZooKeeper znodes

Was this helpful?

Learn how to when using the multi-stage query engine (v2), and see .

Find instructions on , or see a high-level overview of .

Limitations of the multi-stage query engine

We are continuously improving the multi-stage query engine. A few limitations to call out:

Support for multi-value columns is limited

Support for multi-value columns is limited to projections, and predicates must use the arrayToMv function. For example, to successfully run the following query:

-- example 1: used in GROUP-BY
SELECT count(*), RandomAirports FROM airlineStats 
GROUP BY RandomAirports

-- example 2: used in PREDICATE
SELECT * FROM airlineStats WHERE RandomAirports IN ('SFO', 'JFK')

-- example 3: used in ORDER-BY
SELECT count(*), RandomAirports FROM airlineStats 
GROUP BY RandomAirports
ORDER BY RandomAirports DESC

You must include arrayToMv in the query as follows:

-- example 1: used in GROUP-BY
SELECT count(*), arrayToMv(RandomAirports) FROM airlineStats 
GROUP BY arrayToMv(RandomAirports)

-- example 2: used in PREDICATE
SELECT * FROM airlineStats WHERE arrayToMv(RandomAirports) IN ('SFO', 'JFK')

-- example 3: used in ORDER-BY
SELECT count(*), arrayToMV(RandomAirports) FROM airlineStats 
GROUP BY arrayToMV(RandomAirports)
ORDER BY arrayToMV(RandomAirports) DESC

Schema and other prefixes are not supported

Schema and other prefixes are not supported in queries. For example, the following queries are not supported:

SELECT* from default.myTable;
SELECT * from schemaName.myTable;

Queries without prefixes are supported:

SELECT * from myTable;

Modifying query behavior based on the cluster config is not supported

Modifying query behavior based on the cluster configuration is not supported. distinctcounthll, distinctcounthllmv, distinctcountrawhll, and `distinctcountrawhllmv` use a different default value of log2mParam in the multi-stage engine. In multi-stage, this value can no longer be configured. Therefore, the following query may produce different results in single-stage and multi-stage engine:

select distinctcounthll(col) from myTable

To ensure multi-stage returns the same result, specify the log2mParam value in your query:

select distinctcounthll(col, 8) from myTable

Ambiguous reference to a projected column in statement clauses

If a column is repeated more than once in SELECT statement, that column requires disambiguate aliasing. For example, in the following query, the reference to colA is ambiguous whether it's to the first or second projected colA:

SELECT colA, colA, COUNT(*)
FROM myTable GROUP BY colA ORDER BY colA

The solution is to rewrite the query either use aliasing:

SELECT colA AS tmpA, colA as tmpB, COUNT(*) 
FROM myTable GROUP BY tmpA, tmpB ORDER BY tmpA

Or use index-based referencing:

SELECT colA, colA, COUNT(*) 
FROM myTable GROUP BY 1, 2 ORDER BY 1

Tightened restriction on function naming

Pinot single-stage query engine automatically removes the underscore _ character from function names. So co_u_n_t()is equivalent to count().

In multi-stage, function naming restrictions were tightened, so the underscore(_) character is only allowed to separate word boundaries in a function name. Also camel case is supported in function names. For example, the following function names are allowed:

is_distinct_from(...)
isDistinctFrom(...)

Tightened restriction on function signature and type matching

Pinot single-stage query engine automatically do implicit type casts in many of the situations, for example when running the following:

timestampCol >= longCol

it will automatically convert both values to long datatypes before comparison. This behavior however could cause issues and thus it is not so widely applied in the multi-stage engine where a stricter datatype conformance is enforced. the example above should be explicitly written as:

CAST(timestampCol AS BITINT) >= longCol 

Default names for projections with function calls

Default names for projections with function calls are different between single and multi-stage.

  • For example, in multi-stage, the following query:

  SELECT count(*) from mytable 

Returns the following result:

    "columnNames": [
        "EXPR$0"
      ],
  • In single-stage, the following function:

  SELECT count(*) from mytable

Returns the following result:

      "columnNames": [
        "count(*)"
      ],

Table names and column names are case sensitive

In multi-stage, table and column names and are case sensitive. In single-stage they were not. For example, the following two queries are not equivalent in multi-stage engine:

select * from myTable

select * from mytable

Note: Function names are not case sensitive in neither single nor multi-stage.

Arbitrary number of arguments isn't supported

An arbitrary number of arguments is no longer supported in multi-stage. For example, in single-stage, the following query worked:

select add(1,2,3,4,5) from table

In multi-stage, this query must be rewritten as follows:

select add(1, add(2,add(3, add(4,5)))) from table

Note: Remember that select 1 + 2 + 3 + 4 + 5 from table is still valid in multi-stage

NULL function support

Custom transform function support

In multi-stage:

  • The histogram function is not supported.

  • The timeConvert function is not supported, see dateTimeConvert for more details.

  • The dateTimeConvertWindowHop function is not supported.

  • Array & Map-related functions are not supported.

Custom aggregate function support

  • aggregate function that requires literal input (such as percentile, firstWithTime) might result in a non-compilable query plan.

Different type names

The multi-stage engine uses different type names than the single-stage engine. Although the classical names must still be used in schemas and some SQL expressions, the new names must be used in CAST expressions.

The following table shows the differences in type names:

Single-stage engine
Multi-stage engine

NULL

NULL

BOOLEAN

BOOLEAN

INT

INT

LONG

BIGINT

BIG_DECIMAL

DECIMAL

FLOAT

FLOAT/REAL

DOUBLE

DOUBLE

INTERVAL

INTERVAL

TIMESTAMP

TIMESTAMP

STRING

VARCHAR

BYTES

VARBINARY

-

ARRAY

JSON

-

Varbinary literals

VARBINARY literals in multi-stage engine must be prefixed with X or x. For example, the following query:

SELECT col1, col2 FROM myTable where bytesCol = X'4a220e6096b25eadb88358cb44068a3248254675'

In single-stage engine the same query would be:

-- not supported in multi-stage
SELECT col1, col2 FROM myTable where bytesCol = '4a220e6096b25eadb88358cb44068a3248254675'

Troubleshoot errors

Troubleshoot semantic/runtime errors and timeout errors.

Semantic/runtime errors

  • Try downloading the latest docker image or building from the latest master commit.

    • We continuously push bug fixes for the multi-stage engine so bugs you encountered might have already been fixed in the latest master build.

  • Try rewriting your query.

    • Some functions previously supported in the single-stage query engine (v1) may have a new way to express in the multi-stage engine (v2). Check and see if you are using any non-standard SQL functions or semantics.

Timeout errors

  • Try reducing the size of the table(s) used.

    • Add higher selectivity filters to the tables.

  • Try executing part of the subquery or a simplified version of the query first.

    • This helps to determine the selectivity and scale of the query being executed.

  • Try adding more servers.

    • The new multi-stage engine runs distributed across the entire cluster, so adding more servers to partitioned queries such as GROUP BY aggregates, and equality JOINs help speed up the query runtime.

Null handling is not supported when tables use table based null storing. You have to use column base null storing instead. See

null handling support
how to enable the multi-stage query engine
how the multi-stage query engine works
troubleshoot errors
multi-stage query engine limitations