MySQL select...into variable yields null, even though data is there

My stored procedure performs SELECT ... INTO var yielding NULL, but if I repeat the same SELECT myself, I get a value.

Here's the relevant part of the schema

CREATE TABLE UrlAuthority
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,name            VARCHAR(255) NOT NULL COMMENT 'includes TLD suffix'
    ,UNIQUE(name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE UrlFqdn
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,authority       BIGINT NOT NULL COMMENT 'references example.com'
    ,name            VARCHAR(255) NOT NULL COMMENT 'host.example.com'
    ,FOREIGN KEY (authority) REFERENCES UrlAuthority (id)
    ,UNIQUE (authority, name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE Url
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,fqdn            BIGINT NOT NULL
    ,path            VARCHAR(255) NOT NULL
    ,FOREIGN KEY (fqdn) REFERENCES UrlFqdn (id)
    ,UNIQUE (fqdn, path)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And here's the procedure

```
CREATE PROCEDURE UrlToId (url TEXT)
BEGIN
DECLARE host TEXT;
DECLARE authority TEXT;
DECLARE tld TEXT;
DECLARE pathtext TEXT;

DECLARE authority_tld TEXT;
DECLARE fqdn TEXT;
DECLARE authority_id BIGINT;
DECLARE fqdn_id BIGINT;
DECLARE url_id BIGINT;
DECLARE err TEXT;

CALL UnpackURL (url, host, authority, tld, pathtext);

SELECT JoinHostNames(authority, tld) INTO authority_tld;
SELECT JoinHostNames(host, authority_tld) INTO fqdn;

INSERT IGNORE INTO UrlAuthority (name) VALUES (authority_tld);

SELECT id
INTO authority_id
FROM UrlAuthority
WHERE name=authority_tld;

-- LOG ('UrlAuthority', authority_tld, authority_id)

INSERT IGNORE INTO UrlFqdn (authority, name)
VALUES (authority_id, fqdn);

SELECT id

For a while, this one eluded me. I don't like SELECT ... INTO, so I really wanted to blame it for your trouble, but no.

Here'e the problem.

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE authority=authority_id AND name=fqdn;

How does MySQL interpret this?

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE 'mail4' = 1 AND name = 'mail4.z.uk';
/* Impossible WHERE */
/* see DECLARE authority TEXT; */

“A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”


— http://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

Using the same identifier for a variable and a column name makes a debugging mess, one way or another.

StackExchange Database Administrators Q#152348, answer score: 7