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:
Is it a known issue? If so is there a way to track it? If not how can it be reported?
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.
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;
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;"
}
]
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?