Strange behaviour running SQL through DBeaver/JDBC driver

[rad-pat]

Originally logged as issue with Databend itself(https://github.com/datafuselabs/databend/issues/16074), but turns out it was client related. This SQL, when ran through DBeaver/JDBC driver gives null results for specific order.

How to Reproduce?

I'm attempting to write something to parse an interval string, but the query yields different/incorrect results depending on the order

WITH interval_data AS (
  SELECT '1 y 2 mo 3 d 04:05:06.789' AS interval_str 
  UNION ALL 
  SELECT '2 hours 10 minutes 5 seconds' AS interval_str 
  UNION ALL 
  SELECT '4:05:06' AS interval_str
)

SELECT
  interval_str
  , CAST(NULLIF(regexp_replace(regexp_substr(interval_str, '(-?\\d+)\\s*y'), '\\D', ''), '') AS INT) AS years
FROM interval_data
 ORDER BY interval_str desc; -- fails
-- ORDER BY interval_str; -- works

When I run this, sometimes it will correctly parse the years for the first example as 1, or it sometimes reports null. This seems to depend on the order of the results and can be changed by altering the order BY.

Incorrect:

interval_str                |years|
----------------------------+-----+
4:05:06                     |     |
2 hours 10 minutes 5 seconds|     |
1 y 2 mo 3 d 04:05:06.789   |     |

Correct:

interval_str                |years|
----------------------------+-----+
1 y 2 mo 3 d 04:05:06.789   |    1|
2 hours 10 minutes 5 seconds|     |
4:05:06                     |     |

[hantmac]

@rad-pat Hi, maybe same with this issue https://github.com/datafuselabs/databend-jdbc/issues/232, it has been fixed and will be release in v0.2.8. If you want to use it early, you can build the jar by yourself.

[rad-pat]

Ah yes, could be the same issue, I had not spotted that one. I'll wait for next version and close if fixed 👍

[rad-pat]

Closing, this was fixed by #232