cannot simplify predicate ```isnull(not null column)```

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (
    id VARCHAR(64) PRIMARY KEY
);
CREATE TABLE t2 (
    c1 VARCHAR(64) NOT NULL,
    c2 VARCHAR(64) NOT NULL,
    c3 VARCHAR(64) NOT NULL,
    PRIMARY KEY (c1, c2, c3),
    KEY c3 (c3)
);
CREATE TABLE t3 (
    c1 VARCHAR(64) NOT NULL,
    c2 VARCHAR(64) NOT NULL,
    c3 VARCHAR(64) NOT NULL,
    PRIMARY KEY (c1, c2, c3),
    KEY c3 (c3)
);
CREATE TABLE t4 (
    c1 VARCHAR(64) NOT NULL,
    c2 VARCHAR(64) NOT NULL,
    c3 VARCHAR(64) NOT NULL,
    state VARCHAR(64) NOT NULL DEFAULT 'ACTIVE',
    PRIMARY KEY (c1, c2, c3),
    KEY c3 (c3)
);
CREATE TABLE t5 (
    c1 VARCHAR(64) NOT NULL,
    c2 VARCHAR(64) NOT NULL,
    PRIMARY KEY (c1, c2)
);
explain format='verbose' SELECT i.id, ip_products.products
FROM t1 AS i
LEFT JOIN t4 ON i.id = t4.c3
LEFT JOIN (
    SELECT t4.c3,
           GROUP_CONCAT(DISTINCT t2.c3 ORDER BY t2.c3 ASC) AS products
    FROM t4
    JOIN t3 ON t4.c1 = t3.c1
           AND t4.c2 = t3.c2
    LEFT JOIN t2 ON t4.c1 = t2.c1
                AND t4.c2 = t2.c2
    WHERE t3.c3 = 'production'
      AND t4.state = 'ACTIVE'
    GROUP BY t4.c3, t4.c1, t4.c2
) AS ip_products ON t4.c3 = ip_products.c3
LEFT JOIN t5 ON i.id = t5.c1
            AND t5.c2 = 'production'
WHERE t4.state = 'ACTIVE'
  AND t5.c1 IS NULL
GROUP BY i.id, ip_products.products
HAVING FIND_IN_SET('info', products)
ORDER BY i.id ASC
LIMIT 500 OFFSET 5500;

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

TopN_36 8.00 1177779.76 root  test.t1.id, offset:5500, count:500
└─HashAgg_41 8.00 495969.50 root  group by:Column#18, test.t1.id, funcs:firstrow(test.t1.id)->test.t1.id, funcs:firstrow(Column#18)->Column#18
  └─Selection_42 8.00 493651.71 root  isnull(test.t5.c1)
    └─Projection_43 10.00 493152.71 root  test.t1.id, Column#18, test.t5.c1
      └─HashJoin_56 10.00 493149.71 root  inner join, equal:[eq(test.t4.c3, test.t4.c3)]
        ├─Selection_123(Build) 6.40 18442.06 root  find_in_set("info", Column#18)
        │ └─HashAgg_124 8.00 18042.86 root  group by:test.t4.c3, test.t4.c2, test.t4.c1, funcs:group_concat(distinct test.t2.c3 order by test.t2.c3 separator ",")->Column#18, funcs:firstrow(test.t4.c3)->test.t4.c3
        │   └─IndexJoin_127 15.62 15105.72 root  left outer join, inner:TableReader_177, left side:IndexJoin_160, outer key:test.t4.c1, test.t4.c2, inner key:test.t2.c1, test.t2.c2, equal cond:eq(test.t4.c2, test.t2.c2), eq(test.t4.c1, test.t2.c1)
        │     ├─IndexJoin_160(Build) 12.50 7150.52 root  inner join, inner:TableReader_173, outer key:test.t3.c1, test.t3.c2, inner key:test.t4.c1, test.t4.c2, equal cond:eq(test.t3.c2, test.t4.c2), eq(test.t3.c1, test.t4.c1)
        │     │ ├─IndexReader_175(Build) 10.00 655.36 root  index:IndexRangeScan_174
        │     │ │ └─IndexRangeScan_174 10.00 3177.59 cop[tikv] table:t3, index:c2(c2) range:["production","production"], keep order:false, stats:pseudo
        │     │ └─TableReader_173(Probe) 0.01 23.96 root  data:Selection_172
        │     │   └─Selection_172 0.01 358.61 cop[tikv]  eq(test.t4.state, "ACTIVE")
        │     │     └─TableRangeScan_171 10.00 308.71 cop[tikv] table:t4 range: decided by [eq(test.t4.c1, test.t3.c1) eq(test.t4.c2, test.t3.c2)], keep order:false, stats:pseudo
        │     └─TableReader_177(Probe) 12.50 57.47 root  data:TableRangeScan_176
        │       └─TableRangeScan_176 12.50 291.82 cop[tikv] table:t2 range: decided by [eq(test.t2.c1, test.t4.c1) eq(test.t2.c2, test.t4.c2)], keep order:false, stats:pseudo
        └─IndexJoin_59(Probe) 12.50 471600.51 root  left outer join, inner:TableReader_113, left side:IndexJoin_90, outer key:test.t1.id, inner key:test.t5.c1, equal cond:eq(test.t1.id, test.t5.c1)
          ├─IndexJoin_90(Build) 12.50 463857.00 root  inner join, inner:TableReader_108, outer key:test.t4.c3, inner key:test.t1.id, equal cond:eq(test.t4.c3, test.t1.id)
          │ ├─TableReader_106(Build) 10.00 457241.47 root  data:Selection_105
          │ │ └─Selection_105 10.00 6854186.91 cop[tikv]  eq(test.t4.state, "ACTIVE")
          │ │   └─TableFullScan_104 10000.00 6355186.91 cop[tikv] table:t4 keep order:false, stats:pseudo
          │ └─TableReader_108(Probe) 10.00 28.57 root  data:TableRangeScan_107
          │   └─TableRangeScan_107 10.00 238.42 cop[tikv] table:i range: decided by [eq(test.t1.id, test.t4.c3)], keep order:false, stats:pseudo
          └─TableReader_113(Probe) 0.01 21.22 root  data:Selection_112
            └─Selection_112 0.01 317.91 cop[tikv]  eq(test.t5.c2, "production")
              └─TableRangeScan_111 12.50 268.01 cop[tikv] table:t5 range: decided by [eq(test.t5.c1, test.t1.id) eq(test.t5.c2, production)], keep order:false, stats:pseud
        	            	 

3. What did you see instead (Required)

+------------------------------------------+----------+------------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                       | estRows  | estCost    | task      | access object          | operator info                                                                                                                                                                                          |
+------------------------------------------+----------+------------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN_36                                  | 8.00     | 1211034.27 | root      |                        | test.t1.id, offset:5500, count:500                                                                                                                                                                     |
| └─HashAgg_41                             | 8.00     | 529224.01  | root      |                        | group by:Column#16, test.t1.id, funcs:firstrow(test.t1.id)->test.t1.id, funcs:firstrow(Column#16)->Column#16                                                                                           |
|   └─Selection_42                         | 8.00     | 526906.22  | root      |                        | isnull(test.t5.c1)                                                                                                                                                                                     |
|     └─Projection_43                      | 10.00    | 526407.22  | root      |                        | test.t1.id, Column#16, test.t5.c1                                                                                                                                                                      |
|       └─HashJoin_56                      | 10.00    | 526404.22  | root      |                        | inner join, equal:[eq(test.t4.c3, test.t4.c3)]                                                                                                                                                         |
|         ├─Selection_129(Build)           | 6.40     | 18442.06   | root      |                        | find_in_set("info", Column#16)                                                                                                                                                                         |
|         │ └─HashAgg_130                  | 8.00     | 18042.86   | root      |                        | group by:test.t4.c1, test.t4.c2, test.t4.c3, funcs:group_concat(distinct test.t2.c3 order by test.t2.c3 separator ",")->Column#16, funcs:firstrow(test.t4.c3)->test.t4.c3                              |
|         │   └─IndexJoin_133              | 15.62    | 15105.72   | root      |                        | left outer join, inner:TableReader_183, left side:IndexJoin_166, outer key:test.t4.c1, test.t4.c2, inner key:test.t2.c1, test.t2.c2, equal cond:eq(test.t4.c1, test.t2.c1), eq(test.t4.c2, test.t2.c2) |
|         │     ├─IndexJoin_166(Build)     | 12.50    | 7150.52    | root      |                        | inner join, inner:TableReader_179, outer key:test.t3.c1, test.t3.c2, inner key:test.t4.c1, test.t4.c2, equal cond:eq(test.t3.c1, test.t4.c1), eq(test.t3.c2, test.t4.c2)                               |
|         │     │ ├─IndexReader_181(Build) | 10.00    | 655.36     | root      |                        | index:IndexRangeScan_180                                                                                                                                                                               |
|         │     │ │ └─IndexRangeScan_180   | 10.00    | 3177.59    | cop[tikv] | table:t3, index:c3(c3) | range:["production","production"], keep order:false, stats:pseudo                                                                                                                                      |
|         │     │ └─TableReader_179(Probe) | 0.01     | 23.96      | root      |                        | data:Selection_178                                                                                                                                                                                     |
|         │     │   └─Selection_178        | 0.01     | 358.61     | cop[tikv] |                        | eq(test.t4.state, "ACTIVE")                                                                                                                                                                            |
|         │     │     └─TableRangeScan_177 | 10.00    | 308.71     | cop[tikv] | table:t4               | range: decided by [eq(test.t4.c1, test.t3.c1) eq(test.t4.c2, test.t3.c2)], keep order:false, stats:pseudo                                                                                              |
|         │     └─TableReader_183(Probe)   | 12.50    | 57.47      | root      |                        | data:TableRangeScan_182                                                                                                                                                                                |
|         │       └─TableRangeScan_182     | 12.50    | 291.82     | cop[tikv] | table:t2               | range: decided by [eq(test.t2.c1, test.t4.c1) eq(test.t2.c2, test.t4.c2)], keep order:false, stats:pseudo                                                                                              |
|         └─IndexJoin_59(Probe)            | 12.49    | 504855.27  | root      |                        | left outer join, inner:TableReader_119, left side:IndexJoin_91, outer key:test.t1.id, inner key:test.t5.c1, equal cond:eq(test.t1.id, test.t5.c1)                                                      |
|           ├─IndexJoin_91(Build)          | 12.49    | 497118.00  | root      |                        | inner join, inner:TableReader_114, outer key:test.t4.c3, inner key:test.t1.id, equal cond:eq(test.t4.c3, test.t1.id)                                                                                   |
|           │ ├─TableReader_107(Build)     | 9.99     | 490507.84  | root      |                        | data:Selection_106                                                                                                                                                                                     |
|           │ │ └─Selection_106            | 9.99     | 7353186.91 | cop[tikv] |                        | eq(test.t4.state, "ACTIVE"), not(isnull(test.t4.c3))                                                                                                                                                   |
|           │ │   └─TableFullScan_105      | 10000.00 | 6355186.91 | cop[tikv] | table:t4               | keep order:false, stats:pseudo                                                                                                                                                                         |
|           │ └─TableReader_114(Probe)     | 9.99     | 27.83      | root      |                        | data:TableRangeScan_113                                                                                                                                                                                |
|           │   └─TableRangeScan_113       | 9.99     | 227.31     | cop[tikv] | table:i                | range: decided by [eq(test.t1.id, test.t4.c3)], keep order:false, stats:pseudo                                                                                                                         |
|           └─TableReader_119(Probe)       | 0.01     | 21.22      | root      |                        | data:Selection_118
                                                                                           |
|             └─Selection_118              | 0.01     | 317.91     | cop[tikv] |                        | eq(test.t5.c2, "production")
                                                                                           |
|               └─TableRangeScan_117       | 12.49    | 268.01     | cop[tikv] | table:t5               | range: decided by [eq(test.t5.c1, test.t1.id) eq(test.t5.c2, production)], keep order:false, stats:pseudo
                                                                                           |
+------------------------------------------+----------+------------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)