Array intersection operator

[OriolLopezMassaguer]

Hi!

I tried a query using "&&" postgresql array operator:

select count(*)
from clickhouse_test.pv_pm_reports3 ppr 
where ppr.administration_routes && array['Oral'] 

where administration_routes is an array field accessed through the fdw but it fails:

ERROR: clickhouse_fdw: DB::Exception: Syntax error: failed at position 74 ('&'): && ['Oral'])). Unrecognized token: '&'

Are array operators avaliable through the fdw?

[ildus]

Hi. No, I don't remember implementing them. But I always open to accept new pull requests :)

[OriolLopezMassaguer]

Can you give some guidance on how to implememt that?

[ildus]

Actually this case is more complex, clickhouse doesn't have direct equivalent of this operation. What do you expect to see on clickhouse side?

[OriolLopezMassaguer]

We need the intersection && operator in posgresql to check if two arrays have elements in common: array functions

I think this operator is equivalent to hasAny operator in clickhouse:

clickhouse array functions

Am I wrong?

Assuming this is correct I implemented it in a fork:

fdw fork

I translate the following operators:

• @> as hasAll(op1,op2)
• <@ as hasAll(op2, op1)
• && as hasAny(op1, op2)

[ildus]

Missed these functions, could you add tests to check that they are working correctly and make a pull request?

[OriolLopezMassaguer]

I will do

El dg., 18 de jul. 2021, 19:16, Ildus Kurbangaliev @.***> va escriure:

Missed these functions, could you add tests that they are working correctly and make a pull request?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/adjust/clickhouse_fdw/issues/73#issuecomment-882089574, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAJEDVCRFGL4QMPDC75ZFSLTYMD7TANCNFSM5AIUWUWQ .