Skip to content

Unable to cross join two views that depend on the same cube since 1.3.14 (SQL API) #9719

Open
@DReigada

Description

@DReigada

Describe the bug

Unable to cross join two views that depend on the same cube starting with version 1.3.14.

To Reproduce
Steps to reproduce the behavior:

  1. Define cubes as shown in "Minimally reproducible Cube Schema"
  2. Run query against SQL API
       select
           date,
           measure(view1.value) as measure1,
           measure(view2.value) as measure2
       from view1, view2
       group by 1
  3. Error Arrow error: Compute error: Can't find join path to join 'view1,view2', 'cube1,cube1', 'cube1'

Expected behavior

The following result (screenshot from version 1.3.13)

Image

Minimally reproducible Cube Schema

cubes:
  - name: cube1

    sql: |-
      select 1 as measure1, 2 as measure2, '2025-01-01' as date UNION ALL
      select 3 as measure1, 4 as measure2, '2025-01-02' as date UNION ALL
      select 5 as measure1, 6 as measure2, '2025-01-03' as date

    dimensions:
      - name: date
        type: time
        sql: TIMESTAMP({CUBE}.date)
        primary_key: true

    measures:
      - name: measure1
        type: number
        sql: sum({CUBE}.measure1)

      - name: measure2
        type: number
        sql: sum({CUBE}.measure2)

views:
  - name: view1
    cubes:
      - join_path: cube1
        includes:
          - name: measure1
            alias: value
          - date
  - name: view2
    cubes:
      - join_path: cube1
        includes:
          - name: measure2
            alias: value
          - date

Version:
Starting with 1.3.14

Additional notes:

If we explain the query we get the same plan for both versions, except that from version 1.3.14 we also get a joinHints key.
Either way, if we use the query plan in the playground, both plans work (we don't get the join error)

This is the plan for 1.3.14: {"measures":["view1.value","view2.value"],"dimensions":["view1.date"],"segments":[],"order":[],"joinHints":[["view1","view2"]]}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions