Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Server hangs with NTH_VALUE () IGNORE NULLS query

lukaseder
SQL-Fighter

I'm using Exasol 7.1.8 on Windows with Docker:

docker pull exasol/docker-db
docker run --name EXASOL -p 127.0.0.1:9563:8563 --detach --privileged --stop-timeout 120  exasol/docker-db:latest

Using this schema:

 

 

create table t (i int, j int);
insert into t values (1, 1), (2, 1), (3, 2), (4, 2);

 

 

Now, run this query:

 

 

select
  t.i,
  nth_value(nullif(t.i, 1), 2) ignore nulls over (
    partition by t.j
    order by t.i
  )
from t
order by t.i;

 

 

The client hangs until I kill the connection. Remove the IGNORE NULLS clause or the NULLIF() function (replacing it by t.i), and the query works again. It seems the IGNORE NULLS usage causes problems as soon as actual null values are encountered.

Alternatively, remove the PARTITION BY and ORDER BY clauses, and this works as well:

 

 

select
  t.i,
  nth_value(nullif(t.i, 1), 2) ignore nulls over ()
from t
order by t.i;

 

 

1 REPLY 1

exa-SimonR
Team Exasol
Team Exasol

Hi Lukas👋

Thanks once again for the bug report. It was easy to reproduce and I think I have a fix for this problem. I will keep you up to date here when I know the fix version.

The problem happens if

  • NTH_VALUE with IGNORE NULLS
  • The second argument to NTH_VALUE is a constant
  • Window frame has (ROWS|RANGE|GROUPS) BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (which is the default for an empty window frame clause)
  • There is a PARTITION BY and ORDER BY clause
  • For NTH_VALUE(i,k), there is at least one window of values of i that has less than k non-null values. I.e. NTH_VALUE should return NULL, because there is no kth value.

So for example, the following statement also breaks even though there are no NULL values in the input at all:

 

select nth_value(t.i, 2) ignore nulls over ( partition by t.j order by 1 ) from values (1,1) as t(i,j);