MySQL Vector Datatype: create your operations (part 1)

MySQL 9.0.0 has brought the VECTOR datatype to your favorite Open Source Database.

There are already some functions available to deal with those vectors:

• STRING_TO_VECTOR()
• VECTOR_DIM()
• VECTOR_TO_STRING()

This post will show how to deal with vectors and create our own functions to create operations between vectors.

We will use the MLE Component capability to create JavaScript functions. JS stored procedures are available in MySQL HeatWave and MySQL Enterprise Edition. You can use MySQL EE for free while learning, developing, and prototyping. See here.

For more information regarding Javascript functions in MySQL, please check the following posts:

• Introducing JavaScript support in MySQL
• A Quick Introduction to JavaScript Stored Programs in MySQL
• Javascript support in MySQL: the UUID example
• Filtering JSON Arrays with JavaScript in MySQL
• Using the Global Intl JavaScript Object in MySQL
• More JavaScript in MySQL
• Sorting JSON Arrays in MySQL with JavaScript
• Debugging JavaScript Stored Functions in MySQL

For the Community Users, we will see in part 2 how to implement the same functions as UDF creating a component.

Operations

We will create a function for each of the 4 mathematical basic operators between two vectors of the same size: addition (+), subtraction (-), multiplication (x), and division (:).

All the functions will take two vectors represented by a string as arguments and return a string.

vector_addition_js

drop function if exists vector_addition_js;
create function vector_addition_js(a varchar(15000), b varchar(15000))
    returns varchar(15000) language javascript as $$
    const vec1 = JSON.parse(a)
    const vec2 = JSON.parse(b)
    if (vec1.length !== vec2.length) {
        throw new Error("Vectors must have the same dimension")
    }
    const result = []
    let i = 0
    while (i < vec1.length) {
        result.push(vec1[i] + vec2[i])
        i++
    }
    const resultStr = JSON.stringify(result)
    try {
        const parsedResult = JSON.parse(resultStr)
        return resultStr
    } catch (error) {
        throw new Error("Invalid vector conversion")
    }

$$;

Let’s test it:

SQL> select vector_addition_js('[1,2,3]','[4,5,6]');
+-----------------------------------------+
| vector_addition_js('[1,2,3]','[4,5,6]') |
+-----------------------------------------+
| [5,7,9]                                 |
+-----------------------------------------+
1 row in set (0.0053 sec)

Let’s create a table with the VECTOR datatype to test with our function:

SQL> create table t1 (
        id int unsigned auto_increment primary key, 
        vec1 vector, 
        vec2 vector
     );
SQL> insert into t1 values
     (0,string_to_vector('[4,9,1]'),string_to_vector('[2,3,1]'));
SQL> insert into t1 values
     (0,string_to_vector('[0,36.6,144]'),string_to_vector('[2,6,12]'));
SQL> select * from t1;
+----+----------------------------+----------------------------+
| id | vec1                       | vec2                       |
+----+----------------------------+----------------------------+
|  1 | 0x00008040000010410000803F | 0x00000040000040400000803F |
|  2 | 0x000000006666124200001043 | 0x000000400000C04000004041 |
+----+----------------------------+----------------------------+
2 rows in set (0.0027 sec)

Let’s try our function to add vec1 to vec2 for each row:

SQL> select id, vector_addition_js(
           vector_to_string(vec1), vector_to_string(vec2)
     ) vec3 from t1;
+----+--------------+
| id | vec3         |
+----+--------------+
|  1 | [6,12,2]     |
|  2 | [2,42.6,156] |
+----+--------------+
2 rows in set (0.0015 sec)

vector_subtraction_js

The function is almost the same, we replace the sign + with –:

drop function if exists vector_subtraction_js;
create function vector_subtraction_js(a varchar(15000), b varchar(15000))
    returns varchar(15000) language javascript as $$
    const vec1 = JSON.parse(a)
    const vec2 = JSON.parse(b)
    if (vec1.length !== vec2.length) {
        throw new Error("Vectors must have the same dimension")
    }
    const result = []
    let i = 0
    while (i < vec1.length) {
        result.push(vec1[i] - vec2[i])
        i++
    }
    const resultStr = JSON.stringify(result)
    try {
        const parsedResult = JSON.parse(resultStr)
        return resultStr
    } catch (error) {
        throw new Error("Invalid vector conversion")
    }

$$;

Let’s try it:

SQL> select id, 
     vector_subtraction_js(
       vector_to_string(vec1), vector_to_string(vec2)
     ) vec3 from t1;
+----+---------------+
| id | vec3          |
+----+---------------+
|  1 | [2,6,0]       |
|  2 | [-2,30.6,132] |
+----+---------------+
2 rows in set (0.0068 sec)

vector_multiplication_js

You have the principle now and should create the function by yourself:

SQL> select id, vector_multiplication_js(
         vector_to_string(vec1), vector_to_string(vec2)
     ) vec3 from t1;
+----+-----------------------------+
| id | vec3                        |
+----+-----------------------------+
|  1 | [8,27,1]                    |
|  2 | [0,219.60000000000002,1728] |
+----+-----------------------------+
2 rows in set (0.0029 sec)

SQL> select vector_multiplication_js('[1,2,3]','[0,0,0]');
+-----------------------------------------------+
| vector_multiplication_js('[1,2,3]','[0,0,0]') |
+-----------------------------------------------+
| [0,0,0]                                       |
+-----------------------------------------------+
1 row in set (0.0011 sec)

vector_division_js

This time we add a check to not accept division by zero in the while loop:

while (i < vec1.length) {
        if (vec2[i] == 0) {
           throw new Error("Division by zero is undefined")
        }
        result.push(vec1[i] / vec2[i])
        i++
    }

We can test the function using again the same records in our table:

SQL> select id, vector_division_js(
        vector_to_string(vec1), vector_to_string(vec2)
     ) vec3 from t1;
+----+---------------------------+
| id | vec3                      |
+----+---------------------------+
|  1 | [2,3,1]                   |
|  2 | [0,6.1000000000000005,12] |
+----+---------------------------+
2 rows in set (0.0028 sec)

SQL> select id, vector_division_js(
         vector_to_string(vec2), vector_to_string(vec1)
     ) vec3 from t1;
ERROR: 6113: JavaScript> Error: Division by zero is undefined

Conclusion

The support of the VECTOR datatype is a nice step further in MySQL and as you can see, it’s quite straightforward to use them and create any function you need around vectors.

This is a wonderful addition to MySQL and certainly to MySQL EE and HeatWave, where it is very easy to extend MySQL with any type of function coded in JavaScript.

Enjoy MySQL & the VECTOR datatype and happy coding your JS functions!