INFORMATION_SCHEMA.TABLES , field TABLE_TYPE not standard

[GoogleCodeExporter]

Hi,

In the SQL1999 specification the field TABLE_TYPE of INFORMATION_SCHEMA.TABLES 
should be one of ’BASE TABLE’, ’VIEW’, ’GLOBAL TEMPORARY’, ’LOCAL 
TEMPORARY’

In H2, when I create a persistent table the value is TABLE, and when I create a 
temporary table the value is also TABLE.

ISO/IEC 9075-2:1999 (E) Page 926
```sql
TABLE_TYPE
 INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT TABLE_TYPE_NOT_NULL
NOT NULL
CONSTRAINT TABLE_TYPE_CHECK
CHECK ( TABLE_TYPE IN
( ’BASE TABLE’, ’VIEW’, ’GLOBAL TEMPORARY’, ’LOCAL TEMPORARY’ ) 
),

Do you want to fix it, and if yes do you want a patch ?

Regards,

-Nicolas Fortin Atelier SIG IRSTV FR CNRS 2488

Original issue reported on code.google.com by `nico.de...@gmail.com` on 1 Aug 2013 at 9:17

[GoogleCodeExporter]

I did some quick checking on how other DBs do this:

DB2    has ALIAS / MATERIALIZED QUERY TABLE / TABLE / VIEW
MySQL  has BASE TABLE / VIEW and does not list temporary tables at all
MSSQL  has BASE TABLE / VIEW
Oracle does not support this at all

So it looks like no-one follows the standard very closely.

You are welcome to supply such a patch, but note that the behaviour will have 
to be hidden behind a configuration switch, so that we don't break backwards 
compatibility.

Also, in your patch, do not forget to update the ResultSetMetaData code.

Original comment by noelgrandin on 2 Aug 2013 at 6:46

[GoogleCodeExporter]

Hi,

PostgreSQL follow the standard. I will use the PostgreSQL compatibility switch 
then.

Original comment by nico.de...@gmail.com on 2 Aug 2013 at 7:23

[GoogleCodeExporter]

Hi,

> PostgreSQL follow the standard. I will use the PostgreSQL compatibility 
switch then.

If you use the PostgreSQL mode, it doesn't mean H2 will suddenly _be_ 
PostgreSQL :-) The changes in the PostgreSQL mode are listed in the 
documentation.

Original comment by thomas.t...@gmail.com on 2 Aug 2013 at 10:01

[GoogleCodeExporter]

Why don't you use DatabaseMetaData.getTables, which is more standardized? Many 
databases don't support INFORMATION_SCHEMA.TABLES.

Apache Derby uses TABLE, SYSTEM TABLE, VIEW, and does not list temporary tables.
HSQLDB uses TABLE, SYSTEM TABLE, VIEW, and does not list temporary tables.
SQLite uses TABLE, VIEW, TABLE (for temp tables), does not have system tables
MySQL uses TABLE, VIEW, does not list system tables and temp tables
PostgreSQL uses TABLE, TEMPORARY TABLE, VIEW, SYSTEM TABLE, SYSTEM VIEW,...

---------

The test I have used, running the H2 Console:

drop view test_view;
drop table test;
create table test(id int);
create view test_view as select * from test;

-- Apache Derby:
declare global temporary table temp_table(id int) not logged;

-- HSQLDB:
create temporary table temp_table_local(id int);

@tables

Original comment by thomas.t...@gmail.com on 2 Aug 2013 at 10:15

[GoogleCodeExporter]

Original comment by noelgrandin on 9 Oct 2013 at 7:54

• Changed state: PatchesWelcome

[GoogleCodeExporter]

As you say it is ok with DatabaseMetaData.getTables, you can close this issue.

Original comment by nico.de...@gmail.com on 4 Dec 2013 at 8:16

[GoogleCodeExporter]

Makes sense! Currently no changes are planned.

Original comment by thomas.t...@gmail.com on 14 Dec 2013 at 12:55

• Changed state: WontFix