I seem to get bit by a datalist filter bug in combination with an innerJoin.

I have the following relationship:
Course has_many CourseInstances
CourseInstance has_one Course

Let's report on all the CourseInstances with an empty duration sorted by Course Title:

Filter on the Duration:

		$instances = CourseInstance::get();
		$instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
		return $instances->filter('Duration',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("Duration" = '') Column 'Duration' in where clause is ambiguous

Ok, I'll be more explicit and filter on Course.Duration:

		$instances = CourseInstance::get();
		$instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
		return $instances->filter('Course.Duration',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("Course.Duration" = '') Unknown column 'Course.Duration' in 'where clause'

Clearly I should have escaped Course.Duration:

		$instances= CourseInstance::get();
		$instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
		return $instances->filter('"Course"."Duration"',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("\"Course\".\"Duration\"" = '') You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Course\".\"Duration\"" = '')' at line 1

I've tried seperating table from field using single quotes, escaped single quotes, double quotes, escaped double quotes.
I've tried combining the key with the value in combination of the above.

The result is always that it doesn't escape enough, that it double escapes or that the for loop fails because I've combined the two filter arguments.

How?

The answer is btw:

$instances = $instances->where("CourseInstances.Duration IS NULL");

But I would like to know if this is a bug and if not how do I do this with a filter?

$instances = $instances->filter('Course.Duration:ExactMatch', '');

You don't need to do the join yourself. However, there's currently no way to do a IS NULL with the filter() method, so if your values are actually null you will still need to do the join and then use where().