docs/0.20.0/querying/sql.html - druid-website - Git at Google

 <!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>SQL · Apache Druid</title><meta name="viewport" content="width=device-width"/><link rel="canonical" href="https://druid.apache.org/docs/0.20.0/querying/sql.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="&lt;!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="0.20.0" /><meta property="og:title" content="SQL · Apache Druid"/><meta property="og:type" content="website"/><meta property="og:url" content="https://druid.apache.org/index.html"/><meta property="og:description" content="&lt;!--"/><meta property="og:image" content="https://druid.apache.org/img/druid_nav.png"/><meta name="twitter:card" content="summary"/><meta name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"/><link rel="shortcut icon" href="/img/favicon.png"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.css"/><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/default.min.css"/><script async="" src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script><script>
               window.dataLayer = window.dataLayer || [];
               function gtag(){dataLayer.push(arguments); }
               gtag('js', new Date());
               gtag('config', 'UA-131010415-1');
             </script><link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css"/><link rel="stylesheet" href="/css/code-block-buttons.css"/><script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.4/clipboard.min.js"></script><script type="text/javascript" src="/js/code-block-buttons.js"></script><script src="/js/scrollSpy.js"></script><link rel="stylesheet" href="/css/main.css"/><script src="/js/codetabs.js"></script></head><body class="sideNavVisible separateOnPageNav"><div class="fixedHeaderContainer"><div class="headerWrapper wrapper"><header><a href="/"><img class="logo" src="/img/druid_nav.png" alt="Apache Druid"/></a><div class="navigationWrapper navigationSlider"><nav class="slidingNav"><ul class="nav-site nav-site-internal"><li class=""><a href="/technology" target="_self">Technology</a></li><li class=""><a href="/use-cases" target="_self">Use Cases</a></li><li class=""><a href="/druid-powered" target="_self">Powered By</a></li><li class="siteNavGroupActive"><a href="/docs/0.20.0/design/index.html" target="_self">Docs</a></li><li class=""><a href="/community/" target="_self">Community</a></li><li class=""><a href="https://www.apache.org" target="_self">Apache</a></li><li class=""><a href="/downloads.html" target="_self">Download</a></li><li class="navSearchWrapper reactNavSearchWrapper"><input type="text" id="search_input_react" placeholder="Search" title="Search"/></li></ul></nav></div></header></div></div><div class="navPusher"><div class="docMainWrapper wrapper"><div class="docsNavContainer" id="docsNav"><nav class="toc"><div class="toggleNav"><section class="navWrapper wrapper"><div class="navBreadcrumb wrapper"><div class="navToggle" id="navToggler"><div class="hamburger-menu"><div class="line1"></div><div class="line2"></div><div class="line3"></div></div></div><h2><i>›</i><span>Querying</span></h2><div class="tocToggler" id="tocToggler"><i class="icon-toc"></i></div></div><div class="navGroups"><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Getting started<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/index.html">Introduction to Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/index.html">Quickstart</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/docker.html">Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/single-server.html">Single server deployment</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/cluster.html">Clustered deployment</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Tutorials<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-batch.html">Loading files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-kafka.html">Load from Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-batch-hadoop.html">Load from Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-query.html">Querying data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-rollup.html">Roll-up</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-retention.html">Configuring data retention</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-update-data.html">Updating existing data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-compaction.html">Compacting segments</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-delete-data.html">Deleting data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-ingestion-spec.html">Writing an ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-transform-spec.html">Transforming input data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Design<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/architecture.html">Design</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/segments.html">Segments</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/processes.html">Processes and servers</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/dependencies/deep-storage.html">Deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/dependencies/metadata-storage.html">Metadata storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/dependencies/zookeeper.html">ZooKeeper</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Ingestion<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/index.html">Ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/data-formats.html">Data formats</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/schema-design.html">Schema design tips</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/data-management.html">Data management</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Stream ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/kafka-ingestion.html">Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/kinesis-ingestion.html">Amazon Kinesis</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/tranquility.html">Tranquility</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Batch ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/native-batch.html">Native batch</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/hadoop.html">Hadoop-based</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/tasks.html">Task reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/faq.html">Troubleshooting FAQ</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Querying<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem navListItemActive"><a class="navItem" href="/docs/0.20.0/querying/sql.html">Druid SQL</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/querying.html">Native queries</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/query-execution.html">Query execution</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Concepts</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/datasource.html">Datasources</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/joins.html">Joins</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/lookups.html">Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/multi-value-dimensions.html">Multi-value dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/multitenancy.html">Multitenancy</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/caching.html">Query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/query-context.html">Context parameters</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query types</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/timeseriesquery.html">Timeseries</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/topnquery.html">TopN</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/groupbyquery.html">GroupBy</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/scan-query.html">Scan</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/searchquery.html">Search</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/timeboundaryquery.html">TimeBoundary</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/segmentmetadataquery.html">SegmentMetadata</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/datasourcemetadataquery.html">DatasourceMetadata</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query components</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/filters.html">Filters</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/granularities.html">Granularities</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/dimensionspecs.html">Dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/aggregations.html">Aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/post-aggregations.html">Post-aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/misc/math-expr.html">Expressions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/having.html">Having filters (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/limitspec.html">Sorting and limiting (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/topnmetricspec.html">Sorting (topN)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/sorting-orders.html">String comparators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/virtual-columns.html">Virtual columns</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/geo.html">Spatial filters</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Configuration<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/configuration/index.html">Configuration reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions.html">Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/configuration/logging.html">Logging</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Operations<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/druid-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/getting-started.html">Getting started with Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/basic-cluster-tuning.html">Basic cluster tuning</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/api-reference.html">API reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/high-availability.html">High availability</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/rolling-updates.html">Rolling updates</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/rule-configuration.html">Retaining or automatically dropping data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/metrics.html">Metrics</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/alerts.html">Alerts</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/other-hadoop.html">Working with different versions of Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/http-compression.html">HTTP compression</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/tls-support.html">TLS support</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/password-provider.html">Password providers</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/dump-segment.html">dump-segment tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/reset-cluster.html">reset-cluster tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/insert-segment-to-db.html">insert-segment-to-db tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/pull-deps.html">pull-deps tool</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Misc</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/management-uis.html">Legacy Management UIs</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/deep-storage-migration.html">Deep storage migration</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/export-metadata.html">Export Metadata Tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/metadata-migration.html">Metadata Migration</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/segment-optimization.html">Segment Size Optimization</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/operations/use_sbt_to_build_fat_jar.html">Content for build.sbt</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Development<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/overview.html">Developing on Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/modules.html">Creating extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/javascript.html">JavaScript functionality</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/build.html">Build from source</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/versioning.html">Versioning</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/experimental.html">Experimental features</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Misc<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/misc/papers-and-talks.html">Papers</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Hidden<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-elasticsearch.html">Apache Druid vs Elasticsearch</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-key-value.html">Apache Druid vs. Key/Value Stores (HBase/Cassandra/OpenTSDB)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-kudu.html">Apache Druid vs Kudu</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-redshift.html">Apache Druid vs Redshift</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-spark.html">Apache Druid vs Spark</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/comparisons/druid-vs-sql-on-hadoop.html">Apache Druid vs SQL-on-Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/auth.html">Authentication and Authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/broker.html">Broker</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/coordinator.html">Coordinator Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/historical.html">Historical Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/indexer.html">Indexer Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/indexing-service.html">Indexing Service</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/middlemanager.html">MiddleManager Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/overlord.html">Overlord Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/router.html">Router Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/design/peons.html">Peons</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/approximate-histograms.html">Approximate Histogram aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/avro.html">Apache Avro</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/azure.html">Microsoft Azure</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/bloom-filter.html">Bloom Filter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/datasketches-extension.html">DataSketches extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/datasketches-hll.html">DataSketches HLL Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/datasketches-quantiles.html">DataSketches Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/datasketches-theta.html">DataSketches Theta Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/datasketches-tuple.html">DataSketches Tuple Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/druid-basic-security.html">Basic Security</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/druid-kerberos.html">Kerberos</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/druid-lookups.html">Cached Lookup Module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/druid-ranger-security.html">Apache Ranger Security</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/google.html">Google Cloud Storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/hdfs.html">HDFS</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/kafka-extraction-namespace.html">Apache Kafka Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/lookups-cached-global.html">Globally Cached Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/mysql.html">MySQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/orc.html">ORC Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/druid-pac4j.html">Druid pac4j based Security extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/parquet.html">Apache Parquet Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/postgresql.html">PostgreSQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/protobuf.html">Protobuf</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/s3.html">S3-compatible</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/simple-client-sslcontext.html">Simple SSLContext Provider Module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/stats.html">Stats aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-core/test-stats.html">Test Stats Aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/ambari-metrics-emitter.html">Ambari Metrics Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/cassandra.html">Apache Cassandra</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/cloudfiles.html">Rackspace Cloud Files</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/distinctcount.html">DistinctCount Aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/graphite.html">Graphite Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/influx.html">InfluxDB Line Protocol Parser</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/influxdb-emitter.html">InfluxDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/kafka-emitter.html">Kafka Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/materialized-view.html">Materialized View</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/momentsketch-quantiles.html">Moment Sketches for Approximate Quantiles module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/moving-average-query.html">Moving Average Query</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/opentsdb-emitter.html">OpenTSDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/redis-cache.html">Druid Redis Cache</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/sqlserver.html">Microsoft SQLServer</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/statsd.html">StatsD Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/tdigestsketch-quantiles.html">T-Digest Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/thrift.html">Thrift</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/time-min-max.html">Timestamp Min/Max aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/gce-extensions.html">GCE Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/development/extensions-contrib/aliyun-oss.html">Aliyun OSS</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/hll-old.html">Cardinality/HyperUnique aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/querying/select-query.html">Select</a></li><li class="navListItem"><a class="navItem" href="/docs/0.20.0/ingestion/standalone-realtime.html">Realtime Process</a></li></ul></div></div></section></div><script>
             var coll = document.getElementsByClassName('collapsible');
             var checkActiveCategory = true;
             for (var i = 0; i < coll.length; i++) {
               var links = coll[i].nextElementSibling.getElementsByTagName('*');
               if (checkActiveCategory){
                 for (var j = 0; j < links.length; j++) {
                   if (links[j].classList.contains('navListItemActive')){
                     coll[i].nextElementSibling.classList.toggle('hide');
                     coll[i].childNodes[1].classList.toggle('rotate');
                     checkActiveCategory = false;
                     break;
                   }
                 }
               }

               coll[i].addEventListener('click', function() {
                 var arrow = this.childNodes[1];
                 arrow.classList.toggle('rotate');
                 var content = this.nextElementSibling;
                 content.classList.toggle('hide');
               });
             }

             document.addEventListener('DOMContentLoaded', function() {
               createToggler('#navToggler', '#docsNav', 'docsSliderActive');
               createToggler('#tocToggler', 'body', 'tocActive');

               var headings = document.querySelector('.toc-headings');
               headings && headings.addEventListener('click', function(event) {
                 var el = event.target;
                 while(el !== headings){
                   if (el.tagName === 'A') {
                     document.body.classList.remove('tocActive');
                     break;
                   } else{
                     el = el.parentNode;
                   }
                 }
               }, false);

               function createToggler(togglerSelector, targetSelector, className) {
                 var toggler = document.querySelector(togglerSelector);
                 var target = document.querySelector(targetSelector);

                 if (!toggler) {
                   return;
                 }

                 toggler.onclick = function(event) {
                   event.preventDefault();

                   target.classList.toggle(className);
                 };
               }
             });
         </script></nav></div><div class="container mainContainer docsContainer"><div class="wrapper"><div class="post"><header class="postHeader"><a class="edit-page-link button" href="https://github.com/apache/druid/edit/master/docs/querying/sql.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">SQL</h1></header><article><div><span><!--
   ~ Licensed to the Apache Software Foundation (ASF) under one
   ~ or more contributor license agreements.  See the NOTICE file
   ~ distributed with this work for additional information
   ~ regarding copyright ownership.  The ASF licenses this file
   ~ to you under the Apache License, Version 2.0 (the
   ~ "License"); you may not use this file except in compliance
   ~ with the License.  You may obtain a copy of the License at
   ~
   ~   http://www.apache.org/licenses/LICENSE-2.0
   ~
   ~ Unless required by applicable law or agreed to in writing,
   ~ software distributed under the License is distributed on an
   ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   ~ KIND, either express or implied.  See the License for the
   ~ specific language governing permissions and limitations
   ~ under the License.
   -->
 <!--
   The format of the tables that describe the functions and operators
   should not be changed without updating the script create-sql-function-doc
   in web-console/script/create-sql-function-doc, because the script detects
   patterns in this markdown file and parse it to TypeScript file for web console
 -->
 <blockquote>
 <p>Apache Druid supports two query languages: Druid SQL and <a href="/docs/0.20.0/querying/querying.html">native queries</a>.
 This document describes the SQL language.</p>
 </blockquote>
 <p>Druid SQL is a built-in SQL layer and an alternative to Druid's native JSON-based query language, and is powered by a
 parser and planner based on <a href="https://calcite.apache.org/">Apache Calcite</a>. Druid SQL translates SQL into native Druid
 queries on the query Broker (the first process you query), which are then passed down to data processes as native Druid
 queries. Other than the (slight) overhead of <a href="#query-translation">translating</a> SQL on the Broker, there isn't an
 additional performance penalty versus native queries.</p>
 <h2><a class="anchor" aria-hidden="true" id="query-syntax"></a><a href="#query-syntax" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Query syntax</h2>
 <p>Druid SQL supports SELECT queries with the following structure:</p>
 <pre><code class="hljs">[ <span class="hljs-keyword">EXPLAIN</span> PLAN <span class="hljs-keyword">FOR</span> ]
 [ <span class="hljs-keyword">WITH</span> tableName [ ( column1, column2, ... ) ] <span class="hljs-keyword">AS</span> ( <span class="hljs-keyword">query</span> ) ]
 <span class="hljs-keyword">SELECT</span> [ <span class="hljs-keyword">ALL</span> | <span class="hljs-keyword">DISTINCT</span> ] { * | exprs }
 <span class="hljs-keyword">FROM</span> { &lt;<span class="hljs-keyword">table</span>&gt; | (&lt;subquery&gt;) | &lt;o1&gt; [ <span class="hljs-keyword">INNER</span> | <span class="hljs-keyword">LEFT</span> ] <span class="hljs-keyword">JOIN</span> &lt;o2&gt; <span class="hljs-keyword">ON</span> condition }
 [ <span class="hljs-keyword">WHERE</span> expr ]
 [ <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> [ exprs | <span class="hljs-keyword">GROUPING</span> <span class="hljs-keyword">SETS</span> ( (exprs), ... ) | <span class="hljs-keyword">ROLLUP</span> (exprs) | <span class="hljs-keyword">CUBE</span> (exprs) ] ]
 [ <span class="hljs-keyword">HAVING</span> expr ]
 [ <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> expr [ <span class="hljs-keyword">ASC</span> | <span class="hljs-keyword">DESC</span> ], expr [ <span class="hljs-keyword">ASC</span> | <span class="hljs-keyword">DESC</span> ], ... ]
 [ <span class="hljs-keyword">LIMIT</span> <span class="hljs-keyword">limit</span> ]
 [ <span class="hljs-keyword">OFFSET</span> <span class="hljs-keyword">offset</span> ]
 [ <span class="hljs-keyword">UNION</span> <span class="hljs-keyword">ALL</span> &lt;another <span class="hljs-keyword">query</span>&gt; ]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="from"></a><a href="#from" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>FROM</h3>
 <p>The FROM clause can refer to any of the following:</p>
 <ul>
 <li><a href="datasource.html#table">Table datasources</a> from the <code>druid</code> schema. This is the default schema, so Druid table
 datasources can be referenced as either <code>druid.dataSourceName</code> or simply <code>dataSourceName</code>.</li>
 <li><a href="datasource.html#lookup">Lookups</a> from the <code>lookup</code> schema, for example <code>lookup.countries</code>. Note that lookups can
 also be queried using the <a href="#string-functions"><code>LOOKUP</code> function</a>.</li>
 <li><a href="datasource.html#query">Subqueries</a>.</li>
 <li><a href="datasource.html#join">Joins</a> between anything in this list, except between native datasources (table, lookup,
 query) and system tables. The join condition must be an equality between expressions from the left- and right-hand side
 of the join.</li>
 <li><a href="#metadata-tables">Metadata tables</a> from the <code>INFORMATION_SCHEMA</code> or <code>sys</code> schemas. Unlike the other options for the
 FROM clause, metadata tables are not considered datasources. They exist only in the SQL layer.</li>
 </ul>
 <p>For more information about table, lookup, query, and join datasources, refer to the <a href="datasource.html">Datasources</a>
 documentation.</p>
 <h3><a class="anchor" aria-hidden="true" id="where"></a><a href="#where" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>WHERE</h3>
 <p>The WHERE clause refers to columns in the FROM table, and will be translated to <a href="filters.html">native filters</a>. The
 WHERE clause can also reference a subquery, like <code>WHERE col1 IN (SELECT foo FROM ...)</code>. Queries like this are executed
 as a join on the subquery, described below in the <a href="#subqueries">Query translation</a> section.</p>
 <h3><a class="anchor" aria-hidden="true" id="group-by"></a><a href="#group-by" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>GROUP BY</h3>
 <p>The GROUP BY clause refers to columns in the FROM table. Using GROUP BY, DISTINCT, or any aggregation functions will
 trigger an aggregation query using one of Druid's <a href="#query-types">three native aggregation query types</a>. GROUP BY
 can refer to an expression or a select clause ordinal position (like <code>GROUP BY 2</code> to group by the second selected
 column).</p>
 <p>The GROUP BY clause can also refer to multiple grouping sets in three ways. The most flexible is GROUP BY GROUPING SETS,
 for example <code>GROUP BY GROUPING SETS ( (country, city), () )</code>. This example is equivalent to a <code>GROUP BY country, city</code>
 followed by <code>GROUP BY ()</code> (a grand total). With GROUPING SETS, the underlying data is only scanned one time, leading to
 better efficiency. Second, GROUP BY ROLLUP computes a grouping set for each level of the grouping expressions. For
 example <code>GROUP BY ROLLUP (country, city)</code> is equivalent to <code>GROUP BY GROUPING SETS ( (country, city), (country), () )</code>
 and will produce grouped rows for each country / city pair, along with subtotals for each country, along with a grand
 total. Finally, GROUP BY CUBE computes a grouping set for each combination of grouping expressions. For example,
 <code>GROUP BY CUBE (country, city)</code> is equivalent to <code>GROUP BY GROUPING SETS ( (country, city), (country), (city), () )</code>.
 Grouping columns that do not apply to a particular row will contain <code>NULL</code>. For example, when computing
 <code>GROUP BY GROUPING SETS ( (country, city), () )</code>, the grand total row corresponding to <code>()</code> will have <code>NULL</code> for the
 &quot;country&quot; and &quot;city&quot; columns.</p>
 <p>When using GROUP BY GROUPING SETS, GROUP BY ROLLUP, or GROUP BY CUBE, be aware that results may not be generated in the
 order that you specify your grouping sets in the query. If you need results to be generated in a particular order, use
 the ORDER BY clause.</p>
 <h3><a class="anchor" aria-hidden="true" id="having"></a><a href="#having" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>HAVING</h3>
 <p>The HAVING clause refers to columns that are present after execution of GROUP BY. It can be used to filter on either
 grouping expressions or aggregated values. It can only be used together with GROUP BY.</p>
 <h3><a class="anchor" aria-hidden="true" id="order-by"></a><a href="#order-by" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>ORDER BY</h3>
 <p>The ORDER BY clause refers to columns that are present after execution of GROUP BY. It can be used to order the results
 based on either grouping expressions or aggregated values. ORDER BY can refer to an expression or a select clause
 ordinal position (like <code>ORDER BY 2</code> to order by the second selected column). For non-aggregation queries, ORDER BY
 can only order by the <code>__time</code> column. For aggregation queries, ORDER BY can order by any column.</p>
 <h3><a class="anchor" aria-hidden="true" id="limit"></a><a href="#limit" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>LIMIT</h3>
 <p>The LIMIT clause limits the number of rows returned. In some situations Druid will push down this limit to data servers,
 which boosts performance. Limits are always pushed down for queries that run with the native Scan or TopN query types.
 With the native GroupBy query type, it is pushed down when ordering on a column that you are grouping by. If you notice
 that adding a limit doesn't change performance very much, then it's possible that Druid wasn't able to push down the
 limit for your query.</p>
 <h3><a class="anchor" aria-hidden="true" id="offset"></a><a href="#offset" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>OFFSET</h3>
 <p>The OFFSET clause skips a certain number of rows when returning results.</p>
 <p>If both LIMIT and OFFSET are provided, then OFFSET will be applied first, followed by LIMIT. For example, using
 LIMIT 100 OFFSET 10 will return 100 rows, starting from row number 10.</p>
 <p>Together, LIMIT and OFFSET can be used to implement pagination. However, note that if the underlying datasource is
 modified between page fetches, then the different pages will not necessarily align with each other.</p>
 <p>There are two important factors that can affect the performance of queries that use OFFSET:</p>
 <ul>
 <li>Skipped rows still need to be generated internally and then discarded, meaning that raising offsets to high values
 can cause queries to use additional resources.</li>
 <li>OFFSET is only supported by the Scan and GroupBy <a href="#query-types">native query types</a>. Therefore, a query with OFFSET
 will use one of those two types, even if it might otherwise have run as a Timeseries or TopN. Switching query engines
 in this way can affect performance.</li>
 </ul>
 <h3><a class="anchor" aria-hidden="true" id="union-all"></a><a href="#union-all" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>UNION ALL</h3>
 <p>The &quot;UNION ALL&quot; operator fuses multiple queries together. Druid SQL supports the UNION ALL operator in two situations:
 top-level and table-level. Queries that use UNION ALL in any other way will not be able to execute.</p>
 <h4><a class="anchor" aria-hidden="true" id="top-level"></a><a href="#top-level" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Top-level</h4>
 <p>UNION ALL can be used at the very top outer layer of a SQL query (not in a subquery, and not in the FROM clause). In
 this case, the underlying queries will be run separately, back to back, and their results will all be returned in
 one result set.</p>
 <p>For example:</p>
 <pre><code class="hljs"><span class="hljs-keyword">SELECT</span> COUNT<span class="hljs-comment">(*) FROM tbl WHERE my_column = 'value1'
 UNION ALL
 SELECT COUNT(*)</span> <span class="hljs-keyword">FROM</span> tbl <span class="hljs-keyword">WHERE</span> my_column = <span class="hljs-string">'value2'</span>
 </code></pre>
 <p>When UNION ALL occurs at the top level of a query like this, the results from the unioned queries are concatenated
 together and appear one after the other.</p>
 <h4><a class="anchor" aria-hidden="true" id="table-level"></a><a href="#table-level" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Table-level</h4>
 <p>UNION ALL can be used to query multiple tables at the same time. In this case, it must appear in the FROM clause,
 and the subqueries that are inputs to the UNION ALL operator must be simple table SELECTs (no expressions, column
 aliasing, etc). The query will run natively using a <a href="/docs/0.20.0/querying/datasource.html#union">union datasource</a>.</p>
 <p>The same columns must be selected from each table in the same order, and those columns must either have the same types,
 or types that can be implicitly cast to each other (such as different numeric types). For this reason, it is generally
 more robust to write your queries to select specific columns. If you use <code>SELECT *</code>, you will need to modify your
 queries if a new column is added to one of the tables but not to the others.</p>
 <p>For example:</p>
 <pre><code class="hljs">SELECT col1, COUNT(*)
 <span class="hljs-keyword">FROM</span> (
   SELECT col1, col2, col3 <span class="hljs-keyword">FROM</span> tbl1
   UNION ALL
   SELECT col1, col2, col3 <span class="hljs-keyword">FROM</span> tbl2
 )<span class="hljs-built_in">
 GROUP </span>BY col1
 </code></pre>
 <p>When UNION ALL occurs at the table level, the rows from the unioned tables are not guaranteed to be processed in
 any particular order. They may be processed in an interleaved fashion. If you need a particular result ordering,
 use <a href="#order-by">ORDER BY</a>.</p>
 <h3><a class="anchor" aria-hidden="true" id="explain-plan"></a><a href="#explain-plan" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>EXPLAIN PLAN</h3>
 <p>Add &quot;EXPLAIN PLAN FOR&quot; to the beginning of any query to get information about how it will be translated. In this case,
 the query will not actually be executed. Refer to the <a href="#query-translation">Query translation</a> documentation for help
 interpreting EXPLAIN PLAN output.</p>
 <h3><a class="anchor" aria-hidden="true" id="identifiers-and-literals"></a><a href="#identifiers-and-literals" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Identifiers and literals</h3>
 <p>Identifiers like datasource and column names can optionally be quoted using double quotes. To escape a double quote
 inside an identifier, use another double quote, like <code>&quot;My &quot;&quot;very own&quot;&quot; identifier&quot;</code>. All identifiers are case-sensitive
 and no implicit case conversions are performed.</p>
 <p>Literal strings should be quoted with single quotes, like <code>'foo'</code>. Literal strings with Unicode escapes can be written
 like <code>U&amp;'fo\00F6'</code>, where character codes in hex are prefixed by a backslash. Literal numbers can be written in forms
 like <code>100</code> (denoting an integer), <code>100.0</code> (denoting a floating point value), or <code>1.0e5</code> (scientific notation). Literal
 timestamps can be written like <code>TIMESTAMP '2000-01-01 00:00:00'</code>. Literal intervals, used for time arithmetic, can be
 written like <code>INTERVAL '1' HOUR</code>, <code>INTERVAL '1 02:03' DAY TO MINUTE</code>, <code>INTERVAL '1-2' YEAR TO MONTH</code>, and so on.</p>
 <h3><a class="anchor" aria-hidden="true" id="dynamic-parameters"></a><a href="#dynamic-parameters" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Dynamic parameters</h3>
 <p>Druid SQL supports dynamic parameters using question mark (<code>?</code>) syntax, where parameters are bound to <code>?</code> placeholders
 at execution time. To use dynamic parameters, replace any literal in the query with a <code>?</code> character and provide a
 corresponding parameter value when you execute the query. Parameters are bound to the placeholders in the order in
 which they are passed. Parameters are supported in both the <a href="#http-post">HTTP POST</a> and <a href="#jdbc">JDBC</a> APIs.</p>
 <h2><a class="anchor" aria-hidden="true" id="data-types"></a><a href="#data-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Data types</h2>
 <h3><a class="anchor" aria-hidden="true" id="standard-types"></a><a href="#standard-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Standard types</h3>
 <p>Druid natively supports five basic column types: &quot;long&quot; (64 bit signed int), &quot;float&quot; (32 bit float), &quot;double&quot; (64 bit
 float) &quot;string&quot; (UTF-8 encoded strings and string arrays), and &quot;complex&quot; (catch-all for more exotic data types like
 hyperUnique and approxHistogram columns).</p>
 <p>Timestamps (including the <code>__time</code> column) are treated by Druid as longs, with the value being the number of
 milliseconds since 1970-01-01 00:00:00 UTC, not counting leap seconds. Therefore, timestamps in Druid do not carry any
 timezone information, but only carry information about the exact moment in time they represent. See the
 <a href="#time-functions">Time functions</a> section for more information about timestamp handling.</p>
 <p>The following table describes how Druid maps SQL types onto native types at query runtime. Casts between two SQL types
 that have the same Druid runtime type will have no effect, other than exceptions noted in the table. Casts between two
 SQL types that have different Druid runtime types will generate a runtime cast in Druid. If a value cannot be properly
 cast to another value, as in <code>CAST('foo' AS BIGINT)</code>, the runtime will substitute a default value. NULL values cast
 to non-nullable types will also be substituted with a default value (for example, nulls cast to numbers will be
 converted to zeroes).</p>
 <table>
 <thead>
 <tr><th>SQL type</th><th>Druid runtime type</th><th>Default value</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>CHAR</td><td>STRING</td><td><code>''</code></td><td></td></tr>
 <tr><td>VARCHAR</td><td>STRING</td><td><code>''</code></td><td>Druid STRING columns are reported as VARCHAR. Can include <a href="#multi-value-strings">multi-value strings</a> as well.</td></tr>
 <tr><td>DECIMAL</td><td>DOUBLE</td><td><code>0.0</code></td><td>DECIMAL uses floating point, not fixed point math</td></tr>
 <tr><td>FLOAT</td><td>FLOAT</td><td><code>0.0</code></td><td>Druid FLOAT columns are reported as FLOAT</td></tr>
 <tr><td>REAL</td><td>DOUBLE</td><td><code>0.0</code></td><td></td></tr>
 <tr><td>DOUBLE</td><td>DOUBLE</td><td><code>0.0</code></td><td>Druid DOUBLE columns are reported as DOUBLE</td></tr>
 <tr><td>BOOLEAN</td><td>LONG</td><td><code>false</code></td><td></td></tr>
 <tr><td>TINYINT</td><td>LONG</td><td><code>0</code></td><td></td></tr>
 <tr><td>SMALLINT</td><td>LONG</td><td><code>0</code></td><td></td></tr>
 <tr><td>INTEGER</td><td>LONG</td><td><code>0</code></td><td></td></tr>
 <tr><td>BIGINT</td><td>LONG</td><td><code>0</code></td><td>Druid LONG columns (except <code>__time</code>) are reported as BIGINT</td></tr>
 <tr><td>TIMESTAMP</td><td>LONG</td><td><code>0</code>, meaning 1970-01-01 00:00:00 UTC</td><td>Druid's <code>__time</code> column is reported as TIMESTAMP. Casts between string and timestamp types assume standard SQL formatting, e.g. <code>2000-01-02 03:04:05</code>, <em>not</em> ISO8601 formatting. For handling other formats, use one of the <a href="#time-functions">time functions</a></td></tr>
 <tr><td>DATE</td><td>LONG</td><td><code>0</code>, meaning 1970-01-01</td><td>Casting TIMESTAMP to DATE rounds down the timestamp to the nearest day. Casts between string and date types assume standard SQL formatting, e.g. <code>2000-01-02</code>. For handling other formats, use one of the <a href="#time-functions">time functions</a></td></tr>
 <tr><td>OTHER</td><td>COMPLEX</td><td>none</td><td>May represent various Druid column types such as hyperUnique, approxHistogram, etc</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="multi-value-strings"></a><a href="#multi-value-strings" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Multi-value strings</h3>
 <p>Druid's native type system allows strings to potentially have multiple values. These
 <a href="multi-value-dimensions.html">multi-value string dimensions</a> will be reported in SQL as <code>VARCHAR</code> typed, and can be
 syntactically used like any other VARCHAR. Regular string functions that refer to multi-value string dimensions will be
 applied to all values for each row individually. Multi-value string dimensions can also be treated as arrays via special
 <a href="#multi-value-string-functions">multi-value string functions</a>, which can perform powerful array-aware operations.</p>
 <p>Grouping by a multi-value expression will observe the native Druid multi-value aggregation behavior, which is similar to
 the <code>UNNEST</code> functionality available in some other SQL dialects. Refer to the documentation on
 <a href="multi-value-dimensions.html">multi-value string dimensions</a> for additional details.</p>
 <blockquote>
 <p>Because multi-value dimensions are treated by the SQL planner as <code>VARCHAR</code>, there are some inconsistencies between how
 they are handled in Druid SQL and in native queries. For example, expressions involving multi-value dimensions may be
 incorrectly optimized by the Druid SQL planner: <code>multi_val_dim = 'a' AND multi_val_dim = 'b'</code> will be optimized to
 <code>false</code>, even though it is possible for a single row to have both &quot;a&quot; and &quot;b&quot; as values for <code>multi_val_dim</code>. The
 SQL behavior of multi-value dimensions will change in a future release to more closely align with their behavior
 in native queries.</p>
 </blockquote>
 <h3><a class="anchor" aria-hidden="true" id="null-values"></a><a href="#null-values" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>NULL values</h3>
 <p>The <code>druid.generic.useDefaultValueForNull</code> <a href="../configuration/index.html#sql-compatible-null-handling">runtime property</a>
 controls Druid's NULL handling mode.</p>
 <p>In the default mode (<code>true</code>), Druid treats NULLs and empty strings interchangeably, rather than according to the SQL
 standard. In this mode Druid SQL only has partial support for NULLs. For example, the expressions <code>col IS NULL</code> and
 <code>col = ''</code> are equivalent, and both will evaluate to true if <code>col</code> contains an empty string. Similarly, the expression
 <code>COALESCE(col1, col2)</code> will return <code>col2</code> if <code>col1</code> is an empty string. While the <code>COUNT(*)</code> aggregator counts all rows,
 the <code>COUNT(expr)</code> aggregator will count the number of rows where expr is neither null nor the empty string. Numeric
 columns in this mode are not nullable; any null or missing values will be treated as zeroes.</p>
 <p>In SQL compatible mode (<code>false</code>), NULLs are treated more closely to the SQL standard. The property affects both storage
 and querying, so for best behavior, it should be set at both ingestion time and query time. There is some overhead
 associated with the ability to handle NULLs; see the <a href="/docs/0.20.0/design/segments.html#sql-compatible-null-handling">segment internals</a>
 documentation for more details.</p>
 <h2><a class="anchor" aria-hidden="true" id="aggregation-functions"></a><a href="#aggregation-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Aggregation functions</h2>
 <p>Aggregation functions can appear in the SELECT clause of any query. Any aggregator can be filtered using syntax like
 <code>AGG(expr) FILTER(WHERE whereExpr)</code>. Filtered aggregators will only aggregate rows that match their filter. It's
 possible for two aggregators in the same SQL query to have different filters.</p>
 <p>Only the COUNT aggregation can accept DISTINCT.</p>
 <blockquote>
 <p>The order of aggregation operations across segments is not deterministic. This means that non-commutative aggregation
 functions can produce inconsistent results across the same query.</p>
 <p>Functions that operate on an input type of &quot;float&quot; or &quot;double&quot; may also see these differences in aggregation
 results across multiple query runs because of this. If precisely the same value is desired across multiple query runs,
 consider using the <code>ROUND</code> function to smooth out the inconsistencies between queries.</p>
 </blockquote>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>COUNT(*)</code></td><td>Counts the number of rows.</td></tr>
 <tr><td><code>COUNT(DISTINCT expr)</code></td><td>Counts distinct values of expr, which can be string, numeric, or hyperUnique. By default this is approximate, using a variant of <a href="http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf">HyperLogLog</a>. To get exact counts set &quot;useApproximateCountDistinct&quot; to &quot;false&quot;. If you do this, expr must be string or numeric, since exact counts are not possible using hyperUnique columns. See also <code>APPROX_COUNT_DISTINCT(expr)</code>. In exact mode, only one distinct count per query is permitted.</td></tr>
 <tr><td><code>SUM(expr)</code></td><td>Sums numbers.</td></tr>
 <tr><td><code>MIN(expr)</code></td><td>Takes the minimum of numbers.</td></tr>
 <tr><td><code>MAX(expr)</code></td><td>Takes the maximum of numbers.</td></tr>
 <tr><td><code>AVG(expr)</code></td><td>Averages numbers.</td></tr>
 <tr><td><code>APPROX_COUNT_DISTINCT(expr)</code></td><td>Counts distinct values of expr, which can be a regular column or a hyperUnique column. This is always approximate, regardless of the value of &quot;useApproximateCountDistinct&quot;. This uses Druid's built-in &quot;cardinality&quot; or &quot;hyperUnique&quot; aggregators. See also <code>COUNT(DISTINCT expr)</code>.</td></tr>
 <tr><td><code>APPROX_COUNT_DISTINCT_DS_HLL(expr, [lgK, tgtHllType])</code></td><td>Counts distinct values of expr, which can be a regular column or an <a href="../development/extensions-core/datasketches-hll.html">HLL sketch</a> column. The <code>lgK</code> and <code>tgtHllType</code> parameters are described in the HLL sketch documentation. This is always approximate, regardless of the value of &quot;useApproximateCountDistinct&quot;. See also <code>COUNT(DISTINCT expr)</code>. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>APPROX_COUNT_DISTINCT_DS_THETA(expr, [size])</code></td><td>Counts distinct values of expr, which can be a regular column or a <a href="../development/extensions-core/datasketches-theta.html">Theta sketch</a> column. The <code>size</code> parameter is described in the Theta sketch documentation. This is always approximate, regardless of the value of &quot;useApproximateCountDistinct&quot;. See also <code>COUNT(DISTINCT expr)</code>. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>DS_HLL(expr, [lgK, tgtHllType])</code></td><td>Creates an <a href="../development/extensions-core/datasketches-hll.html">HLL sketch</a> on the values of expr, which can be a regular column or a column containing HLL sketches. The <code>lgK</code> and <code>tgtHllType</code> parameters are described in the HLL sketch documentation. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>DS_THETA(expr, [size])</code></td><td>Creates a <a href="../development/extensions-core/datasketches-theta.html">Theta sketch</a> on the values of expr, which can be a regular column or a column containing Theta sketches. The <code>size</code> parameter is described in the Theta sketch documentation. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>APPROX_QUANTILE(expr, probability, [resolution])</code></td><td>Computes approximate quantiles on numeric or <a href="../development/extensions-core/approximate-histograms.html#approximate-histogram-aggregator">approxHistogram</a> exprs. The &quot;probability&quot; should be between 0 and 1 (exclusive). The &quot;resolution&quot; is the number of centroids to use for the computation. Higher resolutions will give more precise results but also have higher overhead. If not provided, the default resolution is 50. The <a href="../development/extensions-core/approximate-histograms.html">approximate histogram extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>APPROX_QUANTILE_DS(expr, probability, [k])</code></td><td>Computes approximate quantiles on numeric or <a href="../development/extensions-core/datasketches-quantiles.html">Quantiles sketch</a> exprs. The &quot;probability&quot; should be between 0 and 1 (exclusive). The <code>k</code> parameter is described in the Quantiles sketch documentation. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>APPROX_QUANTILE_FIXED_BUCKETS(expr, probability, numBuckets, lowerLimit, upperLimit, [outlierHandlingMode])</code></td><td>Computes approximate quantiles on numeric or <a href="../development/extensions-core/approximate-histograms.html#fixed-buckets-histogram">fixed buckets histogram</a> exprs. The &quot;probability&quot; should be between 0 and 1 (exclusive). The <code>numBuckets</code>, <code>lowerLimit</code>, <code>upperLimit</code>, and <code>outlierHandlingMode</code> parameters are described in the fixed buckets histogram documentation. The <a href="../development/extensions-core/approximate-histograms.html">approximate histogram extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>DS_QUANTILES_SKETCH(expr, [k])</code></td><td>Creates a <a href="../development/extensions-core/datasketches-quantiles.html">Quantiles sketch</a> on the values of expr, which can be a regular column or a column containing quantiles sketches. The <code>k</code> parameter is described in the Quantiles sketch documentation. The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use this function.</td></tr>
 <tr><td><code>BLOOM_FILTER(expr, numEntries)</code></td><td>Computes a bloom filter from values produced by <code>expr</code>, with <code>numEntries</code> maximum number of distinct values before false positive rate increases. See <a href="../development/extensions-core/bloom-filter.html">bloom filter extension</a> documentation for additional details.</td></tr>
 <tr><td><code>TDIGEST_QUANTILE(expr, quantileFraction, [compression])</code></td><td>Builds a T-Digest sketch on values produced by <code>expr</code> and returns the value for the quantile. Compression parameter (default value 100) determines the accuracy and size of the sketch. Higher compression means higher accuracy but more space to store sketches. See <a href="../development/extensions-contrib/tdigestsketch-quantiles.html">t-digest extension</a> documentation for additional details.</td></tr>
 <tr><td><code>TDIGEST_GENERATE_SKETCH(expr, [compression])</code></td><td>Builds a T-Digest sketch on values produced by <code>expr</code>. Compression parameter (default value 100) determines the accuracy and size of the sketch Higher compression means higher accuracy but more space to store sketches. See <a href="../development/extensions-contrib/tdigestsketch-quantiles.html">t-digest extension</a> documentation for additional details.</td></tr>
 <tr><td><code>VAR_POP(expr)</code></td><td>Computes variance population of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>VAR_SAMP(expr)</code></td><td>Computes variance sample of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>VARIANCE(expr)</code></td><td>Computes variance sample of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>STDDEV_POP(expr)</code></td><td>Computes standard deviation population of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>STDDEV_SAMP(expr)</code></td><td>Computes standard deviation sample of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>STDDEV(expr)</code></td><td>Computes standard deviation sample of <code>expr</code>. See <a href="../development/extensions-core/stats.html">stats extension</a> documentation for additional details.</td></tr>
 <tr><td><code>EARLIEST(expr)</code></td><td>Returns the earliest value of <code>expr</code>, which must be numeric. If <code>expr</code> comes from a relation with a timestamp column (like a Druid datasource) then &quot;earliest&quot; is the value first encountered with the minimum overall timestamp of all values being aggregated. If <code>expr</code> does not come from a relation with a timestamp, then it is simply the first value encountered.</td></tr>
 <tr><td><code>EARLIEST(expr, maxBytesPerString)</code></td><td>Like <code>EARLIEST(expr)</code>, but for strings. The <code>maxBytesPerString</code> parameter determines how much aggregation space to allocate per string. Strings longer than this limit will be truncated. This parameter should be set as low as possible, since high values will lead to wasted memory.</td></tr>
 <tr><td><code>LATEST(expr)</code></td><td>Returns the latest value of <code>expr</code>, which must be numeric. If <code>expr</code> comes from a relation with a timestamp column (like a Druid datasource) then &quot;latest&quot; is the value last encountered with the maximum overall timestamp of all values being aggregated. If <code>expr</code> does not come from a relation with a timestamp, then it is simply the last value encountered.</td></tr>
 <tr><td><code>LATEST(expr, maxBytesPerString)</code></td><td>Like <code>LATEST(expr)</code>, but for strings. The <code>maxBytesPerString</code> parameter determines how much aggregation space to allocate per string. Strings longer than this limit will be truncated. This parameter should be set as low as possible, since high values will lead to wasted memory.</td></tr>
 <tr><td><code>ANY_VALUE(expr)</code></td><td>Returns any value of <code>expr</code> including null. <code>expr</code> must be numeric. This aggregator can simplify and optimize the performance by returning the first encountered value (including null)</td></tr>
 <tr><td><code>ANY_VALUE(expr, maxBytesPerString)</code></td><td>Like <code>ANY_VALUE(expr)</code>, but for strings. The <code>maxBytesPerString</code> parameter determines how much aggregation space to allocate per string. Strings longer than this limit will be truncated. This parameter should be set as low as possible, since high values will lead to wasted memory.</td></tr>
 </tbody>
 </table>
 <p>For advice on choosing approximate aggregation functions, check out our <a href="aggregations.html#approx">approximate aggregations documentation</a>.</p>
 <h2><a class="anchor" aria-hidden="true" id="scalar-functions"></a><a href="#scalar-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Scalar functions</h2>
 <h3><a class="anchor" aria-hidden="true" id="numeric-functions"></a><a href="#numeric-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Numeric functions</h3>
 <p>For mathematical operations, Druid SQL will use integer math if all operands involved in an expression are integers.
 Otherwise, Druid will switch to floating point math. You can force this to happen by casting one of your operands
 to FLOAT. At runtime, Druid will widen 32-bit floats to 64-bit for most expressions.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>ABS(expr)</code></td><td>Absolute value.</td></tr>
 <tr><td><code>CEIL(expr)</code></td><td>Ceiling.</td></tr>
 <tr><td><code>EXP(expr)</code></td><td>e to the power of expr.</td></tr>
 <tr><td><code>FLOOR(expr)</code></td><td>Floor.</td></tr>
 <tr><td><code>LN(expr)</code></td><td>Logarithm (base e).</td></tr>
 <tr><td><code>LOG10(expr)</code></td><td>Logarithm (base 10).</td></tr>
 <tr><td><code>POWER(expr, power)</code></td><td>expr to a power.</td></tr>
 <tr><td><code>SQRT(expr)</code></td><td>Square root.</td></tr>
 <tr><td><code>TRUNCATE(expr[, digits])</code></td><td>Truncate expr to a specific number of decimal digits. If digits is negative, then this truncates that many places to the left of the decimal point. Digits defaults to zero if not specified.</td></tr>
 <tr><td><code>TRUNC(expr[, digits])</code></td><td>Synonym for <code>TRUNCATE</code>.</td></tr>
 <tr><td><code>ROUND(expr[, digits])</code></td><td><code>ROUND(x, y)</code> would return the value of the x rounded to the y decimal places. While x can be an integer or floating-point number, y must be an integer. The type of the return value is specified by that of x. y defaults to 0 if omitted. When y is negative, x is rounded on the left side of the y decimal points. If <code>expr</code> evaluates to either <code>NaN</code>, <code>expr</code> will be converted to 0. If <code>expr</code> is infinity, <code>expr</code> will be converted to the nearest finite double.</td></tr>
 <tr><td><code>x + y</code></td><td>Addition.</td></tr>
 <tr><td><code>x - y</code></td><td>Subtraction.</td></tr>
 <tr><td><code>x * y</code></td><td>Multiplication.</td></tr>
 <tr><td><code>x / y</code></td><td>Division.</td></tr>
 <tr><td><code>MOD(x, y)</code></td><td>Modulo (remainder of x divided by y).</td></tr>
 <tr><td><code>SIN(expr)</code></td><td>Trigonometric sine of an angle expr.</td></tr>
 <tr><td><code>COS(expr)</code></td><td>Trigonometric cosine of an angle expr.</td></tr>
 <tr><td><code>TAN(expr)</code></td><td>Trigonometric tangent of an angle expr.</td></tr>
 <tr><td><code>COT(expr)</code></td><td>Trigonometric cotangent of an angle expr.</td></tr>
 <tr><td><code>ASIN(expr)</code></td><td>Arc sine of expr.</td></tr>
 <tr><td><code>ACOS(expr)</code></td><td>Arc cosine of expr.</td></tr>
 <tr><td><code>ATAN(expr)</code></td><td>Arc tangent of expr.</td></tr>
 <tr><td><code>ATAN2(y, x)</code></td><td>Angle theta from the conversion of rectangular coordinates (x, y) to polar * coordinates (r, theta).</td></tr>
 <tr><td><code>DEGREES(expr)</code></td><td>Converts an angle measured in radians to an approximately equivalent angle measured in degrees</td></tr>
 <tr><td><code>RADIANS(expr)</code></td><td>Converts an angle measured in degrees to an approximately equivalent angle measured in radians</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="string-functions"></a><a href="#string-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>String functions</h3>
 <p>String functions accept strings, and return a type appropriate to the function.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>x || y</code></td><td>Concat strings x and y.</td></tr>
 <tr><td><code>CONCAT(expr, expr...)</code></td><td>Concats a list of expressions.</td></tr>
 <tr><td><code>TEXTCAT(expr, expr)</code></td><td>Two argument version of CONCAT.</td></tr>
 <tr><td><code>STRING_FORMAT(pattern[, args...])</code></td><td>Returns a string formatted in the manner of Java's <a href="https://docs.oracle.com/javase/8/docs/api/java/lang/String.html#format-java.lang.String-java.lang.Object...-">String.format</a>.</td></tr>
 <tr><td><code>LENGTH(expr)</code></td><td>Length of expr in UTF-16 code units.</td></tr>
 <tr><td><code>CHAR_LENGTH(expr)</code></td><td>Synonym for <code>LENGTH</code>.</td></tr>
 <tr><td><code>CHARACTER_LENGTH(expr)</code></td><td>Synonym for <code>LENGTH</code>.</td></tr>
 <tr><td><code>STRLEN(expr)</code></td><td>Synonym for <code>LENGTH</code>.</td></tr>
 <tr><td><code>LOOKUP(expr, lookupName)</code></td><td>Look up expr in a registered <a href="lookups.html">query-time lookup table</a>. Note that lookups can also be queried directly using the <a href="#from"><code>lookup</code> schema</a>.</td></tr>
 <tr><td><code>LOWER(expr)</code></td><td>Returns expr in all lowercase.</td></tr>
 <tr><td><code>PARSE_LONG(string[, radix])</code></td><td>Parses a string into a long (BIGINT) with the given radix, or 10 (decimal) if a radix is not provided.</td></tr>
 <tr><td><code>POSITION(needle IN haystack [FROM fromIndex])</code></td><td>Returns the index of needle within haystack, with indexes starting from 1. The search will begin at fromIndex, or 1 if fromIndex is not specified. If the needle is not found, returns 0.</td></tr>
 <tr><td><code>REGEXP_EXTRACT(expr, pattern, [index])</code></td><td>Apply regular expression <code>pattern</code> to <code>expr</code> and extract a capture group, or <code>NULL</code> if there is no match. If index is unspecified or zero, returns the first substring that matched the pattern. The pattern may match anywhere inside <code>expr</code>; if you want to match the entire string instead, use the <code>^</code> and <code>$</code> markers at the start and end of your pattern. Note: when <code>druid.generic.useDefaultValueForNull = true</code>, it is not possible to differentiate an empty-string match from a non-match (both will return <code>NULL</code>).</td></tr>
 <tr><td><code>REGEXP_LIKE(expr, pattern)</code></td><td>Returns whether <code>expr</code> matches regular expression <code>pattern</code>. The pattern may match anywhere inside <code>expr</code>; if you want to match the entire string instead, use the <code>^</code> and <code>$</code> markers at the start and end of your pattern. Similar to <a href="#comparison-operators"><code>LIKE</code></a>, but uses regexps instead of LIKE patterns. Especially useful in WHERE clauses.</td></tr>
 <tr><td><code>CONTAINS_STRING(&lt;expr&gt;, str)</code></td><td>Returns true if the <code>str</code> is a substring of <code>expr</code>.</td></tr>
 <tr><td><code>ICONTAINS_STRING(&lt;expr&gt;, str)</code></td><td>Returns true if the <code>str</code> is a substring of <code>expr</code>. The match is case-insensitive.</td></tr>
 <tr><td><code>REPLACE(expr, pattern, replacement)</code></td><td>Replaces pattern with replacement in expr, and returns the result.</td></tr>
 <tr><td><code>STRPOS(haystack, needle)</code></td><td>Returns the index of needle within haystack, with indexes starting from 1. If the needle is not found, returns 0.</td></tr>
 <tr><td><code>SUBSTRING(expr, index, [length])</code></td><td>Returns a substring of expr starting at index, with a max length, both measured in UTF-16 code units.</td></tr>
 <tr><td><code>RIGHT(expr, [length])</code></td><td>Returns the rightmost length characters from expr.</td></tr>
 <tr><td><code>LEFT(expr, [length])</code></td><td>Returns the leftmost length characters from expr.</td></tr>
 <tr><td><code>SUBSTR(expr, index, [length])</code></td><td>Synonym for SUBSTRING.</td></tr>
 <tr><td><code>TRIM([BOTH | LEADING | TRAILING] [<chars> FROM] expr)</code></td><td>Returns expr with characters removed from the leading, trailing, or both ends of &quot;expr&quot; if they are in &quot;chars&quot;. If &quot;chars&quot; is not provided, it defaults to &quot; &quot; (a space). If the directional argument is not provided, it defaults to &quot;BOTH&quot;.</td></tr>
 <tr><td><code>BTRIM(expr[, chars])</code></td><td>Alternate form of <code>TRIM(BOTH &lt;chars&gt; FROM &lt;expr&gt;)</code>.</td></tr>
 <tr><td><code>LTRIM(expr[, chars])</code></td><td>Alternate form of <code>TRIM(LEADING &lt;chars&gt; FROM &lt;expr&gt;)</code>.</td></tr>
 <tr><td><code>RTRIM(expr[, chars])</code></td><td>Alternate form of <code>TRIM(TRAILING &lt;chars&gt; FROM &lt;expr&gt;)</code>.</td></tr>
 <tr><td><code>UPPER(expr)</code></td><td>Returns expr in all uppercase.</td></tr>
 <tr><td><code>REVERSE(expr)</code></td><td>Reverses expr.</td></tr>
 <tr><td><code>REPEAT(expr, [N])</code></td><td>Repeats expr N times</td></tr>
 <tr><td><code>LPAD(expr, length[, chars])</code></td><td>Returns a string of <code>length</code> from <code>expr</code> left-padded with <code>chars</code>. If <code>length</code> is shorter than the length of <code>expr</code>, the result is <code>expr</code> which is truncated to <code>length</code>. The result will be null if either <code>expr</code> or <code>chars</code> is null. If <code>chars</code> is an empty string, no padding is added, however <code>expr</code> may be trimmed if necessary.</td></tr>
 <tr><td><code>RPAD(expr, length[, chars])</code></td><td>Returns a string of <code>length</code> from <code>expr</code> right-padded with <code>chars</code>. If <code>length</code> is shorter than the length of <code>expr</code>, the result is <code>expr</code> which is truncated to <code>length</code>. The result will be null if either <code>expr</code> or <code>chars</code> is null. If <code>chars</code> is an empty string, no padding is added, however <code>expr</code> may be trimmed if necessary.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="time-functions"></a><a href="#time-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Time functions</h3>
 <p>Time functions can be used with Druid's <code>__time</code> column, with any column storing millisecond timestamps through use
 of the <code>MILLIS_TO_TIMESTAMP</code> function, or with any column storing string timestamps through use of the <code>TIME_PARSE</code>
 function. By default, time operations use the UTC time zone. You can change the time zone by setting the connection
 context parameter &quot;sqlTimeZone&quot; to the name of another time zone, like &quot;America/Los_Angeles&quot;, or to an offset like
 &quot;-08:00&quot;. If you need to mix multiple time zones in the same query, or if you need to use a time zone other than
 the connection time zone, some functions also accept time zones as parameters. These parameters always take precedence
 over the connection time zone.</p>
 <p>Literal timestamps in the connection time zone can be written using <code>TIMESTAMP '2000-01-01 00:00:00'</code> syntax. The
 simplest way to write literal timestamps in other time zones is to use TIME_PARSE, like
 <code>TIME_PARSE('2000-02-01 00:00:00', NULL, 'America/Los_Angeles')</code>.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>CURRENT_TIMESTAMP</code></td><td>Current timestamp in the connection's time zone.</td></tr>
 <tr><td><code>CURRENT_DATE</code></td><td>Current date in the connection's time zone.</td></tr>
 <tr><td><code>DATE_TRUNC(&lt;unit&gt;, &lt;timestamp_expr&gt;)</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.</td></tr>
 <tr><td><code>TIME_CEIL(&lt;timestamp_expr&gt;, &lt;period&gt;, [&lt;origin&gt;, [&lt;timezone&gt;]])</code></td><td>Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;. This function is similar to <code>CEIL</code> but is more flexible.</td></tr>
 <tr><td><code>TIME_FLOOR(&lt;timestamp_expr&gt;, &lt;period&gt;, [&lt;origin&gt;, [&lt;timezone&gt;]])</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;. This function is similar to <code>FLOOR</code> but is more flexible.</td></tr>
 <tr><td><code>TIME_SHIFT(&lt;timestamp_expr&gt;, &lt;period&gt;, &lt;step&gt;, [&lt;timezone&gt;])</code></td><td>Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;.</td></tr>
 <tr><td><code>TIME_EXTRACT(&lt;timestamp_expr&gt;, [&lt;unit&gt;, [&lt;timezone&gt;]])</code></td><td>Extracts a time part from expr, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of <a href="https://en.wikipedia.org/wiki/ISO_week_date">week year</a>), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;. This function is similar to <code>EXTRACT</code> but is more flexible. Unit and time zone must be literals, and must be provided quoted, like <code>TIME_EXTRACT(__time, 'HOUR')</code> or <code>TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')</code>.</td></tr>
 <tr><td><code>TIME_PARSE(&lt;string_expr&gt;, [&lt;pattern&gt;, [&lt;timezone&gt;]])</code></td><td>Parses a string into a timestamp using a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html">Joda DateTimeFormat pattern</a>, or ISO8601 (e.g. <code>2000-01-02T03:04:05Z</code>) if the pattern is not provided. The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.</td></tr>
 <tr><td><code>TIME_FORMAT(&lt;timestamp_expr&gt;, [&lt;pattern&gt;, [&lt;timezone&gt;]])</code></td><td>Formats a timestamp as a string with a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html">Joda DateTimeFormat pattern</a>, or ISO8601 (e.g. <code>2000-01-02T03:04:05Z</code>) if the pattern is not provided. The time zone, if provided, should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;. Pattern and time zone must be literals.</td></tr>
 <tr><td><code>MILLIS_TO_TIMESTAMP(millis_expr)</code></td><td>Converts a number of milliseconds since the epoch into a timestamp.</td></tr>
 <tr><td><code>TIMESTAMP_TO_MILLIS(timestamp_expr)</code></td><td>Converts a timestamp into a number of milliseconds since the epoch.</td></tr>
 <tr><td><code>EXTRACT(&lt;unit&gt; FROM timestamp_expr)</code></td><td>Extracts a time part from expr, returning it as a number. Unit can be EPOCH, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), ISODOW (ISO day of week), DOY (day of year), WEEK (week of year), MONTH, QUARTER, YEAR, ISOYEAR, DECADE, CENTURY or MILLENNIUM. Units must be provided unquoted, like <code>EXTRACT(HOUR FROM __time)</code>.</td></tr>
 <tr><td><code>FLOOR(timestamp_expr TO &lt;unit&gt;)</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr>
 <tr><td><code>CEIL(timestamp_expr TO &lt;unit&gt;)</code></td><td>Rounds up a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr>
 <tr><td><code>TIMESTAMPADD(&lt;unit&gt;, &lt;count&gt;, &lt;timestamp&gt;)</code></td><td>Equivalent to <code>timestamp + count * INTERVAL '1' UNIT</code>.</td></tr>
 <tr><td><code>TIMESTAMPDIFF(&lt;unit&gt;, &lt;timestamp1&gt;, &lt;timestamp2&gt;)</code></td><td>Returns the (signed) number of <code>unit</code> between <code>timestamp1</code> and <code>timestamp2</code>. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr>
 <tr><td><code>timestamp_expr { + | - } &lt;interval_expr&gt;<code></td><td>Add or subtract an amount of time from a timestamp. interval_expr can include interval literals like <code>INTERVAL '2' HOUR</code>, and may include interval arithmetic as well. This operator treats days as uniformly 86400 seconds long, and does not take into account daylight savings time. To account for daylight savings time, use TIME_SHIFT instead.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="reduction-functions"></a><a href="#reduction-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Reduction functions</h3>
 <p>Reduction functions operate on zero or more expressions and return a single expression. If no expressions are passed as
 arguments, then the result is <code>NULL</code>. The expressions must all be convertible to a common data type, which will be the
 type of the result:</p>
 <ul>
 <li>If all argument are <code>NULL</code>, the result is <code>NULL</code>. Otherwise, <code>NULL</code> arguments are ignored.</li>
 <li>If the arguments comprise a mix of numbers and strings, the arguments are interpreted as strings.</li>
 <li>If all arguments are integer numbers, the arguments are interpreted as longs.</li>
 <li>If all arguments are numbers and at least one argument is a double, the arguments are interpreted as doubles.</li>
 </ul>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>GREATEST([expr1, ...])</code></td><td>Evaluates zero or more expressions and returns the maximum value based on comparisons as described above.</td></tr>
 <tr><td><code>LEAST([expr1, ...])</code></td><td>Evaluates zero or more expressions and returns the minimum value based on comparisons as described above.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="ip-address-functions"></a><a href="#ip-address-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>IP address functions</h3>
 <p>For the IPv4 address functions, the <code>address</code> argument can either be an IPv4 dotted-decimal string
 (e.g., '192.168.0.1') or an IP address represented as an integer (e.g., 3232235521). The <code>subnet</code>
 argument should be a string formatted as an IPv4 address subnet in CIDR notation (e.g.,
 '192.168.0.0/16').</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>IPV4_MATCH(address, subnet)</code></td><td>Returns true if the <code>address</code> belongs to the <code>subnet</code> literal, else false. If <code>address</code> is not a valid IPv4 address, then false is returned. This function is more efficient if <code>address</code> is an integer instead of a string.</td></tr>
 <tr><td><code>IPV4_PARSE(address)</code></td><td>Parses <code>address</code> into an IPv4 address stored as an integer . If <code>address</code> is an integer that is a valid IPv4 address, then it is passed through. Returns null if <code>address</code> cannot be represented as an IPv4 address.</td></tr>
 <tr><td><code>IPV4_STRINGIFY(address)</code></td><td>Converts <code>address</code> into an IPv4 address dotted-decimal string. If <code>address</code> is a string that is a valid IPv4 address, then it is passed through. Returns null if <code>address</code> cannot be represented as an IPv4 address.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="comparison-operators"></a><a href="#comparison-operators" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Comparison operators</h3>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>x = y</code></td><td>Equals.</td></tr>
 <tr><td><code>x &lt;&gt; y</code></td><td>Not-equals.</td></tr>
 <tr><td><code>x &gt; y</code></td><td>Greater than.</td></tr>
 <tr><td><code>x &gt;= y</code></td><td>Greater than or equal to.</td></tr>
 <tr><td><code>x &lt; y</code></td><td>Less than.</td></tr>
 <tr><td><code>x &lt;= y</code></td><td>Less than or equal to.</td></tr>
 <tr><td><code>x BETWEEN y AND z</code></td><td>Equivalent to <code>x &gt;= y AND x &lt;= z</code>.</td></tr>
 <tr><td><code>x NOT BETWEEN y AND z</code></td><td>Equivalent to <code>x &lt; y OR x &gt; z</code>.</td></tr>
 <tr><td><code>x LIKE pattern [ESCAPE esc]</code></td><td>True if x matches a SQL LIKE pattern (with an optional escape).</td></tr>
 <tr><td><code>x NOT LIKE pattern [ESCAPE esc]</code></td><td>True if x does not match a SQL LIKE pattern (with an optional escape).</td></tr>
 <tr><td><code>x IS NULL</code></td><td>True if x is NULL or empty string.</td></tr>
 <tr><td><code>x IS NOT NULL</code></td><td>True if x is neither NULL nor empty string.</td></tr>
 <tr><td><code>x IS TRUE</code></td><td>True if x is true.</td></tr>
 <tr><td><code>x IS NOT TRUE</code></td><td>True if x is not true.</td></tr>
 <tr><td><code>x IS FALSE</code></td><td>True if x is false.</td></tr>
 <tr><td><code>x IS NOT FALSE</code></td><td>True if x is not false.</td></tr>
 <tr><td><code>x IN (values)</code></td><td>True if x is one of the listed values.</td></tr>
 <tr><td><code>x NOT IN (values)</code></td><td>True if x is not one of the listed values.</td></tr>
 <tr><td><code>x IN (subquery)</code></td><td>True if x is returned by the subquery. This will be translated into a join; see <a href="#query-translation">Query translation</a> for details.</td></tr>
 <tr><td><code>x NOT IN (subquery)</code></td><td>True if x is not returned by the subquery. This will be translated into a join; see <a href="#query-translation">Query translation</a> for details.</td></tr>
 <tr><td><code>x AND y</code></td><td>Boolean AND.</td></tr>
 <tr><td><code>x OR y</code></td><td>Boolean OR.</td></tr>
 <tr><td><code>NOT x</code></td><td>Boolean NOT.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="sketch-functions"></a><a href="#sketch-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Sketch functions</h3>
 <p>These functions operate on expressions or columns that return sketch objects.</p>
 <h4><a class="anchor" aria-hidden="true" id="hll-sketch-functions"></a><a href="#hll-sketch-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>HLL sketch functions</h4>
 <p>The following functions operate on <a href="../development/extensions-core/datasketches-hll.html">DataSketches HLL sketches</a>.
 The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>HLL_SKETCH_ESTIMATE(expr, [round])</code></td><td>Returns the distinct count estimate from an HLL sketch. <code>expr</code> must return an HLL sketch. The optional <code>round</code> boolean parameter will round the estimate if set to <code>true</code>, with a default of <code>false</code>.</td></tr>
 <tr><td><code>HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, [numStdDev])</code></td><td>Returns the distinct count estimate and error bounds from an HLL sketch. <code>expr</code> must return an HLL sketch. An optional <code>numStdDev</code> argument can be provided.</td></tr>
 <tr><td><code>HLL_SKETCH_UNION([lgK, tgtHllType], expr0, expr1, ...)</code></td><td>Returns a union of HLL sketches, where each input expression must return an HLL sketch. The <code>lgK</code> and <code>tgtHllType</code> can be optionally specified as the first parameter; if provided, both optional parameters must be specified.</td></tr>
 <tr><td><code>HLL_SKETCH_TO_STRING(expr)</code></td><td>Returns a human-readable string representation of an HLL sketch for debugging. <code>expr</code> must return an HLL sketch.</td></tr>
 </tbody>
 </table>
 <h4><a class="anchor" aria-hidden="true" id="theta-sketch-functions"></a><a href="#theta-sketch-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Theta sketch functions</h4>
 <p>The following functions operate on <a href="../development/extensions-core/datasketches-theta.html">theta sketches</a>.
 The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>THETA_SKETCH_ESTIMATE(expr)</code></td><td>Returns the distinct count estimate from a theta sketch. <code>expr</code> must return a theta sketch.</td></tr>
 <tr><td><code>THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, errorBoundsStdDev)</code></td><td>Returns the distinct count estimate and error bounds from a theta sketch. <code>expr</code> must return a theta sketch.</td></tr>
 <tr><td><code>THETA_SKETCH_UNION([size], expr0, expr1, ...)</code></td><td>Returns a union of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr>
 <tr><td><code>THETA_SKETCH_INTERSECT([size], expr0, expr1, ...)</code></td><td>Returns an intersection of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr>
 <tr><td><code>THETA_SKETCH_NOT([size], expr0, expr1, ...)</code></td><td>Returns a set difference of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr>
 </tbody>
 </table>
 <h4><a class="anchor" aria-hidden="true" id="quantiles-sketch-functions"></a><a href="#quantiles-sketch-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Quantiles sketch functions</h4>
 <p>The following functions operate on <a href="../development/extensions-core/datasketches-quantiles.html">quantiles sketches</a>.
 The <a href="../development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>DS_GET_QUANTILE(expr, fraction)</code></td><td>Returns the quantile estimate corresponding to <code>fraction</code> from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr>
 <tr><td><code>DS_GET_QUANTILES(expr, fraction0, fraction1, ...)</code></td><td>Returns a string representing an array of quantile estimates corresponding to a list of fractions from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr>
 <tr><td><code>DS_HISTOGRAM(expr, splitPoint0, splitPoint1, ...)</code></td><td>Returns a string representing an approximation to the histogram given a list of split points that define the histogram bins from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr>
 <tr><td><code>DS_CDF(expr, splitPoint0, splitPoint1, ...)</code></td><td>Returns a string representing approximation to the Cumulative Distribution Function given a list of split points that define the edges of the bins from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr>
 <tr><td><code>DS_RANK(expr, value)</code></td><td>Returns an approximation to the rank of a given value that is the fraction of the distribution less than that value from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr>
 <tr><td><code>DS_QUANTILE_SUMMARY(expr)</code></td><td>Returns a string summary of a quantiles sketch, useful for debugging. <code>expr</code> must return a quantiles sketch.</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="other-scalar-functions"></a><a href="#other-scalar-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Other scalar functions</h3>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>CAST(value AS TYPE)</code></td><td>Cast value to another type. See <a href="#data-types">Data types</a> for details about how Druid SQL handles CAST.</td></tr>
 <tr><td><code>CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END</code></td><td>Simple CASE.</td></tr>
 <tr><td><code>CASE WHEN boolean_expr1 THEN result1 \[ WHEN boolean_expr2 THEN result2 ... \] \[ ELSE resultN \] END</code></td><td>Searched CASE.</td></tr>
 <tr><td><code>NULLIF(value1, value2)</code></td><td>Returns NULL if value1 and value2 match, else returns value1.</td></tr>
 <tr><td><code>COALESCE(value1, value2, ...)</code></td><td>Returns the first value that is neither NULL nor empty string.</td></tr>
 <tr><td><code>NVL(expr,expr-for-null)</code></td><td>Returns 'expr-for-null' if 'expr' is null (or empty string for string type).</td></tr>
 <tr><td><code>BLOOM_FILTER_TEST(&lt;expr&gt;, &lt;serialized-filter&gt;)</code></td><td>Returns true if the value is contained in a Base64-serialized bloom filter. See the <a href="../development/extensions-core/bloom-filter.html">Bloom filter extension</a> documentation for additional details.</td></tr>
 </tbody>
 </table>
 <h2><a class="anchor" aria-hidden="true" id="multi-value-string-functions"></a><a href="#multi-value-string-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Multi-value string functions</h2>
 <p>All 'array' references in the multi-value string function documentation can refer to multi-value string columns or
 <code>ARRAY</code> literals.</p>
 <table>
 <thead>
 <tr><th>Function</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td><code>ARRAY(expr1,expr ...)</code></td><td>constructs a SQL ARRAY literal from the expression arguments, using the type of the first argument as the output array type</td></tr>
 <tr><td><code>MV_LENGTH(arr)</code></td><td>returns length of array expression</td></tr>
 <tr><td><code>MV_OFFSET(arr,long)</code></td><td>returns the array element at the 0 based index supplied, or null for an out of range index</td></tr>
 <tr><td><code>MV_ORDINAL(arr,long)</code></td><td>returns the array element at the 1 based index supplied, or null for an out of range index</td></tr>
 <tr><td><code>MV_CONTAINS(arr,expr)</code></td><td>returns 1 if the array contains the element specified by expr, or contains all elements specified by expr if expr is an array, else 0</td></tr>
 <tr><td><code>MV_OVERLAP(arr1,arr2)</code></td><td>returns 1 if arr1 and arr2 have any elements in common, else 0</td></tr>
 <tr><td><code>MV_OFFSET_OF(arr,expr)</code></td><td>returns the 0 based index of the first occurrence of expr in the array, or <code>-1</code> or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code> if no matching elements exist in the array.</td></tr>
 <tr><td><code>MV_ORDINAL_OF(arr,expr)</code></td><td>returns the 1 based index of the first occurrence of expr in the array, or <code>-1</code> or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code> if no matching elements exist in the array.</td></tr>
 <tr><td><code>MV_PREPEND(expr,arr)</code></td><td>adds expr to arr at the beginning, the resulting array type determined by the type of the array</td></tr>
 <tr><td><code>MV_APPEND(arr1,expr)</code></td><td>appends expr to arr, the resulting array type determined by the type of the first array</td></tr>
 <tr><td><code>MV_CONCAT(arr1,arr2)</code></td><td>concatenates 2 arrays, the resulting array type determined by the type of the first array</td></tr>
 <tr><td><code>MV_SLICE(arr,start,end)</code></td><td>return the subarray of arr from the 0 based index start(inclusive) to end(exclusive), or <code>null</code>, if start is less than 0, greater than length of arr or less than end</td></tr>
 <tr><td><code>MV_TO_STRING(arr,str)</code></td><td>joins all elements of arr by the delimiter specified by str</td></tr>
 <tr><td><code>STRING_TO_MV(str1,str2)</code></td><td>splits str1 into an array on the delimiter specified by str2</td></tr>
 </tbody>
 </table>
 <h2><a class="anchor" aria-hidden="true" id="query-translation"></a><a href="#query-translation" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Query translation</h2>
 <p>Druid SQL translates SQL queries to <a href="/docs/0.20.0/querying/querying.html">native queries</a> before running them, and understanding how this
 translation works is key to getting good performance.</p>
 <h3><a class="anchor" aria-hidden="true" id="best-practices"></a><a href="#best-practices" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Best practices</h3>
 <p>Consider this (non-exhaustive) list of things to look out for when looking into the performance implications of
 how your SQL queries are translated to native queries.</p>
 <ol>
 <li><p>If you wrote a filter on the primary time column <code>__time</code>, make sure it is being correctly translated to an
 <code>&quot;intervals&quot;</code> filter, as described in the <a href="#time-filters">Time filters</a> section below. If not, you may need to change
 the way you write the filter.</p></li>
 <li><p>Try to avoid subqueries underneath joins: they affect both performance and scalability. This includes implicit
 subqueries generated by conditions on mismatched types, and implicit subqueries generated by conditions that use
 expressions to refer to the right-hand side.</p></li>
 <li><p>Currently, Druid does not support pushing down predicates (condition and filter) past a Join (i.e. into
 Join's children). Druid only supports pushing predicates into the join if they originated from
 above the join. Hence, the location of predicates and filters in your Druid SQL is very important.
 Also, as a result of this, comma joins should be avoided.</p></li>
 <li><p>Read through the <a href="/docs/0.20.0/querying/query-execution.html">Query execution</a> page to understand how various types of native queries
 will be executed.</p></li>
 <li><p>Be careful when interpreting EXPLAIN PLAN output, and use request logging if in doubt. Request logs will show the
 exact native query that was run. See the <a href="#interpreting-explain-plan-output">next section</a> for more details.</p></li>
 <li><p>If you encounter a query that could be planned better, feel free to
 <a href="https://github.com/apache/druid/issues/new/choose">raise an issue on GitHub</a>. A reproducible test case is always
 appreciated.</p></li>
 </ol>
 <h3><a class="anchor" aria-hidden="true" id="interpreting-explain-plan-output"></a><a href="#interpreting-explain-plan-output" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Interpreting EXPLAIN PLAN output</h3>
 <p>The <a href="#explain-plan">EXPLAIN PLAN</a> functionality can help you understand how a given SQL query will
 be translated to native. For simple queries that do not involve subqueries or joins, the output of EXPLAIN PLAN
 is easy to interpret. The native query that will run is embedded as JSON inside a &quot;DruidQueryRel&quot; line:</p>
 <pre><code class="hljs">&gt; EXPLAIN PLAN FOR SELECT COUNT(<span class="hljs-name">*</span>) FROM wikipedia

 DruidQueryRel(<span class="hljs-name">query=</span>[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"timeseries"</span>,<span class="hljs-string">"dataSource"</span>:<span class="hljs-string">"wikipedia"</span>,<span class="hljs-string">"intervals"</span>:<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{a0<span class="hljs-symbol">:LONG</span>}])
 </code></pre>
 <p>For more complex queries that do involve subqueries or joins, EXPLAIN PLAN is somewhat more difficult to interpret.
 For example, consider this query:</p>
 <pre><code class="hljs">&gt; EXPLAIN PLAN <span class="hljs-keyword">FOR</span>
 &gt; SELECT
 &gt;     channel,
 &gt;     COUNT(*)
 &gt; <span class="hljs-keyword">FROM</span> wikipedia
 &gt; WHERE channel <span class="hljs-keyword">IN</span> (SELECT<span class="hljs-built_in"> page </span><span class="hljs-keyword">FROM</span> wikipedia<span class="hljs-built_in"> GROUP </span>BY<span class="hljs-built_in"> page </span>ORDER BY COUNT(*) DESC LIMIT 10)
 &gt;<span class="hljs-built_in"> GROUP </span>BY channel

 DruidJoinQueryRel(condition=[=(<span class="hljs-variable">$1</span>, <span class="hljs-variable">$3</span>)], joinType=[inner], query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"groupBy"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"__join__"</span>},<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"dimensions"</span>:[<span class="hljs-string">"channel"</span>],<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{d0:STRING, a0:LONG}])
   DruidQueryRel(query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"scan"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"wikipedia"</span>},<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"resultFormat"</span>:<span class="hljs-string">"compactedList"</span>,<span class="hljs-string">"columns"</span>:[<span class="hljs-string">"__time"</span>,<span class="hljs-string">"channel"</span>,<span class="hljs-string">"page"</span>],<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>}], signature=[{__time:LONG, channel:STRING, page:STRING}])
   DruidQueryRel(query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"topN"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"wikipedia"</span>},<span class="hljs-string">"dimension"</span>:<span class="hljs-string">"page"</span>,<span class="hljs-string">"metric"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"numeric"</span>,<span class="hljs-string">"metric"</span>:<span class="hljs-string">"a0"</span>},<span class="hljs-string">"threshold"</span>:10,<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{d0:STRING}])
 </code></pre>
 <p>Here, there is a join with two inputs. The way to read this is to consider each line of the EXPLAIN PLAN output as
 something that might become a query, or might just become a simple datasource. The <code>query</code> field they all have is
 called a &quot;partial query&quot; and represents what query would be run on the datasource represented by that line, if that
 line ran by itself. In some cases — like the &quot;scan&quot; query in the second line of this example — the query does not
 actually run, and it ends up being translated to a simple table datasource. See the <a href="#joins">Join translation</a> section
 for more details about how this works.</p>
 <p>We can see this for ourselves using Druid's <a href="/docs/0.20.0/configuration/index.html#request-logging">request logging</a> feature. After
 enabling logging and running this query, we can see that it actually runs as the following native query.</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"groupBy"</span>,
   <span class="hljs-attr">"dataSource"</span>: {
     <span class="hljs-attr">"type"</span>: <span class="hljs-string">"join"</span>,
     <span class="hljs-attr">"left"</span>: <span class="hljs-string">"wikipedia"</span>,
     <span class="hljs-attr">"right"</span>: {
       <span class="hljs-attr">"type"</span>: <span class="hljs-string">"query"</span>,
       <span class="hljs-attr">"query"</span>: {
         <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"topN"</span>,
         <span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"wikipedia"</span>,
         <span class="hljs-attr">"dimension"</span>: {<span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"page"</span>, <span class="hljs-attr">"outputName"</span>: <span class="hljs-string">"d0"</span>},
         <span class="hljs-attr">"metric"</span>: {<span class="hljs-attr">"type"</span>: <span class="hljs-string">"numeric"</span>, <span class="hljs-attr">"metric"</span>: <span class="hljs-string">"a0"</span>},
         <span class="hljs-attr">"threshold"</span>: <span class="hljs-number">10</span>,
         <span class="hljs-attr">"intervals"</span>: <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,
         <span class="hljs-attr">"granularity"</span>: <span class="hljs-string">"all"</span>,
         <span class="hljs-attr">"aggregations"</span>: [
           { <span class="hljs-attr">"type"</span>: <span class="hljs-string">"count"</span>, <span class="hljs-attr">"name"</span>: <span class="hljs-string">"a0"</span>}
         ]
       }
     },
     <span class="hljs-attr">"rightPrefix"</span>: <span class="hljs-string">"j0."</span>,
     <span class="hljs-attr">"condition"</span>: <span class="hljs-string">"(\"page\" == \"j0.d0\")"</span>,
     <span class="hljs-attr">"joinType"</span>: <span class="hljs-string">"INNER"</span>
   },
   <span class="hljs-attr">"intervals"</span>: <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,
   <span class="hljs-attr">"granularity"</span>: <span class="hljs-string">"all"</span>,
   <span class="hljs-attr">"dimensions"</span>: [
     {<span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"channel"</span>, <span class="hljs-attr">"outputName"</span>: <span class="hljs-string">"d0"</span>}
   ],
   <span class="hljs-attr">"aggregations"</span>: [
     { <span class="hljs-attr">"type"</span>: <span class="hljs-string">"count"</span>, <span class="hljs-attr">"name"</span>: <span class="hljs-string">"a0"</span>}
   ]
 }
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="query-types"></a><a href="#query-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Query types</h3>
 <p>Druid SQL uses four different native query types.</p>
 <ul>
 <li><p><a href="scan-query.html">Scan</a> is used for queries that do not aggregate (no GROUP BY, no DISTINCT).</p></li>
 <li><p><a href="timeseriesquery.html">Timeseries</a> is used for queries that GROUP BY <code>FLOOR(__time TO &lt;unit&gt;)</code> or <code>TIME_FLOOR(__time, period)</code>, have no other grouping expressions, no HAVING or LIMIT clauses, no nesting, and either no ORDER BY, or an
 ORDER BY that orders by same expression as present in GROUP BY. It also uses Timeseries for &quot;grand total&quot; queries that
 have aggregation functions but no GROUP BY. This query type takes advantage of the fact that Druid segments are sorted
 by time.</p></li>
 <li><p><a href="topnquery.html">TopN</a> is used by default for queries that group by a single expression, do have ORDER BY and LIMIT
 clauses, do not have HAVING clauses, and are not nested. However, the TopN query type will deliver approximate ranking
 and results in some cases; if you want to avoid this, set &quot;useApproximateTopN&quot; to &quot;false&quot;. TopN results are always
 computed in memory. See the TopN documentation for more details.</p></li>
 <li><p><a href="groupbyquery.html">GroupBy</a> is used for all other aggregations, including any nested aggregation queries. Druid's
 GroupBy is a traditional aggregation engine: it delivers exact results and rankings and supports a wide variety of
 features. GroupBy aggregates in memory if it can, but it may spill to disk if it doesn't have enough memory to complete
 your query. Results are streamed back from data processes through the Broker if you ORDER BY the same expressions in your
 GROUP BY clause, or if you don't have an ORDER BY at all. If your query has an ORDER BY referencing expressions that
 don't appear in the GROUP BY clause (like aggregation functions) then the Broker will materialize a list of results in
 memory, up to a max of your LIMIT, if any. See the GroupBy documentation for details about tuning performance and memory
 use.</p></li>
 </ul>
 <h3><a class="anchor" aria-hidden="true" id="time-filters"></a><a href="#time-filters" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Time filters</h3>
 <p>For all native query types, filters on the <code>__time</code> column will be translated into top-level query &quot;intervals&quot; whenever
 possible, which allows Druid to use its global time index to quickly prune the set of data that must be scanned.
 Consider this (non-exhaustive) list of time filters that will be recognized and translated to &quot;intervals&quot;:</p>
 <ul>
 <li><code>__time &gt;= TIMESTAMP '2000-01-01 00:00:00'</code> (comparison to absolute time)</li>
 <li><code>__time &gt;= CURRENT_TIMESTAMP - INTERVAL '8' HOUR</code> (comparison to relative time)</li>
 <li><code>FLOOR(__time TO DAY) = TIMESTAMP '2000-01-01 00:00:00'</code> (specific day)</li>
 </ul>
 <p>Refer to the <a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a> section for details on confirming
 that time filters are being translated as you expect.</p>
 <h3><a class="anchor" aria-hidden="true" id="joins"></a><a href="#joins" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Joins</h3>
 <p>SQL join operators are translated to native join datasources as follows:</p>
 <ol>
 <li><p>Joins that the native layer can handle directly are translated literally, to a <a href="/docs/0.20.0/querying/datasource.html#join">join datasource</a>
 whose <code>left</code>, <code>right</code>, and <code>condition</code> are faithful translations of the original SQL. This includes any SQL join where
 the right-hand side is a lookup or subquery, and where the condition is an equality where one side is an expression based
 on the left-hand table, the other side is a simple column reference to the right-hand table, and both sides of the
 equality are the same data type.</p></li>
 <li><p>If a join cannot be handled directly by a native <a href="/docs/0.20.0/querying/datasource.html#join">join datasource</a> as written, Druid SQL
 will insert subqueries to make it runnable. For example, <code>foo INNER JOIN bar ON foo.abc = LOWER(bar.def)</code> cannot be
 directly translated, because there is an expression on the right-hand side instead of a simple column access. A subquery
 will be inserted that effectively transforms this clause to
 <code>foo INNER JOIN (SELECT LOWER(def) AS def FROM bar) t ON foo.abc = t.def</code>.</p></li>
 <li><p>Druid SQL does not currently reorder joins to optimize queries.</p></li>
 </ol>
 <p>Refer to the <a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a> section for details on confirming
 that joins are being translated as you expect.</p>
 <p>Refer to the <a href="/docs/0.20.0/querying/query-execution.html#join">Query execution</a> page for information about how joins are executed.</p>
 <h3><a class="anchor" aria-hidden="true" id="subqueries"></a><a href="#subqueries" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Subqueries</h3>
 <p>Subqueries in SQL are generally translated to native query datasources. Refer to the
 <a href="/docs/0.20.0/querying/query-execution.html#query">Query execution</a> page for information about how subqueries are executed.</p>
 <blockquote>
 <p>Note: Subqueries in the WHERE clause, like <code>WHERE col1 IN (SELECT foo FROM ...)</code> are translated to inner joins.</p>
 </blockquote>
 <h3><a class="anchor" aria-hidden="true" id="approximations"></a><a href="#approximations" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Approximations</h3>
 <p>Druid SQL will use approximate algorithms in some situations:</p>
 <ul>
 <li><p>The <code>COUNT(DISTINCT col)</code> aggregation functions by default uses a variant of
 <a href="http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf">HyperLogLog</a>, a fast approximate distinct counting
 algorithm. Druid SQL will switch to exact distinct counts if you set &quot;useApproximateCountDistinct&quot; to &quot;false&quot;, either
 through query context or through Broker configuration.</p></li>
 <li><p>GROUP BY queries over a single column with ORDER BY and LIMIT may be executed using the TopN engine, which uses an
 approximate algorithm. Druid SQL will switch to an exact grouping algorithm if you set &quot;useApproximateTopN&quot; to &quot;false&quot;,
 either through query context or through Broker configuration.</p></li>
 <li><p>Aggregation functions that are labeled as using sketches or approximations, such as APPROX_COUNT_DISTINCT, are always
 approximate, regardless of configuration.</p></li>
 </ul>
 <h3><a class="anchor" aria-hidden="true" id="unsupported-features"></a><a href="#unsupported-features" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Unsupported features</h3>
 <p>Druid does not support all SQL features. In particular, the following features are not supported.</p>
 <ul>
 <li>JOIN between native datasources (table, lookup, subquery) and <a href="#metadata-tables">system tables</a>.</li>
 <li>JOIN conditions that are not an equality between expressions from the left- and right-hand sides.</li>
 <li>JOIN conditions containing a constant value inside the condition.</li>
 <li>JOIN conditions on a column which contains a multi-value dimension.</li>
 <li>OVER clauses, and analytic functions such as <code>LAG</code> and <code>LEAD</code>.</li>
 <li>ORDER BY for a non-aggregating query, except for <code>ORDER BY __time</code> or <code>ORDER BY __time DESC</code>, which are supported.
 This restriction only applies to non-aggregating queries; you can ORDER BY any column in an aggregating query.</li>
 <li>DDL and DML.</li>
 <li>Using Druid-specific functions like <code>TIME_PARSE</code> and <code>APPROX_QUANTILE_DS</code> on <a href="#metadata-tables">system tables</a>.</li>
 </ul>
 <p>Additionally, some Druid native query features are not supported by the SQL language. Some unsupported Druid features
 include:</p>
 <ul>
 <li><a href="datasource.html#inline">Inline datasources</a>.</li>
 <li><a href="../development/geo.html">Spatial filters</a>.</li>
 <li><a href="querying.html#query-cancellation">Query cancellation</a>.</li>
 <li><a href="#multi-value-strings">Multi-value dimensions</a> are only partially implemented in Druid SQL. There are known
 inconsistencies between their behavior in SQL queries and in native queries due to how they are currently treated by
 the SQL planner.</li>
 </ul>
 <h2><a class="anchor" aria-hidden="true" id="client-apis"></a><a href="#client-apis" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Client APIs</h2>
 <p><a name="json-over-http"></a></p>
 <h3><a class="anchor" aria-hidden="true" id="http-post"></a><a href="#http-post" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>HTTP POST</h3>
 <p>You can make Druid SQL queries using HTTP via POST to the endpoint <code>/druid/v2/sql/</code>. The request should
 be a JSON object with a &quot;query&quot; field, like <code>{&quot;query&quot; : &quot;SELECT COUNT(*) FROM data_source WHERE foo = 'bar'&quot;}</code>.</p>
 <h5><a class="anchor" aria-hidden="true" id="request"></a><a href="#request" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Request</h5>
 <table>
 <thead>
 <tr><th>Property</th><th>Description</th><th>Default</th></tr>
 </thead>
 <tbody>
 <tr><td><code>query</code></td><td>SQL query string.</td><td>none (required)</td></tr>
 <tr><td><code>resultFormat</code></td><td>Format of query results. See <a href="#responses">Responses</a> for details.</td><td><code>&quot;object&quot;</code></td></tr>
 <tr><td><code>header</code></td><td>Whether or not to include a header. See [Responses] for details.</td><td><code>false</code></td></tr>
 <tr><td><code>context</code></td><td>JSON object containing <a href="#connection-context">connection context parameters</a>.</td><td><code>{}</code> (empty)</td></tr>
 <tr><td><code>parameters</code></td><td>List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like <code>{&quot;type&quot;: &quot;VARCHAR&quot;, &quot;value&quot;: &quot;foo&quot;}</code>. The type should be a SQL type; see <a href="#data-types">Data types</a> for a list of supported SQL types.</td><td><code>[]</code> (empty)</td></tr>
 </tbody>
 </table>
 <p>You can use <em>curl</em> to send SQL queries from the command-line:</p>
 <pre><code class="hljs css language-bash">$ cat query.json
 {<span class="hljs-string">"query"</span>:<span class="hljs-string">"SELECT COUNT(*) AS TheCount FROM data_source"</span>}

 $ curl -XPOST -H<span class="hljs-string">'Content-Type: application/json'</span> http://BROKER:8082/druid/v2/sql/ -d @query.json
 [{<span class="hljs-string">"TheCount"</span>:24433}]
 </code></pre>
 <p>There are a variety of <a href="#connection-context">connection context parameters</a> you can provide by adding a &quot;context&quot; map,
 like:</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"query"</span> : <span class="hljs-string">"SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time &gt; TIMESTAMP '2000-01-01 00:00:00'"</span>,
   <span class="hljs-attr">"context"</span> : {
     <span class="hljs-attr">"sqlTimeZone"</span> : <span class="hljs-string">"America/Los_Angeles"</span>
   }
 }
 </code></pre>
 <p>Parameterized SQL queries are also supported:</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"query"</span> : <span class="hljs-string">"SELECT COUNT(*) FROM data_source WHERE foo = ? AND __time &gt; ?"</span>,
   <span class="hljs-attr">"parameters"</span>: [
     { <span class="hljs-attr">"type"</span>: <span class="hljs-string">"VARCHAR"</span>, <span class="hljs-attr">"value"</span>: <span class="hljs-string">"bar"</span>},
     { <span class="hljs-attr">"type"</span>: <span class="hljs-string">"TIMESTAMP"</span>, <span class="hljs-attr">"value"</span>: <span class="hljs-string">"2000-01-01 00:00:00"</span> }
   ]
 }
 </code></pre>
 <p>Metadata is available over HTTP POST by querying <a href="#metadata-tables">metadata tables</a>.</p>
 <h4><a class="anchor" aria-hidden="true" id="responses"></a><a href="#responses" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Responses</h4>
 <p>Druid SQL's HTTP POST API supports a variety of result formats. You can specify these by adding a &quot;resultFormat&quot;
 parameter, like:</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"query"</span> : <span class="hljs-string">"SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time &gt; TIMESTAMP '2000-01-01 00:00:00'"</span>,
   <span class="hljs-attr">"resultFormat"</span> : <span class="hljs-string">"object"</span>
 }
 </code></pre>
 <p>The supported result formats are:</p>
 <table>
 <thead>
 <tr><th>Format</th><th>Description</th><th>Content-Type</th></tr>
 </thead>
 <tbody>
 <tr><td><code>object</code></td><td>The default, a JSON array of JSON objects. Each object's field names match the columns returned by the SQL query, and are provided in the same order as the SQL query.</td><td>application/json</td></tr>
 <tr><td><code>array</code></td><td>JSON array of JSON arrays. Each inner array has elements matching the columns returned by the SQL query, in order.</td><td>application/json</td></tr>
 <tr><td><code>objectLines</code></td><td>Like &quot;object&quot;, but the JSON objects are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.</td><td>text/plain</td></tr>
 <tr><td><code>arrayLines</code></td><td>Like &quot;array&quot;, but the JSON arrays are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.</td><td>text/plain</td></tr>
 <tr><td><code>csv</code></td><td>Comma-separated values, with one row per line. Individual field values may be escaped by being surrounded in double quotes. If double quotes appear in a field value, they will be escaped by replacing them with double-double-quotes like <code>&quot;&quot;this&quot;&quot;</code>. To make it possible to detect a truncated response, this format includes a trailer of one blank line.</td><td>text/csv</td></tr>
 </tbody>
 </table>
 <p>You can additionally request a header by setting &quot;header&quot; to true in your request, like:</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"query"</span> : <span class="hljs-string">"SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time &gt; TIMESTAMP '2000-01-01 00:00:00'"</span>,
   <span class="hljs-attr">"resultFormat"</span> : <span class="hljs-string">"arrayLines"</span>,
   <span class="hljs-attr">"header"</span> : <span class="hljs-literal">true</span>
 }
 </code></pre>
 <p>In this case, the first result returned will be a header. For the <code>csv</code>, <code>array</code>, and <code>arrayLines</code> formats, the header
 will be a list of column names. For the <code>object</code> and <code>objectLines</code> formats, the header will be an object where the
 keys are column names, and the values are null.</p>
 <p>Errors that occur before the response body is sent will be reported in JSON, with an HTTP 500 status code, in the
 same format as <a href="../querying/querying.html#query-errors">native Druid query errors</a>. If an error occurs while the response body is
 being sent, at that point it is too late to change the HTTP status code or report a JSON error, so the response will
 simply end midstream and an error will be logged by the Druid server that was handling your request.</p>
 <p>As a caller, it is important that you properly handle response truncation. This is easy for the &quot;object&quot; and &quot;array&quot;
 formats, since truncated responses will be invalid JSON. For the line-oriented formats, you should check the
 trailer they all include: one blank line at the end of the result set. If you detect a truncated response, either
 through a JSON parsing error or through a missing trailing newline, you should assume the response was not fully
 delivered due to an error.</p>
 <h3><a class="anchor" aria-hidden="true" id="jdbc"></a><a href="#jdbc" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>JDBC</h3>
 <p>You can make Druid SQL queries using the <a href="https://calcite.apache.org/avatica/downloads/">Avatica JDBC driver</a>. We recommend using Avatica JDBC driver version 1.17.0 or later. Note that as of the time of this writing, Avatica 1.17.0, the latest version, does not support passing connection string parameters from the URL to Druid, so you must pass them using a <code>Properties</code> object. Once you've downloaded the Avatica client jar, add it to your classpath and use the connect string <code>jdbc:avatica:remote:url=http://BROKER:8082/druid/v2/sql/avatica/</code>.</p>
 <p>Example code:</p>
 <pre><code class="hljs css language-java"><span class="hljs-comment">// Connect to /druid/v2/sql/avatica/ on your Broker.</span>
 String url = <span class="hljs-string">"jdbc:avatica:remote:url=http://localhost:8082/druid/v2/sql/avatica/"</span>;

 <span class="hljs-comment">// Set any connection context parameters you need here (see "Connection context" below).</span>
 <span class="hljs-comment">// Or leave empty for default behavior.</span>
 Properties connectionProperties = <span class="hljs-keyword">new</span> Properties();

 <span class="hljs-keyword">try</span> (Connection connection = DriverManager.getConnection(url, connectionProperties)) {
   <span class="hljs-keyword">try</span> (
       <span class="hljs-keyword">final</span> Statement statement = connection.createStatement();
       <span class="hljs-keyword">final</span> ResultSet resultSet = statement.executeQuery(query)
   ) {
     <span class="hljs-keyword">while</span> (resultSet.next()) {
       <span class="hljs-comment">// process result set</span>
     }
   }
 }
 </code></pre>
 <p>Table metadata is available over JDBC using <code>connection.getMetaData()</code> or by querying the
 <a href="#metadata-tables">&quot;INFORMATION_SCHEMA&quot; tables</a>.</p>
 <h4><a class="anchor" aria-hidden="true" id="connection-stickiness"></a><a href="#connection-stickiness" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Connection stickiness</h4>
 <p>Druid's JDBC server does not share connection state between Brokers. This means that if you're using JDBC and have
 multiple Druid Brokers, you should either connect to a specific Broker, or use a load balancer with sticky sessions
 enabled. The Druid Router process provides connection stickiness when balancing JDBC requests, and can be used to achieve
 the necessary stickiness even with a normal non-sticky load balancer. Please see the
 <a href="/docs/0.20.0/design/router.html">Router</a> documentation for more details.</p>
 <p>Note that the non-JDBC <a href="#http-post">JSON over HTTP</a> API is stateless and does not require stickiness.</p>
 <h3><a class="anchor" aria-hidden="true" id="dynamic-parameters-1"></a><a href="#dynamic-parameters-1" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Dynamic Parameters</h3>
 <p>You can also use parameterized queries in JDBC code, as in this example;</p>
 <pre><code class="hljs css language-java">PreparedStatement statement = connection.prepareStatement(<span class="hljs-string">"SELECT COUNT(*) AS cnt FROM druid.foo WHERE dim1 = ? OR dim1 = ?"</span>);
 statement.setString(<span class="hljs-number">1</span>, <span class="hljs-string">"abc"</span>);
 statement.setString(<span class="hljs-number">2</span>, <span class="hljs-string">"def"</span>);
 <span class="hljs-keyword">final</span> ResultSet resultSet = statement.executeQuery();
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="connection-context"></a><a href="#connection-context" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Connection context</h3>
 <p>Druid SQL supports setting connection parameters on the client. The parameters in the table below affect SQL planning.
 All other context parameters you provide will be attached to Druid queries and can affect how they run. See
 <a href="query-context.html">Query context</a> for details on the possible options.</p>
 <pre><code class="hljs css language-java">String url = <span class="hljs-string">"jdbc:avatica:remote:url=http://localhost:8082/druid/v2/sql/avatica/"</span>;

 <span class="hljs-comment">// Set any query context parameters you need here.</span>
 Properties connectionProperties = <span class="hljs-keyword">new</span> Properties();
 connectionProperties.setProperty(<span class="hljs-string">"sqlTimeZone"</span>, <span class="hljs-string">"America/Los_Angeles"</span>);
 connectionProperties.setProperty(<span class="hljs-string">"useCache"</span>, <span class="hljs-string">"false"</span>);

 <span class="hljs-keyword">try</span> (Connection connection = DriverManager.getConnection(url, connectionProperties)) {
   <span class="hljs-comment">// create and execute statements, process result sets, etc</span>
 }
 </code></pre>
 <p>Note that to specify an unique identifier for SQL query, use <code>sqlQueryId</code> instead of <code>queryId</code>. Setting <code>queryId</code> for a SQL
 request has no effect, all native queries underlying SQL will use auto-generated queryId.</p>
 <p>Connection context can be specified as JDBC connection properties or as a &quot;context&quot; object in the JSON API.</p>
 <table>
 <thead>
 <tr><th>Parameter</th><th>Description</th><th>Default value</th></tr>
 </thead>
 <tbody>
 <tr><td><code>sqlQueryId</code></td><td>Unique identifier given to this SQL query. For HTTP client, it will be returned in <code>X-Druid-SQL-Query-Id</code> header.</td><td>auto-generated</td></tr>
 <tr><td><code>sqlTimeZone</code></td><td>Sets the time zone for this connection, which will affect how time functions and timestamp literals behave. Should be a time zone name like &quot;America/Los_Angeles&quot; or offset like &quot;-08:00&quot;.</td><td>druid.sql.planner.sqlTimeZone on the Broker (default: UTC)</td></tr>
 <tr><td><code>useApproximateCountDistinct</code></td><td>Whether to use an approximate cardinality algorithm for <code>COUNT(DISTINCT foo)</code>.</td><td>druid.sql.planner.useApproximateCountDistinct on the Broker (default: true)</td></tr>
 <tr><td><code>useApproximateTopN</code></td><td>Whether to use approximate <a href="topnquery.html">TopN queries</a> when a SQL query could be expressed as such. If false, exact <a href="groupbyquery.html">GroupBy queries</a> will be used instead.</td><td>druid.sql.planner.useApproximateTopN on the Broker (default: true)</td></tr>
 </tbody>
 </table>
 <h2><a class="anchor" aria-hidden="true" id="metadata-tables"></a><a href="#metadata-tables" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Metadata tables</h2>
 <p>Druid Brokers infer table and column metadata for each datasource from segments loaded in the cluster, and use this to
 plan SQL queries. This metadata is cached on Broker startup and also updated periodically in the background through
 <a href="segmentmetadataquery.html">SegmentMetadata queries</a>. Background metadata refreshing is triggered by
 segments entering and exiting the cluster, and can also be throttled through configuration.</p>
 <p>Druid exposes system information through special system tables. There are two such schemas available: Information Schema and Sys Schema.
 Information schema provides details about table and column types. The &quot;sys&quot; schema provides information about Druid internals like segments/tasks/servers.</p>
 <h3><a class="anchor" aria-hidden="true" id="information-schema"></a><a href="#information-schema" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>INFORMATION SCHEMA</h3>
 <p>You can access table and column metadata through JDBC using <code>connection.getMetaData()</code>, or through the
 INFORMATION_SCHEMA tables described below. For example, to retrieve metadata for the Druid
 datasource &quot;foo&quot;, use the query:</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> INFORMATION_SCHEMA.COLUMNS <span class="hljs-keyword">WHERE</span> TABLE_SCHEMA = <span class="hljs-string">'druid'</span> <span class="hljs-keyword">AND</span> TABLE_NAME = <span class="hljs-string">'foo'</span>
 </code></pre>
 <blockquote>
 <p>Note: INFORMATION_SCHEMA tables do not currently support Druid-specific functions like <code>TIME_PARSE</code> and
 <code>APPROX_QUANTILE_DS</code>. Only standard SQL functions can be used.</p>
 </blockquote>
 <h4><a class="anchor" aria-hidden="true" id="schemata-table"></a><a href="#schemata-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SCHEMATA table</h4>
 <p><code>INFORMATION_SCHEMA.SCHEMATA</code> provides a list of all known schemas, which include <code>druid</code> for standard <a href="/docs/0.20.0/querying/datasource.html#table">Druid Table datasources</a>, <code>lookup</code> for <a href="/docs/0.20.0/querying/datasource.html#lookup">Lookups</a>, <code>sys</code> for the virtual <a href="#system-schema">System metadata tables</a>, and <code>INFORMATION_SCHEMA</code> for these virtual tables. Tables are allowed to have the same name across different schemas, so the schema may be included in an SQL statement to distinguish them, e.g. <code>lookup.table</code> vs <code>druid.table</code>.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>CATALOG_NAME</td><td>Always set as <code>druid</code></td></tr>
 <tr><td>SCHEMA_NAME</td><td><code>druid</code>, <code>lookup</code>, <code>sys</code>, or <code>INFORMATION_SCHEMA</code></td></tr>
 <tr><td>SCHEMA_OWNER</td><td>Unused</td></tr>
 <tr><td>DEFAULT_CHARACTER_SET_CATALOG</td><td>Unused</td></tr>
 <tr><td>DEFAULT_CHARACTER_SET_SCHEMA</td><td>Unused</td></tr>
 <tr><td>DEFAULT_CHARACTER_SET_NAME</td><td>Unused</td></tr>
 <tr><td>SQL_PATH</td><td>Unused</td></tr>
 </tbody>
 </table>
 <h4><a class="anchor" aria-hidden="true" id="tables-table"></a><a href="#tables-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>TABLES table</h4>
 <p><code>INFORMATION_SCHEMA.TABLES</code> provides a list of all known tables and schemas.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>TABLE_CATALOG</td><td>Always set as <code>druid</code></td></tr>
 <tr><td>TABLE_SCHEMA</td><td>The 'schema' which the table falls under, see <a href="#schemata-table">SCHEMATA table for details</a></td></tr>
 <tr><td>TABLE_NAME</td><td>Table name. For the <code>druid</code> schema, this is the <code>dataSource</code>.</td></tr>
 <tr><td>TABLE_TYPE</td><td>&quot;TABLE&quot; or &quot;SYSTEM_TABLE&quot;</td></tr>
 <tr><td>IS_JOINABLE</td><td>If a table is directly joinable if on the right hand side of a <code>JOIN</code> statement, without performing a subquery, this value will be set to <code>YES</code>, otherwise <code>NO</code>. Lookups are always joinable because they are globally distributed among Druid query processing nodes, but Druid datasources are not, and will use a less efficient subquery join.</td></tr>
 <tr><td>IS_BROADCAST</td><td>If a table is 'broadcast' and distributed among all Druid query processing nodes, this value will be set to <code>YES</code>, such as lookups and Druid datasources which have a 'broadcast' load rule, else <code>NO</code>.</td></tr>
 </tbody>
 </table>
 <h4><a class="anchor" aria-hidden="true" id="columns-table"></a><a href="#columns-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>COLUMNS table</h4>
 <p><code>INFORMATION_SCHEMA.COLUMNS</code> provides a list of all known columns across all tables and schema.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>TABLE_CATALOG</td><td>Always set as <code>druid</code></td></tr>
 <tr><td>TABLE_SCHEMA</td><td>The 'schema' which the table column falls under, see <a href="#schemata-table">SCHEMATA table for details</a></td></tr>
 <tr><td>TABLE_NAME</td><td>The 'table' which the column belongs to, see <a href="#tables-table">TABLES table for details</a></td></tr>
 <tr><td>COLUMN_NAME</td><td>The column name</td></tr>
 <tr><td>ORDINAL_POSITION</td><td>The order in which the column is stored in a table</td></tr>
 <tr><td>COLUMN_DEFAULT</td><td>Unused</td></tr>
 <tr><td>IS_NULLABLE</td><td></td></tr>
 <tr><td>DATA_TYPE</td><td></td></tr>
 <tr><td>CHARACTER_MAXIMUM_LENGTH</td><td>Unused</td></tr>
 <tr><td>CHARACTER_OCTET_LENGTH</td><td>Unused</td></tr>
 <tr><td>NUMERIC_PRECISION</td><td></td></tr>
 <tr><td>NUMERIC_PRECISION_RADIX</td><td></td></tr>
 <tr><td>NUMERIC_SCALE</td><td></td></tr>
 <tr><td>DATETIME_PRECISION</td><td></td></tr>
 <tr><td>CHARACTER_SET_NAME</td><td></td></tr>
 <tr><td>COLLATION_NAME</td><td></td></tr>
 <tr><td>JDBC_TYPE</td><td>Type code from java.sql.Types (Druid extension)</td></tr>
 </tbody>
 </table>
 <h3><a class="anchor" aria-hidden="true" id="system-schema"></a><a href="#system-schema" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SYSTEM SCHEMA</h3>
 <p>The &quot;sys&quot; schema provides visibility into Druid segments, servers and tasks.</p>
 <blockquote>
 <p>Note: &quot;sys&quot; tables do not currently support Druid-specific functions like <code>TIME_PARSE</code> and
 <code>APPROX_QUANTILE_DS</code>. Only standard SQL functions can be used.</p>
 </blockquote>
 <h4><a class="anchor" aria-hidden="true" id="segments-table"></a><a href="#segments-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SEGMENTS table</h4>
 <p>Segments table provides details on all Druid segments, whether they are published yet or not.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Type</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>segment_id</td><td>STRING</td><td>Unique segment identifier</td></tr>
 <tr><td>datasource</td><td>STRING</td><td>Name of datasource</td></tr>
 <tr><td>start</td><td>STRING</td><td>Interval start time (in ISO 8601 format)</td></tr>
 <tr><td>end</td><td>STRING</td><td>Interval end time (in ISO 8601 format)</td></tr>
 <tr><td>size</td><td>LONG</td><td>Size of segment in bytes</td></tr>
 <tr><td>version</td><td>STRING</td><td>Version string (generally an ISO8601 timestamp corresponding to when the segment set was first started). Higher version means the more recently created segment. Version comparing is based on string comparison.</td></tr>
 <tr><td>partition_num</td><td>LONG</td><td>Partition number (an integer, unique within a datasource+interval+version; may not necessarily be contiguous)</td></tr>
 <tr><td>num_replicas</td><td>LONG</td><td>Number of replicas of this segment currently being served</td></tr>
 <tr><td>num_rows</td><td>LONG</td><td>Number of rows in current segment, this value could be null if unknown to Broker at query time</td></tr>
 <tr><td>is_published</td><td>LONG</td><td>Boolean is represented as long type where 1 = true, 0 = false. 1 represents this segment has been published to the metadata store with <code>used=1</code>. See the <a href="/docs/0.20.0/design/architecture.html#segment-lifecycle">Architecture page</a> for more details.</td></tr>
 <tr><td>is_available</td><td>LONG</td><td>Boolean is represented as long type where 1 = true, 0 = false. 1 if this segment is currently being served by any process(Historical or realtime). See the <a href="/docs/0.20.0/design/architecture.html#segment-lifecycle">Architecture page</a> for more details.</td></tr>
 <tr><td>is_realtime</td><td>LONG</td><td>Boolean is represented as long type where 1 = true, 0 = false. 1 if this segment is <em>only</em> served by realtime tasks, and 0 if any historical process is serving this segment.</td></tr>
 <tr><td>is_overshadowed</td><td>LONG</td><td>Boolean is represented as long type where 1 = true, 0 = false. 1 if this segment is published and is <em>fully</em> overshadowed by some other published segments. Currently, is_overshadowed is always false for unpublished segments, although this may change in the future. You can filter for segments that &quot;should be published&quot; by filtering for <code>is_published = 1 AND is_overshadowed = 0</code>. Segments can briefly be both published and overshadowed if they were recently replaced, but have not been unpublished yet. See the <a href="/docs/0.20.0/design/architecture.html#segment-lifecycle">Architecture page</a> for more details.</td></tr>
 <tr><td>shardSpec</td><td>STRING</td><td>The toString of specific <code>ShardSpec</code></td></tr>
 <tr><td>dimensions</td><td>STRING</td><td>The dimensions of the segment</td></tr>
 <tr><td>metrics</td><td>STRING</td><td>The metrics of the segment</td></tr>
 <tr><td>last_compaction_state</td><td>STRING</td><td>The configurations of the compaction task which created this segment. May be null if segment was not created by compaction task.</td></tr>
 </tbody>
 </table>
 <p>For example to retrieve all segments for datasource &quot;wikipedia&quot;, use the query:</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.segments <span class="hljs-keyword">WHERE</span> datasource = <span class="hljs-string">'wikipedia'</span>
 </code></pre>
 <p>Another example to retrieve segments total_size, avg_size, avg_num_rows and num_segments per datasource:</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
     datasource,
     <span class="hljs-keyword">SUM</span>(<span class="hljs-string">"size"</span>) <span class="hljs-keyword">AS</span> total_size,
     <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">SUM</span>(<span class="hljs-string">"size"</span>) = <span class="hljs-number">0</span> <span class="hljs-keyword">THEN</span> <span class="hljs-number">0</span> <span class="hljs-keyword">ELSE</span> <span class="hljs-keyword">SUM</span>(<span class="hljs-string">"size"</span>) / (<span class="hljs-keyword">COUNT</span>(*) FILTER(<span class="hljs-keyword">WHERE</span> <span class="hljs-string">"size"</span> &gt; <span class="hljs-number">0</span>)) <span class="hljs-keyword">END</span> <span class="hljs-keyword">AS</span> avg_size,
     <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">SUM</span>(num_rows) = <span class="hljs-number">0</span> <span class="hljs-keyword">THEN</span> <span class="hljs-number">0</span> <span class="hljs-keyword">ELSE</span> <span class="hljs-keyword">SUM</span>(<span class="hljs-string">"num_rows"</span>) / (<span class="hljs-keyword">COUNT</span>(*) FILTER(<span class="hljs-keyword">WHERE</span> num_rows &gt; <span class="hljs-number">0</span>)) <span class="hljs-keyword">END</span> <span class="hljs-keyword">AS</span> avg_num_rows,
     <span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">AS</span> num_segments
 <span class="hljs-keyword">FROM</span> sys.segments
 <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-number">1</span>
 <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-number">2</span> <span class="hljs-keyword">DESC</span>
 </code></pre>
 <p>If you want to retrieve segment that was compacted (ANY compaction):</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.segments <span class="hljs-keyword">WHERE</span> last_compaction_state <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>
 </code></pre>
 <p>or if you want to retrieve segment that was compacted only by a particular compaction spec (such as that of the auto compaction):</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.segments <span class="hljs-keyword">WHERE</span> last_compaction_state == <span class="hljs-string">'SELECT * FROM sys.segments where last_compaction_state = '</span>CompactionState{partitionsSpec=DynamicPartitionsSpec{maxRowsPerSegment=<span class="hljs-number">5000000</span>, maxTotalRows=<span class="hljs-number">9223372036854775807</span>}, indexSpec={<span class="hljs-keyword">bitmap</span>={<span class="hljs-keyword">type</span>=roaring, compressRunOnSerialization=<span class="hljs-literal">true</span>}, dimensionCompression=lz4, metricCompression=lz4, longEncoding=longs, segmentLoader=<span class="hljs-literal">null</span>}}<span class="hljs-string">'
 </span></code></pre>
 <p><em>Caveat:</em> Note that a segment can be served by more than one stream ingestion tasks or Historical processes, in that case it would have multiple replicas. These replicas are weakly consistent with each other when served by multiple ingestion tasks, until a segment is eventually served by a Historical, at that point the segment is immutable. Broker prefers to query a segment from Historical over an ingestion task. But if a segment has multiple realtime replicas, for e.g.. Kafka index tasks, and one task is slower than other, then the sys.segments query results can vary for the duration of the tasks because only one of the ingestion tasks is queried by the Broker and it is not guaranteed that the same task gets picked every time. The <code>num_rows</code> column of segments table can have inconsistent values during this period. There is an open <a href="https://github.com/apache/druid/issues/5915">issue</a> about this inconsistency with stream ingestion tasks.</p>
 <h4><a class="anchor" aria-hidden="true" id="servers-table"></a><a href="#servers-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SERVERS table</h4>
 <p>Servers table lists all discovered servers in the cluster.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Type</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>server</td><td>STRING</td><td>Server name in the form host:port</td></tr>
 <tr><td>host</td><td>STRING</td><td>Hostname of the server</td></tr>
 <tr><td>plaintext_port</td><td>LONG</td><td>Unsecured port of the server, or -1 if plaintext traffic is disabled</td></tr>
 <tr><td>tls_port</td><td>LONG</td><td>TLS port of the server, or -1 if TLS is disabled</td></tr>
 <tr><td>server_type</td><td>STRING</td><td>Type of Druid service. Possible values include: COORDINATOR, OVERLORD,  BROKER, ROUTER, HISTORICAL, MIDDLE_MANAGER or PEON.</td></tr>
 <tr><td>tier</td><td>STRING</td><td>Distribution tier see <a href="../configuration/index.html#historical-general-configuration">druid.server.tier</a>. Only valid for HISTORICAL type, for other types it's null</td></tr>
 <tr><td>current_size</td><td>LONG</td><td>Current size of segments in bytes on this server. Only valid for HISTORICAL type, for other types it's 0</td></tr>
 <tr><td>max_size</td><td>LONG</td><td>Max size in bytes this server recommends to assign to segments see <a href="../configuration/index.html#historical-general-configuration">druid.server.maxSize</a>. Only valid for HISTORICAL type, for other types it's 0</td></tr>
 </tbody>
 </table>
 <p>To retrieve information about all servers, use the query:</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.servers;
 </code></pre>
 <h4><a class="anchor" aria-hidden="true" id="server_segments-table"></a><a href="#server_segments-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SERVER_SEGMENTS table</h4>
 <p>SERVER_SEGMENTS is used to join servers with segments table</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Type</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>server</td><td>STRING</td><td>Server name in format host:port (Primary key of <a href="#servers-table">servers table</a>)</td></tr>
 <tr><td>segment_id</td><td>STRING</td><td>Segment identifier (Primary key of <a href="#segments-table">segments table</a>)</td></tr>
 </tbody>
 </table>
 <p>JOIN between &quot;servers&quot; and &quot;segments&quot; can be used to query the number of segments for a specific datasource,
 grouped by server, example query:</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">count</span>(segments.segment_id) <span class="hljs-keyword">as</span> num_segments <span class="hljs-keyword">from</span> sys.segments <span class="hljs-keyword">as</span> segments
 <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> sys.server_segments <span class="hljs-keyword">as</span> server_segments
 <span class="hljs-keyword">ON</span> segments.segment_id  = server_segments.segment_id
 <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> sys.servers <span class="hljs-keyword">as</span> servers
 <span class="hljs-keyword">ON</span> servers.server = server_segments.server
 <span class="hljs-keyword">WHERE</span> segments.datasource = <span class="hljs-string">'wikipedia'</span>
 <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> servers.server;
 </code></pre>
 <h4><a class="anchor" aria-hidden="true" id="tasks-table"></a><a href="#tasks-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>TASKS table</h4>
 <p>The tasks table provides information about active and recently-completed indexing tasks. For more information
 check out the documentation for <a href="../ingestion/tasks.html">ingestion tasks</a>.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Type</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>task_id</td><td>STRING</td><td>Unique task identifier</td></tr>
 <tr><td>group_id</td><td>STRING</td><td>Task group ID for this task, the value depends on the task <code>type</code>. For example, for native index tasks, it's same as <code>task_id</code>, for sub tasks, this value is the parent task's ID</td></tr>
 <tr><td>type</td><td>STRING</td><td>Task type, for example this value is &quot;index&quot; for indexing tasks. See <a href="../ingestion/tasks.html">tasks-overview</a></td></tr>
 <tr><td>datasource</td><td>STRING</td><td>Datasource name being indexed</td></tr>
 <tr><td>created_time</td><td>STRING</td><td>Timestamp in ISO8601 format corresponding to when the ingestion task was created. Note that this value is populated for completed and waiting tasks. For running and pending tasks this value is set to 1970-01-01T00:00:00Z</td></tr>
 <tr><td>queue_insertion_time</td><td>STRING</td><td>Timestamp in ISO8601 format corresponding to when this task was added to the queue on the Overlord</td></tr>
 <tr><td>status</td><td>STRING</td><td>Status of a task can be RUNNING, FAILED, SUCCESS</td></tr>
 <tr><td>runner_status</td><td>STRING</td><td>Runner status of a completed task would be NONE, for in-progress tasks this can be RUNNING, WAITING, PENDING</td></tr>
 <tr><td>duration</td><td>LONG</td><td>Time it took to finish the task in milliseconds, this value is present only for completed tasks</td></tr>
 <tr><td>location</td><td>STRING</td><td>Server name where this task is running in the format host:port, this information is present only for RUNNING tasks</td></tr>
 <tr><td>host</td><td>STRING</td><td>Hostname of the server where task is running</td></tr>
 <tr><td>plaintext_port</td><td>LONG</td><td>Unsecured port of the server, or -1 if plaintext traffic is disabled</td></tr>
 <tr><td>tls_port</td><td>LONG</td><td>TLS port of the server, or -1 if TLS is disabled</td></tr>
 <tr><td>error_msg</td><td>STRING</td><td>Detailed error message in case of FAILED tasks</td></tr>
 </tbody>
 </table>
 <p>For example, to retrieve tasks information filtered by status, use the query</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.tasks <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">status</span>=<span class="hljs-string">'FAILED'</span>;
 </code></pre>
 <h4><a class="anchor" aria-hidden="true" id="supervisors-table"></a><a href="#supervisors-table" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SUPERVISORS table</h4>
 <p>The supervisors table provides information about supervisors.</p>
 <table>
 <thead>
 <tr><th>Column</th><th>Type</th><th>Notes</th></tr>
 </thead>
 <tbody>
 <tr><td>supervisor_id</td><td>STRING</td><td>Supervisor task identifier</td></tr>
 <tr><td>state</td><td>STRING</td><td>Basic state of the supervisor. Available states: <code>UNHEALTHY_SUPERVISOR</code>, <code>UNHEALTHY_TASKS</code>, <code>PENDING</code>, <code>RUNNING</code>, <code>SUSPENDED</code>, <code>STOPPING</code>. Check <a href="../development/extensions-core/kafka-ingestion.html#operations">Kafka Docs</a> for details.</td></tr>
 <tr><td>detailed_state</td><td>STRING</td><td>Supervisor specific state. (See documentation of the specific supervisor for details, e.g. <a href="../development/extensions-core/kafka-ingestion.html">Kafka</a> or <a href="../development/extensions-core/kinesis-ingestion.html">Kinesis</a>)</td></tr>
 <tr><td>healthy</td><td>LONG</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 indicates a healthy supervisor</td></tr>
 <tr><td>type</td><td>STRING</td><td>Type of supervisor, e.g. <code>kafka</code>, <code>kinesis</code> or <code>materialized_view</code></td></tr>
 <tr><td>source</td><td>STRING</td><td>Source of the supervisor, e.g. Kafka topic or Kinesis stream</td></tr>
 <tr><td>suspended</td><td>LONG</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 indicates supervisor is in suspended state</td></tr>
 <tr><td>spec</td><td>STRING</td><td>JSON-serialized supervisor spec</td></tr>
 </tbody>
 </table>
 <p>For example, to retrieve supervisor tasks information filtered by health status, use the query</p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.supervisors <span class="hljs-keyword">WHERE</span> healthy=<span class="hljs-number">0</span>;
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="server-configuration"></a><a href="#server-configuration" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Server configuration</h2>
 <p>Druid SQL planning occurs on the Broker and is configured by
 <a href="../configuration/index.html#sql">Broker runtime properties</a>.</p>
 <h2><a class="anchor" aria-hidden="true" id="security"></a><a href="#security" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Security</h2>
 <p>Please see <a href="../development/extensions-core/druid-basic-security.html#sql-permissions">Defining SQL permissions</a> in the
 basic security documentation for information on what permissions are needed for making SQL queries.</p>
 </span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/0.20.0/ingestion/faq.html"><span class="arrow-prev">← </span><span>Troubleshooting FAQ</span></a><a class="docs-next button" href="/docs/0.20.0/querying/querying.html"><span>Native queries</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#query-syntax">Query syntax</a><ul class="toc-headings"><li><a href="#from">FROM</a></li><li><a href="#where">WHERE</a></li><li><a href="#group-by">GROUP BY</a></li><li><a href="#having">HAVING</a></li><li><a href="#order-by">ORDER BY</a></li><li><a href="#limit">LIMIT</a></li><li><a href="#offset">OFFSET</a></li><li><a href="#union-all">UNION ALL</a></li><li><a href="#explain-plan">EXPLAIN PLAN</a></li><li><a href="#identifiers-and-literals">Identifiers and literals</a></li><li><a href="#dynamic-parameters">Dynamic parameters</a></li></ul></li><li><a href="#data-types">Data types</a><ul class="toc-headings"><li><a href="#standard-types">Standard types</a></li><li><a href="#multi-value-strings">Multi-value strings</a></li><li><a href="#null-values">NULL values</a></li></ul></li><li><a href="#aggregation-functions">Aggregation functions</a></li><li><a href="#scalar-functions">Scalar functions</a><ul class="toc-headings"><li><a href="#numeric-functions">Numeric functions</a></li><li><a href="#string-functions">String functions</a></li><li><a href="#time-functions">Time functions</a></li><li><a href="#reduction-functions">Reduction functions</a></li><li><a href="#ip-address-functions">IP address functions</a></li><li><a href="#comparison-operators">Comparison operators</a></li><li><a href="#sketch-functions">Sketch functions</a></li><li><a href="#other-scalar-functions">Other scalar functions</a></li></ul></li><li><a href="#multi-value-string-functions">Multi-value string functions</a></li><li><a href="#query-translation">Query translation</a><ul class="toc-headings"><li><a href="#best-practices">Best practices</a></li><li><a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a></li><li><a href="#query-types">Query types</a></li><li><a href="#time-filters">Time filters</a></li><li><a href="#joins">Joins</a></li><li><a href="#subqueries">Subqueries</a></li><li><a href="#approximations">Approximations</a></li><li><a href="#unsupported-features">Unsupported features</a></li></ul></li><li><a href="#client-apis">Client APIs</a><ul class="toc-headings"><li><a href="#http-post">HTTP POST</a></li><li><a href="#jdbc">JDBC</a></li><li><a href="#dynamic-parameters-1">Dynamic Parameters</a></li><li><a href="#connection-context">Connection context</a></li></ul></li><li><a href="#metadata-tables">Metadata tables</a><ul class="toc-headings"><li><a href="#information-schema">INFORMATION SCHEMA</a></li><li><a href="#system-schema">SYSTEM SCHEMA</a></li></ul></li><li><a href="#server-configuration">Server configuration</a></li><li><a href="#security">Security</a></li></ul></nav></div><footer class="nav-footer druid-footer" id="footer"><div class="container"><div class="text-center"><p><a href="/technology">Technology</a> · <a href="/use-cases">Use Cases</a> · <a href="/druid-powered">Powered by Druid</a> · <a href="/docs/0.20.0/latest">Docs</a> · <a href="/community/">Community</a> · <a href="/downloads.html">Download</a> · <a href="/faq">FAQ</a></p></div><div class="text-center"><a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a> · <a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a> · <a title="Download via Apache" href="https://www.apache.org/dyn/closer.cgi?path=/incubator/druid/{{ site.druid_versions[0].versions[0].version }}/apache-druid-{{ site.druid_versions[0].versions[0].version }}-bin.tar.gz" target="_blank"><span class="fas fa-feather"></span></a> · <a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a></div><div class="text-center license">Copyright © 2019 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br/>Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br/>Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></footer></div><script type="text/javascript" src="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.js"></script><script>
                 document.addEventListener('keyup', function(e) {
                   if (e.target !== document.body) {
                     return;
                   }
                   // keyCode for '/' (slash)
                   if (e.keyCode === 191) {
                     const search = document.getElementById('search_input_react');
                     search && search.focus();
                   }
                 });
               </script><script>
               var search = docsearch({

                 apiKey: '2de99082a9f38e49dfaa059bbe4c901d',
                 indexName: 'apache_druid',
                 inputSelector: '#search_input_react',
                 algoliaOptions: {"facetFilters":["language:en","version:0.20.0"]}
               });
             </script></body></html>