Drill | mongoDB | Issue with Alias naming conventions

[piyushShama]

Bug Description:

When using an alias with special characters in a Superset SQL query, the query returns null instead of the expected result.

Steps to Reproduce:

1. Use the following SQL query as an example:
   SELECT COUNT(*) AS COUNT(*) FROM (SELECT name, grade FROM mongo.test.data) AS virtual_table WHERE grade = 'A' LIMIT 50000;

2. Observe that the query returns null when the alias contains special characters.

Expected Behavior:

The SQL query should return the count of records satisfying the condition, and not null, regardless of whether the alias contains special characters.

Actual Behavior:

The query returns null when an alias with special characters is used.

Additional Information:

Version of Apache Superset: 3.0.0
Database type and version: MongoDB 6.0

[cgivre]

What version of Drill are you using?

[piyushShama]

I'm using this apache-drill-1.21.1, I tried to use other versions as well. but it is failing for all.

[piyushShama]

I found another software, Trino, to establish a connection between MongoDB and Superset. However, Trino is memory-intensive. Drill is a better option for my use cases. But, since a functionality is broken, I'll have to switch to Trino and explore it further to manage it according to my requirements.

[piyushShama]

But it should not be broken if the alias contains any special characters. The issue might be occurring at the MongoDB end, where a key with special characters could be causing the problem. However, this should be handled at the Drill end to create an alias without any special characters.

[NomadBYlife]

But it should not be broken if the alias contains any special characters. The issue might be occurring at the MongoDB end, where a key with special characters could be causing the problem. However, this should be handled at the Drill end to create an alias without any special characters.

Have you found any solution? Totally the same situation. Drill 1.21 superset 3.0.1
The superset itself automatically sends requests with special characters. for example, in the “Drill to detail” functionality on the dashboard

[piyushShama]

Yes, I have found a way to resolve this issue !!
Keys with the special characters are the reason for the failure of mongodb query

i don’t remember the project, class name and method name, will let you know tomorrow !! You will have to modify the existing code of apache drill to make it work

you can check the logs once , to get an idea about the stacktrace to get the method where you need to do few modifications!! What i did ? Replaced alias count(*) by count !! Not valid for all cases, but working for me

Additionally, you might get some issues with the filters with daterange for that there is a workaround

[NomadBYlife]

I tried changing this option. But nothing seems to change in the way queries work.

[NomadBYlife]

Yes, I have found a way to resolve this issue !! Keys with the special characters are the reason for the failure of mongodb query

i don’t remember the project, class name and method name, will let you know tomorrow !! You will have to modify the existing code of apache drill to make it work

you can check the logs once , to get an idea about the stacktrace to get the method where you need to do few modifications!! What i did ? Replaced alias count(*) by count !! Not valid for all cases, but working for me

Additionally, you might get some issues with the filters with daterange for that there is a workaround

I will be very grateful if you tell me where to change what. I don't know Java, you'll just save me a lot of time.

[piyushShama]

Yes I’ll definitely help you !!

Please wait till tomorrow as I dont have access to my workstation now. I will provide you all the issues i have faced so far and their fixes as well !!

[piyushShama]

For now, you can clone the apache drill git repo in your local environment and you will need maven installed in your system

and once cloned, you can import them in your workspace

I’ll share the screenshots or lines of code along with the project name, class name, method name here tomorrow so that you can amend the changes in the required class and then can use the mvn to build your project i.e jar file for that project

once the jar is built , you will have to replace a jar available in lib folder in drill installation package by the built jar

[cgivre]

All, Instead of copying screen shots of code for this fix, would someone please create a pull request so that everyone will benefit from the fix? Thanks, -- C

…

On Jan 18, 2024, at 12:17, piyushShama ***@***.***> wrote: For now, you can clone the apache drill git repo in your local environment and you will need maven installed in your system and once cloned, you can import them in your workspace I’ll share the screenshots or lines of code along with the project name, class name, method name here tomorrow so that you can amend the changes in the required class and then can use the mvn to build your project i.e jar file for that project once the jar is built , you will have to replace a jar available in lib folder in drill installation package by the built jar — Reply to this email directly, view it on GitHub <#2858 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABKB7PWRKRZKZJI7W4YJORDYPFKJNAVCNFSM6AAAAABBDMY7Q6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJYHA4TMOJUGE>. You are receiving this because you commented.

[piyushShama]

Yes , will do that over this weekend !!

[piyushShama]

@NomadBYlife the first change will be required in class Foreman.java in project drill/exec/java-exec
Method: run()
Line no: 268

Note: this code is working for all sql based queries. Though it is working for mongodb, it may fail for other plugins !! So you can use it locally, I’ll try to figure out the actual place in mongodb storage plugin project to implement this logic

I’ll try to push my changes to the git over this weekend

[cgivre]

@piyushShama Have you looked at the SQLAlchemy Driver for Drill? (https://github.com/JohnOmernik/sqlalchemy-drill)
IMHO, your fix have have gotten the query to work, but I'm fairly certain that likely broke other things.
My suggestion would be to take a look at the SQL Alchemy driver for Drill and you can have it rewrite queries. Simply have it rename the column something that Drill will accept.

[piyushShama]

Not yet, but will do it !! Yes, the changes I have done are working for me but it may break other things