N1QL for analytics - Combining Aggregate Function with Conditional and Collection operations

Hello all.

I was trying to do a quite simple query combining Conditional (case when) with Collection (some|any in) operations but it is failing with a Internal error error, which I assume that the N1QL is valid and passes compilation but fails in the execution.

Here you can see an example query using the beer-sample data:

 select 100 * (multiple_address / total) as m_adrr_pct
FROM (
SELECT
 count(*)  as total,
sum(case when ARRAY_COUNT(address) > 1 then 1 else 0 end) as multiple_address
--,sum(case when (SOME a IN address SATISFIES a LIKE '%Street%') then 1 else 0 end) as addr_str
from breweries) as brew_summaries;

Uncomment the line and you will get the error. The CB version I tested is: Enterprise Edition 6.0.1 build 2037, the latest from docker at the time of this writing.

With that I have two questions:

  1. Is it a known issue? If so is there a way to track it? If not how can it be reported?
  2. Does anyone know how to achieve the same as the query above without using it in a similar fashion? The example above I’m basically trying to count how documents have any of the elements in an array satisfying a condition.

Thank you!

END is Missing in SOME clause. (2 END’s needed one for SOME, one for CASE)

select 100 * (multiple_address / total) as m_adrr_pct
FROM
(select count(*)  as total,
sum(case when ARRAY_COUNT(address) > 1 then 1 else 0 end) as multiple_address
,sum(case when (SOME a IN address SATISFIES a LIKE '%Street%' end) then 1 else 0 end) as addr_str
from `beer-sample`) as brew_summaries;

Hi @vsr1.

Thank you for your reply, however I tried that and it still doesn’t work. I’m trying to do that in the Analytics Service, not in the regular N1QL query service. I noticed that in your example you used from beer-sample, but I’m actually doing that in the Default dataverse and have created a simple breweries dataset as stated in the Tutorial:

 CREATE DATASET breweries ON `beer-sample` WHERE `type` = "brewery";

Here is the full response I’m getting (which is not helpful at all):

[
  {
    "code": 25000,
    "msg": "Internal error",
    "query_from_user": "select 100 * (multiple_address / total) as m_adrr_pct\nFROM\n(select count(*)  as total,\nsum(case when ARRAY_COUNT(address) > 1 then 1 else 0 end) as multiple_address\n,sum(case when (SOME a IN address SATISFIES a LIKE '%Street%' end) then 1 else 0 end) as addr_str\nfrom breweries) as brew_summaries;"
  }
]

Sorry if that wasn’t clear in the original post.

cc @till, @dmitry.lychagin

Also try this

 select 100 * (multiple_address / total) as m_adrr_pct
FROM
 count(*)  as total,
sum(case when ARRAY_COUNT(address) > 1 then 1 else 0 end) as multiple_address
,sum(case when xa then 1 else 0 end) as addr_str
from breweries
LET xa = SOME a IN address SATISFIES a LIKE '%Street%' end ) as brew_summaries;

I was able to achieve my goal in a different way by moving the Collection Operation into the subquery:

select 
100 * (SUM(case when multiple_address then 1 else 0 end) / count(*)) as multiple_addresses_pct,
100 * (SUM(case when any_address_at_street then 1 else 0 end) / count(*)) as address_at_street_pct,
count(*)  as total
from (
  select
  ARRAY_COUNT(b.address) > 1 as multiple_address,
  ANY a IN b.address SATISFIES a LIKE '%Street%' as any_address_at_street
  from breweries b) 
as brew_address_summaries;

It also does work when combining only Conditional and Collection operations like this:

select 
100 * (SUM(case when multiple_address then 1 else 0 end) / count(*)) as multiple_addresses_pct,
100 * (SUM(case when any_address_at_street then 1 else 0 end) / count(*)) as address_at_street_pct,
count(*)  as total
from (
  select
  ARRAY_COUNT(b.address) > 1 as multiple_address,
  case when ANY a IN b.address SATISFIES a LIKE '%Street%' then true else false end as any_address_at_street
  from breweries b) 
as brew_address_summaries;

So the problem is only when combining Aggregate Function with Conditional and Collection operations. The original query still is a valid N1QL right? If not supported at least a better error message rather than a not-very-helpful Internal Error would be better right?

The original query is valid in Analytics. The failure is caused by a bug in the query compiler. https://issues.couchbase.com/browse/MB-33595 has been filed to track it.

1 Like

Great. Thank you for filling the bug @dmitry.lychagin!