bug: only_null for nullable column returns true when column is empty

[hanyisong]

Search before asking

• I had searched in the issues and found no similar issues.

Version

main

What's Wrong?

return error after adding form and join.

ref clickhouse

How to Reproduce?

DROP DATABASE IF EXISTS databend0;
CREATE DATABASE databend0;
USE databend0;
CREATE TABLE t0(c0BOOLEAN BOOL NULL DEFAULT(false));
CREATE TABLE t1(c0VARCHAR VARCHAR NULL, c1BOOLEAN BOOLEAN NULL DEFAULT(false));
INSERT INTO t1(c1boolean, c0varchar) VALUES (true, '0');

MySQL [databend0]> SELECT (false and NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT FROM t1,t0;
ERROR 1105 (HY000): Code: 1010, displayText = Can't cast column from nullable data into non-nullable type (while in processor thread 0).

MySQL [databend0]> SELECT (false and NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT;
+--------------------------------------------------+
| false and null not in(0.1, 0.2, 0.3, 0.4)::int64 |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set (0.003 sec)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[xudong963]

Not related to join, the following case also will return error

SELECT (NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT FROM t1;
ERROR 1105 (HY000): Code: 1010, displayText = Can't cast column from null into non-nullable type (while in processor thread 0).

[hanyisong]

Hi @xudong963
e.g. SELECT (false and NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT; return 0,
SELECT (false and NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT FROM t1,t0; return error,
SELECT (false and NULL NOT IN (0.1, 0.2, 0.3)) ::BIGINT FROM t1,t0; return empty set.

[xudong963]

You can check the explain

mysql> explain SELECT (false and NULL NOT IN (0.1, 0.2, 0.3)) ::BIGINT FROM t1,t0;
+------------------------------------------------------------+
| explain                                                    |
+------------------------------------------------------------+
| EvalScalar                                                 |
| ├── expressions: [0]                                       |
| └── HashJoin                                               |
|     ├── join type: CROSS                                   |
|     ├── build keys: []                                     |
|     ├── probe keys: []                                     |
|     ├── filters: []                                        |
|     ├── TableScan(Build)                                   |
|     │   ├── table: default.default.t0                      |
|     │   ├── read rows: 0                                   |
|     │   ├── read bytes: 0                                  |
|     │   ├── partitions total: 0                            |
|     │   ├── partitions scanned: 0                          |
|     │   └── push downs: [filters: [], limit: NONE]         |
|     └── TableScan(Probe)                                   |
|         ├── table: default.default.t1                      |
|         ├── read rows: 1                                   |
|         ├── read bytes: 30                                 |
|         ├── partitions total: 1                            |
|         ├── partitions scanned: 1                          |
|         └── push downs: [filters: [], limit: NONE]         |
+------------------------------------------------------------+
21 rows in set (0.04 sec)
Read 0 rows, 0.00 B in 0.004 sec., 0 rows/sec., 0.00 B/sec.

mysql> explain SELECT (false and NULL NOT IN (0.1, 0.2, 0.3,0.4)) ::BIGINT FROM t1,t0;
+------------------------------------------------------------------------------------------+
| explain                                                                                  |
+------------------------------------------------------------------------------------------+
| EvalScalar                                                                               |
| ├── expressions: [CAST(and(false, not(in(NULL, (0.1, 0.2, 0.3, 0.4)))) AS BIGINT)]       |
| └── HashJoin                                                                             |
|     ├── join type: CROSS                                                                 |
|     ├── build keys: []                                                                   |
|     ├── probe keys: []                                                                   |
|     ├── filters: []                                                                      |
|     ├── TableScan(Build)                                                                 |
|     │   ├── table: default.default.t0                                                    |
|     │   ├── read rows: 0                                                                 |
|     │   ├── read bytes: 0                                                                |
|     │   ├── partitions total: 0                                                          |
|     │   ├── partitions scanned: 0                                                        |
|     │   └── push downs: [filters: [], limit: NONE]                                       |
|     └── TableScan(Probe)                                                                 |
|         ├── table: default.default.t1                                                    |
|         ├── read rows: 1                                                                 |
|         ├── read bytes: 30                                                               |
|         ├── partitions total: 1                                                          |
|         ├── partitions scanned: 1                                                        |
|         └── push downs: [filters: [], limit: NONE]                                       |
+------------------------------------------------------------------------------------------+
21 rows in set (0.03 sec)
Read 0 rows, 0.00 B in 0.005 sec., 0 rows/sec., 0.00 B/sec.

mysql>

The result depends on how databend processes expressions.