Add some new Presto dialect functions

malloydata / malloy

Malloy is an experimental language for describing data relationships and transformations.

http://www.malloydata.dev

MIT License

2k stars 76 forks source link

Add some new Presto dialect functions #1902

Closed carlineng closed 2 months ago

carlineng commented 2 months ago

Adds the following functions, and associated tests:

date_format
from_unixtime
regexp_replace
to_unixtime
approx_percentile
date_parse
bool_or
corr
bitwise_and
bitwise_or
min_by
max_by
json_extract_scalar
regexp_like
bool_and
variance
arbitrary
bitwise_or_agg
bitwise_and_agg
bitwise_xor_agg

I was having trouble with local tests on date_parse and to_unixtime; I think something related to timezones and converting between Javascript datetime objects and Presto timestamps... any advice on how to resolve those would be appreciated.

carlineng commented 2 months ago

Updated PR to address feedback, PTAL

carlineng commented 2 months ago

Trino test is failing because the function signature for APPROX_PERCENTILE is different between Presto and Trino.

Trino is approx_percentile(x, w, percentage), where "w" is a weight parameter that must be > 1 Presto is approx_percentile(x, percentage, accuracy), where accuracy is a parameter between 0 and 1

carlineng commented 2 months ago

MotherDuck tests are failing for an unrelated reason... looks like count_approx in the all/functions.spec.ts is not returning the expected result. I'm going to merge this anyway since I didn't alter those functions in this PR, so I think that failure is unrelated to the changes here.