Query Planner Fails to Validate Valid ABFSS Path with Wildcard (`**`)

[matteohexagon]

Subject: Query Planner Fails to Validate Valid ABFSS Path with Wildcard (**)

Component: Storage - Azure

Apache Drill Version: 1.22.0

Summary:

A SELECT query against a specific directory path on Azure Blob Storage (using the ABFSS connector) fails during the validation phase with an "Object not found" error. However, Drill's own file listing tools (SHOW FILES) can see and list the contents of the exact same path, and a global wildcard query can read the data successfully.

The issue appears to be a bug in the query planner's path validation logic. The planner seems to develop a "stuck" or "corrupted" state for certain directory names, refusing to acknowledge them in SELECT statements while other parts of Drill can access them without issue. The bug persists even after restarting the Drillbit and completely deleting/recreating the storage plugin.

Environment:

• Storage Plugin: file
• Connection Type: Azure Blob Storage (abfss://<container>@<account>.dfs.core.windows.net)
• Authentication: SharedKey

Storage Plugin Configuration:

{
  "type": "file",
  "enabled": true,
  "connection": "abfss://<container>@<account>.dfs.core.windows.net",
  "config": {
    "fs.azure.account.auth.type": "SharedKey",
    "fs.azure.account.key.observercondenseddata.dfs.core.windows.net": "...",
    "fs.azure.createRemoteFileSystemDuringInitialization": "false",
    "fs.azure.io.list.recursive": "true"
  },
  "workspaces": {
    "root": {
      "location": "/",
      "writable": false,
      "allowRecursiveScan": true
    },
    "monthly": {
       "location": "/prod-condenser-logs-1-Month/",
       "writable": false,
       "allowRecursiveScan": true
     },
     "daily": {
       "location": "/prod-condenser-logs-1-day/",
       "writable": false,
       "allowRecursiveScan": true
     },
     "hourly": {
       "location": "/prod-condenser-logs-1-hour/",
       "writable": false,
       "allowRecursiveScan": true
     }
  },
  "formats": {
    "log": {
      "type": "logRegex",
      "extension": "log",
      "regex": "^(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2},\\d{3}) - (\\w+) - (.*)|^(.+)",
      "maxErrors": 100000,
      "schema": [
        {"fieldName": "log_timestamp", "fieldType": "TIMESTAMP", "format": "yyyy-MM-dd HH:mm:ss,SSS"},
        {"fieldName": "log_level"},
        {"fieldName": "structured_message"},
        {"fieldName": "unstructured_line"}
      ]
    }
  }
}

Directory Structure on Azure:

/
├── prod-condenser-logs-1-Month/
│   └── 2025/
│       └── 07/
├── prod-condenser-logs-1-day/
│   └── 2025/
│       ├── 07/
│       └── 08/
└── prod-condenser-logs-1-hour/
    └── 2025/
        └── ...

Steps to Reproduce:

1. A query on a sibling directory works correctly: The following query against the ...-1-Month directory executes successfully every time.

   SELECT * FROM az.root.`prod-condenser-logs-1-Month/2025/**` LIMIT 10;

2. An identical query on the target directory fails: The following query against the ...-1-day directory consistently fails.

   SELECT * FROM az.root.`prod-condenser-logs-1-day/2025/**` LIMIT 10;

3. Drill's listing tools prove the path is visible: Contradicting the query failure, the SHOW FILES command can see and list the contents of the failing directory, proving the path is valid and accessible to Drill.

   -- This command SUCCEEDS and shows the '2025' directory within
   SHOW FILES FROM az.root.`prod-condenser-logs-1-day`;

Expected Behavior:

The SELECT query against az.root.prod-condenser-logs-1-day/2025/**`` should execute successfully, just as the query against the sibling ...-1-Month directory does.

Actual Behavior:

The query fails during the validation phase with the error:
VALIDATION ERROR: ... Object 'prod-condenser-logs-1-day/2025/**' not found within 'az.root'

Troubleshooting Steps Attempted (All Failed to Resolve the Issue):

• Restarting the Drillbit: The issue persists immediately after a full restart.
• Deleting and Recreating the Storage Plugin: The exact same behavior occurs after completely removing the az plugin and recreating it from the saved configuration.
• Renaming/Duplicating the Source Directory: Renaming the directory in Azure to a new name (e.g., prod-condenser-logs-daily-new) and querying it results in the same "Object not found" error.
• Using Defined Workspaces: Querying via the az.daily workspace (e.g., FROM az.daily.2025/**``) also fails with the same error, even though SHOW FILES IN az.daily correctly lists the contents.
• REFRESH TABLE METADATA: This command fails because Drill does not recognize the paths as tables.

Final Workaround Discovered:

The only reliable method to query the data in the affected directories is to use a global wildcard from the root (FROM az.root.**``) and then filter the desired path using a WHERE clause. This proves the data is readable and the bug is specific to the planner's path validation.

-- This query WORKS and returns data from the '...-1-day' directory
SELECT *
FROM az.root.`**`
WHERE filepath LIKE '%/prod-condenser-logs-1-day/%'
LIMIT 10;

This workaround suggests the core data reading engine is functional, but the upfront query validation is failing on specific path strings.

[cgivre]

@matteohexagon Have you tried running the query w/o the wildcard?

For example:

SELECT * FROM az.root.`prod-condenser-logs-1-day/2025/` LIMIT 10;

Drill's default behavior will be to query all the files in a directory so it is not really necessary to add the wildcard. Regardless, I've always used a single * when using a wildcard on a path and that has worked for me.

[matteohexagon]

Hi thanks for the feedback, but yes the problem persists.

Query run:

SELECT * FROM az.root.`prod-condenser-logs-1-day/2025/` LIMIT 10;

web UI:

UserRemoteException : 	VALIDATION ERROR: From line 1, column 15 to line 1, column 55: Object 'prod-condenser-logs-1-day/2025/' not found within 'az.root'
    
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 15 to line 1, column 55: Object 'prod-condenser-logs-1-day/2025/' not found within 'az.root'

log snippet:

2025-08-19 13:36:13,145 [175b8232-4550-7e92-b930-5d92195d2f3e:foreman] INFO  o.a.drill.exec.work.foreman.Foreman - Query text for query with id 175b8232-4550-7e92-b930-5d92195d2f3e issued by anonymous: SELECT * FROM az.root.`prod-condenser-logs-1-day/2025/` LIMIT 10
2025-08-19 13:36:13,490 [175b8232-4550-7e92-b930-5d92195d2f3e:foreman] INFO  o.a.d.e.p.s.conversion.SqlConverter - User Error Occurred: From line 1, column 15 to line 1, column 55: Object 'prod-condenser-logs-1-day/2025/' not found within 'az.root' (Fr
om line 1, column 15 to line 1, column 55: Object 'prod-condenser-logs-1-day/2025/' not found within 'az.root')
org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From line 1, column 15 to line 1, column 55: Object 'prod-condenser-logs-1-day/2025/' not found within 'az.root'


[Error Id: 6181cb16-d6d1-47ba-91c8-ca5c79f574b3 ]
        at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:688)
        at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:220)
[CUT]

[matteohexagon]

just FYI

result in this

[cgivre]

Sorry...
I also meant to ask if you have tried with the workspace:

SELECT * FROM az.daily.`*` LIMIT 10;

[matteohexagon]

Hi no problem,

yes i tried also witht he different workspaces and it doesn't work

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 15 to line 1, column 26: Object '*' not found within 'az.daily'

from the logs

2025-08-20 12:58:18,451 [175a3995-01d4-898d-357c-ff67db576691:foreman] INFO  o.a.drill.exec.work.foreman.Foreman - Query text for query with id 175a3995-01d4-898d-357c-ff67db576691 issued by anonymous: SELECT * FROM az.daily.`*` LIMIT 10
2025-08-20 12:58:18,819 [175a3995-01d4-898d-357c-ff67db576691:foreman] INFO  o.a.d.e.p.s.conversion.SqlConverter - User Error Occurred: From line 1, column 15 to line 1, column 26: Object '*' not found within 'az.daily' (From line 1, column 15 to line 
1, column 26: Object '*' not found within 'az.daily')
org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From line 1, column 15 to line 1, column 26: Object '*' not found within 'az.daily'


[Error Id: c5153dc8-daa0-43f7-be7b-a2a2b77dc821 ]
        at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:688)
        at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:220)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:662)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:298)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:179)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:129)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94)

whereas the show files:

show files FROM az.daily;

[cgivre]

A few more questions:

1. Have you tried querying a specific file within the daily folder?
2. Have you tried *.*?
3. I don't know if this will make any difference, but have you tried using wasbs in the URL instead of abfss? 1 2

[matteohexagon]

1. yes i did

SELECT * FROM az.daily.`/2025/07/10/condenseAll_202507/orchestrator.log` LIMIT 10;

works and it shows a result

2. yes also this doesn't work

UserRemoteException : 	VALIDATION ERROR: From line 1, column 15 to line 1, column 28: Object '*.*' not found within 'az.daily'
    
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 15 to line 1, column 28: Object '*.*' not found within 'az.daily'


[Error Id: c241b198-b7cc-4129-a884-9986e7c29857 ]

from the logs:

025-08-20 14:12:05,525 [175a284a-2341-64d2-0986-625449009312:foreman] INFO  o.a.drill.exec.work.foreman.Foreman - Query text for query with id 175a284a-2341-64d2-0986-625449009312 issued by anonymous: SELECT * FROM az.daily.`*.*` LIMIT 10
2025-08-20 14:12:05,609 [175a284a-2341-64d2-0986-625449009312:foreman] INFO  o.a.d.e.p.s.conversion.SqlConverter - User Error Occurred: From line 1, column 15 to line 1, column 28: Object '*.*' not found within 'az.daily' (From line 1, column 15 to line 1, column 28: Object '*.*' not found within 'az.daily')
org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From line 1, column 15 to line 1, column 28: Object '*.*' not found within 'az.daily'


[Error Id: c241b198-b7cc-4129-a884-9986e7c29857 ]
        at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:688)
        at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:220)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:662)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:298)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:179)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:129)
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94)
        at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:594)
        at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:274)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)

3. for completeness i tried your suggestion to add a wasbs configuration

{
  "type": "file",
  "connection": "wasbs://<container>@<account>.blob.core.windows.net",
  "config": {
    "fs.azure.account.key.observercondenseddata.dfs.core.windows.net": "...",
    "fs.azure.createRemoteFileSystemDuringInitialization": "false",
    "fs.azure.io.list.recursive": "true",
    "fs.azure.account.auth.type": "SharedKey"
  },
  "workspaces": {
    "root": {
      "location": "/",
      "writable": false,
      "defaultInputFormat": null,
      "allowAccessOutsideWorkspace": false
    },
    "daily": {
      "location": "/prod-condenser-logs-1-day/",
      "writable": false,
      "defaultInputFormat": null,
      "allowAccessOutsideWorkspace": false
    },
    "monthly": {
      "location": "/prod-condenser-logs-1-Month/",
      "writable": false,
      "defaultInputFormat": null,
      "allowAccessOutsideWorkspace": false
    },
    "hourly": {
      "location": "/prod-condenser-logs-1-hour/",
      "writable": false,
      "defaultInputFormat": null,
      "allowAccessOutsideWorkspace": false
    }
  },
  "formats": {
    "log": {
      "type": "logRegex",
      "regex": "^(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2},\\d{3}) - (\\w+) - (.*)|^(.+)",
      "extension": "log",
      "maxErrors": 100000,
      "schema": [
        {
          "fieldName": "log_timestamp",
          "fieldType": "TIMESTAMP",
          "format": "yyyy-MM-dd HH:mm:ss,SSS"
        },
        {
          "fieldName": "log_level"
        },
        {
          "fieldName": "structured_message"
        },
        {
          "fieldName": "unstructured_line"
        }
      ]
    }
  },
  "authMode": "SHARED_USER",
  "enabled": true
}

that is loaded corrrectly but no change:

UserRemoteException : 	VALIDATION ERROR: From line 1, column 15 to line 1, column 32: Object '*' not found within 'az_wasbs.daily'
    
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 15 to line 1, column 32: Object '*' not found within 'az_wasbs.daily'


[Error Id: 6525cbfc-5cc7-4b81-ab1e-844aa6774256 ]

[cgivre]

Are you a developer? Would you be willing to assist in fixing this? I can try but I don't have the infrastructure to test and debug.

[matteohexagon]

Hi yes i am a developer, i could help to fix this bug but i don't know where to start to set up the environment to debug the problem,

i installed drill on a azure vm, but i need way more insight on what is going wrong and then create a fix.

[cgivre]

@matteohexagon You'll need to create a local copy of Drill and an IDE ideally. Take a look at our developer docs here:

https://github.com/apache/drill/blob/master/docs/dev/Environment.md
https://github.com/apache/drill/tree/master/dev-support.

Drill's code for interacting with file systems is here:
https://github.com/apache/drill/tree/master/exec/java-exec/src/main/java/org/apache/drill/exec/store/dfs

You may also want to look here:
https://github.com/apache/drill/blob/master/exec/java-exec/src/main/java/org/apache/drill/exec/store/dfs/FileSystemPlugin.java

I'm not sure where exactly the path gets interpreted, but that would probably be a good place to start.

[cgivre]

@matteohexagon I'm willing to help fix this, but I don't have an Azure account to reproduce the error. Would you be able to create a shared folder on Azure with some non-sensitive data and share the keys.

If you join the Drill slack channel we can discuss further. Or you can email me at cgivre(at)apache.org.