Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.20
-
None
-
10.1.22
Description
I added this as a comment to MDEV-9429 and then realized it wasn't going to be seen and was in fact different.
The gist of it
SELECT 2 * COLUMN_GET( |
COLUMN_GET(
|
COLUMN_CREATE('a', COLUMN_CREATE('b', 123)), |
'a' AS char |
),
|
'b' AS char |
)
|
returns 246 in 10.0 and -64 in 10.1.
I'm guessing this was added to the docs as resolution for MDEV-9429:
If you are trying to get a nested dynamic column as a string use 'as BINARY' as the last argument of COLUMN_GET (otherwise problems with character set conversion and illegal symbols are possible)...
but the wording doesn't exactly apply. I am not trying to get a nested column as a string. I have a previously functioning ORM that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The integer value of the expression is a function of server version.
I don't understand how the server works but it appears the server was changed so that in
- column_get(column_get(…, 'foo' as char), …)
Maria 10.1 modifies the value of the 1st param of the outer column_get() by converting from the client charset (utf8) to binary? It's not clear to me:
- why it should introduce this conversion when the context doesn't call for it (friendly context-appropriate conversion is apparent elsewhere in mysql, e.g. 2 * "2" gives 4)
- how this change is an improvement
- why give a spurious integer answer (and a warning) instead of no answer or an error
1. Before upgrade
In good-old 10.0, char was understood when it was inside a
MariaDB [(none)]> show variables like 'version';
|
+---------------+--------------------------------+
|
| Variable_name | Value |
|
+---------------+--------------------------------+
|
| version | 10.0.27-MariaDB-1~trusty-wsrep |
|
+---------------+--------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2);
|
+-----------------------------------------------------------------------------------------------------------------+
|
| (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2) |
|
+-----------------------------------------------------------------------------------------------------------------+
|
| 246 |
|
+-----------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2);
|
+-------------------------------------------------------------------------------------------------------------------+
|
| (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2) |
|
+-------------------------------------------------------------------------------------------------------------------+
|
| 246 |
|
+-------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
2. After upgrade
Now we need to do more work in our ORM
MariaDB [(none)]> show variables like 'version';
|
+---------------+-----------------+
|
| Variable_name | Value |
|
+---------------+-----------------+
|
| version | 10.1.20-MariaDB |
|
+---------------+-----------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2);
|
+-----------------------------------------------------------------------------------------------------------------+
|
| (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2) |
|
+-----------------------------------------------------------------------------------------------------------------+
|
| -64 |
|
+-----------------------------------------------------------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
MariaDB [(none)]> SHOW WARNINGS;
|
+---------+------+---------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------+
|
| Warning | 1300 | Invalid utf8 character string: '\xF6' |
|
+---------+------+---------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2);
|
+-------------------------------------------------------------------------------------------------------------------+
|
| (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2) |
|
+-------------------------------------------------------------------------------------------------------------------+
|
| 246 |
|
+-------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|