Description:
The result set of a query changes depending solely on whether an index is defined as VISIBLE or INVISIBLE.
The schema, data, and query text are identical in both cases. The only difference is index visibility.
Index visibility should affect optimization decisions but must not affect query semantics. However, the test case below produces different result sets.
How to repeat:
```sql
-- db0: with INVISIBEL
DROP DATABASE IF EXISTS db0;
CREATE DATABASE db0;
USE db0;
CREATE TABLE t0 (c1 INT, c2 DECIMAL) ENGINE = MEMORY;
CREATE TABLE t1 (c1 TEXT);
INSERT INTO t0(c1, c2) VALUES(0, 0);
INSERT INTO t1(c1) VALUES('0B'), ('');
CREATE INDEX i2 ON t0(c1, c2) INVISIBLE;
SELECT * FROM t0 AS ta0 INNER JOIN t1 AS ta1 ON (ta0.c2) = (ta1.c1) RIGHT JOIN t0 AS ta2 ON (ta0.c1) = (ta2.c1);
-- returns 2 rows: ( 0, 0, 0B, 0, 0), (0, 0, , 0, 0)
-- db1: without INVISIBEL
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
CREATE TABLE t0 (c1 INT, c2 DECIMAL) ENGINE = MEMORY;
CREATE TABLE t1 (c1 TEXT);
INSERT INTO t0(c1, c2) VALUES(0, 0);
INSERT INTO t1(c1) VALUES('0B'), ('');
CREATE INDEX i2 ON t0(c1, c2);
SELECT * FROM t0 AS ta0 INNER JOIN t1 AS ta1 ON (ta0.c2) = (ta1.c1) RIGHT JOIN t0 AS ta2 ON (ta0.c1) = (ta2.c1);
-- returns 1 row: (0, 0, , 0, 0)
```
Description: The result set of a query changes depending solely on whether an index is defined as VISIBLE or INVISIBLE. The schema, data, and query text are identical in both cases. The only difference is index visibility. Index visibility should affect optimization decisions but must not affect query semantics. However, the test case below produces different result sets. How to repeat: ```sql -- db0: with INVISIBEL DROP DATABASE IF EXISTS db0; CREATE DATABASE db0; USE db0; CREATE TABLE t0 (c1 INT, c2 DECIMAL) ENGINE = MEMORY; CREATE TABLE t1 (c1 TEXT); INSERT INTO t0(c1, c2) VALUES(0, 0); INSERT INTO t1(c1) VALUES('0B'), (''); CREATE INDEX i2 ON t0(c1, c2) INVISIBLE; SELECT * FROM t0 AS ta0 INNER JOIN t1 AS ta1 ON (ta0.c2) = (ta1.c1) RIGHT JOIN t0 AS ta2 ON (ta0.c1) = (ta2.c1); -- returns 2 rows: ( 0, 0, 0B, 0, 0), (0, 0, , 0, 0) -- db1: without INVISIBEL DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; USE db1; CREATE TABLE t0 (c1 INT, c2 DECIMAL) ENGINE = MEMORY; CREATE TABLE t1 (c1 TEXT); INSERT INTO t0(c1, c2) VALUES(0, 0); INSERT INTO t1(c1) VALUES('0B'), (''); CREATE INDEX i2 ON t0(c1, c2); SELECT * FROM t0 AS ta0 INNER JOIN t1 AS ta1 ON (ta0.c2) = (ta1.c1) RIGHT JOIN t0 AS ta2 ON (ta0.c1) = (ta2.c1); -- returns 1 row: (0, 0, , 0, 0) ```