[MDEV-11876] CONVERT produces a warning and result depends on strict mode Created: 2017-01-23  Updated: 2023-11-13

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Data types
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7635 update defaults and simplify mysqld c... Closed
relates to MDEV-19362 New STRICT flags for sql_mode Open

 Description    

MariaDB [test]> set sql_mode='';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select convert(_big5 0xC84041 using ucs2);
 
+------------------------------------+
 
| convert(_big5 0xC84041 using ucs2) |
 
+------------------------------------+
 
| ?A                                 |
 
+------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------------+
 
| Level   | Code | Message                                          |
 
+---------+------+--------------------------------------------------+
 
| Warning | 1977 | Cannot convert 'big5' character 0xC840 to 'ucs2' |
 
+---------+------+--------------------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> set sql_mode='STRICT_ALL_TABLES';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select convert(_big5 0xC84041 using ucs2);
 
+------------------------------------+
 
| convert(_big5 0xC84041 using ucs2) |
 
+------------------------------------+
 
| NULL                               |
 
+------------------------------------+
 
1 row in set, 1 warning (0.01 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------------+
 
| Level   | Code | Message                                          |
 
+---------+------+--------------------------------------------------+
 
| Warning | 1977 | Cannot convert 'big5' character 0xC840 to 'ucs2' |
 
+---------+------+--------------------------------------------------+
 
1 row in set (0.00 sec)

I don't know whether it should produce the warning, but in any case I don't think it should return different results in strict/non-strict mode while producing the same warning in both.

It started happening in 10.1.
MariaDB 5.5, 10.0, MySQL 5.5-5.7 produce the non-null result without any warnings regardless the strict mode.



 Comments   
Comment by Alexander Barkov [ 2017-01-23 ]

Sergei told on slack:
as we've discussed similar issues before, I don't think that STRICT_ALL_TABLES should affect anything when no tables are involved

Comment by Elena Stepanova [ 2017-01-23 ]

Similar thing with CHAR:

MariaDB [test]> set sql_mode='';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select hex(char(0x01020304 using utf32));
 
+-----------------------------------+
 
| hex(char(0x01020304 using utf32)) |
 
+-----------------------------------+
 
|                                   |
 
+-----------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+------------------------------------------+
 
| Level   | Code | Message                                  |
 
+---------+------+------------------------------------------+
 
| Warning | 1300 | Invalid utf32 character string: '010203' |
 
+---------+------+------------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select hex(char(0x01020304 using utf32));
 
+-----------------------------------+
 
| hex(char(0x01020304 using utf32)) |
 
+-----------------------------------+
 
| NULL                              |
 
+-----------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+------------------------------------------+
 
| Level   | Code | Message                                  |
 
+---------+------+------------------------------------------+
 
| Warning | 1300 | Invalid utf32 character string: '010203' |
 
+---------+------+------------------------------------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 07:53:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.