Notice Undefined index: ORDER BY in phpmyadmin stored procedure

Question

I've written an procedure which looks like the below code:

DELIMITER $$
CREATE PROCEDURE get_news(IN lat DECIMAL(10,8), IN lon DECIMAL(11,8), IN miles FLOAT)
BEGIN
    SELECT `latitude`,`longitude`,COUNT(*) count, (
        3959 *
        acos(
            cos(radians(38.9071923)) *
            cos(radians(`latitude`)) *
            cos(radians(`longitude`) - radians(77.0368707)) +
            sin(radians(38.9071923)) *
            sin(radians(`latitude`))
        )
    ) `distance`
    FROM `news`
    GROUP BY `latitude`,`longitude`
    HAVING `distance` < miles;
END$$
DELIMITER ;

and when I call this procedure from phpmyadmin, I get the following error:

Notice in ./libraries/sql-parser/src/Utils/Query.php#570
 Undefined index: ORDER BY

Backtrace

./libraries/sql-parser/src/Utils/Query.php#666: SqlParser\Utils\Query::getClause(
object,
object,
string 'ORDER BY',
integer -1,
boolean false,
)
./libraries/DisplayResults.php#1385: SqlParser\Utils\Query::replaceClause(
object,
object,
string 'ORDER BY',
string '',
)
./libraries/DisplayResults.php#4368: PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(
array,
string '',
)
./libraries/sql.lib.php#1643: PMA\libraries\DisplayResults->getTable(
object,
array,
array,
boolean false,
)
./libraries/sql.lib.php#1965: PMA_getHtmlForSqlQueryResultsTable(
object,
string './themes/pmahomme/img/',
NULL,
array,
boolean false,
integer 80,
integer 80,
NULL,
object,
array,
)
./libraries/sql.lib.php#2184: PMA_getQueryResponseForResultsReturned(
object,
array,
string 'news-api',
string '',
NULL,
NULL,
object,
string './themes/pmahomme/img/',
integer 80,
integer 80,
NULL,
NULL,
NULL,
NULL,
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
)
./import.php#800: PMA_executeQueryAndGetQueryResponse(
array,
boolean false,
string 'news-api',
string '',
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
NULL,
NULL,
NULL,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
NULL,
)

When I run the query directly, It works with no error, but when I call the procedure, I get the above errors in phpmyadmin. If anybody has faced this problem, please help me with this.

Thanks

Answer 1

You have used HAVING without an Aggregate Function. You could have used HAVING COUNT(xyz) = 5 if you really want to use HAVING clause here. Otherwise, just use WHERE clause.

Having is a Where clause for Aggregate functions. Whenever you want to use a condition with aggregate functions like AVG, SUM, COUNT, etc, you need to use Having. If you just want to check a condition, then use Where clause instead.