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+
### Log Fragments
_No response_
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_uniquevindex withignore_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_uniquevindex withignore_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" } ] } } }insert data with NULLs
create lookup vindexes
show that the first lookup vindex table has empty lookup rows despite
ignore_nulls: "true"show that the second lookup vindex table was created and empty lookup rows despite `ignore_nulls: "false"
updating the record with a NULL does not update the existing lookup record but adds a new one
Binary Version
Operating System and Environment details