Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query w/ select and calculate: ... { partition_by: ... } fails in BigQuery #1604

Open
carlineng opened this issue Feb 2, 2024 · 0 comments
Assignees
Labels
L2 Modest change, bug fix, or refactor language An issue in the Malloy language

Comments

@carlineng
Copy link
Contributor

What happens?

In BigQuery, we are getting a PROJECT cannot be used on queries with turtles when attempgint to run the following query:

run: events -> {
    where: trim(event_name) = "purchase"
    select: event_date
    select: event_name
    calculate: prev_occur is lag(event_date) {
        partition_by: event_name
        order_by: event_date asc
    }
    order_by: event_name asc, event_date asc
}

image

This same pattern DOES work on DuckDB though... running this query in the ECommerce DuckDB sample data produces correct results:

run: order_items -> {
    select: 
      user_id
      created_at

    calculate: prev_occur is lag(created_at) {
        partition_by: user_id
        order_by: created_at asc
    }
    order_by: user_id asc, created_at asc
}

To Reproduce

Using the BigQuery GA4 sample dataset, run the following query:

run: events -> {
    where: trim(event_name) = "purchase"
    select: event_date
    select: event_name
    calculate: prev_occur is lag(event_date) {
        partition_by: event_name
        order_by: event_date asc
    }
    order_by: event_name asc, event_date asc
}

OS:

macOS

Malloy Client:

VS Code

Malloy Client Version:

v0.3.1706811226

Database Connection:

BigQuery

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
L2 Modest change, bug fix, or refactor language An issue in the Malloy language
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants