Warning-producing CAST expression in WHERE clause exposes base table column values filtered out by view predicate

-- setup

--disable_warnings

DROP DATABASE IF EXISTS test_db;

DROP USER IF EXISTS 'low_priv_user'@'localhost';

--enable_warnings

CREATE DATABASE test_db;

CREATE USER 'low_priv_user'@'localhost' IDENTIFIED BY 'low_priv_user_pw';

CREATE TABLE test_db.accounts (

  id INT PRIMARY KEY AUTO_INCREMENT,

  tenant_id INT NOT NULL,

  email VARCHAR(128) NOT NULL,

  note VARCHAR(128) NOT NULL,

  UNIQUE KEY uq_email (email),

  KEY note_idx (note),

  KEY tenant_email (tenant_id, email)

);

INSERT INTO test_db.accounts (tenant_id, email, note) VALUES

    (10, 'visible@example.test', '12345'),

    (20, 'hidden@example.test', 'hidden-secret-marker'),

    (20, 'payroll@example.test', 'salary-band-confidential');

CREATE SQL SECURITY DEFINER VIEW test_db.tenant10_accounts AS

  SELECT id, email, note

  FROM test_db.accounts

  WHERE tenant_id = 10;

-- The low-privileged user gets SELECT on the views ONLY, never the base table.

GRANT SELECT ON test_db.tenant10_accounts      TO 'low_priv_user'@'localhost';

GRANT USAGE ON *.* TO 'low_priv_user'@'localhost';

SHOW GRANTS FOR 'low_priv_user'@'localhost';

connect (low, localhost, low_priv_user, low_priv_user_pw, test_db);

-- base table rows not accessible for user (correct)

--error ER_TABLEACCESS_DENIED_ERROR

SELECT * FROM test_db.accounts;

--error ER_TABLEACCESS_DENIED_ERROR

SELECT * FROM test_db.accounts WHERE email='hidden@example.test';

-- row not present in view (filtered by the view`s where) producing warning and the value

SELECT * FROM test_db.tenant10_accounts

  WHERE email='hidden@example.test'

  AND CAST(note AS UNSIGNED)=0;

SHOW WARNINGS;

Level	Code	Message

Warning	1292	Truncated incorrect INTEGER value: 'hidden-secret-marker'

SELECT * FROM test_db.tenant10_accounts

  WHERE email='payroll@example.test'

  AND CAST(note AS UNSIGNED)=0;

SHOW WARNINGS;

-- row absent; no warning produced.

SELECT * FROM test_db.tenant10_accounts

    WHERE email='absent@example.test'

    AND CAST(note AS UNSIGNED)=0;

SHOW WARNINGS;

-- cleanup

connection default;

disconnect low;

--disable_warnings

DROP DATABASE IF EXISTS test_db;

DROP USER IF EXISTS 'low_priv_user'@'localhost';

--enable_warnings