You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "techdocsmith (via GitHub)" <gi...@apache.org> on 2024/07/08 22:04:20 UTC

[PR] [Docs] Adds a migration guide for two-value to three-value logic null handling (druid)

techdocsmith opened a new pull request, #16704:
URL: https://github.com/apache/druid/pull/16704

   Adds a migration guide for two-value to three-value logic null handling
   
   ### Description
   
   Adds a migration guide to help Druid operators migrate to Druid's current ANSI sql-complaint null handling mode.
   
   This PR has:
   
   - [ ] been self-reviewed.
   - [ ] been tested in a test Druid cluster.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "clintropolis (via GitHub)" <gi...@apache.org>.
clintropolis commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1681848918


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.
+
+At query time, Druid treats data from segments written with the legacy two-value logic as follows:

Review Comment:
   given that we discuss the legacy mode right after this, i wonder if it is needed since these seem like obvious statements.



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.
+
+At query time, Druid treats data from segments written with the legacy two-value logic as follows:
+- Empty strings, `''` are non-null values.
+- 0 is a non-null value.
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which used default values instead of nulls.

Review Comment:
   i wonder if in this section we should mention that people who are using this mode are the target audience of this migration guide (or put that somewhere)



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.
+
+At query time, Druid treats data from segments written with the legacy two-value logic as follows:
+- Empty strings, `''` are non-null values.
+- 0 is a non-null value.
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which used default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null. Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows. Gherefore Druid stores 0 instead of null.
+
+In legacy mode, numeric columns do not have a null value bitmap, and so can have slightly decreased segment sizes.
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+Note that these configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you can emulate the behavior in the following ways:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to handle the new behavior at query time.
+    This means you preserve the incoming data with nulls in tact.

Review Comment:
   ```suggestion
       This means you preserve the incoming data with nulls intact.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.
+
+At query time, Druid treats data from segments written with the legacy two-value logic as follows:
+- Empty strings, `''` are non-null values.
+- 0 is a non-null value.
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which used default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null. Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows. Gherefore Druid stores 0 instead of null.
+
+In legacy mode, numeric columns do not have a null value bitmap, and so can have slightly decreased segment sizes.
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+Note that these configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you can emulate the behavior in the following ways:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to handle the new behavior at query time.
+    This means you preserve the incoming data with nulls in tact.
+    However, you must rewrite any affected client-side queries.
+    If you may want to convert to SQL-compliant behavior in the future, or if you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batcn">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Handle null values at query time
+
+If you want to maintain null values in your data within Druid, you can emulate the legacy null handling mode mode as follows:
+
+- Modify inequality queries to include null values.
+  `x <> 'some value'` becomes `(x <> 'some value' OR x IS NULL)`.
+- Use COALESCE to replace nulls with a value.
+  `x + 1` becomes ` COALESCE(numeric_value, 0)+1`
+
+Consider the following Druid datasource `null_example`:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `null`| null |
+
+Druid excludes null strings from equality comparisons. For example:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE "string_example"<> 'my_string'
+```
+
+Druid returns 1 because null is considered unknown: neither equal nor inequal to the value.
+
+To count null values in the result, use an OR operator:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE ("string_example"<> 'my_string') OR "string_example" IS NULL
+```
+
+Druid returns 2.
+
+Similarly, arithmatec operators on null return null. For example:
+
+```sql
+SELECT "number_example" + 1 AS additon_example
+FROM "null_example"
+```
+
+Druid returns the following because, according to the SQL standard, null + any value is null:
+
+| `addition_example`|
+| -- |
+| 100 |
+| 1 |
+| null |
+
+Use NVL to avoid nulls with arithmetic. For example:
+
+```sql
+SELECT NVL("number_example",0) + 1 AS additon_example
+FROM"null_example"

Review Comment:
   ```suggestion
   FROM "null_example"
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.

Review Comment:
   this example isn't quite correct these days because the `(x + y)` plans to a separate virtual column and uses native not equals filter which only does 3vl if `druid.generic.useThreeValueLogicForNativeFilters=true`. If you change it to something like `x <> y` then it plans into a pure expression filter.
   
   That said, if the original example was a native query written with a pure expression filter this would be true, but when using SQL we only plan into pure expression filters when there are no other options. Direct column comparisons are one of those things that tend to be expression filters, there are some other cases too but I don't remember too well off the top of my head.



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.
+
+At query time, Druid treats data from segments written with the legacy two-value logic as follows:
+- Empty strings, `''` are non-null values.
+- 0 is a non-null value.
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which used default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null. Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows. Gherefore Druid stores 0 instead of null.
+
+In legacy mode, numeric columns do not have a null value bitmap, and so can have slightly decreased segment sizes.
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+Note that these configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you can emulate the behavior in the following ways:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to handle the new behavior at query time.
+    This means you preserve the incoming data with nulls in tact.
+    However, you must rewrite any affected client-side queries.
+    If you may want to convert to SQL-compliant behavior in the future, or if you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batcn">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Handle null values at query time
+
+If you want to maintain null values in your data within Druid, you can emulate the legacy null handling mode mode as follows:
+
+- Modify inequality queries to include null values.
+  `x <> 'some value'` becomes `(x <> 'some value' OR x IS NULL)`.
+- Use COALESCE to replace nulls with a value.
+  `x + 1` becomes ` COALESCE(numeric_value, 0)+1`
+
+Consider the following Druid datasource `null_example`:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `null`| null |
+
+Druid excludes null strings from equality comparisons. For example:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE "string_example"<> 'my_string'
+```
+
+Druid returns 1 because null is considered unknown: neither equal nor inequal to the value.
+
+To count null values in the result, use an OR operator:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE ("string_example"<> 'my_string') OR "string_example" IS NULL

Review Comment:
   you can also use `WHERE "string_example" IS DISTINCT FROM 'my_string'` though it doesn't really save much



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683713056


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
+According to this three-valued logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:

Review Comment:
   ```suggestion
   The default Druid configurations for 28.0.0 and later that enable ANSI SQL compatible null handling mode are the following:
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian merged PR #16704:
URL: https://github.com/apache/druid/pull/16704


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683712804


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.

Review Comment:
   ```suggestion
   As of Druid 28.0.0, Druid writes segments in an ANSI SQL compatible null handling mode by default.
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683275602


##########
docs/release-info/migration-guide.md:
##########
@@ -30,12 +30,18 @@ The guides in this section outline breaking changes introduced in Druid 25.0.0 a
 
 ## Migrate from multi-value dimensions to arrays
 
-Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](migr-mvd-array.md).
+Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](./migr-mvd-array.md).
 
 ## Migrate to front-coded dictionary encoding
 
-Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](migr-front-coded-dict.md) for more information.
+Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](./migr-front-coded-dict.md) for more information.
 
 ## Migrate from `maxSubqueryRows` to `maxSubqueryBytes`
 
-Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](migr-subquery-limit.md) for more information.
+Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](./migr-subquery-limit.md) for more information.
+
+## Migrate to SQL compliant null handling mode
+
+BY default the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md

Review Comment:
   ```suggestion
   This guide provides strategies for Druid operators and users who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md).
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683714455


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
+According to this three-valued logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid created segments with the following characteristics at ingestion time:
+
+- String columns couldn't distinguish an empty string, '', from null.
+    Therefore, Druid treated them both as interchangeable values.
+- Numeric columns couldn't represent null valued rows.
+    Therefore Druid stored 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode were the following:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+After the configurations are removed, Druid will ignore them if they exist in your configuration files and use the default SQL compliant mode.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+- Modify incoming data to either [avoid nulls](#replace-null-values-at-ingestion-time) or [avoid empty strings](#coerce-empty-strings-to-null-at-ingestion-time) to achieve the same query behavior as legacy mode. This means modifying your ingestion SQL and ingestion specs to handle nulls or empty strings.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+
+- Preserve null values and update all of your SQL queries to be ANSI SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.

Review Comment:
   ```suggestion
       However, you must rewrite any affected client-side queries to be ANSI SQL compliant.
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683713783


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
+According to this three-valued logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid created segments with the following characteristics at ingestion time:
+
+- String columns couldn't distinguish an empty string, '', from null.
+    Therefore, Druid treated them both as interchangeable values.
+- Numeric columns couldn't represent null valued rows.
+    Therefore Druid stored 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode were the following:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+After the configurations are removed, Druid will ignore them if they exist in your configuration files and use the default SQL compliant mode.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+- Modify incoming data to either [avoid nulls](#replace-null-values-at-ingestion-time) or [avoid empty strings](#coerce-empty-strings-to-null-at-ingestion-time) to achieve the same query behavior as legacy mode. This means modifying your ingestion SQL and ingestion specs to handle nulls or empty strings.

Review Comment:
   ```suggestion
   - Modify incoming data to either [avoid nulls](#replace-null-values-at-ingestion-time) or [avoid empty strings](#coerce-empty-strings-to-null-at-ingestion-time) to achieve the same query behavior as legacy mode. This means modifying your ingestion SQL queries and ingestion specs to handle nulls or empty strings.
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683240186


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 

Review Comment:
   ```suggestion
   The legacy behavior is scheduled to be removed from Druid. We recommend you migrate to the new compliant behavior prior to the removal so that you have time to make and test changes. 
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "clintropolis (via GitHub)" <gi...@apache.org>.
clintropolis commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683562758


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is scheduled to be removed from Druid. 

Review Comment:
   nit: scheduled seems not quite correct since i'm not certain we've fully committed to which version it will be removed in yet



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.

Review Comment:
   i imagine we might make warning logs on startup if we detect the configs that no longer do anything



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683714345


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
+According to this three-valued logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid created segments with the following characteristics at ingestion time:
+
+- String columns couldn't distinguish an empty string, '', from null.
+    Therefore, Druid treated them both as interchangeable values.
+- Numeric columns couldn't represent null valued rows.
+    Therefore Druid stored 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode were the following:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+After the configurations are removed, Druid will ignore them if they exist in your configuration files and use the default SQL compliant mode.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+- Modify incoming data to either [avoid nulls](#replace-null-values-at-ingestion-time) or [avoid empty strings](#coerce-empty-strings-to-null-at-ingestion-time) to achieve the same query behavior as legacy mode. This means modifying your ingestion SQL and ingestion specs to handle nulls or empty strings.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+
+- Preserve null values and update all of your SQL queries to be ANSI SQL compliant.

Review Comment:
   ```suggestion
   - Preserve null values and [update all of your SQL queries to be ANSI SQL compliant](#rewrite-your-queries-to-be-sql-compliant).
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683275353


##########
docs/release-info/migration-guide.md:
##########
@@ -30,12 +30,18 @@ The guides in this section outline breaking changes introduced in Druid 25.0.0 a
 
 ## Migrate from multi-value dimensions to arrays
 
-Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](migr-mvd-array.md).
+Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](./migr-mvd-array.md).
 
 ## Migrate to front-coded dictionary encoding
 
-Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](migr-front-coded-dict.md) for more information.
+Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](./migr-front-coded-dict.md) for more information.
 
 ## Migrate from `maxSubqueryRows` to `maxSubqueryBytes`
 
-Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](migr-subquery-limit.md) for more information.
+Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](./migr-subquery-limit.md) for more information.
+
+## Migrate to SQL compliant null handling mode
+
+BY default the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with the SQL standard.

Review Comment:
   ```suggestion
   By default, the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with ANSI SQL.
   ```



##########
docs/release-info/migration-guide.md:
##########
@@ -30,12 +30,18 @@ The guides in this section outline breaking changes introduced in Druid 25.0.0 a
 
 ## Migrate from multi-value dimensions to arrays
 
-Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](migr-mvd-array.md).
+Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](./migr-mvd-array.md).
 
 ## Migrate to front-coded dictionary encoding
 
-Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](migr-front-coded-dict.md) for more information.
+Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](./migr-front-coded-dict.md) for more information.
 
 ## Migrate from `maxSubqueryRows` to `maxSubqueryBytes`
 
-Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](migr-subquery-limit.md) for more information.
+Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](./migr-subquery-limit.md) for more information.
+
+## Migrate to SQL compliant null handling mode
+
+BY default the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md

Review Comment:
   ```suggestion
   This guide provides strategies for Druid operators and users who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683616782


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is scheduled to be removed from Druid. 

Review Comment:
   ```suggestion
   Legacy mode is planned for removal from Druid. 
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1681910026


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,266 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard:
+
+The SQL standard defines any comparison to null to be unknown.
+Therefore, according to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+Now, Druid stores segments in a SQL compatible null handling mode by default.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Note Druid has always applied three-value logic by default to expressions.
+Therefore, queries such as `(x+y) <> ‘some value’` exclude null values even prior to Druid 28.0.0.

Review Comment:
   I removed this. It seems like it is not necessary for this topic.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683275602


##########
docs/release-info/migration-guide.md:
##########
@@ -30,12 +30,18 @@ The guides in this section outline breaking changes introduced in Druid 25.0.0 a
 
 ## Migrate from multi-value dimensions to arrays
 
-Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](migr-mvd-array.md).
+Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](./migr-mvd-array.md).
 
 ## Migrate to front-coded dictionary encoding
 
-Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](migr-front-coded-dict.md) for more information.
+Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](./migr-front-coded-dict.md) for more information.
 
 ## Migrate from `maxSubqueryRows` to `maxSubqueryBytes`
 
-Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](migr-subquery-limit.md) for more information.
+Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](./migr-subquery-limit.md) for more information.
+
+## Migrate to SQL compliant null handling mode
+
+BY default the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md

Review Comment:
   ```suggestion
   This guide provides strategies for Druid operators and users who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md).
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "clintropolis (via GitHub)" <gi...@apache.org>.
clintropolis commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1681936535


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,272 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications.

Review Comment:
   ```suggestion
   This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
   ```
   Also I wonder if we should fit in the "why" which is because legacy mode is deprecated and eventually will be removed



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,272 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications.
+It provides strategies to emulate legacy null handling mode while operating Druid in SQL compliant null handling mode.
+
+## SQL compliant null handling in Druid
+
+Now, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+Note that these configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you can emulate the null handling behavior while operating Druid in SQL compatible null handling mode.

Review Comment:
   While ingest time transformations to eliminate nulls are somewhat emulating the behavior, I feel like updating your queries is more just "transitioning your queries into standard SQL" to adapt to the change.
   
   Also we might need to be kind of careful with the word "emulate" since that isn't totally true. In legacy mode for strings, the empty value and null are used interchangably. This means like where clauses such as `WHERE x is null` would match the empty string in legacy mode, but this will not be true in SQL compatible mode, as the empty string is strictly the empty string value. I'm not entirely sure of a better word, but we might want to mention this quirk of legacy mode.
   
   I think that is maybe why I suggested when we had a chat offline that it is also possible to coerce the empty strings into `NULL` using the `NULLIF` function; if someone is more used to querying those values and treating them as `NULL` instead of the empty string, it might make migration easier if the input data has empty strings, or a mixture of null and empty strings.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683243097


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid

Review Comment:
   Do we needto say `in Druid`? 



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.

Review Comment:
   Saying just operators makes me think ppl who administer druid
   
   ```suggestion
   This guide provides strategies for Druid operators and users who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.

Review Comment:
   ```suggestion
   * Preserve null values and update all of your SQL queries to be ANSI SQL compliant.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.
+If your queries rely on empty strings to represent nulls, you can coerce empty strings to null at ingestion time using NULLIF.
+
+For example, consider the following sample input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string"}
+{"time":"2024-01-02T00:00:00.000Z","string_example":""}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null}
+```
+
+In legacy mode, Druid would write an empty string for the third record.
+Therefore the following query would return 2:

Review Comment:
   ```suggestion
   Therefore the following query returned 2:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:

Review Comment:
   ```suggestion
   In legacy mode, Druid segments created at ingestion time had the following characteristics:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.

Review Comment:
   ```suggestion
   According to this three-valued logic, `x <> 'some value'` only returns non-null values.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:

Review Comment:
   ```suggestion
   The Druid configurations for the deprecated legacy mode were the following:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.

Review Comment:
   ```suggestion
   In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 

Review Comment:
   ```suggestion
   The legacy behavior is scheduled to be removed from Druid. We recommend you migrate to the new compliant behavior prior to the removal so that you time to make and test changes. 
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.

Review Comment:
   The rest of this block should be past tense too imo since we're describing legacy behavior 



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:

Review Comment:
   ```suggestion
   The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 

Review Comment:
   ```suggestion
   - String columns couldn't distinguish an empty string, '', from null.
       Therefore, Druid treated them both as interchangeable values.
   - Numeric columns couldn't represent null valued rows.
       Therefore Druid stored 0 instead of null. 
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.

Review Comment:
   ```suggestion
   This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.

Review Comment:
   Do they have to actively remove these configs or if they leave them will they be ignored? IIRC, they'll be ignored, but we should be explicit on whether or not they need to actively do anything or not. e.g.
   
   ```
   If these are in your configuration, they'll be ignored once the legacy mode is removed.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.

Review Comment:
   ```suggestion
       This method modifies incoming data.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time

Review Comment:
   Maybe link to the corresponding anchor from each bullet point.



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.

Review Comment:
   Maybe lead with the impact:
   
   ```suggestion
   * Modify all incoming data to change nulls but preserve the same behavior as legacy mode. Update your ingestion SQL queries and ingestion specs to handle nulls at ingestion time.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.
+If your queries rely on empty strings to represent nulls, you can coerce empty strings to null at ingestion time using NULLIF.
+
+For example, consider the following sample input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string"}
+{"time":"2024-01-02T00:00:00.000Z","string_example":""}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null}
+```
+
+In legacy mode, Druid would write an empty string for the third record.

Review Comment:
   ```suggestion
   In legacy mode, Druid wrote an empty string for the third record.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.

Review Comment:
   IMO put an empty line between the two options to more visually separate them



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.
+If your queries rely on empty strings to represent nulls, you can coerce empty strings to null at ingestion time using NULLIF.
+
+For example, consider the following sample input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string"}
+{"time":"2024-01-02T00:00:00.000Z","string_example":""}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null}
+```
+
+In legacy mode, Druid would write an empty string for the third record.
+Therefore the following query would return 2:
+
+```sql
+SELECT count(*)
+FROM "null_string"
+WHERE "string_example" IS NULL
+```
+
+In SQL compliant mode, Druid differentiates between empty strings and nulls, so the same query would return 1.
+The following example shows how to coerce empty strings into null to accommodate IS NULL comparisons:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batcn">
+
+```sql
+REPLACE INTO "null_string" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  NULLIF("string_example",'') AS "string_example"
+FROM "ext"
+PARTITIONED BY MONTH
+```
+
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "null_string",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "expression": "case_searched((\"string_example\" == ''),null,\"string_example\")",
+            "name": "string_example"
+          }
+        ]
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example"
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "month"
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no empty strings as follows:
+
+| `__time` | `string_examle` |
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`|
+| `2024-01-02T00:00:00.000Z`| `null`|
+| `2024-01-03T00:00:00.000Z`| `null`|
+
+Therefore `SELECT count(*) FROM "null_string" WHERE "string_example" IS NULL` returns 2.
+
+### Rewrite your queries to be SQL compliant
+
+If you want to maintain null values in your data within Druid, you can use the following SQL compliant querying strategies to achieve the same results as legacy null handling:
+
+- Modify inequality queries to include null values.
+  For example, `x <> 'some value'` becomes `(x <> 'some value' OR x IS NULL)`.
+- Use COALESCE or NVL to replace nulls with a value.
+  For example, `x + 1` becomes `NVL(numeric_value, 0)+1`
+
+Consider the following Druid datasource `null_example`:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `null`| null |
+
+Druid excludes null strings from equality comparisons. For example:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE "string_example"<> 'my_string'
+```
+
+Druid returns 1 because null is considered unknown: neither equal nor unequal to the value.
+
+To count null values in the result, you can use an OR operator:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE ("string_example"<> 'my_string') OR "string_example" IS NULL
+```
+
+Druid returns 2.
+To achieve the same result, you could use IS DISTINCT FROM for null-safe comparison:

Review Comment:
   ```suggestion
   To achieve the same result, you can use IS DISTINCT FROM for null-safe comparison:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.
+If your queries rely on empty strings to represent nulls, you can coerce empty strings to null at ingestion time using NULLIF.
+
+For example, consider the following sample input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string"}
+{"time":"2024-01-02T00:00:00.000Z","string_example":""}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null}
+```
+
+In legacy mode, Druid would write an empty string for the third record.
+Therefore the following query would return 2:
+
+```sql
+SELECT count(*)
+FROM "null_string"
+WHERE "string_example" IS NULL
+```
+
+In SQL compliant mode, Druid differentiates between empty strings and nulls, so the same query would return 1.
+The following example shows how to coerce empty strings into null to accommodate IS NULL comparisons:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batcn">
+
+```sql
+REPLACE INTO "null_string" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  NULLIF("string_example",'') AS "string_example"
+FROM "ext"
+PARTITIONED BY MONTH
+```
+
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "null_string",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "expression": "case_searched((\"string_example\" == ''),null,\"string_example\")",
+            "name": "string_example"
+          }
+        ]
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example"
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "month"
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no empty strings as follows:
+
+| `__time` | `string_examle` |
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`|
+| `2024-01-02T00:00:00.000Z`| `null`|
+| `2024-01-03T00:00:00.000Z`| `null`|
+
+Therefore `SELECT count(*) FROM "null_string" WHERE "string_example" IS NULL` returns 2.
+
+### Rewrite your queries to be SQL compliant
+
+If you want to maintain null values in your data within Druid, you can use the following SQL compliant querying strategies to achieve the same results as legacy null handling:

Review Comment:
   ```suggestion
   If you want to maintain null values in your data within Druid, you can use the following ANSI SQL compliant querying strategies to achieve the same results as legacy null handling:
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.

Review Comment:
   ```suggestion
   In legacy mode, Druid recognized empty strings as nulls for equality comparison.
   ```



##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,385 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy is scheduled to be removed from Druid. 
+
+## SQL compliant null handling in Druid
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because SQL the standard defines any comparison to null to be unknown.
+According to this three-value logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for SQL compatible null handling mode is as follows:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid segments created at ingestion time have the following characteristics:
+
+- String columns can not distinguish an empty string, '', from null.
+    Therefore, Druid treats them both as interchangeable values.
+- Numeric columns can not represent null valued rows.
+    Therefore Druid stores 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode are as follows:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:
+
+* Modify your ingestion SQL and ingestion specs to handle nulls at ingestion time.
+    This means that you are modifying incoming data.
+    For example, replacing a null for a string column with an empty string or a 0 for a numeric column.
+    However, it means that your existing queries should operate as if Druid were in legacy mode.
+    If you do not care about preserving null values, this is a good option for you.
+* Update your SQL queries to be SQL compliant.
+    This means you can preserve the incoming data with nulls intact.
+    However, you must rewrite any affected client-side queries to be SQL compliant.
+    If you have a requirement to preserve null values, choose this option.
+
+### Replace null values at ingestion time
+
+If you don't need to preserve null values within Druid, you can use a transform at ingestion time to replace nulls with other values.
+
+Consider the following input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string","number_example":99}
+{"time":"2024-01-02T00:00:00.000Z","string_example":"","number_example":0}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null,"number_example":null}
+```
+ 
+The following example illustrates how to use COALESCE and NVL at ingestion time to avoid null values in Druid:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batch">
+
+```sql
+REPLACE INTO "no_nulls_example" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR, "number_example" BIGINT)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  -- Replace any null string values with an empty string
+  COALESCE("string_example",'') AS string_example,
+  -- Replace any null numeric values with 0
+  NVL("number_example",0) AS number_example
+FROM "ext"
+PARTITIONED BY MONTH
+```
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\",\"number_example\":99}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\",\"number_example\":0}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null,\"number_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "inline_data_native",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example",
+          {
+            "type": "long",
+            "name": "number_example"
+          }
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "MONTH"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "name": "string_example",
+            "expression": "COALESCE(\"string_example\",'')"
+          },
+          {
+            "type": "expression",
+            "name": "number_example",
+            "expression": "NVL(\"number_example\",0)"
+          }
+        ]
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no null values as follows:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `empty`| 0 |
+
+### Coerce empty strings to null at ingestion time
+
+In legacy mode, Druid recognizes empty strings as nulls for equality comparison.
+If your queries rely on empty strings to represent nulls, you can coerce empty strings to null at ingestion time using NULLIF.
+
+For example, consider the following sample input data:
+
+```json
+{"time":"2024-01-01T00:00:00.000Z","string_example":"my_string"}
+{"time":"2024-01-02T00:00:00.000Z","string_example":""}
+{"time":"2024-01-03T00:00:00.000Z","string_example":null}
+```
+
+In legacy mode, Druid would write an empty string for the third record.
+Therefore the following query would return 2:
+
+```sql
+SELECT count(*)
+FROM "null_string"
+WHERE "string_example" IS NULL
+```
+
+In SQL compliant mode, Druid differentiates between empty strings and nulls, so the same query would return 1.
+The following example shows how to coerce empty strings into null to accommodate IS NULL comparisons:
+
+<Tabs>
+
+<TabItem value="0" label="SQL-based batcn">
+
+```sql
+REPLACE INTO "null_string" OVERWRITE ALL
+WITH "ext" AS (
+  SELECT *
+  FROM TABLE(
+    EXTERN(
+      '{"type":"inline","data":"{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"}',
+      '{"type":"json"}'
+    )
+  ) EXTEND ("time" VARCHAR, "string_example" VARCHAR)
+)
+SELECT
+  TIME_PARSE("time") AS "__time",
+  NULLIF("string_example",'') AS "string_example"
+FROM "ext"
+PARTITIONED BY MONTH
+```
+
+</TabItem>
+
+<TabItem value="1" label="JSON-based batch">
+
+```json
+{
+  "type": "index_parallel",
+  "spec": {
+    "ioConfig": {
+      "type": "index_parallel",
+      "inputSource": {
+        "type": "inline",
+        "data": "{\"time\":\"2024-01-01T00:00:00.000Z\",\"string_example\":\"my_string\"}\n{\"time\":\"2024-01-02T00:00:00.000Z\",\"string_example\":\"\"}\n{\"time\":\"2024-01-03T00:00:00.000Z\",\"string_example\":null}"
+      },
+      "inputFormat": {
+        "type": "json"
+      }
+    },
+    "tuningConfig": {
+      "type": "index_parallel",
+      "partitionsSpec": {
+        "type": "dynamic"
+      }
+    },
+    "dataSchema": {
+      "dataSource": "null_string",
+      "timestampSpec": {
+        "column": "time",
+        "format": "iso"
+      },
+      "transformSpec": {
+        "transforms": [
+          {
+            "type": "expression",
+            "expression": "case_searched((\"string_example\" == ''),null,\"string_example\")",
+            "name": "string_example"
+          }
+        ]
+      },
+      "dimensionsSpec": {
+        "dimensions": [
+          "string_example"
+        ]
+      },
+      "granularitySpec": {
+        "queryGranularity": "none",
+        "rollup": false,
+        "segmentGranularity": "month"
+      }
+    }
+  }
+}
+```
+
+</TabItem>
+</Tabs>
+
+Druid ingests the data with no empty strings as follows:
+
+| `__time` | `string_examle` |
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`|
+| `2024-01-02T00:00:00.000Z`| `null`|
+| `2024-01-03T00:00:00.000Z`| `null`|
+
+Therefore `SELECT count(*) FROM "null_string" WHERE "string_example" IS NULL` returns 2.
+
+### Rewrite your queries to be SQL compliant
+
+If you want to maintain null values in your data within Druid, you can use the following SQL compliant querying strategies to achieve the same results as legacy null handling:
+
+- Modify inequality queries to include null values.
+  For example, `x <> 'some value'` becomes `(x <> 'some value' OR x IS NULL)`.
+- Use COALESCE or NVL to replace nulls with a value.
+  For example, `x + 1` becomes `NVL(numeric_value, 0)+1`
+
+Consider the following Druid datasource `null_example`:
+
+| `__time` | `string_examle` | `number_example`|
+| -- | -- | -- |
+| `2024-01-01T00:00:00.000Z`| `my_string`| 99 |
+| `2024-01-02T00:00:00.000Z`| `empty`| 0 |
+| `2024-01-03T00:00:00.000Z`| `null`| null |
+
+Druid excludes null strings from equality comparisons. For example:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE "string_example"<> 'my_string'
+```
+
+Druid returns 1 because null is considered unknown: neither equal nor unequal to the value.
+
+To count null values in the result, you can use an OR operator:
+
+```sql
+SELECT COUNT(*) AS count_example
+FROM "null_example"
+WHERE ("string_example"<> 'my_string') OR "string_example" IS NULL
+```
+
+Druid returns 2.
+To achieve the same result, you could use IS DISTINCT FROM for null-safe comparison:
+
+```sql
+SELECT COUNT(*) as count_example
+FROM "null_example"
+WHERE "string_example" IS DISTINCT FROM 'my_string'
+```
+
+Similarly, arithmetic operators on null return null. For example:
+
+```sql
+SELECT "number_example" + 1 AS additon_example
+FROM "null_example"
+```
+
+Druid returns the following because, according to the SQL standard, null + any value is null:

Review Comment:
   ```suggestion
   Druid returns the following because null + any value is null for the ANSI SQL standard:
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683277310


##########
docs/release-info/migration-guide.md:
##########
@@ -30,12 +30,18 @@ The guides in this section outline breaking changes introduced in Druid 25.0.0 a
 
 ## Migrate from multi-value dimensions to arrays
 
-Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](migr-mvd-array.md).
+Druid now supports SQL-compliant array types. Whenever possible, you should use the array type over multi-value dimensions. See [Migration guide: MVDs to arrays](./migr-mvd-array.md).
 
 ## Migrate to front-coded dictionary encoding
 
-Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](migr-front-coded-dict.md) for more information.
+Druid encodes string columns into dictionaries for better compression. Front-coded dictionary encoding reduces storage and improves performance by optimizing for strings that share similar beginning substrings. See [Migration guide: front-coded dictionaries](./migr-front-coded-dict.md) for more information.
 
 ## Migrate from `maxSubqueryRows` to `maxSubqueryBytes`
 
-Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](migr-subquery-limit.md) for more information.
+Druid allows you to set a byte-based limit on subquery size to prevent Brokers from running out of memory when handling large subqueries. The byte-based subquery limit overrides Druid's row-based subquery limit. We recommend that you move towards using byte-based limits starting in Druid 30.0.0. See [Migration guide: subquery limit](./migr-subquery-limit.md) for more information.
+
+## Migrate to SQL compliant null handling mode
+
+BY default the Druid [null handling](../querying/sql-data-types.md#null-values) mode is now compliant with the SQL standard.
+This guide provides strategies for Druid operators who rely on the legacy Druid null handling behavior in their applications to transition to SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md

Review Comment:
   ```suggestion
   This guide provides strategies for Druid operators and users who rely on the legacy Druid null handling behavior in their applications to transition to ANSI SQL compliant mode.  See [Migration guide: SQL compliant mode](./migr-ansi-sql-null.md
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [PR] [Docs] Adds a migration guide SQL compatible null handling (druid)

Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #16704:
URL: https://github.com/apache/druid/pull/16704#discussion_r1683713230


##########
docs/release-info/migr-ansi-sql-null.md:
##########
@@ -0,0 +1,386 @@
+---
+id: migr-ansi-sql-null
+title: "Migration guide: SQL compliant mode"
+sidebar_label: SQL compliant mode
+---
+
+<!--
+  ~ 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.
+-->
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In Apache Druid 28.0.0, the default [null handling](../querying/sql-data-types.md#null-values) mode changed to be compliant with the ANSI SQL standard.
+This guide provides strategies for Druid operators who rely on legacy Druid null handling behavior in their applications to transition to SQL compliant mode.
+Legacy mode is planned for removal from Druid. 
+
+## SQL compliant null handling
+
+As of Druid 28.0.0, Druid writes segments in a SQL compatible null handling mode by default.
+This means that Druid stores null values distinctly from empty strings for string dimensions and distinctly from 0 for numeric dimensions.
+
+This can impact your application behavior because the ANSI SQL standard defines any comparison to null to be unknown.
+According to this three-valued logic, `x <> 'some value'` only returns non-null values.
+
+The default Druid configurations for 28.0.0 and later that enable SQL compatible null handling mode are the following:
+
+* `druid.generic.useDefaultValueForNull=false`
+* `druid.expressions.useStrictBooleans=true`
+* `druid.generic.useThreeValueLogicForNativeFilters=true` 
+
+Follow the [Null handling tutorial](../tutorials/tutorial-sql-null.md) to learn how the default null handling works in Druid.
+
+## Legacy null handling and two-value logic
+
+Prior to Druid 28.0.0, Druid defaulted to a legacy mode which stored default values instead of nulls.
+In legacy mode, Druid created segments with the following characteristics at ingestion time:
+
+- String columns couldn't distinguish an empty string, '', from null.
+    Therefore, Druid treated them both as interchangeable values.
+- Numeric columns couldn't represent null valued rows.
+    Therefore Druid stored 0 instead of null. 
+
+The Druid configurations for the deprecated legacy mode were the following:
+
+* `druid.generic.useDefaultValueForNull=true`
+* `druid.expressions.useStrictBooleans=false`
+* `druid.generic.useThreeValueLogicForNativeFilters=true`
+
+These configurations are deprecated and scheduled for removal.
+After the configurations are removed, Druid will ignore them if they exist in your configuration files and use the default SQL compliant mode.
+
+## Migrate to SQL compliant mode
+
+If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in SQL compatible null handling mode:

Review Comment:
   ```suggestion
   If your business logic relies on the behavior of legacy mode, you have the following options to operate Druid in an ANSI SQL compatible null handling mode:
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org