0

I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list one at a time and then rebuild the nested sets at certain time intervals).

However, I've hit a snag with this bit:

SortPath =  CAST(
                    CAST(anchor.EmployeeID AS BINARY(4)) 
                AS VARBINARY(4000)) --Up to 1000 levels deep.

I've switched it to this:

CAST(CAST(anchor.EmployeeID AS BINARY(4)) AS BINARY(4000)) AS sort_path ## Also with 8000

I also tried CAST(anchor.EmployeeID AS Binary(4)) AS sort_path

All approaches have thus far yielded a variant of this error message:

Truncated incorrect DOUBLE value: '1\x00\x00\x00'

EDIT

The full sql statement being run is this:

##===== Conditionally drop Temp tables to make reruns easy
DROP TABLE IF EXISTS hierarchy;

##===== Build the new table on-the-fly including some place holders
CREATE TABLE hierarchy AS

WITH RECURSIVE cteBuildPath AS 
( ##=== This is the "anchor" part of the recursive CTE.
     ## The only thing it does is load the Root Node.
 SELECT anchor.tree_user_id, 
        anchor.upline_id, 
        1 AS hlevel,
        CAST(anchor.tree_user_id AS BINARY(4)) AS sort_path ##Up to 1000 levels deep.
  FROM example.tree AS anchor
  WHERE upline_id IS NULL ##Only the Root Node has a NULL ManagerID
  UNION ALL 
 ##==== This is the "recursive" part of the CTE that adds 1 for each level
     ## and concatenates each level of EmployeeID's to the SortPath column.  
 SELECT recur.tree_user_id, 
        recur.upline_id, 
        cte.HLevel + 1,
        cte.sort_path + CAST(recur.tree_user_id AS BINARY(4))
   FROM example.tree AS recur
  INNER JOIN cteBuildPath AS cte 
          ON cte.tree_user_id = recur.upline_id
) ##=== This final SELECT/INTO creates the Node # in the same order as a
     ## push-stack would. It also creates the final table with some
     ## "reserved" columns on the fly. We'll leave the SortPath column in
     ## place because we're still going to need it later.
     ## The ISNULLs make NOT NULL columns.
 SELECT IFNULL(sorted.tree_user_id,0) AS tree_user_id,
        sorted.upline_id,
        IFNULL(sorted.hlevel,0) AS hlevel,
      #  IFNULL(CAST(0 AS INT),0) AS left_bower, ##Place holder
      #  IFNULL(CAST(0 AS INT),0) AS right_bower, ##Place holder
      (@n := @n + 1) AS node_number,
      #  IFNULL(CAST(0 AS INT),0) AS node_count, ##Place holder
        IFNULL(sorted.sort_path,sorted.sort_path) AS sort_path
   FROM cteBuildPath AS sorted
   JOIN (SELECT @n := 0) n ON 1=1
   #ORDER BY sorted.sort_path
 #OPTION (MAXRECURSION 100) ##Change this IF necessary
;

What is the correct way to duplicate the desired logic in MariaDB 10.2?

  • I'd recommend editing the question to include the table CREATE statement for the relevant table(s), i.e. any table you are selecting from as well as any you're writing to. As well as a full SQL statement (SELECT?) for the 'snag bit'. – dbdemon Apr 9 '18 at 10:05
  • I'm not sure where the double is coming from ....? BINARY(4) is four bytes, so can store an INT, but not a BIGINT. What is the data type of example.tree.tree_user_id? Maybe try BINARY(8) or BINARY(16) instead, just to be sure. (And in both the places you use CAST.) – dbdemon Apr 9 '18 at 15:12
0

After much googling, I came to realize that the error message was exactly correct though somewhat vague.

Truncated incorrect DOUBLE value: '1\x00\x00\x00' meant that somewhere in my query, my CAST BINARY was being converted back to a DOUBLE data type.

Once I figured that out, re-reading the query made me consider how this little gem was working: cte.sort_path + CAST(recur.tree_user_id AS BINARY(4)). Turns out, based on a small sample query that the BINARY value was being converted so addition could be performed.

Switching to CONCAT(cte.sort_path, CAST(recur.tree_user_id AS BINARY(8))) appears to have corrected the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.