Wrong query result of Apply operator when late materialization is enable

[Lloyd-Pottiger]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `invoice_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_type` tinyint(3) DEFAULT NULL,
  `client_no` char(18) DEFAULT NULL,
  `taxpayer_no` varchar(50) DEFAULT NULL,
  `status` tinyint(3) DEFAULT '0',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_no` char(18) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `tax_registry_no` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uk_company_no` (`company_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE invoice_info SET TIFLASH REPLICA 1;

INSERT INTO test.invoice_info(id, taxpayer_no, client_no, client_type, status, update_time)
VALUES
    (1, 'TAX001', 'Z9005', 1, 1, '2024-02-18 10:00:00'),
    (2, 'TAX002', 'Z9005', 1, 0, '2024-02-18 09:00:00'),
    (3, 'TAX003', 'Z9005', 2, 1, '2024-02-18 08:00:00'),
    (4, 'TAX004', 'Z9006', 1, 1, '2024-02-18 12:00:00');


INSERT INTO test.company(company_no,name,tax_registry_no) 
VALUES
    ('Z9005','AA','aaa'),
    ('Z9006','BB','bbb'),
    ('Z9007','CC','ccc');

SELECT  company_no,  IFNULL(   (    SELECT  /*+ READ_FROM_STORAGE(TIFLASH[test.invoice_info]) */   taxpayer_no    FROM     test.invoice_info    WHERE     client_no = c.company_no    AND client_type = 1    AND STATUS = 1    ORDER BY     update_time DESC    LIMIT 1   ),   tax_registry_no  ) AS tax_registry_no FROM  test.company c WHERE  company_no = 'Z9005' LIMIT 1;
+------------+-----------------+
| company_no | tax_registry_no |
+------------+-----------------+
| Z9005      | aaa             |
+------------+-----------------+

2. What did you expect to see? (Required)

SELECT  company_no,  IFNULL(   (    SELECT  /*+ READ_FROM_STORAGE(TIFLASH[test.invoice_info]) */   taxpayer_no    FROM     test.invoice_info    WHERE     client_no = c.company_no    AND client_type = 1    AND STATUS = 1    ORDER BY     update_time DESC    LIMIT 1   ),   tax_registry_no  ) AS tax_registry_no FROM  test.company c WHERE  company_no = 'Z9005' LIMIT 1;
+------------+-----------------+
| company_no | tax_registry_no |
+------------+-----------------+
| Z9005      | TAX001          |
+------------+-----------------+

3. What did you see instead (Required)

SELECT  company_no,  IFNULL(   (    SELECT  /*+ READ_FROM_STORAGE(TIFLASH[test.invoice_info]) */   taxpayer_no    FROM     test.invoice_info    WHERE     client_no = c.company_no    AND client_type = 1    AND STATUS = 1    ORDER BY     update_time DESC    LIMIT 1   ),   tax_registry_no  ) AS tax_registry_no FROM  test.company c WHERE  company_no = 'Z9005' LIMIT 1;
+------------+-----------------+
| company_no | tax_registry_no |
+------------+-----------------+
| Z9005      | aaa             |
+------------+-----------------+

4. What is your TiDB version? (Required)

[JaySon-Huang]

/label affects-7.5

[JaySon-Huang]

/label affects-7.1

[JaySon-Huang]

Another similar fixed case #49241

[JaySon-Huang]

The execution plan of this query. There is an "Apply_24" operator in the execution plan that satisfies parallel apply and triggers this bug.

explain SELECT company_no,
       Ifnull((SELECT /*+ READ_FROM_STORAGE(TIFLASH[test.invoice_info]) */
              taxpayer_no
               FROM   test.invoice_info
               WHERE  client_no = c.company_no
                      AND client_type = 1
                      AND status = 1
               ORDER  BY update_time DESC
               LIMIT  1), tax_registry_no) AS tax_registry_no
FROM   test.company c
WHERE  company_no = 'Z9005'
LIMIT  1; 

+----------------------------------+---------+--------------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task         | access object                                  | operator info                                                                                               |
+----------------------------------+---------+--------------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------+
| Projection_19                    | 1.00    | root         |                                                | test.company.company_no, ifnull(test.invoice_info.taxpayer_no, test.company.tax_registry_no)->Column#18     |
| └─Limit_22                       | 1.00    | root         |                                                | offset:0, count:1                                                                                           |
|   └─Apply_24                     | 1.00    | root         |                                                | CARTESIAN left outer join                                                                                   |
|     ├─Limit_26(Build)            | 1.00    | root         |                                                | offset:0, count:1                                                                                           |
|     │ └─Point_Get_27             | 1.00    | root         | table:company, index:uk_company_no(company_no) |                                                                                                             |
|     └─TopN_30(Probe)             | 0.00    | root         |                                                | test.invoice_info.update_time:desc, offset:0, count:1                                                       |
|       └─TableReader_38           | 0.00    | root         |                                                | MppVersion: 2, data:ExchangeSender_37                                                                       |
|         └─ExchangeSender_37      | 0.00    | mpp[tiflash] |                                                | ExchangeType: PassThrough                                                                                   |
|           └─TopN_36              | 0.00    | mpp[tiflash] |                                                | test.invoice_info.update_time:desc, offset:0, count:1                                                       |
|             └─Selection_35       | 0.00    | mpp[tiflash] |                                                | eq(test.invoice_info.client_type, 1), eq(test.invoice_info.status, 1)                                       |
|               └─TableFullScan_34 | 10.00   | mpp[tiflash] | table:invoice_info                             | pushed down filter:eq(test.invoice_info.client_no, test.company.company_no), keep order:false, stats:pseudo |
+----------------------------------+---------+--------------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------+

[seiya-annie]

/found customer