inner_product()¶
Function Description¶
INNER_PRODUCT(vector1, vector2) returns the negative dot product, -(vector1 · vector2). Both vectors must have the same dimension.
Function Syntax¶
SELECT inner_product(vector1, vector2) AS result FROM table_name;
Example¶
drop table if exists vec_table;
create table vec_table(a int, b vecf32(3), c vecf64(3));
insert into vec_table values(1, "[1,2,3]", "[4,5,6]");
mysql> select * from vec_table;
+-------+--------------------------+
| a | b | c |
+-------+--------------------------+
| 1 | [1, 2, 3] | [4, 5, 6] |
+-------+--------------------------+
1 row in set (0.00 sec)
mysql> select inner_product(b,"[1,2,3]") from vec_table;
+--------------------------------+
| inner_product(b, [1,2,3]) |
+--------------------------------+
| -14 |
+--------------------------------+
1 row in set (0.00 sec)
limit¶
Two parameter vectors must have the same dimension.
Sign and ordering¶
The current MOI backend returns the negative dot product for INNER_PRODUCT(a, b): -(a · b). The mathematical dot product of [1,2,3] and [4,5,6] is 32, while the function returns -32. For [1,2,3] against itself, it returns -14.
Run this read-only query on the target instance to confirm the sign for its backend version:
SELECT INNER_PRODUCT('[1,2,3]', '[4,5,6]') AS negative_dot_product,
INNER_PRODUCT('[1,2,3]', '[1,2,3]') AS self_negative_dot_product;
Under the negative-dot-product convention, the results are -32 and -14. For similarity retrieval, use ORDER BY INNER_PRODUCT(vector_column, query_vector) ASC: a larger mathematical dot product produces a smaller negative dot product. Use -INNER_PRODUCT(a, b) when you need the mathematical dot product.
Run the constant query again after changing backend versions before choosing the sort direction. If it returns positive values, that instance uses a different sign convention and the ASC ordering above does not apply directly.