mail alias on domain alias does not work

Summary

email to an aliased email on an aliased domain does not work

Steps to reproduce

1. have an email alice@example.com

2. have an email alias bob@example.com => alice@example.com

3. have a domain alias @alternate.com => @example.com

4. Try to send an email to bob@alternate.com

5. 550 5.1.1 bob@alternate.com: Recipient address rejected: User unknown in virtual alias table;

Correct behaviour

Mail to be send to alice@example.com

Environment

Debian 11 ISPConfig Version: 3.2.9p1 Apache2.4

Proposed fix

See the patched query entry in /etc/postfix/mysql-virtual_forwardings.cf

#> postmap -q "bob@alternate.com" mysql:/etc/postfix/mysql-virtual_forwardings.cf

alice@example.com

query = SELECT s.destination AS target FROM mail_forwarding AS s
 WHERE (s.source = '%s' OR s.source = CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d'))
   AND s.type IN ('alias', 'forward') AND s.active = 'y' AND s.server_id = 1
 UNION
  SELECT s.destination AS target FROM mail_forwarding AS s
   WHERE s.source = '@%d' AND s.type = 'catchall'
     AND s.active = 'y' AND s.server_id = 1
     AND NOT EXISTS (
         SELECT email FROM mail_user
         WHERE (email = '%s' OR email = CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d'))
           AND EXISTS (
               SELECT domain_id FROM mail_domain
                WHERE domain = SUBSTRING_INDEX('%s', '@', -1)
                  AND active = 'y' AND server_id = 1
            )
            AND server_id = 1
     )
     AND NOT EXISTS (
         SELECT source FROM mail_forwarding
          WHERE (source = '%s' OR source = CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d'))
            AND active = 'y' AND server_id = 1
     )
 UNION
  SELECT s.destination AS target FROM mail_forwarding AS s
   INNER JOIN mail_forwarding AS s2
      ON ( s2.source = '@%d'
           AND s2.type = 'aliasdomain'
           AND s2.active = 'y'
           AND s2.server_id = 1)
   WHERE (s.source = CONCAT(SUBSTRING_INDEX('%u', '+', 1), s2.destination))
     AND s.type IN ('alias', 'forward')
     AND s.active = 'y'
     AND s.server_id = 1
  

PS: This settings (multiple alias) was working under ISPConfig Version: 3.1.12