Join Fan-out issue: Publicly shared dataset/workflows rows duplicated in the hub. (Has RCA and suggested fix)

[Mrudhulraj]

What happened?

Any dataset/workflow shared publicly and shared with explicit results will result in duplicate rows on hub page.

How to reproduce?

Consider we are sharing dataset publicly and to a user named user_A:

1. Create a dataset/workflow from owner account and share it publicly and with user_A explicitly with read/write permissions.
   a. Publicly shared users have only READ privilege by default. -> No issue here
   b. The user_A to whom the dataset/workflow is shared has READ/WRITE privilege. -> Issue observed

2. Log in to user_A and try to view the dataset/ workflow in the hub page.

4. user_A will see the same entity ID dataset/workflow being listed twice.

RCA:

1. When listing entities in the hub, we are filtering with the following operations:
   a. User is having access to the corresponding entity
   b. User is an owner of the entity
   c. Apply OR operator with entity includePublic flag.
   d. This results in duplicate entries if user has access and the includePublic flag is enabled.

Code: amber\src\main\scala\org\apache\texera\web\resource\dashboard\DatasetSearchQueryBuilder.scala

override protected def constructFromClause(
      uid: Integer,
      params: DashboardResource.SearchQueryParams,
      includePublic: Boolean = false
  ): TableLike[_] = {
    val baseJoin = DATASET
      .leftJoin(DATASET_USER_ACCESS)
      .on(DATASET_USER_ACCESS.DID.eq(DATASET.DID))
      .leftJoin(USER)
      .on(USER.UID.eq(DATASET.OWNER_UID))

    // Default condition starts as true, ensuring all datasets are selected initially.
    var condition: Condition = DSL.trueCondition()

    if (uid == null) {
      // If `uid` is null, the user is not logged in or performing a public search
      // We only select datasets marked as public
      condition = DATASET.IS_PUBLIC.eq(true)
    } else {
      // When `uid` is present, we add a condition to only include datasets with direct user access.
      val userAccessCondition = DATASET_USER_ACCESS.UID.eq(uid)

      if (includePublic) {
        // If `includePublic` is true, we extend visibility to public datasets as well.
        condition = userAccessCondition.or(DATASET.IS_PUBLIC.eq(true))
      } else {
        condition = userAccessCondition
      }
    }
    baseJoin.where(condition)
  }

This is in the fanout issue on join.

Will suggest the fix in the comments below for review.

Scenarios to be addressed:

Version/Branch

1.3.0-incubating-SNAPSHOT (main)

Commit Hash (Optional)

No response

What browsers are you seeing the problem on?

No response

[github-actions]

👋 Thanks for your first contribution to Texera, @Mrudhulraj!

If you're looking for a good place to start, browse issues labeled starter-task; they're scoped to be approachable for newcomers.

You can drive common housekeeping yourself by commenting one of these commands on its own line:

• Issues. Comment /take to assign an open issue to yourself, or /untake to release it. You can find unclaimed work with the search filter is:issue is:open no:assignee.
• Sub-issues. To link issues into a parent/child hierarchy, comment /sub-issue #5166 #5222 on the parent to attach those children (or /unsub-issue #5166 #5222 to detach them). From a child issue, comment /parent-issue #5166 to set its parent, or /unparent-issue to clear it (the current parent is detected automatically). References may be written as #5166 or as a bare 5166; cross-repository references are not supported.
• Pull requests (author only). Comment /request-review @user to request a review from someone, or /unrequest-review @user to withdraw that request.

Each command must match exactly: /take this will not work, only /take does. For the full contribution flow, see CONTRIBUTING.md.

[Mrudhulraj]

I can address this issue once we confirm if it's a bug.
I'm a master's student at UCI.

[Mrudhulraj]

/take

[chenlica]

Thanks, @Mrudhulraj .

@seongjinyoon Can you confirm?

[Mrudhulraj]

I have done the RCA and can confirm this is a bug @@seongjinyoon.
Will suggest a fix for this, but I would also appreciate your comments too.

cc: @chenlica

[chenlica]

@carloea2 Can you work with @Mrudhulraj to verify and solve this issue?

[Mrudhulraj]

Suggested fix and result:

   // 1. Select datasets that any `uid` has access to
    // 2. if `uid` is null the left join operation on USER_ACCESS is skipped
    // 3. if `uid` is not null find tables of specific `uid` filtered
    val baseJoin = DATASET
       .leftJoin(DATASET_USER_ACCESS)
       .on(DATASET_USER_ACCESS.DID.eq(DATASET.DID))
       .and(if (uid == null) DSL.falseCondition() else DATASET_USER_ACCESS.UID.eq(uid))
       .leftJoin(USER)
       .on(USER.UID.eq(DATASET.OWNER_UID))

    // Set the `condition` where clause here    
    val condition: Condition =
       if (uid == null) {
        // Get all the public datasets by default
         DATASET.IS_PUBLIC.eq(true)
       } else if (includePublic) {
        // Get all the datasets that `uid` has access to and the publio datasets
         DATASET.IS_PUBLIC.eq(true).or(DATASET_USER_ACCESS.UID.isNotNull)
       } else {
        // If `includePublic`` is false get only user accessible datasets
         DATASET_USER_ACCESS.UID.isNotNull
       }
     baseJoin.where(condition)

[carloea2]

Where should solution designs be? PR or Issue?

[Mrudhulraj]

I have raised the PR @carloea2.