Mapping over an array

[rajivr]

I am trying to figure out the correct way to map over an array using PartiQL

Suppose I have the following environment

{
'record': { 'fdb_rl_type': 'message_TestScalarFieldAccess', 'fdb_rl_value': { 'field': { 'fdb_rl_type': 'string', 'fdb_rl_value': 'Plants' }, 'repeat_me': { 'fdb_rl_type': 'repeated_string', 'fdb_rl_value': [{ 'fdb_rl_type': 'string', 'fdb_rl_value': 'Boxes' }, { 'fdb_rl_type': 'string', 'fdb_rl_value': 'Bowls' }] }, 'bytes_field': { 'fdb_rl_type': 'bytes', 'fdb_rl_value': NULL }, 'uuid_field': { 'fdb_rl_type': 'message_fdb_rl.field.v1.UUID', 'fdb_rl_value': NULL } } }
}

With PartiQL CLI we can see record to have the following form.

$ ./bin/partiql --typing-mode=PERMISSIVE --environment ../fdb_rl.env                          
Welcome to the PartiQL shell!
Typing mode: PERMISSIVE
Using version: 0.14.4-6634bc04
PartiQL> record
   | 
===' 
{
  'fdb_rl_type': 'message_TestScalarFieldAccess',
  'fdb_rl_value': {
    'field': {
      'fdb_rl_type': 'string',
      'fdb_rl_value': 'Plants'
    },
    'repeat_me': {
      'fdb_rl_type': 'repeated_string',
      'fdb_rl_value': [
        {
          'fdb_rl_type': 'string',
          'fdb_rl_value': 'Boxes'
        },
        {
          'fdb_rl_type': 'string',
          'fdb_rl_value': 'Bowls'
        }
      ]
    },
    'bytes_field': {
      'fdb_rl_type': 'bytes',
      'fdb_rl_value': NULL
    },
    'uuid_field': {
      'fdb_rl_type': 'message_fdb_rl.field.v1.UUID',
      'fdb_rl_value': NULL
    }
  }
}
--- 
OK!

With the following query, the value of attr is a bag of Boxes and Bowls.

PartiQL> SELECT VALUE
   |  { 'attr': repeat_me.fdb_rl_value[*].fdb_rl_value } 
   | FROM record as r, r.fdb_rl_value.repeat_me as repeat_me                                 
   | 
===' 
<<
  {
    'attr': <<
      'Boxes',
      'Bowls'
    >>
  }
>>
--- 
OK!

How might I rewrite this query to get an array of Boxes and Bowls instead?

Something of the form

<<
  {
    'attr': [
      'Boxes',
      'Bowls'
    ]
  }
>>

[RCHowell]

Generally, mapping over an array elements would be,

-- Kotlin:   elements.map { e -> fn(e) }
-- Rust:     elements.map(|e| fn(e))
-- Python:   fn(e) for e in elements


SELECT VALUE fn(e) FROM elemente AS e

If you want to make a bag a list, then try CAST(repeat_me.fdb_rl_value[*].fdb_rl_value AS LIST) or use ORDER BY.

[rajivr]

Thanks for the reply. Few additional questions regarding CAST and ORDER BY.

1. CAST

As suggested the following query works in the CLI.

PartiQL> SELECT VALUE { 'attr': CAST(repeat_me.fdb_rl_value[*].fdb_rl_value AS LIST) } 
   | FROM record AS r, r.fdb_rl_value.repeat_me AS repeat_me                                 
   | 
===' 
<<
  {
    'attr': [
      'Boxes',
      'Bowls'
    ]
  }
>>
--- 
OK!

It looks to me repeat_me.fdb_rl_value[*].fdb_rl_value is generating a value of type bag. Since a bag as per PartiQL spec has no ordering, is the cast expected to preserve original ordering from which the bag was created? If so, could this behavior be documented in the spec?

The reason why I'm asking is because in my use-case, I would be generating index keys based on the casted value, and preserving ordering of the array is needed for correctness.

When I applied the above query in Rust, I got the following error.

running 1 test
thread 'metadata::tests::wip' panicked at fdb-rl/src/metadata/mod.rs:153:63:
called `Result::unwrap()` on an `Err` value: AstTransformationError { errors: [NotYetImplemented("PathStep::PathWildCard")] }

Would it be possible to consider implementing this feature in Rust?

2. ORDER BY

When I use ORDER BY, a nested list is generated.

PartiQL> SELECT VALUE
   |  { 'repeat_me': (
   |       SELECT VALUE [ fdb_rl_value ] FROM repeat_me.fdb_rl_value AS x AT i ORDER BY i 
   |    ) }
   | FROM record AS r, r.fdb_rl_value.repeat_me AS repeat_me
   | 
===' 
<<
  {
    'repeat_me': [
      [
        'Boxes'
      ],
      [
        'Bowls'
      ]
    ]
  }
>>
--- 
OK!

Does PartiQL have someway to join/flatten nested array [ [ 'Boxes' ], [ 'Bowls' ] ]? I could be wrong, but I get the sense that when using SELECT VALUE it is not possible to generate a simple list of string. When using ORDER BY, SELECT VALUE { ... } creates an array of tuple, while SELECT VALUE [ ... ] creates an array of array. (Btw, I am hoping UDF support lands soon in Rust, so I can write these transformations as UDFs)

Also when I attempted to run above query in Rust, the AST parsing went okay, but lowering failed. In the following code,

let mut bindings = MapBindings::default();
bindings.insert("record", Value::try_from(well_formed_dynamic_message)?);

let catalog = PartiqlCatalog::default();

let logical_planner = LogicalPlanner::new(&catalog);
let mut eval_planner = EvaluatorPlanner::new(EvaluationMode::Permissive, &catalog);

let parser = Parser::default();

let parsed_ast = parser.parse("SELECT VALUE { 'repeat_me': ( SELECT VALUE [ fdb_rl_value ] FROM repeat_me.fdb_rl_value AS x AT i ORDER BY i ) } FROM record AS r, r.fdb_rl_value.repeat_me AS repeat_me").unwrap();

let logical_plan = logical_planner.lower(&parsed_ast).unwrap();

logical_planner.lower(&parsed_ast).unwrap(); panics as follows.

running 1 test
thread 'metadata::tests::wip' panicked at fdb-rl/src/metadata/mod.rs:153:63:
called `Result::unwrap()` on an `Err` value: AstTransformationError { errors: [IllegalState("env.len() is not even")] }
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
test metadata::tests::wip ... FAILED

I was wondering if this might be a bug in Rust implementation?

[RCHowell]

You got a nested list because your select value constructor was a list.

-- SELECT VALUE <constructor> FROM ... 

-- constructor is a list
SELECT VALUE [ fdb_rl_value ] FROM repeat_me.fdb_rl_value AS x AT i ORDER BY i 

-- what you want
SELECT VALUE fdb_rl_value FROM repeat_me.fdb_rl_value AS x AT i ORDER BY i 

[rajivr]

Thanks for this reply. Also my apologies. I should have spent more time studying and understanding the behavior of SELECT VALUE from the spec.

[RCHowell]

No apologies necessary! SELECT VALUE is one of the "++" parts of SQL++ so seeing it as equivalent code in other languages help illustrate it.

[RCHowell]

Cast does not necessarily preserve original ordering from the collection from which a bag was created. That would be an implementation detail; whereas the specification states that FROM always returns a bag.

Now, I don't necessarily agree with this part of the spec. In my opinion, I think this FROM <array> should return an <array> — but that's just my personal opinion. I don't see why ranging over an ordered collection would necessarily return an unordered collection.

[RCHowell]

For you question, this is closed.

I am trying to figure out the correct way to map over an array using PartiQL

Answer

-- Mapping values of an array `elements` by applying `fn`

-- Kotlin:   elements.map { e -> fn(e) }
-- Rust:     elements.map(|e| fn(e))
-- Python:   fn(e) for e in elements


SELECT VALUE fn(e) FROM elements AS e

If you want to start a discussion about the specification, please open another topic with the relevant title. I hope we can get some traction on updating the spec to preserve ordering in the FROM clause.

For reference

• https://partiql.org/partiql-lang/#sec:single-item-from

Normally 𝑎 is a collection, i.e, an array [𝑒0,...,𝑒𝑛−1] or a bag <<𝑒0,...,𝑒𝑛−1>>. In either case, the FROM clause outputs a bag. For each 𝑒𝑖, the bag has a binding tuple 〈𝑣:𝑒𝑖〉.