[MDEV-28643] view protocol fails due to different column name Created: 2022-05-23  Updated: 2022-12-02  Resolved: 2022-12-02

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.3.38

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: view-protocol

Attachments: File v1.frm    
Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open

 Description   

Query:

SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED);

gives result:

SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED);
 
CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED)
 
0
 
Warnings:
 
Warning	1292	Truncated incorrect INTEGER value: 'test'

Query ('FF' is absent at the beginning of the value ):

SELECT CONVERT(DES_DECRYPT(0x0dc9fc9537ca75f4, CHAR('1' using ucs2)), UNSIGNED);

gives result:

SELECT CONVERT(DES_DECRYPT(0x0dc9fc9537ca75f4, CHAR('1' using ucs2)), UNSIGNED);
 
CONVERT(DES_DECRYPT(0x0dc9fc9537ca75f4, CHAR('1' using ucs2)), UNSIGNED)
 
0
 
Warnings:
 
Warning	1292	Truncated incorrect INTEGER value: '\x0D\xC9\xFC\x957\xCAu\xF4'

The view (like first 'select'):

CREATE VIEW v1 AS SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED);
 
SELECT * FROM v1;
 
DROP VIEW v1;

gives text in warning as second query not like first:

CREATE VIEW v1 AS SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED);
 
SELECT * FROM v1;
 
Name_exp_1
 
0
 
Warnings:
 
Warning	1292	Truncated incorrect INTEGER value: '\x0D\xC9\xFC\x957\xCAu\xF4'



 Comments   
Comment by Oleksandr Byelkin [ 2022-12-02 ]

FF missed due to truncation (see warning), name of the column differ due to view accept only correct for table field name

Comment by Oleksandr Byelkin [ 2022-12-02 ] 

--- a/mysql-test/main/func_encrypt_ucs2.result
 
+++ b/mysql-test/main/func_encrypt_ucs2.result
 
@@ -9,11 +9,11 @@ CONVERT(DES_ENCRYPT(0, CHAR('1' USING ucs2)),UNSIGNED)
 
 0
 
 Warnings:
 
 Warning        1292    Truncated incorrect INTEGER value: '\xFFT\xDCiK\x92j\xE6\xFC'
 
-SELECT CHAR_LENGTH(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' USING ucs2)));
 
-CHAR_LENGTH(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' USING ucs2)))
 
+SELECT CHAR_LENGTH(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' USING ucs2))) as a;
 
+a
 
 4
 
-SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED);
 
-CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED)
 
+SELECT CONVERT(DES_DECRYPT(0xFF0DC9FC9537CA75F4, CHAR('1' using ucs2)), UNSIGNED) as a;
 
+a
 
 0
 
 Warnings:
 
 Warning        1292    Truncated incorrect INTEGER value: 'test'
 
diff --git a/mysql-test/main/func_encrypt_ucs2.test b/mysql-test/main/func_encrypt_ucs2.test
 
index 6c1306955aa..088af7ffee7 100644
 
--- a/mysql-test/main/func_encrypt_ucs2.test
 
+++ b/mysql-test/main/func_encrypt_ucs2.test
 
@@ -8,8 +8,7 @@
 
 SELECT CHAR_LENGTH(DES_ENCRYPT(0, CHAR('1' USING ucs2)));
 
 SELECT CONVERT(DES_ENCRYPT(0, CHAR('1' USING ucs2)),UNSIGNED);
 
 
-#enable after fix MDEV-28643, MDEV-27871
 
 --disable_view_protocol

Generated at Thu Feb 08 10:02:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.