[Doris on ES]When limit clause is included in SQL, it may result inconsistent result, even more an empty result set #4761
Description
Describe the bug
Sql Result Set is expected to have data, but returned empty.
MySQL [dw]> select count(1) from dw.my_table where from_unixtime(trade_time, '%Y%m%d') = '20201005';
+----------+
| count(1) |
+----------+
| 19665 |
+----------+
1 row in set (4.36 sec)
MySQL [dw]> select sub_trade_id from dw.my_table where from_unixtime(trade_time, '%Y%m%d') = '20201005';
+--------------+
| sub_trade_id |
+--------------+
| 1334976489 |
...
| 1335063157 |
+--------------+
19665 rows in set (5.22 sec)
MySQL [dw]> select trade_id from dw.my_table where from_unixtime(trade_time, '%Y%m%d') = '20201005' limit 1;
Empty set (0.00 sec)
MySQL [dw]> select sub_trade_id from dw.my_table where from_unixtime(trade_time, '%Y%m%d') = '20201005' limit 10;
Empty set (0.00 sec)
After testing with kibana, it is found that the data returned by DSL can not be obtained using from_unixtime function in where clause.
{
"hits" : {
"total" : 1,
"hits" : [
{
"_score" : null,
"fields" : {
"trade_time" : [
1600321018
],
"sub_trade_id" : [
1308628530
]
}
}
]
}
}