Details
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.
Attachments
Issue Links
- duplicates
-
MDEV-13793 Wrong result with combination of CONCAT,SUBSTR,SHA
-
- Closed
-
- is blocked by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
This one. The later one is marked a duplicate (note "Resolution: Duplicate") of this.
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...
*** 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);
|
}
|
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.
Sorry, I'm not familiar with Jira...which bug report should I watch for information about this?
MDEV-13793has a smaller test case and clearer description, but Jira says that bug is closed...