spraff spraff - 7 months ago 10
SQL Question

Why does this stored procedure give a different result from executing its contents manually?

Here's a simple MySQL schema

CREATE TABLE Currency
(
id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY
,code CHAR(3) NOT NULL UNIQUE
,exponent TINYINT NOT NULL
,sign VARCHAR (5) NOT NULL DEFAULT '¤'
,ccx_enabled BOOLEAN NOT NULL DEFAULT FALSE
,is_fiat BOOLEAN NOT NULL
);

CREATE TABLE `SiteAccount`
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,currency SMALLINT NOT NULL
,iban VARCHAR (34) NULL DEFAULT NULL
,ethereum CHAR (40) NULL DEFAULT NULL
,crypto_user INT NULL DEFAULT NULL
,FOREIGN KEY (currency) REFERENCES Currency (id)
);

CREATE PROCEDURE SetUpUserEthereumAddress (id INT, addr TEXT)
INSERT INTO SiteAccount (currency, ethereum, crypto_user)
VALUES
(
(SELECT id FROM Currency WHERE code='ETH'),
addr,
id
);


If I call the stored procedure, it creates a row with the wrong currency.

INSERT INTO Currency (code, exponent, sign, is_fiat, ccx_enabled) VALUES
('GBP', 2, '£', 1, 1)
,('USD', 2, '$', 1, 1)
,('EUR', 2, '€', 1, 1)
,('ETH', 6, 'Ξ', 0, 1)
;

CALL SetUpUserEthereumAddress (1, '5bb0aa60d694714db16cbd3e3574c59d5ee4a95b');

select * from SiteAccount JOIN Currency on SiteAccount.currency=Currency.id;


This creates a row with the currency GBP instead of ETH. But if I copy out the body of the stored procedure and substitute the arguments manually, I get the correct result.

INSERT INTO SiteAccount (currency, ethereum, crypto_user)
VALUES
(
(SELECT id FROM Currency WHERE code='ETH'),
'----aa60d694714db16cbd3e3574c59d5ee4a95b',
2
);

select * from SiteAccount JOIN Currency on SiteAccount.currency=Currency.id;


This creates a record for currency ETH as expected.

Why does the stored procedure select/insert the wrong Currency id?

Answer

When you inside stored procedure, unqualified id in SELECT id FROM Currency WHERE code='ETH' refer to storage procedure id parameter, but not id column in Currency table. To refer to table column you need to qualify id with table name: SELECT Currency.id FROM Currency WHERE code='ETH'.