Strange query error with 'trac%'

[JPan]

Hi All,

I've encountered what seems like a very specific bug. 

For some reason, running a query with the following where clause results in an execution error.

where lower(organization_name) like '%trac%'

There's no execution error when trying "tra" and it works with any other 4th letter in the string (trab, trad, traf, etc). It also doesn't work with any string longer than "trac" like "tracksuit" does not work either.

See a video of testing the error attached. Also error output log is attached.

We're on serverless compute, so this seems like a bug? Wondering if anyone has any thoughts

[TheOC]

hey @JPan 
That's very  strange!
I'm using a serverless cluster and a sample dataset, but the specific script you're running seems to work fine on my end:

Could you try it on a different / sample dataset?

[BS_THE_ANALYST]

@JPan that is really odd. From first glance, it does look like a bug from your video. Interestingly, your error message (from your logs) says it's to do with your SQL Warehouse Version. It says your specific Warehouse Version isn't compatible, I guess. How can that be if you're using Serverless? I thought, and correct me if I'm wrong, we don't need to manage versions if we're using Serverless.

Are you sure you selected Serverless compute ☺️?

I tried replicating your issue. Unfortunately, I didn't encounter the same issue. I'm using the Free Edition of Databricks:

Would love to get to the bottom of this though @JPan .

All the best,
BS

[JPan]

Appreciate the engagement here. Upon further testing, I've found that it only fails in the new SQL editor environment. It worked when switching to the old SQL editor, and it also worked in a notebook connected to the same serverless computes, as well as non-SQL computes.

@TheOC good suggestion. We tried it on different fields in the same dataset, and it was inconsistent. It failed on a couple and worked on the rest. Furthermore, it seemed to work on other datasets as well but we were not exhaustive in testing. It seems specific to how a field may be configured and also something to do with the new SQL editor environment.

@BS_THE_ANALYST yes I we're on serverless. Just tested again. And also we haven't set up any other compute haha.

[BS_THE_ANALYST]

@JPan I noticed you had the new SQL editor mode on in your video. For what it's worth, I flicked between both when testing and both were successful. 

Given that you're finding it's only happening on some fields, do you think this is to do with special characters within those field names, the data types of the fields, or something like primary/foreign key constraints etc? I guess you could find out what the fields that cause issues have in common. 

Nevertheless, it's interesting. Obviously, the long-term solution isn't to just flick back to the old-style editor. Given the rate at which the product moves, I have faith that the bug will be fixed in good time ☺️.

All the best,
BS