2

I came across following situation - I use Optic API, I have first view filtered by second one using existsJoin to get only rowset I want. Then I need to include property which was used in op.on definition (the same column exists in both views) in objects which I return as a result, additionally that property must be present without any qualifiers etc.

Below is the simplest possible code that mimics what I need to do in mine application (using views created by TDE).

const op = require('/MarkLogic/optic');
const testViewToFilter = op.fromLiterals([
  {id:1000, val: 10},
  {id:1001, val: 11},
  {id:1002, val: 12}
], 'testViewToFilter');
const filteringView = op.fromLiterals([{id:1000}], 'filteringView')
testViewToFilter
  .existsJoin(
    filteringView,
    op.on(
      testViewToFilter.col('id'),
      filteringView.col('id')
    )
  )
  .select([testViewToFilter.col('id')], '')
  .result()

What I expect:

[{id: 1000}]

What I get:

[javascript] SQL-AMBCOLUMN: return plan.execute(query); -- Ambiguous column reference: found filteringView.id and id

Am I doing something wrong? How can I achieve the result I expect? I don't understand why do I get Ambiguous column reference for id column.

I get the same results for MarkLogic versions: 10.0-9.2 and 10.0-9.5

CraneSenior
  • 45
  • 4
  • Just want to comment on the quality of this post - great job providing an easily-reproduced issue! – rjrudin Oct 12 '22 at 12:18

1 Answers1

1

This is one of the funny optic API nuances. Once a column has been used with a name more than once (even when using a qualifier), then in the future, you cannot create any column with the unqualified name.

If I have: foo.id and bar.id I cannot then create id

The error is the creation of id in the line .select([testViewToFilter.col('id')], '')

You have two options:

  1. A new qualifier:

    .select([testViewToFilter.col('id')], 'filleMeIN')

    Resulting in {"filleMeIN.id": 1000}

  2. Redefine the column name:

    .select([op.as("newId", testViewToFilter.col('id'))], '')

    Resulting in {"newId": 1000}

Small note: this is language-independent. The same nuance exists when using xQuery

David Ennis -CleverLlamas.com
  • 7,560
  • 12
  • 20
  • Thanks for the answer! During my investigation I noticed that changing qualifier in `.select()` makes it work but I didn't know that this is a nuance :) How did you find out that this is the desired behavior and we have to overcome that by name change or new qualifier? Was something mentioned in the documentation? I didn't find anything regarding this in ML docs. – CraneSenior Oct 12 '22 at 07:13
  • The Optic API documentation is sort of spread across the whole set of docs right now. I have used Optic API almost every day since its inception, so there are just some things I've absorbed. So no, I cannot remember if this is anywhere in the docs or not. – David Ennis -CleverLlamas.com Oct 12 '22 at 10:42