filtering unicode strings using where clause on MSSQL

[mohscorpion]

hi
i have a small problem i used filter for my charts on dashboard and i noticed when i filter data on nvarchar columns the query returns no result .
i traced the problem to where clause on TSQL it needs to be formatted like this for Unicode comparison :
Where MaterialName=N'unicode string'
is there anyway which i can add this functionality to superset ?

[villebro]

@mohscorpion I assume you're using pymssql; did you check what the recommended way of using unicode strings is with it? Any documentation, either sqlalchemy or pymssql, on this would be helpful for assessing a potential fix/workaround.

[mohscorpion]

i guess its pymssql , i read quite a few materials on that matter but i try not to change internals of pymssql or so. i thought maybe there is a settings in superset which i missed .

[villebro]

I'm thinking it might be sufficient to do something along the lines of filter_value.encode('utf8') if the column is nvarchar or equivalent for other engines, and perhaps that would automatically add the N'' prefix. But this is purely speculation at this point. I tried some quick googling but only turned up more questions than answers 😃

[villebro]

The N-prefix seems to a be a thing for Oracle, too (NVARCHAR and NCHAR). I'll try to work on this over the next few days.

[mohscorpion]

that happened for me too :D

[mohscorpion]

The N-prefix seems to a be a thing for Oracle, too (NVARCHAR and NCHAR). I'll try to work on this over the next few days.

N prefix is standard for MSSQL too .its used like this :
select * from test where col=N'[some unicode string]'

[villebro]

Hmm, I was unable to replicate the problem on both Oracle and SQL Server. I tried querying an nvarchar-column with unicode characters, and the where clause worked both with and without N-prefixing. Can you test the query generated by Superset (without N-prefixing) directly in e.g. SSMS to check whether it works there? Furthermore, what version are you running, both Python and Superset?

[mohscorpion]

actually i tried this , i used ms sql profiler to catch the SQL statement issued by superset
i copied the SQL statement in SSMS and it returned nothing .
that is how i found the problem i used N prefix and the query ran as expected

[villebro]

Yes, I was finally able to replicate this, my original test apparently only contained regular ascii-chars 👍

[mohscorpion]

Yes, I was finally able to replicate this, my original test apparently only contained regular ascii-chars 👍

👍

[villebro]

FYI, the functionality for this is missing in the current version of SQLAlchemy, but they were quick to propose a fix: sqlalchemy/sqlalchemy#4442. I think this will still require a small change in Superset, but should be easy to implement once the fix is introduced in SQLAlchemy.