Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Developing on Windows XP32 and MariaDB 5.3.3, I needed columns in 'information_schema.COLUMNS' from the table dbw2.s_kunden. But they are missing.
Normally, when you query
SELECT * FROM information_schema.COLUMNS
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_schema.COLUMNS.
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_schema.COLUMNS.
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_schema.COLUMNS WHERE TABLE_SCHEMA='dbw2';
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_schema.COLUMNS.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Missing columns in information_schema.COLUMNS
Re: Missing columns in information_schema.COLUMNS
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
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( a INT, b INT AS (a+1) VIRTUAL );
SELECT COUNT
FROM information_schema.columns
WHERE table_name = 't1';
ALTER TABLE t1 DROP COLUMN b;
SELECT COUNT FROM information_schema.columns
WHERE table_name = 't1';
ALTER TABLE t1 ADD COLUMN c INT AS (a+1) PERSISTENT;
SELECT COUNT FROM information_schema.columns
WHERE table_name = 't1';
- End of test case
- Test output:
CREATE TABLE t1 ( a INT, b INT AS (a+1) VIRTUAL );
SELECT COUNT FROM information_schema.columns
WHERE table_name = 't1';
COUNT
0
ALTER TABLE t1 DROP COLUMN b;
SELECT COUNT FROM information_schema.columns
WHERE table_name = 't1';
COUNT
1
ALTER TABLE t1 ADD COLUMN c INT AS (a+1) PERSISTENT;
SELECT COUNT FROM information_schema.columns
WHERE table_name = 't1';
COUNT
0
Launchpad bug id: 930814
complete mysql and dbw2 databases
LPexportBug930814_data.7z