Optional function arguments

[Fogapod]

I have this function:

    # deletes 'expired' users that conflict by nickname or email
    # return value indicates if all conflicts were resolved or no
    function delete_conflicting_users(
        nickname: optional name_type,
        email: optional email_type
    ) -> bool {
        volatility := 'VOLATILE';
        using EdgeQL $$
            WITH
                MODULE default,
                conflicting := (
                    SELECT User
                    FILTER
                        (nickname != <str>{} OR email != <str>{})
                        AND (.nickname = nickname OR .email = email)
                    LIMIT 2
                ),
                deleted := (
                    DELETE User
                    FILTER
                        User IN conflicting
                        AND NOT .email_verified
                        AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                )
            SELECT count(conflicting) = count(deleted)
        $$
    }

nickname and email parameters are optional because there are a few contexts where they will require additional query to fetch.

I have a few questions:

• (.nickname != <str>{} OR .email != <str>{}) is this the correct way of checking optional values?
  Actually this happens:

select <optional str>'test' = <str>{};
# {}
select <str>'test' = <str>{};
# {}

• for some reason type constraints are ignored until I pass both arguments:

select delete_conflicting_users(<optional str>{}, <optional str>'aadad');
# {}
select delete_conflicting_users(<optional str>'asda', <optional str>'aadad');
# ERROR: ConstraintViolationError: invalid email_type

email type has regex constraint requiring @, but this constraint is ignored in the 1st case and seems like nothing gets executed because function returns empty set.
EDIT: now this works for some reason, not sure if I even changed anything.

• should I create 4 overloaded functions instead of using optional values here?

Full schema: https://gist.github.com/Fogapod/95bce022040e039843fea0d9ae233a89

[Fogapod]

I was able to solve nickname and email check by replacing it with (nickname != '' OR email != '') and using default values instead of optional: nickname: name_type = '', email: email_type = ''.

I don't quite like it though. Is this the best solution? Having some null/none value as default would be better here. but I did not find anything in docs. Empty sets will not work because of the way the are compared.

EDIT: after doing this 2nd problem returned. Function only works correctly with both arguments.

[elprans]

is this the correct way of checking optional values?

Use coalescing comparison:

                conflicting := (
                    SELECT User
                    FILTER
                        (.nickname ?= nickname OR .email ?= email)
                    LIMIT 2
                ),

[Fogapod]

I don't think this is working correctly.
I created a new database, applied this https://gist.github.com/Fogapod/95bce022040e039843fea0d9ae233a89 schema and this happens:

# 1st value is number of conflicting users
# 2nd value is number of deleted users

select delete_conflicting_users(<optional str>{}, <optional str>{});
select delete_conflicting_users(<optional str>{'eugene'}, <optional str>{});
# {(0, 0)}
# {(0, 0)}
# no users in database - no conflicts

INSERT User {
    nickname := 'eugene',
    email := 'eugene@example.com',

};
select delete_conflicting_users(<optional str>{}, <optional str>{});
select delete_conflicting_users(<optional str>{'eugene'}, <optional str>{});
select delete_conflicting_users(<optional str>{'test'}, <optional str>{'test@example.com'});
# {(1, 0)}
# {(1, 0)}
# {(1, 0)}
# only 2nd invocation should return {(1, 0)} here because nickname `eugene` exists

If I run function body separately and put values in manually, it works as expected:

WITH
                    MODULE default,
                    conflicting := (
                        SELECT User
                        FILTER
                            (.nickname ?= <optional str>'test' OR .email ?= <optional str>{'test@example.com'})
                        LIMIT 2
                    ),
                    deleted := (
                        DELETE User
                        FILTER
                            User IN conflicting
                            AND NOT .email_verified
                            AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                    )
                SELECT (count(conflicting), count(deleted));
# {(0, 0)}

EDIT: reported it here: #1664 (comment)