Bypass truncated data type error in `CREATE TABLE AS`
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.