How do I solve this error caused from a Pivot?

Question

When trying to pivot my table - a dynamic one which I can't define as entries my change can be added or deleted - I receive a fatal error. I used a checked answer from this question and changed it where it was necessary. You will see that I use AVG instead of SUM.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'AVG(IF(country = ''',
          country,
          ''', option_id, NULL)) AS ',
          country
        )
  ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;
SET @sql = CONCAT('SELECT gspa.question_id', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error I get is:

Fatal error: Uncaught TypeError: Argument 1 passed to PhpMyAdmin\Sql::resultSetHasJustOneTable() must be of the type array, boolean given, called in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php on line 1908 and defined in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php:143 Stack trace: #0 /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php(1908): PhpMyAdmin\Sql->resultSetHasJustOneTable(false) #1 /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php(2252): PhpMyAdmin\Sql->getQueryResponseForResultsReturned(true, Array, '1812apmy', 'gs_poll_answers', NULL, NULL, Object(PhpMyAdmin\Display\Results), './themes/pmahom...', '1', 0, NULL, NULL, NULL, NULL, NULL, 'SELECT\r\n\tGROUP_...', NULL) #2 /usr/local/dh/web/phpmyadmin/import.php(736): PhpMyAdmin\Sql->executeQueryAndGetQueryResponse(Array, false, '1812apmy', 'gs_poll_answers', NULL, NULL, NULL, NULL, NULL, NULL, 'tbl_sql.php', './themes/pmahom...', NULL, NULL, NULL, 'SELECT\r\n\tGROUP_...', NULL, NULL) #3 {main} thrown in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php on line 143

For the moment I have no idea what the problem is, except of the idea that it can't handle the Join in the "From".

Is my code the problem, is it the server or anything else?

EDIT: Create Tables for skill_cv_test

CREATE TABLE `skill_cv_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `usr_id` int(32) NOT NULL,
 `category` int(16) NOT NULL COMMENT 'school_or_job',
 `comp_id` int(32) NOT NULL,
 `position` varchar(64) NOT NULL,
 `description` text,
 `country` varchar(16) NOT NULL,
 `state` varchar(16) NOT NULL,
 `city` varchar(64) NOT NULL,
 `start_date` date NOT NULL,
 `end_date` date DEFAULT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8

and gs_poll_answers

CREATE TABLE `gs_poll_answers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(64) NOT NULL,
 `poll_id` int(64) NOT NULL,
 `question_id` int(64) NOT NULL,
 `option_id` int(64) NOT NULL,
 `vote_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8

With out your Tables and data it is not reproducible. Maybe if you produced the result from @sql, but that you could run ion your own simply use instead of the stmt use olny SELECT @sql; — nbk, Mar 28 '20 at 22:25
Well, seems like the issue is on the server. I tried a simpler query very much like the one in the linked solution and I still have the issue. — nucky, Mar 28 '20 at 22:36
It shouldn't be a problem if your sql is correct, but still to reproduce there a a lot of information missing mysql version , php Version phpmyadmin Version. .... — nbk, Mar 28 '20 at 22:39

score 0 · Accepted Answer · answered Mar 28 '20 at 23:06

I have found a solution. It's not exactly a solution on the pivot-issue and the error, but a way to the desired solution. By querying all occurring countries in table skill_cv_test I can create the part of the required code that will remain dynamic through a for-loop in PHP.

Like "SELECT country FROM skill_cv_test WHERE comp_id = 1". With a loop I then can create the following lines: $qry .= "AVG(IF(sct.country = '.$row["country"].',option_id,NULL)) AS '.$row["country"].'". Through this approach I can achieve the same result.

score 0 · Answer 2 · answered Mar 28 '20 at 23:08

AS i suspected, it has nothing to do with your php.

your query is a little bit wrong.

A missing comma When you collect all strings together and i used double quotes, mysql doesn't like the three ''' at all

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'AVG(IF(country = '"',
          country,
          '"', option_id, NULL)) AS "',
          country,'"'
        )
  ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;
SET @sql = CONCAT('SELECT gspa.question_id,', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thanks for spotting the missing comma. Anyway, the issue with the error remains. But can you tell me where I missed the comma? — nucky, Mar 28 '20 at 23:12
It runs, you can check it https://www.db-fiddle.com/f/ihwaUZpdhBvd4vhTqQAVsG/0 — nbk, Mar 28 '20 at 23:35

score 0 · Answer 3 · 2020-03-29T18:14:24.280

Try to escape single quotes when concatenating text. Example based on your code:

Note: in any case add an IF statement to avoid execution in the case of @sql being NULL

SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('AVG(IF(sct.country = \'',sctable.country,'\',', option_id,', 0)) AS ',sctable.country)
    ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;

// at this point @sql should not be null    
IF @sql IS NOT NULL THEN

SET @sql = CONCAT('SELECT gspa.question_id AS question_id,', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;

It isn't enough there is another error as i described in my answer — nbk, Mar 28 '20 at 23:36

How do I solve this error caused from a Pivot?

3 Answers3