MySQL Bugs: #112783: the result of a query to a big table is not right

Bug #112783	the result of a query to a big table is not right
Submitted:	20 Oct 2023 11:29	Modified:	23 Oct 2023 13:49
Reporter:	Andy Zhao Andy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Shell VSCode Extension	Severity:	S3 (Non-critical)
Version:	1.13.4	OS:	Windows (windows 11)
Assigned to:		CPU Architecture:	Any

Description:
I wrote following query statement, and the type of order_id is bigint(20), and there are probably tens of millions of records in the table being queried

SELECT order_id,create_time
FROM r_order
WHERE CAST(order_id AS CHAR) LIKE '%7921%'
  AND create_time BETWEEN '2023-09-01' AND '2023-09-30' ;

A lot of records without 7921 in order_id come back. I tried another tool, the result is right.

parts of records in result are:
1697326789062738000,'2023-09-01 03:13:35.982000'
1697452870470738000,'2023-09-01 11:34:36.133000'
1697539086792179700,'2023-09-01 17:17:11.705000'
1697642738118738000,'2023-09-02 00:09:04.108000'
1697740365787218000,'2023-09-02 06:37:00.359000'
1697768342424658000,'2023-09-02 08:28:10.507000'
1697791688969298000,'2023-09-02 10:00:56.758000'
1697792133275500500,'2023-09-02 10:02:42.688000'
1697921218156871700,'2023-09-02 18:35:38.923000'
1697921639894753300,'2023-09-02 18:37:19.485000'
1697921824539984000,'2023-09-02 18:38:03.503000'
1698579214155915300,'2023-09-04 14:10:17.396000'
1698602590127792000,'2023-09-04 15:43:10.659000'
1698879213750198300,'2023-09-05 10:02:22.871000'
1698901181999792000,'2023-09-05 11:29:40.510000'
1699092446838792200,'2023-09-06 00:09:41.600000'

How to repeat:
always recur

Hi Mr. Andy,

Thank you for your bug report.

However, it is not a bug. It is only quite confusing.

You have set the category wrongly, since you are asking for the help with your query.

Also, you are trying to cast an integer as a string and you expect that like '%.......%' works on integers. It does not.

Not a bug.

Hi my friend.
I didn't agree with you.

I have tested the following statement before making this issue.

SELECT order_id,create_time
FROM r_order
WHERE order_id LIKE '%7921%'
  AND create_time BETWEEN '2023-09-01' AND '2023-09-30' ;

I got same result.

I put some result come back from DBeaver:

1697326789062737921	2023-09-01 03:13:35.982
1697452870470737921	2023-09-01 11:34:36.133
1697539086792179714	2023-09-01 17:17:11.705
1697642738118737921	2023-09-02 00:09:04.108
1697740365787217921	2023-09-02 06:37:00.359
1697768342424657921	2023-09-02 08:28:10.507
1697791688969297921	2023-09-02 10:00:56.758
1697792133275500546	2023-09-02 10:02:42.688

you will see, the ending of order_id and create_time are both not 000
but they are 000 and no time come back from Shell VSCode Extension

# order_id,create_time
1697326789062738000,'2023-09-01 03:13:35.982000'
1697452870470738000,'2023-09-01 11:34:36.133000'
1697539086792179700,'2023-09-01 17:17:11.705000'
1697642738118738000,'2023-09-02 00:09:04.108000'
1697740365787218000,'2023-09-02 06:37:00.359000'
1697768342424658000,'2023-09-02 08:28:10.507000'
1697791688969298000,'2023-09-02 10:00:56.758000'
1697792133275500500,'2023-09-02 10:02:42.688000'

I think there are something wrong in the UI componets or some preprocess before show them

Hi Mr. Andy,

Can you try the same queries through MySQL CLI and let us know the result.

We also think that there is a VSCode extension for our command line interface.

Sure,

the result from CLI:
mysql> SELECT order_id,create_time
    -> FROM r_order
    -> WHERE order_id LIKE '%7921%'
    ->   AND create_time BETWEEN '2023-09-01' AND '2023-09-30' limit 100; 
+---------------------+-------------------------+
| order_id            | create_time             |
+---------------------+-------------------------+
| 1697326789062737921 | 2023-09-01 03:13:35.982 |
| 1697452870470737921 | 2023-09-01 11:34:36.133 |
| 1697539086792179714 | 2023-09-01 17:17:11.705 |
| 1697642738118737921 | 2023-09-02 00:09:04.108 |
| 1697740365787217921 | 2023-09-02 06:37:00.359 |
| 1697768342424657921 | 2023-09-02 08:28:10.507 |

And I tried MySQL Workbench, the result is:
'1697326789062737921', '2023-09-01 03:13:35.982'
'1697452870470737921', '2023-09-01 11:34:36.133'
'1697539086792179714', '2023-09-01 17:17:11.705'
'1697642738118737921', '2023-09-02 00:09:04.108'
'1697740365787217921', '2023-09-02 06:37:00.359'
'1697768342424657921', '2023-09-02 08:28:10.507'

Hi Mr. Andy,

It means that results are identical.

The only difference is that CLI displays them in the text-mode table. 

This can be turned of in CLI.

Not a bug.

This is obviously a bug of Shell VSCode Extension , I think.