Using GROUP BY after calling a stored procedure in MySQL

Question

I want to do something like this:

CALL testFunction('26e4ccaa-b125-3d9c-c7c1-58175227a48f')
GROUP BY id_c;

This doesn't seem to be working, so I tried this as well:

SELECT *
FROM (
   CALL testFunction('26e4ccaa-b125-3d9c-c7c1-58175227a48f')
) t
GROUP BY t.id_c;

And that doesn't work either. I can find no documentation on if this is or isn't possible.

I am using this stored procedure elsewhere, and it seemed redundant to create a whole new stored procedure just so I can include a GROUP BY. I am calling this stored procedure inside of a php application.

EDIT:

Here is the stored procedure:

BEGIN
    DECLARE lastInsertCount INT;
    DECLARE lastLayer INT;
    DROP TABLE IF EXISTS `temp`;
    DROP TABLE IF EXISTS `productGrid`;

    CREATE TABLE `temp`
    (
        `layer` INT(5),
        `title_c` VARCHAR(255), 
        `name` VARCHAR(255), 
        `rep_first_name` VARCHAR(255), 
        `rep_last_name` VARCHAR(255),
        `id_c` CHAR(36),
        `contact_status_reps_c` VARCHAR(255),
        `phone_mobile` VARCHAR(255),
        `contract_type_c` VARCHAR(255),
        `email_address` VARCHAR(255),
        `active_as_of_c` DATETIME,
        `assigned_user_id` CHAR(36)
    );

    CREATE TABLE `productGrid`
    (
        `percentage_c` INT(5),
        `name` VARCHAR(255), 
        `title_c` VARCHAR(255)
    );

    INSERT INTO `productGrid`(`percentage_c`, `name`, `title_c`)
    SELECT aaigloba_crm.pcg_product_comp_grid_cstm.percentage_c, 
           aaigloba_crm.aos_products.name, 
           aaigloba_crm.pcg_product_comp_grid_cstm.title_c
    FROM aaigloba_crm.aos_products_pcg_product_comp_grid_1_c 
        LEFT OUTER JOIN aaigloba_crm.aos_products ON aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.aos_products_pcg_product_comp_grid_1aos_products_ida = aaigloba_crm.aos_products.id
        LEFT OUTER JOIN aaigloba_crm.pcg_product_comp_grid_cstm ON aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.aos_products_pcg_product_comp_grid_1pcg_product_comp_grid_idb = aaigloba_crm.pcg_product_comp_grid_cstm.id_c
    WHERE aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.deleted = '0';

    INSERT INTO `temp`(`layer`, `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`)
    SELECT 0,
    wn_writing_number_cstm.title_c, 
    wn_writing_number.`name`, 
    preps_contracted_reps.first_name, 
    preps_contracted_reps.last_name, 
    wn_writing_number_cstm.id_c, 
    preps_contracted_reps_cstm.contact_status_reps_c, 
    preps_contracted_reps.phone_mobile, 
    preps_contracted_reps_cstm.contract_type_c, 
    email_addresses.email_address,
    STR_TO_DATE(preps_contracted_reps_cstm.active_as_of_c, '%m/%d/%Y'),
    wn_writing_number.assigned_user_id
FROM wn_writing_number LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida = wn_writing_number.id
     LEFT OUTER JOIN cac_customize_agent_comp ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp.id
     LEFT OUTER JOIN cac_customize_agent_comp_cstm ON cac_customize_agent_comp.id = cac_customize_agent_comp_cstm.id_c
     LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
     LEFT OUTER JOIN preps_contracted_reps_wn_writing_number_1_c ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id
     LEFT OUTER JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
     LEFT OUTER JOIN preps_contracted_reps_cstm ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
     LEFT OUTER JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = preps_contracted_reps_cstm.id_c
     LEFT OUTER JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
     LEFT OUTER JOIN wn_writing_number_cstm ON wn_writing_number_cstm.id_c = wn_writing_number.id
    WHERE wn_writing_number_cstm.wn_writing_number_id_c = passId AND wn_writing_number.deleted = '0' AND preps_contracted_reps.deleted = '0' AND wn_writing_number.deleted = '0' AND email_addr_bean_rel.deleted = '0'
    GROUP BY wn_writing_number.`name`;

    SET lastLayer := 0;
    SET lastInsertCount := 1;
    LayerLoop: WHILE lastInsertCount > 0 DO
      INSERT INTO `temp`(`layer`, `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`)
      SELECT lastLayer + 1,
        wn_writing_number_cstm.title_c, 
        wn_writing_number.`name`, 
        preps_contracted_reps.first_name, 
        preps_contracted_reps.last_name, 
        wn_writing_number_cstm.id_c,
        preps_contracted_reps_cstm.contact_status_reps_c, 
        preps_contracted_reps.phone_mobile, 
        preps_contracted_reps_cstm.contract_type_c, 
        email_addresses.email_address,
        STR_TO_DATE(preps_contracted_reps_cstm.active_as_of_c, '%m/%d/%Y'),
        wn_writing_number.assigned_user_id
    FROM wn_writing_number LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida = wn_writing_number.id
         LEFT OUTER JOIN cac_customize_agent_comp ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp.id
         LEFT OUTER JOIN cac_customize_agent_comp_cstm ON cac_customize_agent_comp.id = cac_customize_agent_comp_cstm.id_c
         LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
         LEFT OUTER JOIN preps_contracted_reps_wn_writing_number_1_c ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id
         LEFT OUTER JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
         LEFT OUTER JOIN preps_contracted_reps_cstm ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
         LEFT OUTER JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = preps_contracted_reps_cstm.id_c
         LEFT OUTER JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
         LEFT OUTER JOIN wn_writing_number_cstm ON wn_writing_number_cstm.id_c = wn_writing_number.id
      WHERE preps_contracted_reps.deleted = '0' AND wn_writing_number.deleted = '0' AND email_addr_bean_rel.deleted = '0' AND wn_writing_number_cstm.wn_writing_number_id_c IN (SELECT id_c FROM `temp` WHERE layer = lastLayer)
      GROUP BY wn_writing_number.`name`;

      SET lastInsertCount := ROW_COUNT();
      SET lastLayer := lastLayer + 1;
    END WHILE LayerLoop;

    SELECT `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`
    FROM `temp`
    ORDER BY rep_first_name ASC;
END

Answer 1

The GROUP clause is an SQL clause, and needs to be incorporated into a full SQL expression because the SQL parser optimizes and executes whole expressions atomically. The function returns a table, not an SQL expression, and an SQL clause can't modify a table.

Using temp tables is another sign you're conceptually decomposing your operation, probably deoptimizing it. Decomposition is a good pattern for procedural code, but it's an antipattern for declarative code.

You probably already know that the WHILE loop is problematic in an operation on relational data. Too bad you're not using isam tables.