Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
N/A
-
None
Description
Notes:
A part of it, or all of it, may be documentation-only notes, if so, please re-categorize it as such. It may come as a surprise to users that while all VECTOR(N) look like the same type of different length, they don't necessarily behave as such, so at least it should be documented.
For simplicity, I'll be using IGNORE and ALGORITHM=COPY clauses wherever applicable, to avoid variation in behavior between strict/non-strict mode and copying/non-copying algorithms. It is not essential to the main point of the report.
DML
If we have a traditional BINARY(N) field, then inserting a value shorter than N happens without any warnings or errors, and the data is padded, while inserting a longer value causes WARN_DATA_TRUNCATED ("Data truncated for column"), and the data is indeed truncated:
|
f5c717cd90418370807463dbfb95bcd05bed76dc |
MariaDB [test]> create or replace table t (v binary(8)); |
Query OK, 0 rows affected (0.050 sec) |
|
|
MariaDB [test]> insert ignore into t values (0x31313131),(0x323232323333333334343434); |
Query OK, 2 rows affected, 1 warning (0.013 sec) |
Records: 2 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [test]> show warnings;
|
+---------+------+----------------------------------------+ |
| Level | Code | Message | |
+---------+------+----------------------------------------+ |
| Warning | 1265 | Data truncated for column 'v' at row 2 | |
+---------+------+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [test]> select hex(v) from t; |
+------------------+ |
| hex(v) |
|
+------------------+ |
| 3131313100000000 |
|
| 3232323233333333 |
|
+------------------+ |
2 rows in set (0.001 sec) |
With VECTOR(N) field, inserting either a shorter or a longer value instead causes ER_TRUNCATED_WRONG_VALUE_FOR_FIELD ("Incorrect vector value"), and the data is not truncated/padded, but is replaced by the hard default value:
|
vector |
MariaDB [test]> create or replace table t (v vector(2)); |
Query OK, 0 rows affected (0.056 sec) |
|
|
MariaDB [test]> insert ignore into t values (0x31313131),(0x323232323333333334343434); |
Query OK, 2 rows affected, 2 warnings (0.014 sec) |
Records: 2 Duplicates: 0 Warnings: 2
|
|
|
MariaDB [test]> show warnings;
|
+---------+------+---------------------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+---------------------------------------------------------------------------+ |
| Warning | 1366 | Incorrect vector value: '1111' for column `test`.`t`.`v` at row 1 | |
| Warning | 1366 | Incorrect vector value: '222233334444' for column `test`.`t`.`v` at row 2 | |
+---------+------+---------------------------------------------------------------------------+ |
2 rows in set (0.000 sec) |
|
|
MariaDB [test]> select hex(v) from t; |
+------------------+ |
| hex(v) |
|
+------------------+ |
| 0000000000000000 |
|
| 0000000000000000 |
|
+------------------+ |
2 rows in set (0.001 sec) |
DDL
ALTER TABLE changing the column length, on the other hand, behaves in a similar way for the traditional BINARY and VECTOR – that is, unlike DML, it truncates/pads vector values rather than replacing them:
|
binary |
MariaDB [test]> create or replace table t (v binary(8)); |
Query OK, 0 rows affected (0.065 sec) |
|
|
MariaDB [test]> insert ignore into t values (0x3131313132323232); |
Query OK, 1 row affected (0.016 sec)
|
|
|
MariaDB [test]> alter ignore table t modify v binary(4), algorithm=copy; |
Query OK, 1 row affected, 1 warning (0.194 sec)
|
Records: 1 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [test]> show warnings;
|
+---------+------+----------------------------------------+ |
| Level | Code | Message | |
+---------+------+----------------------------------------+ |
| Warning | 1265 | Data truncated for column 'v' at row 1 | |
+---------+------+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [test]> select hex(v) from t; |
+----------+ |
| hex(v) |
|
+----------+ |
| 31313131 |
|
+----------+ |
1 row in set (0.016 sec) |
|
|
MariaDB [test]> alter ignore table t modify v binary(8), algorithm=copy; |
Query OK, 1 row affected (0.063 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select hex(v) from t; |
+------------------+ |
| hex(v) |
|
+------------------+ |
| 3131313100000000 |
|
+------------------+ |
1 row in set (0.006 sec) |
|
vector |
MariaDB [test]> create or replace table t (v vector(2)); |
Query OK, 0 rows affected (0.105 sec) |
|
|
MariaDB [test]> insert ignore into t values (0x3131313132323232); |
Query OK, 1 row affected (0.013 sec)
|
|
|
MariaDB [test]> alter ignore table t modify v vector(1); |
Query OK, 1 row affected (0.065 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select hex(v) from t; |
+----------+ |
| hex(v) |
|
+----------+ |
| 31313131 |
|
+----------+ |
1 row in set (0.018 sec) |
|
|
MariaDB [test]> alter ignore table t modify v vector(2), algorithm=copy; |
Query OK, 1 row affected (0.068 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select hex(v) from t; |
+------------------+ |
| hex(v) |
|
+------------------+ |
| 3131313100000000 |
|
+------------------+ |
1 row in set (0.007 sec) |
Attachments
Issue Links
- is caused by
-
MDEV-34939 vector search in 11.7
-
- Closed
-