External table of es returned `0000-01-01 00:00:00` but the real value is `null`.

[yongbingwang]

case one:

Steps to reproduce the behavior (Required)

1. create external table of es

CREATE EXTERNAL TABLE test_null (
  `id_date` datetime not NULL COMMENT ""
) ENGINE=ELASTICSEARCH 
COMMENT "ELASTICSEARCH"
PROPERTIES (
"hosts" = "172.26.92.141:9200",
"user" = "root",
"password" = "",
"index" = "es_enable_docvalue_scan_is_true",
"type" = "_doc",
"transport" = "http",
"enable_docvalue_scan" = "true",
"max_docvalue_fields" = "20",
"enable_keyword_sniff" = "true"
);

2. select.

select * from test_null;

Expected behavior (Required)

ERROR 1064 (HY000): col `id_date` is not null, but value from ES is null

Real behavior (Required)

mysql> select * from test_null;
+---------------------+
| id_date             |
+---------------------+
| 0000-01-01 00:00:00 |
| 2022-02-21 14:11:15 |
+---------------------+
2 rows in set (0.01 sec)

case two:

"enable_docvalue_scan" = "false"

1. create external table of es

CREATE EXTERNAL TABLE test_null_2 (
  `id_date` datetime not NULL COMMENT ""
) ENGINE=ELASTICSEARCH 
COMMENT "ELASTICSEARCH"
PROPERTIES (
"hosts" = "172.26.92.141:9200",
"user" = "root",
"password" = "",
"index" = "es_enable_docvalue_scan_is_true",
"type" = "_doc",
"transport" = "http",
"enable_docvalue_scan" = "false",
"max_docvalue_fields" = "20",
"enable_keyword_sniff" = "true"
);

2. select * from test_null_2.

mysql> select * from test_null_2;
ERROR 1064 (HY000): col `id_date` is not null, but value from ES is null

StarRocks version (Required)

• You can get the StarRocks version by executing SQL select current_version()
• all

[RowenWoo]

@yongbingwang the original date data in ES is？

[yongbingwang]

@RowenWoo { "_index" : "es_enable_docvalue_scan_is_true", "_type" : "_doc", "_id" : "PiyMJn8BcuY8i5S_q_Is", "_score" : 1.0, "_source" : { "id_date" : null } }, { "_index" : "es_enable_docvalue_scan_is_true", "_type" : "_doc", "_id" : "PyyMJn8BcuY8i5S_wPKd", "_score" : 1.0, "_source" : { "id_date" : 1645423875003 }

[RowenWoo]

@yongbingwang I test this in the main branch and Problem does not recur， which version are you using?

[yongbingwang]

@RowenWoo I recurred it just now, Both the latest main 48c9cdf and the latest branch-2.3 04bc550 can recur this issue.

mysql> CREATE EXTERNAL TABLE test_null (
    ->   `id_date` datetime not NULL COMMENT ""
    -> ) ENGINE=ELASTICSEARCH
    -> COMMENT "ELASTICSEARCH"
    -> PROPERTIES (
    -> "hosts" = "xxx:9200",
    -> "user" = "root",
    -> "password" = "",
    -> "index" = "es_enable_docvalue_scan_is_true",
    -> "type" = "_doc",
    -> "transport" = "http",
    -> "enable_docvalue_scan" = "true",
    -> "max_docvalue_fields" = "20",
    -> "enable_keyword_sniff" = "true"
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_null;
+---------------------+
| id_date             |
+---------------------+
| 0000-01-01 00:00:00 |
| 2022-02-21 14:11:15 |
+---------------------+
2 rows in set (0.04 sec)

mysql> select current_version();
+----------------------+
| current_version()    |
+----------------------+
| MAIN-RELEASE 48c9cdf |
+----------------------+
1 row in set (0.02 sec)

[yongbingwang]

curl -X GET "xxx:9200/es_enable_docvalue_scan_is_true/_mapping?pretty"
{
  "es_enable_docvalue_scan_is_true" : {
    "mappings" : {
      "properties" : {
        "id_date" : {
          "type" : "long",
          "fields" : {
            "keyword" : {
              "type" : "date"
            }
          }
        }
      }
    }
  }
}

[RowenWoo]

have fixed this problem: https://github.com/StarRocks/starrocks/pull/9226/commits

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!