MySQL - ORDER BY clause causing SQL_CALC_FOUND_ROWS select to not return data

Question

I am using MySQL 5.6 on a centos 6 box (mysql Ver 14.14 Distrib 5.6.44, for Linux (i686) using EditLine wrapper)

I have a query with multiple inner joins. I have identified that when using a ORDER BY clause with SQL_CALC_FOUND_ROWS no data is returned. The query says "ok" for the message, and I have a duration for how long the query took- but nothing is returned.

If I remove "SQL_CALC_FOUND_ROWS" then I get my rows. Inside MySQL workbench I can see a duration but under "fetch" time it doesn't even attempt to fetch.

The query is:

SELECT SQL_CALC_FOUND_ROWS
    l.id AS licensee_id,
    l.agency_id,
    l.licensee_fname,
    l.licensee_name,
    l.licensee_lname,
    l.licensee_email,
    licensee_certs.cert_number,
    agency.agency_name,
    licensee_cert_types.cert_name,
    licensee_certs.cert_issue_date AS approval_timestamp,
    licensee_certs.cert_issue_date AS issue_timestamp,
    licensee_certs.cert_expiration_date AS expire_timestamp,
    licensee_certs.cert_status AS licensee_status,
    licensee_certs.cert_status,
    (licensee_certs.cert_expiration_date - UNIX_TIMESTAMP()) AS days_remaining,
    h_cache.acquired AS total_hours,
    h_cache.pending AS pending_hours,
    CONCAT(IFNULL(licensee_cert_types.cert_name, ''),
            ' ',
            IFNULL(licensee_certs.cert_number, ''),
            ' ',
            IFNULL(licensee_certs.cert_status, ''),
            ' ',
            IFNULL(l.licensee_fname, ''),
            ' ',
            IFNULL(l.licensee_lname, ''),
            ' ',
            IFNULL(l.licensee_email, ''),
            ' ',
            IFNULL(agency.agency_name, ''),
            ' ',
            IFNULL(agency.agency_abbr, '')) AS search
FROM
    licensee AS l
        LEFT JOIN
    licensee_certs ON l.id = licensee_certs.licensee_id
        AND licensee_certs.agency_id = l.agency_id
        LEFT JOIN
    agency ON agency.id = l.agency_id
        LEFT JOIN
    licensee_cert_hour_cache AS h_cache ON h_cache.licensee_id = l.id
        AND h_cache.agency_id = l.agency_id
        AND h_cache.licensee_cert_type_id = licensee_certs.cert_type
        INNER JOIN
    licensee_cert_types ON licensee_certs.cert_type = licensee_cert_types.id
GROUP BY licensee_certs.id
ORDER BY l.licensee_fname ASC
limit 5

If I remove the order by then I get my 5 rows. Alternatively, if I remove the "SQL_CALC_FOUND_ROWS" I get my 5 rows.

Why can I not do both the order by and SQL calc?

I think it's also important to note that this exact query was working just fine on MySQL 5.5. I upgraded to 5.6.44 and getting this behavior.

On our production server, running 5.6.42- we have no issues with this query.

UPDATE: It appears to be this specific query mentioned above. If I run a different query with the SQL_CALC + ORDER BY I get my results, ie:

SELECT SQL_CALC_FOUND_ROWS
    p.*,
    a.agency_name,
    (SELECT 
            COUNT(*)
        FROM
            providership_notes
        WHERE
            providership_notes.provider_id = p.id) AS note_count,
    (SELECT 
            `user_id`
        FROM
            `user_entity_relations`
        WHERE
            `instance_id` = p.id
                AND `entity_type_id` = 2
        LIMIT 1) AS `user_id`,
    CONCAT(IFNULL(providership_name, ''),
            ' ',
            IFNULL(providership_abbr, ''),
            ' ',
            IFNULL(agency_name, ''),
            ' ',
            IFNULL(providership_state, '')) AS search
FROM
    providership AS p
        LEFT JOIN
    agency_providership_relations AS r ON r.providership_id = p.id
        AND `r`.`status` = 'approved'
        LEFT JOIN
    agency AS a ON r.agency_id = a.id
GROUP BY p.id
ORDER BY providership_name ASC
LIMIT 0 , 10```

MySQL - ORDER BY clause causing SQL_CALC_FOUND_ROWS select to not return data

Answers (1)

Related Questions