Re: BUG #14969: Delta in 9.6 and 10

On Tuesday, December 12, 2017, <mohanpatil048(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14969
> Logged by: Mohan Patil
> Email address: mohanpatil048(at)gmail(dot)com
> PostgreSQL version: 10.1
> Operating system: ubuntu 14.04
> Description:
>
> When I execute the below query in 9.6 version,I get the result.
>
> But when I execute it on 10.1, it is giving the error,
>
> ********** Error **********
>
> ERROR: set-returning functions are not allowed in CASE
> SQL state: 0A000
> Hint: You might be able to move the set-returning function into a LATERAL
> FROM item.
>
> QUERY::
> SELECT t.id, t.name
> FROM (SELECT ( CASE
> WHEN CARDINALITY(tier_ids) > 1 THEN UNNEST(tier_ids)
> ELSE NULL
> END ) AS tier_id
> FROM relation
> WHERE id = 1001) AS d
> INNER JOIN tier t ON t.id = d.tier_id ;
>
>
This is not a bug and while I cannot rewrite the query for you the
suggestion provided by the system is a valid one you should consider
following. E same query you end up with in 10 will continue to work with
9.6.

David J.