Subquery with LIMIT in IN clause should be supported

[mkrecek234]

UPDATE ignore everything below as reported very badly.

MySQL/MariaDB repro: https://github.com/atk4/data/issues/1096#issuecomment-1454742258

MySQL issue needed for workaround: TODO open an issue

MariaDB issue needed for workaround: https://jira.mariadb.org/browse/MDEV-32657

---

I have a model that is a bit special, as we have a Customer model, with a hasOne('default_customer_contact_id') and a hasMany(CustomerContact). The default_contact_id is telling which of the multiple contacts is standard. Obviously, this creates the issue, that the sub-query is also limited 0,9 in SQL.

If you use this model and set a limit (e.g. like in Crud's IPP), it renders an issue that LIMIT is not supported in MySQL inside IN statement.

Here is the problematic query:

select 
  `id`, 
  `last_name`, 
  `given_name`, 
  `customer_id`, 
  (
    select 
      `default_contact_id` 
    from 
      `customer` `_C_c_06aa3ddbf548` 
    where 
      (
        `is_deleted` = 0 
        and `id` = `_C_ed9e2d38ef19`.`customer_id`
      )
  ) `default_contact_id`, 
  (
    select 
      `default_invoice_contact_id` 
    from 
      `customer` `_C_c_06aa3ddbf548` 
    where 
      (
        `is_deleted` = 0 
        and `id` = `_C_ed9e2d38ef19`.`customer_id`
      )
  ) `default_invoice_contact_id`, 
  (
    select 
      `default_shipping_contact_id` 
    from 
      `customer` `_C_c_06aa3ddbf548` 
    where 
      (
        `is_deleted` = 0 
        and `id` = `_C_ed9e2d38ef19`.`customer_id`
      )
  ) `default_shipping_contact_id`, 
  (
    select 
      (
        CONCAT(
          `matchcode`, " (# ", `customer_no`, 
          ") "
        )
      ) `name` 
    from 
      `customer` `_C_c_06aa3ddbf548` 
    where 
      (
        `is_deleted` = 0 
        and `id` = `_C_ed9e2d38ef19`.`customer_id`
      )
  ) `customer`, 
  `address_id`, 
  (
    select 
      `company` 
    from 
      `customer_address` `_C_a_9e7dd535952f` 
    where 
      (
        `customer_id` in (
          select 
            `id` 
          from 
            `customer` `_C_c_06aa3ddbf548` 
          where 
            (
              `is_deleted` = 0 
              and `id` in (
                select 
                  `customer_id` 
                from 
                  `customer_contact` `_C_ed9e2d38ef19` 
                where 
                  `customer_id` = 1016981 
                order by 
                  (
                    CONCAT_WS(" ", `given_name`, `last_name`)
                  ) 
                limit 
                  0, 
                  9
              )
            ) 
          order by 
            `LTM_sales` desc, 
            `customer_status_id`, 
            (
              CONCAT(
                `matchcode`, " (# ", `customer_no`, 
                ") "
              )
            )
        ) 
        and `id` = `_C_ed9e2d38ef19`.`address_id`
      )
  ) `company`, 
  `position`, 
  `department`, 
  `email`, 
  `phone`, 
  `mobile`, 
  `fax`, 
  `birthday`, 
  `note`, 
  `language_id`, 
  (
    REGEXP_REPLACE(`phone`, "[ ,(,),+,-]", "")
  ) `phone_clean`, 
  (
    REGEXP_REPLACE(`mobile`, "[ ,(,),+,-]", "")
  ) `mobile_clean`, 
  (
    `id` = (
      select 
        `default_contact_id` 
      from 
        `customer` `_C_c_06aa3ddbf548` 
      where 
        (
          `is_deleted` = 0 
          and `id` = `_C_ed9e2d38ef19`.`customer_id`
        )
    )
  ) `default_contact`, 
  (
    `id` = (
      select 
        `default_shipping_contact_id` 
      from 
        `customer` `_C_c_06aa3ddbf548` 
      where 
        (
          `is_deleted` = 0 
          and `id` = `_C_ed9e2d38ef19`.`customer_id`
        )
    ) 
    OR (
      (
        select 
          `default_shipping_contact_id` 
        from 
          `customer` `_C_c_06aa3ddbf548` 
        where 
          (
            `is_deleted` = 0 
            and `id` = `_C_ed9e2d38ef19`.`customer_id`
          )
      ) IS NULL 
      AND `id` = (
        select 
          `default_contact_id` 
        from 
          `customer` `_C_c_06aa3ddbf548` 
        where 
          (
            `is_deleted` = 0 
            and `id` = `_C_ed9e2d38ef19`.`customer_id`
          )
      )
    )
  ) `default_shipping_contact`, 
  (
    `id` = (
      select 
        `default_invoice_contact_id` 
      from 
        `customer` `_C_c_06aa3ddbf548` 
      where 
        (
          `is_deleted` = 0 
          and `id` = `_C_ed9e2d38ef19`.`customer_id`
        )
    ) 
    OR (
      (
        select 
          `default_invoice_contact_id` 
        from 
          `customer` `_C_c_06aa3ddbf548` 
        where 
          (
            `is_deleted` = 0 
            and `id` = `_C_ed9e2d38ef19`.`customer_id`
          )
      ) IS NULL 
      AND `id` = (
        select 
          `default_contact_id` 
        from 
          `customer` `_C_c_06aa3ddbf548` 
        where 
          (
            `is_deleted` = 0 
            and `id` = `_C_ed9e2d38ef19`.`customer_id`
          )
      )
    )
  ) `default_invoice_contact`, 
  (
    CONCAT_WS(
      ",", 
      IF(
        (
          `id` = (
            select 
              `default_contact_id` 
            from 
              `customer` `_C_c_06aa3ddbf548` 
            where 
              (
                `is_deleted` = 0 
                and `id` = `_C_ed9e2d38ef19`.`customer_id`
              )
          )
        ), 
        "Default", 
        ""
      ), 
      IF(
        (
          `id` = (
            select 
              `default_invoice_contact_id` 
            from 
              `customer` `_C_c_06aa3ddbf548` 
            where 
              (
                `is_deleted` = 0 
                and `id` = `_C_ed9e2d38ef19`.`customer_id`
              )
          ) 
          OR (
            (
              select 
                `default_invoice_contact_id` 
              from 
                `customer` `_C_c_06aa3ddbf548` 
              where 
                (
                  `is_deleted` = 0 
                  and `id` = `_C_ed9e2d38ef19`.`customer_id`
                )
            ) IS NULL 
            AND `id` = (
              select 
                `default_contact_id` 
              from 
                `customer` `_C_c_06aa3ddbf548` 
              where 
                (
                  `is_deleted` = 0 
                  and `id` = `_C_ed9e2d38ef19`.`customer_id`
                )
            )
          )
        ), 
        "Invoice", 
        NULL
      ), 
      IF(
        (
          `id` = (
            select 
              `default_shipping_contact_id` 
            from 
              `customer` `_C_c_06aa3ddbf548` 
            where 
              (
                `is_deleted` = 0 
                and `id` = `_C_ed9e2d38ef19`.`customer_id`
              )
          ) 
          OR (
            (
              select 
                `default_shipping_contact_id` 
              from 
                `customer` `_C_c_06aa3ddbf548` 
              where 
                (
                  `is_deleted` = 0 
                  and `id` = `_C_ed9e2d38ef19`.`customer_id`
                )
            ) IS NULL 
            AND `id` = (
              select 
                `default_contact_id` 
              from 
                `customer` `_C_c_06aa3ddbf548` 
              where 
                (
                  `is_deleted` = 0 
                  and `id` = `_C_ed9e2d38ef19`.`customer_id`
                )
            )
          )
        ), 
        "Shipping", 
        NULL
      )
    )
  ) `default`, 
  (
    CONCAT_WS(" ", `given_name`, `last_name`)
  ) `contact_name`, 
  (
    CONCAT_WS(" ", `given_name`, `last_name`)
  ) `name` 
from 
  `customer_contact` `_C_ed9e2d38ef19` 
where 
  `customer_id` = 1016981 
order by 
  (
    CONCAT_WS(" ", `given_name`, `last_name`)
  ) 
limit 
  0, 
  9

(query reformatted using https://codebeautify.org/sqlformatter)