Warning-producing expression in WHERE clause exposes hidden rows 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', 'visible'),

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

  (20, 'hidden2@example.test', 'hidden-two');

# view with only tenant_id = 10 

CREATE SQL SECURITY DEFINER VIEW test_db.tenant10_accounts AS

  SELECT id, email, note

  FROM test_db.accounts

  WHERE tenant_id = 10;

# grant SELECT privilege to the user on views

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

# connect as low privilege user

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

# base table is not accessible (correct, because the user has no privilege on it)

--error ER_TABLEACCESS_DENIED_ERROR

SELECT * FROM test_db.accounts;

# hidden rows (or rows not in the view) are not visible when queried 

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

# select on view with warning producing WHERE clause

# returns 0 rows but produces a warning, indicates row is present in base table

SELECT * FROM tenant10_accounts

  WHERE email='hidden@example.test'

    AND JSON_EXTRACT(note, CONCAT('$[', note)) IS NULL;

SHOW WARNINGS;

# row does not exist in base table; hence no warnings 

SELECT * FROM tenant10_accounts

  WHERE email='absent@example.test'

    AND JSON_EXTRACT(note, CONCAT('$[', note)) IS NULL;

SHOW WARNINGS;

---

# same as above with a non-indexed column

# row hidden for the view but produces warning indicating its existence in base table

SELECT * FROM tenant10_accounts

  WHERE note='hidden-secret-marker'

    AND JSON_EXTRACT(note, CONCAT('$[', note)) IS NULL;

SHOW WARNINGS;

# absent row, no warning

SELECT * FROM tenant10_accounts

  WHERE note='absent-marker'

    AND JSON_EXTRACT(note, CONCAT('$[', note)) IS NULL;

SHOW WARNINGS;