_empty and _nempty filter in Joi Schema don't align with filters used for database querying

[hanneskuettner]

Describe the Bug

As per our documentation the _empty and _nempty filter operators work as "Is [not] empty (null or falsy)".

In applyQuery we implement it as == null || == ''. So either null or an empty string.

directus/api/src/utils/apply-query.ts

Lines 618 to 628 in f1ad752

	if ((operator === '_empty' && compareValue !== false) || (operator === '_nempty' && compareValue === false)) {
	dbQuery[logical].andWhere((query) => {
	query.whereNull(key).orWhere(key, '=', '');
	});
	}
	if ((operator === '_nempty' && compareValue !== false) || (operator === '_empty' && compareValue === false)) {
	dbQuery[logical].andWhere((query) => {
	query.whereNotNull(key).andWhere(key, '!=', '');
	});
	}

In generateJoi it is only implemented as == ''

directus/packages/utils/shared/generate-joi.ts

Lines 321 to 327 in f1ad752

	if (operator === '_empty') {
	schema[key] = getAnySchema().valid('');
	}
	if (operator === '_nempty') {
	schema[key] = getAnySchema().invalid('');
	}

This can lead to unexpected results, for example when setting up access control and comparing it so API filter results or different outcomes when performing item creation vs item retrieval.
Or even for item creating vs querying, since for creating and updating the Joi schema is used for validation, for querying the database filters.

To Reproduce

.

Directus Version

v10.10.7

Hosting Strategy

Self-Hosted (Docker Image)