The SQL query works on its own, but breaks with an "unknown in on column" in the stored procedure

Question

My select request with an internally connected subquery works when I run it myself, but it results in an error Unknown column 'cities.state' in 'on clause'in the context of the stored procedure. What can cause a query that usually works to break inside a stored procedure? Why return this unknown column error for cities.state, rather than cities.name? MySQL 5.7

CREATE DEFINER=`root`@`localhost` PROCEDURE `geogen`(IN State CHAR(2), CityMin INT(5), CityMax INT(6))
BEGIN

#Create a temporary table to hold the final GeoArea product
DROP TABLE IF EXISTS `geodb`.`geoareas`;
CREATE TEMPORARY TABLE `geodb`.`geoareas` LIKE `geodb`.`geoareatemplate`;

INSERT INTO `geodb`.`geoareas` (`geoarea`, `zip`, `state`)
# Zip code sets of cities/towns with a population between CityMin and CityMax are their own GeoAreas.
SELECT CONCAT(`uszipcode`.`name`, ' ', `uszipcode`.`state`) as 'geoarea', `uszipcode`.`zip`, `uszipcode`.`state`
FROM `geodb`.`uszipcode`
INNER JOIN
(SELECT `name`, `state`, SUM(`population`) AS 'Population'
FROM `geodb`.`uszipcode`
WHERE `uszipcode`.`state` = State
GROUP BY `name`
HAVING (SUM(`population`) >= CityMin AND SUM(`population`) <= CityMax)) as `cities`
ON `uszipcode`.`name` = `cities`.`name`
AND `uszipcode`.`state` = `cities`.`state`
ORDER BY `uszipcode`.`name`, `uszipcode`.`zip`;

SELECT * FROM geodb.geoareas;
END

When I run this query with

CALL geogen('TX', 35000, 70000);

I get an error

0   76  13:10:22    CALL geogen('TX', 35000, 70000) Error Code: 1054. Unknown column 'cities.state' in 'on clause'  0.031 sec

However, when I run the query on my own, the results are returned.

SELECT CONCAT(`uszipcode`.`name`, ' ', `uszipcode`.`state`) as 'geoarea', `uszipcode`.`zip`, `uszipcode`.`state`
FROM `geodb`.`uszipcode`
INNER JOIN
(SELECT `name`, `state`, SUM(`population`) AS 'Population'
FROM `geodb`.`uszipcode`
WHERE `uszipcode`.`state` = 'TX'
GROUP BY `name`
HAVING (SUM(`population`) >= 30000 AND SUM(`population`) <= 70000)) as `cities`
ON `uszipcode`.`name` = `cities`.`name`
AND `uszipcode`.`state` = `cities`.`state`
ORDER BY `uszipcode`.`name`, `uszipcode`.`zip`;

Result

LIMIT 0, 1000   121 row(s) returned 0.453 sec / 0.000 sec

Answer 1

MySQL , / State cities.state. , MySQL, , , , , , , . , State VarState, .

CREATE DEFINER=`root`@`localhost` PROCEDURE `geogen`(IN VarState CHAR(2), CityMin INT(5), CityMax INT(6))
BEGIN

#Create a temporary table to hold the final GeoArea product
DROP TABLE IF EXISTS `geodb`.`geoareas`;
CREATE TEMPORARY TABLE `geodb`.`geoareas` LIKE `geodb`.`geoareatemplate`;

# Zip code sets of cities/towns with a population between CityMin and CityMax are their own GeoAreas.
INSERT INTO `geodb`.`geoareas` (`geoarea`, `zip`, `state`)
SELECT CONCAT(`uszipcode`.`name`, ' ', `uszipcode`.`state`) as 'geoarea', `uszipcode`.`zip`, `uszipcode`.`state`
FROM `geodb`.`uszipcode`
INNER JOIN
(SELECT `name`, `state`, SUM(`population`) AS 'Population'
FROM `geodb`.`uszipcode`
WHERE `uszipcode`.`state` = VarState
GROUP BY `name`
HAVING (SUM(`population`) >= CityMin AND SUM(`population`) <= CityMax)) AS `cities`
ON `uszipcode`.`name` = `cities`.`name`
AND `uszipcode`.`state` = `cities`.`state`
ORDER BY `uszipcode`.`name`, `uszipcode`.`zip`;

SELECT * FROM geodb.geoareas
ORDER BY `geoarea`, `zip`;
END

Answer 2

SELECT () :

INSERT INTO `geodb`.`geoareas` (`geoarea`, `zip`, `state`)
(SELECT CONCAT(`uszipcode`.`name`, ' ', `uszipcode`.`state`) as 'geoarea', `uszipcode`.`zip`, `uszipcode`.`state`
FROM `geodb`.`uszipcode`
INNER JOIN
(SELECT `name`, `state`, SUM(`population`) AS 'Population'
FROM `geodb`.`uszipcode`
WHERE `uszipcode`.`state` = State
GROUP BY `name`, `state`
HAVING (SUM(`population`) >= CityMin AND SUM(`population`) <= CityMax)) as `cities`
ON `uszipcode`.`name` = `cities`.`name`
AND `uszipcode`.`state` = `cities`.`state`
ORDER BY `uszipcode`.`name`, `uszipcode`.`zip`);

Answer 3

try the following:

 DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `geogen`(IN State CHAR(2), CityMin INT(5), CityMax INT(6))
BEGIN

#temp cities
DROP TABLE IF EXISTS `geodb`.`cities_temp`;
CREATE TEMPORARY TABLE `geodb`.`cities_temp`
SELECT `name`, `state`, SUM(`population`) AS 'Population'
FROM `geodb`.`uszipcode`
WHERE `uszipcode`.`state` = State
GROUP BY `name`
HAVING (SUM(`population`) >= CityMin AND SUM(`population`) <= CityMax) ;
#Create a temporary table to hold the final GeoArea product
DROP TABLE IF EXISTS `geodb`.`geoareas`;
CREATE TEMPORARY TABLE `geodb`.`geoareas` LIKE `geodb`.`geoareatemplate`;

INSERT INTO `geodb`.`geoareas` (`geoarea`, `zip`, `state`)
# Zip code sets of cities/towns with a population between CityMin and CityMax are their own GeoAreas.
SELECT CONCAT(`uszipcode`.`name`, ' ', `uszipcode`.`state`) as 'geoarea', `uszipcode`.`zip`, `uszipcode`.`state`
FROM `geodb`.`uszipcode`
INNER JOIN
`geodb`.`cities_temp` as `cities`
ON `uszipcode`.`name` = `cities`.`name`
AND `uszipcode`.`state` = `cities`.`state`
ORDER BY `uszipcode`.`name`, `uszipcode`.`zip`;

SELECT * FROM geodb.geoareas;
END$$

DELIMITER ;