[Bug]: Incompatible result with MySQL

[Dylan0222]

Describe the bug

Environment

• OS Version and CPU Arch(uname -a):

Linux 3.10.0-1160.119.1.el7.x86_64 #1 SMP Tue Jun 4 14:43:51 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

• OB Version(LD_LIBRARY_PATH=../lib:$LD_LIBRARY_PATH ./observer -V):

select version();
-- 5.7.25-OceanBase_CE-v4.3.4.0

Fast Reproduce Steps(Required)

Steps to reproduce the behavior:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
    c0 TIMESTAMP,
    c1 DOUBLE,
    c2 TIMESTAMP AS (c0) VIRTUAL NOT NULL,
    c3 INT UNSIGNED NOT NULL,
    c4 VARCHAR(255),
    c5 JSON,
    PRIMARY KEY (c3),
    UNIQUE INDEX idx1 (c2, c0),
    INDEX idx2 (c4)
) WITH COLUMN GROUP (all columns, each column);

INSERT IGNORE INTO t1(c1, c3, c4, c5) 
VALUES 
    (-10.0, 2900, 'example', '{"key": "value"}'),
    (232.0, 3921, 'test', '{"key": "test"}'),
    (12222.0, 2123, 'demo', '{"key": "demo"}');

Expected behavior

I find that the query in OceanBase is inconsistent with MySQL 8.4.1

// MySQL
MySQL [test]> SELECT c2
    -> FROM t1
    -> WHERE c1 = -10 or JSON_UNQUOTE(JSON_EXTRACT(c5, '$.key')) = 'demo';
+---------------------+
| c2                  |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

Actual Behavior

// OceanBase
MySQL [test]> SELECT /*+ USE_COLUMN_TABLE(t1) */ c2
    -> FROM t1
    -> WHERE c1 = -10 or JSON_UNQUOTE(JSON_EXTRACT(c5, '$.key')) = 'demo';
+----+
| c2 |
+----+
| NULL |
| NULL |
+----+
2 rows in set (0.01 sec)

Additional context

[wpleonardo]

Thank you for reporting this issue and helping us improve OceanBase. We will investigate it promptly.

[Dylan0222]

It appears that the default value handling differs between the two systems. Since column c2 has a NOT NULL constraint, one would theoretically expect it not to return NULL. However, the actual behavior diverges, with MySQL defaulting to 0000-00-00 00:00:00 while OceanBase returns NULL.

[Dylan0222]

It appears that the default value handling differs between the two systems. Since column c2 has a NOT NULL constraint, one would theoretically expect it not to return NULL. However, the actual behavior diverges, with MySQL defaulting to 0000-00-00 00:00:00 while OceanBase returns NULL. @wpleonardo

Excuse me, can you confirm if you’re able to reproduce this issue on your end? Thanks a lot @wpleonardo

[wpleonardo]

Please wait for a while. I'm a little busy today.

[wpleonardo]

Thanks for your patience. We have confirmed it is a bug, and also notified the DEV team.

[Dylan0222]

Thanks for your patience. We have confirmed it is a bug, and also notified the DEV team.

Thanks a lot. Best wishes to you~