Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync
OneDrive's sync client cannot sync a file whose full path is too long.
- Difficulty: Hard
- Topics: Subqueries, Strings, Joins
- Dialect: MySQL
- Problem: #792
Problem statement
OneDrive's sync client cannot sync a file whose full path is too long. A OneDrive support engineer is building a report of the files that will fail, using a pilot limit of 60 characters.
Folders form a tree: a top-level folder has parent_id NULL. A file's full path is a /, then the names of every folder from the top level down to the file's folder, each followed by /, then the file name — for example /Documents/Projects/Plan.pptx. Return file_id, full_path, depth (the number of folders in the path) and path_length (characters in the full path) for every file whose path is longer than 60 characters. A path of exactly 60 syncs. Return the rows in any order.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: OneDriveFolder
| Column | Type |
|---|---|
| folder_id | int |
| parent_id | int |
| folder_name | varchar |
Primary key: folder_id.
One row per folder; parent_id is the folder it sits in, or NULL at the top level. The tree never loops, and it can be any number of levels deep.
Table: OneDriveFile
| Column | Type |
|---|---|
| file_id | int |
| folder_id | int |
| file_name | varchar |
Primary key: file_id.
One row per file; every file sits in a folder.
Examples
Example 1
OneDriveFolder
| folder_id | parent_id | folder_name |
|---|---|---|
| 1 | NULL | Documents |
| 2 | 1 | Projects |
| 3 | 2 | Falcon Launch |
| 4 | 3 | Customer Interviews |
| 5 | NULL | Pictures |
| 6 | 5 | Holi 2025 |
| 7 | 1 | Archive |
OneDriveFile
| file_id | folder_id | file_name |
|---|---|---|
| 10 | 4 | Retail Interview Notes.docx |
| 11 | 3 | Plan.pptx |
| 12 | 6 | Family Celebration in Jaipur.jpg |
| 13 | 3 | Launch Budget FY25 v2.xlsx |
| 14 | 3 | Launch Budget v3 Final.xlsx |
| 15 | 2 | Quarterly Partner Review With Fabrikam.pptx |
Output
| file_id | full_path | depth | path_length |
|---|---|---|---|
| 10 | /Documents/Projects/Falcon Launch/Customer Interviews/Retail Interview Notes.docx | 4 | 81 |
| 14 | /Documents/Projects/Falcon Launch/Launch Budget v3 Final.xlsx | 3 | 61 |
| 15 | /Documents/Projects/Quarterly Partner Review With Fabrikam.pptx | 2 | 63 |
How to solve Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync
A path is a breadcrumb: the names along the route from the root to the node, in order. The depth is not fixed, so a fixed chain of self-joins will always miss the deepest folders; a recursive CTE builds every folder's path one level at a time. The anchor takes the top-level folders, with path /Documents/; each step joins a folder to its parent's row and appends name/, so Falcon Launch becomes /Documents/Projects/Falcon Launch/ at depth 3. A file's full path is its folder's path followed by the file name.
One MySQL detail decides whether this works at all: a recursive CTE's column types come from the anchor member. If the anchor were just CONCAT('/', folder_name, '/'), the column would be as wide as the longest top-level name, and the deeper paths would be cut off, or the query would fail with "Data too long". Casting the anchor to CHAR(1000) gives the recursive rows room. The filter is strict, so Launch Budget FY25 v2.xlsx, at exactly 60 characters, is left out and the 61-character v3 Final is in.
The alternative walks upwards from each file, putting the next folder name in front of the path until the parent is NULL. Top-down builds each folder's path once and shares it among that folder's files. Bottom-up repeats the work for every file, but it would stop early if you only needed some of the files.
Reference solution (MySQL)
WITH RECURSIVE folder_path AS (
SELECT folder_id, CAST(CONCAT('/', folder_name, '/') AS CHAR(1000)) AS path, 1 AS depth
FROM OneDriveFolder
WHERE parent_id IS NULL
UNION ALL
SELECT f.folder_id, CONCAT(p.path, f.folder_name, '/'), p.depth + 1
FROM folder_path p
JOIN OneDriveFolder f ON f.parent_id = p.folder_id
)
SELECT d.file_id, CONCAT(p.path, d.file_name) AS full_path, p.depth,
CHAR_LENGTH(CONCAT(p.path, d.file_name)) AS path_length
FROM OneDriveFile d
JOIN folder_path p ON p.folder_id = d.folder_id
WHERE CHAR_LENGTH(CONCAT(p.path, d.file_name)) > 60Another way
WITH RECURSIVE climb AS (
SELECT file_id, folder_id AS at_folder, CAST(CONCAT('/', file_name) AS CHAR(1000)) AS tail, 0 AS depth
FROM OneDriveFile
UNION ALL
SELECT c.file_id, f.parent_id, CONCAT('/', f.folder_name, c.tail), c.depth + 1
FROM climb c
JOIN OneDriveFolder f ON f.folder_id = c.at_folder
)
SELECT file_id, tail AS full_path, depth, CHAR_LENGTH(tail) AS path_length
FROM climb
WHERE at_folder IS NULL AND CHAR_LENGTH(tail) > 60← Microsoft: Teams Attendees Present for at Least 75% of a Meeting · Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget →