clintropolis commented on code in PR #16185: URL: https://github.com/apache/druid/pull/16185#discussion_r1535336126
########## docs/querying/sql-data-types.md: ########## @@ -140,23 +140,38 @@ as regular JSON arrays instead of in stringified form. ## NULL values +By default, Druid treats NULL values closely to the SQL standard. +In the default mode: +- numeric NULL is permitted +- NULL values and empty strings are not equal. + +This manner of null handling applies to both storage and queries. The [`druid.generic.useDefaultValueForNull`](../configuration/index.md#sql-compatible-null-handling) -runtime property controls Druid's NULL handling mode. For the most SQL compliant behavior, set this to `false` (the default). - -When `druid.generic.useDefaultValueForNull = false` (the default), NULLs are treated more closely to the SQL standard. In this mode, -numeric NULL is permitted, and NULLs and empty strings are no longer treated as interchangeable. This property -affects both storage and querying, and must be set on all Druid service types to be available at both ingestion time -and query time. There is some overhead associated with the ability to handle NULLs; see -the [segment internals](../design/segments.md#handling-null-values) documentation for more details. - -When `druid.generic.useDefaultValueForNull = true` (deprecated legacy mode), 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 `col IS NULL` and `col = ''` are equivalent, and both evaluate to true if `col` contains -an empty string. Similarly, the expression `COALESCE(col1, col2)` returns `col2` if `col1` is an empty string. While -the `COUNT(*)` aggregator counts all rows, the `COUNT(expr)` aggregator counts 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 are -treated as zeroes. This was the default prior to Druid 28.0.0, but will be removed in a future release so that Druid -always behaves in an SQL compatible manner. +runtime property controls Druid's NULL handling mode. For the most SQL compliant behavior, maintain the default value of `false`. + +There is some performance impact for null handling. see [segment internals](../design/segments.md#handling-null-values) for more information. +For examples of null handling, see the [null handling tutorial](../tutorials/tutorial-ansi-sql-null.md). Review Comment: link seems wrong, filename is `tutorial-sql-null.md` ########## docs/tutorials/tutorial-sql-null.md: ########## @@ -0,0 +1,193 @@ +--- +id: tutorial-sql-null +title: Null handling tutorial +sidebar_label: Handling null values +description: Introduction to null handling in Druid +--- + +<!-- + ~ 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. + --> + +This tutorial introduces the basic concepts of null handling for string and numeric columns in Apache Druid. +The tutorial focuses on filters using the logical NOT operation on columns with NULL values. + +## Prerequisites + +Before starting this tutorial, download and run Apache Druid on your local machine as described in +the [Local quickstart](index.md). + +The tutorial assumes you are familiar with using the [Query view](./tutorial-sql-query-view.md) to ingest and query data. Review Comment: i wonder if we need to call out that this tutorial expects `druid.generic.useDefaultValueForNull` and `druid.generic.useThreeValueLogicForNativeFilters` to be set to their default values of `false` and `true` respectively or if that would be confusing -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
