Bug Report: CreateLookupVindex does not honor ignore_nulls

[austenLacy]

Overview of the Issue

When creating a consistent and consistent unique lookup vindex with ignore_nulls: "true" we’re seeing rows added to the lookup table for and with NULL values.

Reproduction Steps

consistent_lookup_unique vindex with ignore_nulls: "true"

cat ./lookup_vindex_1.json
{
    "sharded": true,
    "vindexes": {
        "corder_lookup_1": {
            "type": "consistent_lookup_unique",
            "params": {
                "table": "customer.corder_lookup_1",
                "from": "sku",
                "to": "keyspace_id",
                "ignore_nulls": "true"
            },
            "owner": "corder"
        }
    },
    "tables": {
        "corder": {
            "column_vindexes": [
                {
                    "column": "sku",
                    "name": "corder_lookup_1"
                }
            ]
        }
    }
}

consistent_lookup_unique vindex with ignore_nulls: "false"

cat lookup_vindex_2.json
{
    "sharded": true,
    "vindexes": {
        "corder_lookup_2": {
            "type": "consistent_lookup_unique",
            "params": {
                "table": "customer.corder_lookup_2",
                "from": "sku",
                "to": "keyspace_id"
            },
            "owner": "corder"
        }
    },
    "tables": {
        "corder": {
            "column_vindexes": [
                {
                    "column": "sku",
                    "name": "corder_lookup_2"
                }
            ]
        }
    }
}

./101_initial_cluster.sh
mysql --table < ../common/insert_commerce_data.sql
./201_customer_tablets.sh
vtctldclient ReloadSchemaKeyspace customer
vtctlclient MoveTables -- --source commerce --tables 'customer,corder' Create customer.commerce2customer
vtctlclient MoveTables -- --tablet_types=rdonly,replica SwitchTraffic customer.commerce2customer
vtctlclient MoveTables -- --tablet_types=primary SwitchTraffic customer.commerce2customer
vtctlclient MoveTables -- Complete customer.commerce2customer
vtctldclient ApplySchema --sql-file create_commerce_seq.sql commerce
vtctldclient ApplyVSchema --vschema-file vschema_commerce_seq.json commerce
vtctldclient ApplyVSchema --vschema-file vschema_customer_sharded.json customer
vtctldclient ApplySchema --sql-file create_customer_sharded.sql customer
./302_new_shards.sh
vtctlclient Reshard -- --source_shards '0' --target_shards '-80,80-' Create customer.cust2cust
vtctlclient Reshard -- --tablet_types=rdonly,replica,primary SwitchTraffic customer.cust2cust
vtctlclient Reshard -- Complete customer.cust2cust
for i in 200 201 202; do
 CELL=zone1 TABLET_UID=$i ../common/scripts/vttablet-down.sh
 CELL=zone1 TABLET_UID=$i ../common/scripts/mysqlctl-down.sh
done

insert data with NULLs

INSERT INTO customer.corder(order_id, customer_id, price) values (6, 4, 888);
INSERT INTO customer.corder(order_id, customer_id, price) values (7, 4, 999);

create lookup vindexes

vtctlclient CreateLookupVindex -- --tablet_types=RDONLY customer "$(cat ~/lookup_vindex_1.json)"
vtctlclient CreateLookupVindex -- --tablet_types=RDONLY customer "$(cat ~/lookup_vindex_2.json)"

show that the first lookup vindex table has empty lookup rows despite ignore_nulls: "true"

mysql> use customer;
Database changed

mysql> desc corder;
+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| order_id    | bigint         | NO   | PRI | NULL    |       |
| customer_id | bigint         | YES  |     | NULL    |       |
| sku         | varbinary(128) | YES  |     | NULL    |       |
| price       | bigint         | YES  |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from corder order by order_id asc;
+----------+-------------+-----------+-------+
| order_id | customer_id | sku       | price |
+----------+-------------+-----------+-------+
|        1 |           1 | SKU-1001  |   100 |
|        2 |           2 | SKU-1002  |    30 |
|        3 |           3 | SKU-1002  |    30 |
|        4 |           4 | SKU-1002  |    30 |
|        5 |           5 | SKU-1002  |    30 |
|        5 |           4 | Product_5 |   104 |
|        6 |           4 | NULL      |   888 |
|        7 |           4 | NULL      |   999 |
+----------+-------------+-----------+-------+
8 rows in set (0.01 sec)

mysql> select sku, hex(keyspace_id) from corder_lookup_1;
+-----------+------------------+
| sku       | hex(keyspace_id) |
+-----------+------------------+
|           | D2FD8867D50D2DFE |
| Product_5 | D2FD8867D50D2DFE |
| SKU-1002  | D2FD8867D50D2DFE |
| SKU-1001  | 166B40B44ABA4BD6 |
+-----------+------------------+
4 rows in set (0.01 sec)

show that the second lookup vindex table was created and empty lookup rows despite `ignore_nulls: "false"

note the table has NULLs that should have thrown an error

mysql> select * from corder where sku is NULL;
+----------+-------------+------+-------+
| order_id | customer_id | sku  | price |
+----------+-------------+------+-------+
|        6 |           4 | NULL |   999 |
|        7 |           4 | NULL |   888 |
+----------+-------------+------+-------+

mysql> select sku, hex(keyspace_id) from corder_lookup_2;
+-----------+------------------+
| sku       | hex(keyspace_id) |
+-----------+------------------+
|           | D2FD8867D50D2DFE |
| Product_5 | D2FD8867D50D2DFE |
| SKU-1002  | 06E7EA22CE92708F |
| SKU-1001  | 166B40B44ABA4BD6 |
+-----------+------------------+
4 rows in set (0.00 sec)

updating the record with a NULL does not update the existing lookup record but adds a new one

not sure if this is by design?

mysql> update corder set sku = 'SKU-UPDATED' where order_id = 7;
Query OK, 1 row affected (0.03 sec)

mysql> select * from corder;
+----------+-------------+-------------+-------+
| order_id | customer_id | sku         | price |
+----------+-------------+-------------+-------+
|        1 |           1 | SKU-1001    |   100 |
|        2 |           2 | SKU-1002    |    30 |
|        3 |           3 | SKU-1002    |    30 |
|        5 |           5 | SKU-1002    |    30 |
|        4 |           4 | SKU-1002    |    30 |
|        7 |           4 | SKU-UPDATED |   888 |
|        8 |           4 | NULL        |   999 |
+----------+-------------+-------------+-------+
7 rows in set (0.01 sec)

mysql> select sku, hex(keyspace_id) from corder_lookup;
+-------------+------------------+
| sku         | hex(keyspace_id) |
+-------------+------------------+
| SKU-1001    | 166B40B44ABA4BD6 |
|             | D2FD8867D50D2DFE |
| SKU-1002    | 06E7EA22CE92708F |
| SKU-UPDATED | D2FD8867D50D2DFE |
+-------------+------------------+
4 rows in set (0.01 sec)

Binary Version

./vtgate --version
Version: 15.0.3

Operating System and Environment details

cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

uname -sr
Linux 5.10.162+

uname -m
x86_64

### Log Fragments

_No response_

[mattlord]

Thank you for the report and great test case @austenLacy!