CODEWITHSUNDEEP
mysql
spraff
spraff

Reputation: 33395

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?

Upvotes: 1

Views: 38

Answers (1)

user4003407
user4003407

Reputation: 22102

When you inside stored procedure, unqualified id in SELECT id FROM Currency WHERE code='ETH' refer to stored 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'.

Upvotes: 2

Related Questions