[MDEV-11928] Session charset applied to COLUMN_GET() first arg in 10.1 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.1.20
Fix Version/s: 10.1.21
Component/s: Dynamic Columns
Labels:
None

Sprint:
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)

Attachments

Activity

Ascending order - Click to sort in descending order

Tom created issue - 2017-01-27 19:13

Tom made changes - 2017-01-27 19:14

Field	Original Value	New Value
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. h2. The gist of it {code:sql} SELECT 2 * COLUMN_GET( COLUMN_GET( COLUMN_CREATE('a', COLUMN_CREATE('b', 123)), 'a' AS char ), 'b' AS char ) {code} returns 246 in 10.0 and -64 in 10.1. I'm guessing this was added to the docs as resolution for ~~MDEV-9429~~: {quote} 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)... {quote} 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\|https://github.com/tom--/yii2-dynamic-ar/issues/19] that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The [integer value of the expression](testCustomColumnsExpression) 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 h2. 1. Before upgrade In good-old 10.0, char was understood when it was inside a {code} 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) {code} h2. 2. After upgrade Now we need to do more work in our ORM {code} 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) {code}	I added this as a comment to ~~MDEV-9429~~ and then realized it wasn't going to be seen and was in fact different. h2. The gist of it {code:sql} SELECT 2 * COLUMN_GET( COLUMN_GET( COLUMN_CREATE('a', COLUMN_CREATE('b', 123)), 'a' AS char ), 'b' AS char ) {code} returns 246 in 10.0 and -64 in 10.1. I'm guessing this was added to the docs as resolution for ~~MDEV-9429~~: {quote} 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)... {quote} 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\|https://github.com/tom--/yii2-dynamic-ar/issues/19] that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The [integer value of the expression](https://github.com/tom--/yii2-dynamic-ar/blob/d379e4831b05aea7cae163311773b7a4d29319cb/tests/unit/DynamicActiveRecordTest.php#L367-L387) 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 h2. 1. Before upgrade In good-old 10.0, char was understood when it was inside a {code} 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) {code} h2. 2. After upgrade Now we need to do more work in our ORM {code} 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) {code}

Tom made changes - 2017-01-27 19:15

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.

h2. The gist of it

{code:sql}
SELECT 2 * COLUMN_GET(
    COLUMN_GET(
        COLUMN_CREATE('a', COLUMN_CREATE('b', 123)),
        'a' AS char
    ),
    'b' AS char
)
{code}

returns 246 in 10.0 and -64 in 10.1.

I'm guessing this was added to the docs as resolution for ~~MDEV-9429~~:

{quote}
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)...
{quote}

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|https://github.com/tom--/yii2-dynamic-ar/issues/19] that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The [integer value of the expression](https://github.com/tom--/yii2-dynamic-ar/blob/d379e4831b05aea7cae163311773b7a4d29319cb/tests/unit/DynamicActiveRecordTest.php#L367-L387) 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

h2. 1. Before upgrade

In good-old 10.0, char was understood when it was inside a

{code}
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)
{code}

h2. 2. After upgrade

Now we need to do more work in our ORM

{code}
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)
{code}

I added this as a comment to ~~MDEV-9429~~ and then realized it wasn't going to be seen and was in fact different.

h2. The gist of it

{code:sql}
SELECT 2 * COLUMN_GET(
    COLUMN_GET(
        COLUMN_CREATE('a', COLUMN_CREATE('b', 123)),
        'a' AS char
    ),
    'b' AS char
)
{code}

returns 246 in 10.0 and -64 in 10.1.

I'm guessing this was added to the docs as resolution for ~~MDEV-9429~~:

{quote}
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)...
{quote}

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|https://github.com/tom--/yii2-dynamic-ar/issues/19] that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The [integer value of the expression|https://github.com/tom--/yii2-dynamic-ar/blob/d379e4831b05aea7cae163311773b7a4d29319cb/tests/unit/DynamicActiveRecordTest.php#L367-L387] 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

h2. 1. Before upgrade

In good-old 10.0, char was understood when it was inside a

{code}
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)
{code}

h2. 2. After upgrade

Now we need to do more work in our ORM

{code}
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)
{code}

Elena Stepanova made changes - 2017-02-01 18:11

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2017-02-01 18:11

Fix Version/s

10.1 [ 16100 ]

Elena Stepanova added a comment - 2017-02-01 18:11

Thanks for the report.

Elena Stepanova added a comment - 2017-02-01 18:11 Thanks for the report.

Elena Stepanova made changes - 2017-02-01 18:11

Assignee

Oleksandr Byelkin [ sanja ]

Rasmus Johansson (Inactive) made changes - 2017-03-02 12:09

Sprint

10.1.22 [ 143 ]

Rasmus Johansson (Inactive) made changes - 2017-03-02 12:09

Rank

Ranked lower

Oleksandr Byelkin made changes - 2017-03-03 09:57

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2017-03-03 10:01

It looks like signed/unsigned problem. Actually converting number to character and back looks for me dangerous in any case.

Oleksandr Byelkin added a comment - 2017-03-03 10:01 It looks like signed/unsigned problem. Actually converting number to character and back looks for me dangerous in any case.

Oleksandr Byelkin added a comment - 2017-03-03 10:31

So problem is probably in setup which changed:
show variables like 'version';
Variable_name Value
version 10.1.20-MariaDB-debug
SELECT 2 * COLUMN_GET(
COLUMN_GET(
COLUMN_CREATE('a', COLUMN_CREATE('b', 123)),
'a' AS char
),
'b' AS char
);
2 * COLUMN_GET(
COLUMN_GET(
COLUMN_CREATE('a', COLUMN_CREATE('b', 123)),
'a' AS char
),
'b' AS char
)
246

The problem in your setup that it can't make utf-8 character from number. So I need to know what configuration used to reproduce the problem. or you will see yourself what to change to fix if you really need such dangerous tricks.

Oleksandr Byelkin added a comment - 2017-03-03 10:31 So problem is probably in setup which changed: show variables like 'version'; Variable_name Value version 10.1.20-MariaDB-debug SELECT 2 * COLUMN_GET( COLUMN_GET( COLUMN_CREATE('a', COLUMN_CREATE('b', 123)), 'a' AS char ), 'b' AS char ); 2 * COLUMN_GET( COLUMN_GET( COLUMN_CREATE('a', COLUMN_CREATE('b', 123)), 'a' AS char ), 'b' AS char ) 246 The problem in your setup that it can't make utf-8 character from number. So I need to know what configuration used to reproduce the problem. or you will see yourself what to change to fix if you really need such dangerous tricks.

Oleksandr Byelkin made changes - 2017-03-03 10:36

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2017-03-07 12:48

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2017-03-07 13:57

It looks like a bug in 10.0, it do not react on charset properly. Correct assignment should be with charset mentioning:
SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char charset latin1), 'int' AS char charset latin1) * 2);

but 10.0 ignore them and even following processed as latin1:
SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char charset utf8), 'int' AS char charset utf8) * 2);

Oleksandr Byelkin added a comment - 2017-03-07 13:57 It looks like a bug in 10.0, it do not react on charset properly. Correct assignment should be with charset mentioning: SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char charset latin1), 'int' AS char charset latin1) * 2); but 10.0 ignore them and even following processed as latin1: SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char charset utf8), 'int' AS char charset utf8) * 2);

Tom added a comment - 2017-03-07 14:25

Please confirm my understanding. We can avoid the dangers you mention if we access nested dynamic column values using AS binary in all but the outermost COLUMN_GET()

Thus, for example

COLUMN_GET(

    COLUMN_GET(

        COLUMN_GET(

            `my_blob_column`,

            'foo' AS binary

),

        'bar' AS binary

),

    'baz' AS whatever

Where whatever is some type suitable to the app.

I made this assumption in the fix to the ORM

Tom added a comment - 2017-03-07 14:25 Please confirm my understanding. We can avoid the dangers you mention if we access nested dynamic column values using AS binary in all but the outermost COLUMN_GET() Thus, for example COLUMN_GET( COLUMN_GET( COLUMN_GET( `my_blob_column`, 'foo' AS binary ), 'bar' AS binary ), 'baz' AS whatever ) Where whatever is some type suitable to the app. I made this assumption in the fix to the ORM

Oleksandr Byelkin added a comment - 2017-03-07 15:09

Thanks Alexandr Barkov for finding what changed in 10.1, it was fix of ~~MDEV-7661~~ which bring correct behavior.

To make make conversion number->symbol stable and unreliable on external changes it is better to mention charset explicitly or in any other way (SET NAMES for examples) to set such charset where number you want has sens as a character, otherwise (like with number 123 and utf8) it will be replaced with '?' (what we have here).

Oleksandr Byelkin added a comment - 2017-03-07 15:09 Thanks Alexandr Barkov for finding what changed in 10.1, it was fix of MDEV-7661 which bring correct behavior. To make make conversion number->symbol stable and unreliable on external changes it is better to mention charset explicitly or in any other way (SET NAMES for examples) to set such charset where number you want has sens as a character, otherwise (like with number 123 and utf8) it will be replaced with '?' (what we have here).

Oleksandr Byelkin added a comment - 2017-03-07 15:12

Yes, binary or latin1 or something where all numbers correspond to some characters (actually yes, binary probably is better)

Oleksandr Byelkin added a comment - 2017-03-07 15:12 Yes, binary or latin1 or something where all numbers correspond to some characters (actually yes, binary probably is better)

Oleksandr Byelkin added a comment - 2017-03-07 15:15

So I close the issue as it is actually consequence of bug fixed. If you have more questions ask and reopen it please.

Oleksandr Byelkin added a comment - 2017-03-07 15:15 So I close the issue as it is actually consequence of bug fixed. If you have more questions ask and reopen it please.

Oleksandr Byelkin made changes - 2017-03-07 15:15

Fix Version/s		10.1.21 [ 22113 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Won't Fix [ 2 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 79419 ]

MariaDB v4 [ 151618 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Tom

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-01-27 19:13

Updated:: 2017-03-07 15:15

Resolved:: 2017-03-07 15:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

The gist of it

1. Before upgrade

2. After upgrade

Attachments

Activity

People

Dates

Git Integration