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

Cross Join Not Returning expected results #2035

Closed
rajagp opened this issue Jan 21, 2018 · 7 comments

Comments

@rajagp
Copy link
Contributor

commented Jan 21, 2018


  • Version: Latest feature/2.0 build of iOS
  • Client OS: iOS

Version: Latest feature/2.0 branch of Couchbase Lite iOS. Haven't tried on other platforms.

Test Data: Here

JOIN Query :
Trying to do a cross join between documents of type "employee" and type "department"

Please insert the test query into QuertTest.m of Couchbase_Lite_ios

- (void) testCrossJoin {
    [self loadJSONResource:@"join"];
    CBLQueryExpression* FIRSTNAME  = [CBLQueryExpression property: @"firstname" from:@"employeeDS"];
    CBLQueryExpression* LASTNAME  = [CBLQueryExpression property: @"lastname" from:@"employeeDS"];
    CBLQueryExpression* DEPTNAME  = [CBLQueryExpression property: @"name" from:@"departmentDS"];
    
    NSArray* results = @[[CBLQuerySelectResult expression: FIRSTNAME],
                         [CBLQuerySelectResult expression: LASTNAME],
                         [CBLQuerySelectResult expression: DEPTNAME]];
    
    
    [CBLQuerySelectResult expression: [CBLQueryMeta idFrom: @"employeeDS"]];
    
  
    CBLQueryJoin* join = [CBLQueryJoin crossJoin:[CBLQueryDataSource database: self.db as: @"departmentDS"]];
    CBLQuery* q = [CBLQuery select: results
                              from: [CBLQueryDataSource database: self.db as: @"employeeDS"]
                              join: @[join]];
    Assert(q);
    NSError* error;
   
    
    CBLQueryResultSet* rs = [q execute: &error];
 
    NSUInteger i = 0;
    NSArray* res = [rs allResults];
    for (CBLQueryResult* r in res) {
        NSLog(@" %@",[r toDictionary]);
       
        i++;
    }
    
}

Compiled Query:

 SELECT fl_result(fl_value("employeeDS".body, 'firstname')), fl_result(fl_value("employeeDS".body, 'lastname')), fl_result(fl_value("departmentDS".body, 'name')) FROM kv_default AS "employeeDS" CROSS JOIN kv_default AS "departmentDS" ON ("departmentDS".flags & 1) = 0 WHERE ("employeeDS".flags & 1) = 0

Observed Results:
Quite random. Does not produce a cross join results. Getting empty rows etc

Examples

\\This is not matched with any entry in the "department" document
2018-01-20 18:58:06.094554-0500 xctest[7564:2477971]  {
    firstname = Chas;
    lastname = Cefalu;
}
\\Empty :
2018-01-20 18:58:06.138359-0500 xctest[7564:2477971]  {
}
```
@rajagp

This comment has been minimized.

Copy link
Contributor Author

commented Jan 21, 2018

Some more details and I think that this may not be an issue after all

  • The JSON data also included documents with "type" of "location" that also include the property "code" which was messing up the results

So I updated the select query above to include where to specify exactly the documents to join

- (void) testCrossJoin {
    [self loadJSONResource:@"join"];
    CBLQueryExpression* FIRSTNAME  = [CBLQueryExpression property: @"firstname" from:@"employeeDS"];
    CBLQueryExpression* LASTNAME  = [CBLQueryExpression property: @"lastname" from:@"employeeDS"];
    CBLQueryExpression* DEPTNAME  = [CBLQueryExpression property: @"name" from:@"departmentDS"];
    
    NSArray* results = @[[CBLQuerySelectResult expression: FIRSTNAME],
                         [CBLQuerySelectResult expression: LASTNAME],
                         [CBLQuerySelectResult expression: DEPTNAME]];
    
    
    [CBLQuerySelectResult expression: [CBLQueryMeta idFrom: @"employeeDS"]];
    
  
    CBLQueryExpression* expr1 = [[CBLQueryExpression property:@"type" from:@"employeeDS"]  equalTo: [CBLQueryExpression string:@"employee"]];
    CBLQueryExpression* expr2 = [[CBLQueryExpression property:@"type" from:@"departmentDS"] equalTo :[CBLQueryExpression string:@"department"]];
    
    CBLQueryJoin* join = [CBLQueryJoin crossJoin:[CBLQueryDataSource database: self.db as: @"departmentDS"]];
    CBLQuery* q = [CBLQuery select: results
                               from: [CBLQueryDataSource database: self.db as: @"employeeDS"]
                              join: @[join]
                             where:[expr1 andExpression:expr2]];
                   
    
    Assert(q);
    NSError* error;
   
    NSLog(@"%@",[q explain:nil]);
    
    CBLQueryResultSet* rs = [q execute: &error];
 
    NSUInteger i = 0;
    NSArray* res = [rs allResults];
    for (CBLQueryResult* r in res) {
        NSLog(@" %@",[r toDictionary]);
       
        i++;
    }
    
}

I think this is the way it should be. But any one else have any comments / thoughts? If you agree, I will close the issue.

@pasin

This comment has been minimized.

Copy link
Contributor

commented Jan 22, 2018

If the document type location shouldn't be involved in the cross-join result, then there where clause like you have above is needed.

Quite random. Does not produce a cross join results. Getting empty rows etc

Getting empty rows doesn't seem to be correct. With the cross-joins, should it be like N x N results?

@pasin pasin added the needsmoreinfo label Jan 22, 2018

@rajagp

This comment has been minimized.

Copy link
Contributor Author

commented Jan 22, 2018

Getting empty rows doesn't seem to be correct. With the cross-joins, should it be like N x N results?

I don't get empty rows if I filter using the where clause but I do get empty rows if I don't. It is odd because it should just be a NxM set, You should be able to repro this with the test data and unit test specified above just in case I am overlooking something...latest branch.

@djpongh djpongh removed the needsmoreinfo label Jan 26, 2018

@djpongh djpongh added this to the 2.0.0 milestone Jan 28, 2018

@rajagp

This comment has been minimized.

Copy link
Contributor Author

commented Feb 6, 2018

Not a JOIN expert but Rethinking this issue, doing a cross join across more than 2 documents without a where clause is probably a misuse of the JOIN feature and we should expect to see weird results. So I am inclined to say that this is a non-issue. If we used a where clause to scope the join to <=2 document types, we are good.

@djpongh djpongh assigned pasin and snej and unassigned pasin Feb 7, 2018

@snej

This comment has been minimized.

Copy link
Member

commented Feb 8, 2018

Priya: I tried the (updated) test method in the latest CBL and LiteCore, and it passes. Does that mean the bug doesn't exist anymore? Or am I misunderstanding? (I'm even less of a Join expert than you are!)

@rajagp

This comment has been minimized.

Copy link
Contributor Author

commented Feb 8, 2018

Yes-I would say that we can close this. I was attempting to cross join across more than 2 documents (i.e without where clause) and I haven't come across instances where that's deemed fine.
In case you were wondering what happened - When i tried to do that cross-join across > 2 docs, I ended up with empty rows- do you see that ?

@snej

This comment has been minimized.

Copy link
Member

commented Feb 8, 2018

OK, I will close this then.

@snej snej closed this Feb 12, 2018

@snej snej removed the backlog label Feb 12, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.