[MDEV-2648] LP:930814 - Missing columns in information_schema.COLUMNS for tables with virtual columns Created: 2012-02-12  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: nbrnhardt (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug930814.xml     File LPexportBug930814_data.7z    

 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.

 Comments   
Comment by nbrnhardt (Inactive) [ 2012-02-12 ]

Re: Missing columns in information_schema.COLUMNS

Comment by nbrnhardt (Inactive) [ 2012-02-12 ]

complete mysql and dbw2 databases
LPexportBug930814_data.7z

Comment by Elena Stepanova [ 2012-02-13 ]

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.

  1. 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';

  1. End of test case
  1. 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
Comment by Rasmus Johansson (Inactive) [ 2012-04-03 ]

Launchpad bug id: 930814

Generated at Thu Feb 08 06:43:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.