Major Description
Using a full text search with left outer join can cause some fields to be null when they should not. Note this only occurs if the item on the outer join is null and appears in the query result. Please see the following example.
// Get the database (and create it if it doesn’t exist). |
DatabaseConfiguration config = new DatabaseConfiguration(); |
Database database = new Database("humans", config); |
|
|
database.createIndex(
|
"nameFTSIndex", |
IndexBuilder.fullTextIndex(
|
FullTextIndexItem.property("firstName"), |
FullTextIndexItem.property("lastName")).ignoreAccents(false) |
);
|
|
|
MutableDocument johnDoeDoc = new MutableDocument() |
.setString("type", "Person") |
.setString("firstName", "John") |
.setString("lastName", "Doe") |
.setString("gender", "male") |
.setString("companyID", "companyA"); |
database.save(johnDoeDoc);
|
MutableDocument janeDoeDoc = new MutableDocument() |
.setString("type", "Person") |
.setString("firstName", "Jane") |
.setString("lastName", "Doe") |
.setString("gender", "female"); |
database.save(janeDoeDoc);
|
MutableDocument companyADoc = new MutableDocument() |
.setString("type", "Company") |
.setString("id", "companyA") |
.setString("name", "Company A") |
.setString("location", "USA"); |
database.save(companyADoc);
|
|
|
Query query = QueryBuilder.select(
|
SelectResult.expression(Expression.property("name").from("company")).as("companyName"), // PURPOSELY PUT FIELDS FROM OUTER TABLE FIRST |
SelectResult.expression(Expression.property("firstName").from("person")).as("firstName"), |
SelectResult.expression(Expression.property("lastName").from("person")).as("lastName"), // THIS WILL BE NULL IF COMPANY IS NULL EVEN THOUGH IT SHOULDN'T |
SelectResult.expression(Expression.property("gender").from("person")).as("gender")) |
.from(DataSource.database(database).as("person")) |
.join(Join.leftOuterJoin(DataSource.database(database).as("company")).on( |
Expression.property("companyID").from("person").equalTo(Expression.property("id").from("company")) |
))
|
.where(
|
Expression.property("type").from("person").equalTo(Expression.string("Person")) |
.and(FullTextExpression.index("nameFTSIndex").match("Doe")) |
);
|
|
ResultSet result = query.execute();
|
List<Result> results = result.allResults();
|
Result johnDoe = results.get(0); |
Result janeDoe = results.get(1); |
|
|
if (janeDoe.getValue("lastName") == null) { |
Log.i(TAG, "This should not be null"); |
}
|
If an a row returns with a company the fields are populated but if the company is null then lastName is null. The field that is nullified seems to be relative to where ever a company field is placed. Note, ff the full text search is removed from the where expression the data comes back as expected.
From my investigation it looks like the missingColumns bitmask from here is incorrect. This is also an issue in the iOS sdk, the only difference is that nil is used instead of null.
We have tried moving the fields coming from the outer join to the bottom of the select expression with some success but this doesn't work as soon as you do more than one left outer join.
Explain:
SELECT
fl_result(fl_value("company".body, 'name')),
fl_result(fl_value("person".body, 'firstName')),
fl_result(fl_value("person".body, 'lastName')),
fl_result(fl_value("person".body, 'gender'))
FROM kv_default AS "person"
LEFT OUTER JOIN kv_default AS "company" ON
(fl_value("person".body, 'companyID') = fl_value("company".body, 'id'))
AND ("company".flags & 1 = 0)
WHERE (
fl_value("person".body, 'type') = 'Person')
AND ("person".flags & 1 = 0)3|0|0| SCAN TABLE kv_default AS person
11|0|0| SCAN TABLE kv_default AS companyUnknown macro: {"FROM"},{"AS":"company","JOIN":"LEFT OUTER","ON":["=",[".person.companyID"],[".company.id"]]}],"WHAT":[[".company.name"],[".person.firstName"],[".person.lastName"],[".person.gender"]],"WHERE":["=",[".person.type"],"Person"]}