The error you are getting is more common with filesystem operations. To confirm if the issue is specific to Azure Data Studio, you can try to execute the query using a different tool such as SQL Server Management Studio (SSMS). This way, you can ascertain if the issue is with Azure Data Studio or the Azure SQL Database.
Azure Data Studio might be logging more detailed error information. Check the logs or the output pane for any additional details regarding the error.
Ensure that your Azure Data Studio is up to date. An outdated version might have bugs that have been resolved in later versions.
Although you can see the tables and views, make sure your user has the correct permissions to perform the SELECT query on the specific table you are querying. Though it's not typical for a permissions issue to result in a "file exists" error, it's worth checking.
As you mentioned another user is querying the database successfully, try using that user's credentials in Azure Data Studio to check if the problem is specific to your user ID.
If Azure Data Studio is trying to write a temporary file somewhere (like for caching purposes) and encountering this error, you might want to inspect any associated storage accounts or configurations in Azure related to your SQL Database setup.