Hexadecimal concatenation in recursive CTE is truncated to 64 characters.

[Felipe Lorenzo]

I'm working on Nested Set Hierarchies. I'm using a recursive CTE to build the nested equivalent of adjacency list table. MySQL 8.4 is my platform.

CREATE TABLE dct_node_adjc
(
hdct_node_adjc BIGINT UNSIGNED NOT NULL DEFAULT (UUID_SHORT()),
hdct BIGINT UNSIGNED NOT NULL,
hdct_node_adjc_parent BIGINT UNSIGNED NULL,
CONSTRAINT PRIMARY KEY (hdct_node_adjc),
CONSTRAINT FOREIGN KEY (hdct_node_adjc_parent)
REFERENCES dct_node_adjc(hdct_node_adjc) ON UPDATE CASCADE
);

INSERT INTO dct_node_adjc (hdct_node_adjc, hdct, hdct_node_adjc_parent)
VALUES
(101283669313847302, 101283669313847296, NULL),
(101324752823517184, 101283669313847297, 101283669313847302),
(101283669313847303, 101283669313847298, 101283669313847302),
(101324752823517185, 101283669313847298, 101324752823517184),
(101283669313847304, 101283669313847299, 101283669313847303),
(101324752823517186, 101283669313847299, 101324752823517185),
(101283669313847305, 101283669313847301, 101283669313847304),
(101324752823517187, 101283669313847301, 101324752823517186),
(101283669313847311, 101283669313847306, NULL),
(101283669313847312, 101283669313847307, 101283669313847311),
(101283669313847313, 101283669313847308, 101283669313847312),
(101283669313847314, 101283669313847309, 101283669313847313),
(101283669313847315, 101283669313847310, 101283669313847314),
(101283669313847320, 101283669313847316, NULL),
(101283669313847321, 101283669313847317, 101283669313847320),
(101283669313847322, 101283669313847318, 101283669313847321),
(101283669313847323, 101283669313847319, 101283669313847322);

-- nested set table
DROP TABLE IF EXISTS dct_node_nest;

CREATE TABLE dct_node_nest
(
hdct_node_adjc BIGINT UNSIGNED NOT NULL,
hdct_node_adjc_parent BIGINT UNSIGNED NULL,
hlevel INT NOT NULL DEFAULT 0,
LeftBower INT NOT NULL DEFAULT 0,
RightBower INT NOT NULL DEFAULT 0,
NodeNumber INT NOT NULL DEFAULT 0,
NodeCount INT NOT NULL DEFAULT 0,
SortPath VARBINARY(8000),
CONSTRAINT UNIQUE KEY (hdct_node_adjc)
);

Using recursive cte, to build the SortPath VARBINARY column for the final table.

SET SESSION sql_mode = '';

WITH RECURSIVE cte AS (
SELECT a.hdct_node_adjc, a.hdct_node_adjc_parent
, 1 AS hlevel, 0 AS LeftBower, 0 AS RightBower, 0 AS NodeNumber , 0 AS NodeCount
, UNHEX(HEX(a.hdct_node_adjc)) AS SortPath
FROM dct_node_adjc AS a WHERE a.hdct_node_adjc_parent IS NULL
UNION ALL
SELECT r.hdct_node_adjc, r.hdct_node_adjc_parent
, c.hlevel + 1 AS hlevel, c.LeftBower, c.RightBower, c.NodeNumber, c.NodeCount
, CONCAT(c.SortPath, UNHEX(HEX(r.hdct_node_adjc))) AS SortPath
FROM dct_node_adjc AS r JOIN cte AS c ON c.hdct_node_adjc = r.hdct_node_adjc_parent
)
SELECT c.hdct_node_adjc, c.hlevel, c.SortPath FROM cte AS c;

SET SESSION sql_mode = 'TRADITIONAL';

-- output
+--------------------+--------+--------------------------------------------------------------------+
| hdct_node_adjc | hlevel | SortPath |
+--------------------+--------+--------------------------------------------------------------------+
| 101283669313847302 | 1 | 0x0167D4F5EB000006 |
| 101283669313847311 | 1 | 0x0167D4F5EB00000F |
| 101283669313847320 | 1 | 0x0167D4F5EB000018 |
| 101283669313847303 | 2 | 0x0167D4F5EB0000060167D4F5EB000007 |
| 101324752823517184 | 2 | 0x0167D4F5EB0000060167FA536B000000 |
| 101283669313847312 | 2 | 0x0167D4F5EB00000F0167D4F5EB000010 |
| 101283669313847321 | 2 | 0x0167D4F5EB0000180167D4F5EB000019 |
| 101283669313847304 | 3 | 0x0167D4F5EB0000060167D4F5EB0000070167D4F5EB000008 |
| 101324752823517185 | 3 | 0x0167D4F5EB0000060167FA536B0000000167FA536B000001 |
| 101283669313847313 | 3 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB000011 |
| 101283669313847322 | 3 | 0x0167D4F5EB0000180167D4F5EB0000190167D4F5EB00001A |
| 101283669313847305 | 4 | 0x0167D4F5EB0000060167D4F5EB0000070167D4F5EB0000080167D4F5EB000009 |
| 101324752823517186 | 4 | 0x0167D4F5EB0000060167FA536B0000000167FA536B0000010167FA536B000002 |
| 101283669313847314 | 4 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB0000110167D4F5EB000012 |
| 101283669313847323 | 4 | 0x0167D4F5EB0000180167D4F5EB0000190167D4F5EB00001A0167D4F5EB00001B |
| 101324752823517187 | 5 | 0x0167D4F5EB0000060167FA536B0000000167FA536B0000010167FA536B000002 |
| 101283669313847315 | 5 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB0000110167D4F5EB000012 |
+--------------------+--------+--------------------------------------------------------------------+
17 rows in set (0.02 sec)



Zeros in the select_expr are place holder for later construct. Only the essential columns is selected for testing. The INTO dct_node_nest statement is also not included.

I've tried cast, convert to binary since VARBINARY is rejected by the parser.

Then use of UNHEX(HEX(hdct_node_adjc )) worked fine to get the 8 byte value, and append the subsequent node id - hdct_node_adjc.

Problem: only up to 64 characters (4 id's) is displayed. The 5th id is dropped in rows with hlevel of 5. Why?