Missing columns in information_schema.COLUMNS for tables with virtual columns
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
Developing on Windows XP32 and MariaDB 5.3.3, I needed columns in 'information_
Normally, when you query
SELECT * FROM information_
it should show the columns of all tables in the databases. Instead, from database dbw2 only the columns of the table 'vorgangsartliste' shows up.
Funny enough, if you create a view named 'v_kunden' with
SELECT * FROM s_kunden
the columns of the view show up in information_
If you create a clone table 's_kunden2' with the output of
SHOW CREATE TABLE s_kunden
the columns of this clone don't show up in information_
IF you issue an
SHOW COLUMNS FROM s_kunden, the result is as expected.
OS: Windows 32 Bit
Tested Maria versions: 5.3.3, 5.2.10
How to repeat:
1. Unzip archive "data.7z" to the datadir and run mysqld.
2. Start mysql (root has no pw) and issue following query:
SELECT * FROM information_
3. What we should see are all columns of all 3 tables.
Instead, we only see 3 columns from table vorgangsartliste.
Hint: In table 's_kunden', columns 'online_rma' and 'aktiv' are both of type 'virtual'. If you remove these 2, the remaining columns show up in information_
description: | updated |
description: | updated |
Changed in maria: | |
assignee: | nobody → Igor Babaev (igorb-seattle) |
summary: |
- Missing columns in information_schema.COLUMNS + Missing columns in information_schema.COLUMNS for tables with virtual + columns |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Reproducible on current 5.2, 5.3, 5.5.
If a table has a VIRTUAL or PERSISTENT column, I_S.COLUMNS does not return any columns of this table.
# Test case:
--disable_warnings schema. columns schema. columns schema. columns
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( a INT, b INT AS (a+1) VIRTUAL );
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
ALTER TABLE t1 DROP COLUMN b;
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
ALTER TABLE t1 ADD COLUMN c INT AS (a+1) PERSISTENT;
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
# End of test case
# Test output: schema. columns schema. columns schema. columns
CREATE TABLE t1 ( a INT, b INT AS (a+1) VIRTUAL );
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
COUNT(*)
0
ALTER TABLE t1 DROP COLUMN b;
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
COUNT(*)
1
ALTER TABLE t1 ADD COLUMN c INT AS (a+1) PERSISTENT;
SELECT COUNT(*) FROM information_
WHERE table_name = 't1';
COUNT(*)
0