Query output order changes nondeterministically at each run, but should not

Bug reports
1

Hello :wave:

I encounter a very strange issue with a query, which provides a different result at each run, but should not.

The problem can be reduced down to this query:

SELECT T.created_at
     , my_python_udf() AS x
        
FROM (
    SELECT CURRENT_DATE AS created_at
    UNION SELECT CURRENT_DATE - 1
    UNION SELECT CURRENT_DATE - 2
) T 
    
ORDER BY T.created_at DESC

Executing this query today (Oct 3rd) will first lead to "Oct 3rd" then "Oct 1st" then "Oct 2nd" for example, and then the "Oct 1st" - "Oct 2nd" - "Oct 3rd", and then the expected result: "Oct 3rd" - "Oct 2nd" "Oct 1st" etc.... with no pattern (sometimes two consecutive runs will provide the same result)

my_python_udf is a Python UDF than can be, for example:

CREATE OR REPLACE FUNCTION my_python_udf()
RETURNS FLOAT
LANGUAGE PYTHON
STRICT IMMUTABLE RUNTIME_VERSION='3.8'
HANDLER='my_python_udf'
AS
$$
def my_python_udf():
    return 1
$$;

Here's more information about my config:

  • Google Chrome 105.0.5195.125 (Official Build) (x86_64)
  • OS: macOS Version 11.6.2 (Build 20G314)
  • JavaScript: V8 10.5.218.8
  • Metabase version: 0.44.0
  • DB: Snowflake

I would really appreciate some pointers, as this is blocking us to build a dashboard.

Thank you!

2

Hi @HJ_alan
Metabase has up to 15 connections (by default) to run queries on, so unless your variable is available to all connection sessions, then you'll see different results.

3

Thanks a lot @flamber. I do appreciate the interest in my question.
I'm not sure to follow though:

  • What variable are you referring to exactly?
  • Have you noticed that the function here always returns 1, no matter what connection we'd be using?
  • Also, did you see that we're ordering independently of the output of the function (ORDER BY T.created_at)

Even if it was related to MB connections, that would remain a bug, right?

4

@HJ_alan I was busy with other things and barely looked at anything, just looked like the typical "my variable changes on each request".

So you are saying that the problem on occurs on Metabase and you cannot see similar problem with any other JDBC based applications?
To figure out if the problem is frontend or backend, then first have a look at the request response in your browser developer Network-tab.

5

Haha no worries @jamber, thanks again for looking into this.

It looks like a backend issue:

Screenshot 2022-10-03 at 18.28.54
Screenshot 2022-10-03 at 18.28.541677×446 67.9 KB

you cannot see similar problem with any other JDBC based applications?

I do observe the problem with Metabase and when querying with PyCharm.
I do NOT observe the problem from the Snowflake console, or when using snowflake.connector in Python.

6

@HJ_alan I think you should contact Snowflake and report the problem, since it seems to be specific to their JDBC driver (it's not the first time they've had problems only with their Java connector).

7

Will do. Do you confirm this is just the response you get from Snowflake?

8

@HJ_alan I don't have an account, where I can create a function, so I cannot test.

9

I opened a ticket with Snowflake