Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.21
-
None
Description
It is not possible anymore to modify nested columns within a dynamic column. The needed "as blob" within the COLUMN_GET does now throw always errors:
For example this query:
UPDATE items SET attr=COLUMN_ADD(attr, "level1", COLUMN_ADD(COLUMN_GET(attr, "level1" as blob), "level2", "NEWVALUE")) WHERE id=1; |
Results in:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'blob), "level2", "NEWVALUE")) WHERE id=1' at line 1 |
Attachments
Activity
Well I don't know in which version this has worked. But there are posts indicating that this was possible and has worked:
http://dba.stackexchange.com/questions/91620/modify-a-value-inside-of-a-nested-dynamic-column
https://mariadb.atlassian.net/browse/MDEV-7606?jql=text%20~%20%22dynamic%20columns%22
This suggests that it was possible, I wonder if it is possible in any way to modify a nested column right now?
I don't think AS BLOB ever worked. Data types allowed in COLUMN_GET are explicitly listed in documentation, it's unlikely they had ever been wider.
However, I will assign it to sanja, who is an expert in dynamic columns, to confirm.
I can guess that serg in MDEV-7606 just gave an example of what should be simplified, without paying attention to specific data types; can't say anything about the answer in the stackoverflow article, maybe the person just pasted a wrong query (as they say, there had been a lot of trial/fails).
Using nested columns is of course still possible, as documented on the same KB page, but you need to use CHAR, not BLOB.
MariaDB [test]> create table t1 (dcol blob); |
Query OK, 0 rows affected (0.06 sec) |
|
|
MariaDB [test]> insert into t1 values ( column_create( 'level1', column_create('value', 1) ) ); |
Query OK, 1 row affected (0.05 sec)
|
|
|
MariaDB [test]> SELECT COLUMN_GET(COLUMN_GET(dcol, 'level1' as CHAR), 'value' as INT) FROM t1; |
+----------------------------------------------------------------+ |
| COLUMN_GET(COLUMN_GET(dcol, 'level1' as CHAR), 'value' as INT) | |
+----------------------------------------------------------------+ |
| 1 |
|
+----------------------------------------------------------------+ |
1 row in set (0.00 sec) |
BLOB is a field type and is not related to expressions. AS CHAR should work (it will be charset binary). Do you really faced some limitation of string expression?
To make it even more clear BLOB give no advantage over CHAR when it is in expression. There both are strings.
wzrdtales,
What do you mean by "anymore" and "now"? In which version did you see it work?