Searching for an empty vector column

[mtruyens]

Confirmation Checklist:

• You have searched for an answer in the manual.
• You have considered using the forum for general discussions, which can be more suitable for non-urgent or broad queries.
• You are aware of our community support channels on Slack, Telegram EN, and Telegram RU, where you can interact with other users and our developers.
• You know about Manticore Team's professional services. Engaging with our experts through a support subscription can significantly accelerate resolution times and provide tailored solutions to your specific needs.

Your question:

Hi, is there a possibility to search for empty vector columns, e.g. similar to what "IS NULL" would do for attributes?

[sanikolaev]

@mtruyens It seems that the only way to find empty vector columns is with a query like select *, knn_dist() dist from t where knn(v, 1000, (0,0)) and dist = 0.

E.g.:

mysql> drop table if exists t; create table t ( v float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='l2' ); insert into t(id) values(1); insert into t values(2, (1.2,2.3)); insert into t(id) values(3); select * from t; select *, knn_dist() dist from t where knn(v, 1000, (0,0)) and dist = 0;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t ( v float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='l2' )
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(id) values(1)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
insert into t values(2, (1.2,2.3))
--------------

Query OK, 1 row affected (0.00 sec)

--------------
insert into t(id) values(3)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t
--------------

+------+-------------------+
| id   | v                 |
+------+-------------------+
|    2 | 1.200000,2.300000 |
|    3 | 0.000000,0.000000 |
|    1 | 0.000000,0.000000 |
+------+-------------------+
3 rows in set (0.01 sec)
--- 3 out of 3 results in 0ms ---

--------------
select *, knn_dist() dist from t where knn(v, 1000, (0,0)) and dist = 0
--------------

+------+-------------------+-----------+----------+
| id   | v                 | @knn_dist | dist     |
+------+-------------------+-----------+----------+
|    3 | 0.000000,0.000000 |  0.000000 | 0.000000 |
|    1 | 0.000000,0.000000 |  0.000000 | 0.000000 |
+------+-------------------+-----------+----------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

[mtruyens]

Thanks for the quick reply, that should indeed do the trick!