Add support for key columns which require exact matching to be considered a cache hit

[kaidaguerre]

For example, in the turbot plugin, turbot_resource table, the filter column represents a filter string which is executed to retrieve the data. The filter column value is provided by the query qual, for example

select
 id, filter
    from
        turbot_demo.turbot_resource
    where
        resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'
        AND filter = '$.turbot.custom.createTimestamp:<=T-90d'

if no filter is provided in the qual, the filter column will not have any data

When checking for a cache hit, normally accept data which has fewer quals. For example, if the cached data has the qual

 resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'

we would expect this data to be a superset of the data returned from

resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'
        AND filter = '$.turbot.custom.createTimestamp:<=T-90d'

However in the case of filter columns this will NOT be the case, as the data returned by

resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'

will have null values for the filter column whereas

resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'
        AND filter = '$.turbot.custom.createTimestamp:<=T-90d'

will not.

So we need to introduce a flag which ensures that when checking if existing cached data contains all the data required for a given query, specific quals have exact matches.

So in this case, we need the cached data to have the qual value

filter = '$.turbot.custom.createTimestamp:<=T-90d'

In the case where the cached data has the qual value

 resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'

this will no longer be considered a super set of

resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/accessKey'
        AND filter = '$.turbot.custom.createTimestamp:<=T-90d'

so will NOT be a cache hit.

[LalitLab]

@kaidaguerre is the below case also because of the above-mentioned issue?

> select * from net_dns_record where domain = 'turbot.com' and type = 'NS'
+------------+------+--------+--------------------------+----------+--------+--------+
| domain     | type | ip     | target                   | priority | value  | ttl    |
+------------+------+--------+--------------------------+----------+--------+--------+
| turbot.com | NS   | <null> | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
+------------+------+--------+--------------------------+----------+--------+--------+

Time: 85.174741ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'A'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 4.52282ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'SOA'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 4.568473ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'MX'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 5.069768ms
> select * from net_dns_record where domain = 'turbot.com'
+------------+------+--------+--------------------------+----------+--------+--------+
| domain     | type | ip     | target                   | priority | value  | ttl    |
+------------+------+--------+--------------------------+----------+--------+--------+
| turbot.com | NS   | <null> | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
+------------+------+--------+--------------------------+----------+--------+--------+

Time: 5.259996ms
> .cache clear
> select * from net_dns_record
Error: rpc error: code = Internal desc = 'List' call for table 'net_dns_record' is missing 1 required qual: column:'domain' operator: =
 (SQLSTATE HV000)
> select * from net_dns_record where domain = 'turbot.com'
+------------+------+--------------+--------------------------+----------+--------+--------+
| domain     | type | ip           | target                   | priority | value  | ttl    |
+------------+------+--------------+--------------------------+----------+--------+--------+
| turbot.com | A    | 54.182.0.62  | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.126 | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.125 | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.32  | <null>                   | <null>   | <null> | 60     |
| turbot.com | MX   | <null>       | alt3.aspmx.l.google.com. | 10       | <null> | 300    |
| turbot.com | MX   | <null>       | alt2.aspmx.l.google.com. | 5        | <null> | 300    |
| turbot.com | MX   | <null>       | aspmx.l.google.com.      | 1        | <null> | 300    |
| turbot.com | MX   | <null>       | alt1.aspmx.l.google.com. | 5        | <null> | 300    |
| turbot.com | MX   | <null>       | alt4.aspmx.l.google.com. | 10       | <null> | 300    |
| turbot.com | NS   | <null>       | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | SOA  | <null>       | ns-1312.awsdns-36.org.   | <null>   | <null> | 900    |
+------------+------+--------------+--------------------------+----------+--------+--------+

[kaidaguerre]

@LalitTurbot is type a dynamic column, like filter

[LalitLab]

@LalitTurbot is type a dynamic column, like filter

Yes type is a filter column in the table too