[MDEV-13790] UNHEX() of a somewhat complicated CONCAT() returns NULL Created: 2017-09-13  Updated: 2018-10-15  Resolved: 2018-01-30

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0, 10.1, 10.2.8, 10.2
Fix Version/s: 10.0.34, 10.1.31, 10.2.13, 10.3.5

Type: Bug Priority: Major
Reporter: dave madden Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian 9, compiled-from-source MariaDB

Issue Links:
Blocks
is blocked by MDEV-15118 ExtractValue(xml,something_complex) d... Closed
Duplicate
duplicates MDEV-13793 Wrong result with combination of CONC... Closed
Relates
relates to MDEV-13119 Wrong results with CAST(AS CHAR) and ... Closed
relates to MDEV-13120 Wrong results with MAKE_SET() and sub... Closed
Sprint: 10.1.29, 10.1.31

 Description   

I have a SQL script that works on MySQL (up to current versions). I built a copy of MariaDB-10.2.8 from source on a Linux machine and got the database up and running. However, my SQL script fails, because MariaDB is returning NULL from an UNHEX() call, where it shouldn't.

The call is producing a 20-byte random binary string in a particular format (it's a BitTorrent node ID). I concatenate some required bytes with some random bytes, with certain bytes being limited to a particular range of values. These are constructed as a 40-character hex string, which I then run through UNHEX().

The SQL is:

unhex( concat( '414C2',
 
                        hex( 8 + round( rand() * 7 ) ),
 
                        substr( sha( uuid( ) ), 6, 33 ),
 
                        hex( 2 + round( rand( ) * 8 ) ) ) )

Running this (with select) returns NULL. Running just the concat() returns a hex string, and running unhex() on the hex string presented as a literal returns the expected binary gobbeldygook.

 Comments   
Comment by dave madden [ 2017-09-13 ]

Sorry, I'm not familiar with Jira...which bug report should I watch for information about this? MDEV-13793 has a smaller test case and clearer description, but Jira says that bug is closed...

Comment by Sergei Golubchik [ 2017-09-13 ]

This one. The later one is marked a duplicate (note "Resolution: Duplicate") of this.

Comment by dave madden [ 2017-09-14 ]

Problem is at item_strfunc.cc:606, when concat'ing a substr()'d string. In particuar, when operand res2 is a substr of str, and you copy res into str, you may nuke the part of str that you still need to append.

An easy way to fix this seems to be to check for res2 being a substr of str, and copying it out to a temp string, but that's an ugly, single-purpose hack. Unfortunately, I don't quite understand everything that's going on in Item_fuc_concat::val_str(String *) yet.

I'll keep looking at it...

Comment by dave madden [ 2017-09-14 ] 

*** mariadb-10.2.8-orig/sql/item_strfunc.cc	2017-08-17 03:05:40.000000000 -0700
 
--- mariadb-10.2.8/sql/item_strfunc.cc	2017-09-13 22:30:41.334717295 -0700
 
***************
 
*** 603,608 ****
 
--- 603,614 ----
 
  	  str->replace(0,0,*res);
 
  	else
 
  	{
 
+         String very_temp;	// This is an ugly hack to fix MDEV-13790 -- dhm.
 
+         
+         if (str->ptr( ) <= res2->ptr( ) && str->ptr( ) + str->alloced_length( ) >= res2->ptr( )) {
 
+             very_temp.copy( *res2 );
 
+             res2 = &very_temp;
 
+         }
 
  	  str->copy(*res);
 
  	  str->append(*res2);
 
  	}

Comment by Alexander Barkov [ 2017-11-03 ]

A how-to-repeat script:

SELECT UNHEX(CONCAT('414C2', HEX(8 + ROUND(RAND()*7)), SUBSTR(SHA(UUID()),6,33),HEX(2+ROUND(RAND()*8)))) IS NULL AS c1;


+----+
 
| c1 |
 
+----+
 
|  1 |
 
+----+

The expected result is 0.

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