Column aliased and accentuation

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. CentOS with java version "1.6.0_07" and locale pt_PT.UTF-8
2. select tblTest.id "column_id" from tblTest

What is the expected output? What do you see instead?
Caused by: java.lang.Error: org.h2.jdbc.JdbcSQLException: Column ID not
found [4
2122-105]
And text accentuation problems on INSERTs. Replacing accentuation at '?'.

What version of the product are you using? On what operating system, file
system, and virtual machine?
Version 1.1.105 (2008-12-19, Beta) 

Do you know a workaround?
In Ubuntu 8.04 with java version "1.6.0_10" and locale LANG=pt_PT.UTF-8 not
had this problem.

How important/urgent is the problem for you?
With that problem I cannot used it version.

In your view, is this a defect or a feature request?
A little bug. Or the problem is my wild scenary.

Please provide any additional information below.

Original issue reported on code.google.com by eduv...@gmail.com on 23 Dec 2008 at 12:19

[GoogleCodeExporter]

And when I returned to stable version, all working fine.

Original comment by eduv...@gmail.com on 23 Dec 2008 at 12:25

[GoogleCodeExporter]

In Ubuntu was the java version "1.6.0_07", I have the version 10 too, but my 
NetBeans
uses the 07.

I can't see the diference.

Original comment by eduv...@gmail.com on 23 Dec 2008 at 12:38

[GoogleCodeExporter]

I guess the problem is the column doesn't exist in the database. Could you add 
the
CREATE TABLE statement please? The easiest way to get that is running SCRIPT 
NODATA
in the H2 Console, and then copy & paste the result.

Original comment by thomas.t...@gmail.com on 24 Dec 2008 at 11:20

[GoogleCodeExporter]

The column exists, with stable version work fine. I just change to the new beta
version and got this problems.

Original comment by eduv...@gmail.com on 26 Dec 2008 at 11:37

[GoogleCodeExporter]

I guess the problem is the column doesn't exist in the database. Could you add 
the
CREATE TABLE statement please? The easiest way to get that is running SCRIPT 
NODATA
in the H2 Console, and then copy & paste the result.

Original comment by thomas.t...@gmail.com on 26 Dec 2008 at 12:08

[GoogleCodeExporter]

To find the problem I need a reproducible test case, otherwise I can not solve 
the
problem, sorry. Currently I have not enough information to reproduce it.

Original comment by thomas.t...@gmail.com on 4 Jan 2009 at 1:26

[GoogleCodeExporter]

Please re-open this bug if you have more information.

Original comment by thomas.t...@gmail.com on 26 Jan 2009 at 5:32

• Changed state: WorksForMe

[GoogleCodeExporter]

Isn't easy reproduce the test. Because is in production server. But this night 
i go 
change the H2DataBase and I will go generate the DB script. Sorry the long time 
to 
report more information.

Original comment by eduv...@gmail.com on 26 Jan 2009 at 4:47

[GoogleCodeExporter]

I tried with new version 1.1.107 and have the same bug.

I hope that have a solution for I can do the migration to new version.

Thank you.

Script below:

SET CLUSTER '';
SET DEFAULT_TABLE_TYPE 0;
SET WRITE_DELAY 500;
SET DEFAULT_LOCK_TIMEOUT 2000;
SET MAX_MEMORY_ROWS 1;
SET CACHE_SIZE 1;
SET MAX_MEMORY_UNDO 0;
CREATE USER IF NOT EXISTS SA SALT '11fcfb6f36198f52' HASH 
'66da4228ae2f310df41d4a2460a6528c12946442705c40860564bf9bdbdae815' ADMIN;
CREATE SEQUENCE PUBLIC.CATEGORIA_ID START WITH 11;
CREATE SEQUENCE PUBLIC.TRITAO_GROUP_ID START WITH 1;
CREATE SEQUENCE PUBLIC.TRITAO_TABLE_ID START WITH 8;
CREATE SEQUENCE PUBLIC.TRITAO_USER_ID START WITH 2;
CREATE SEQUENCE PUBLIC.DICIONARIO_AREA_ID START WITH 11;
CREATE SEQUENCE PUBLIC.TRITAO_DESIGN_ID START WITH 26;
CREATE SEQUENCE PUBLIC.TRITAO_USER_RULES_ID START WITH 1;
CREATE SEQUENCE PUBLIC.DICIONARIO_ID START WITH 11;
CREATE SEQUENCE PUBLIC.PRODUTO_IMAGEM_ID START WITH 159;
CREATE SEQUENCE PUBLIC.PRODUTO_ID START WITH 106;
CREATE SEQUENCE PUBLIC.TRITAO_GROUP_RULES_ID START WITH 1;
CREATE CACHED TABLE PUBLIC.TRITAO_TABLE(
    ID INT NOT NULL,
    NAME VARCHAR_IGNORECASE(50) DEFAULT '' NOT NULL,
    DISPLAYNAME VARCHAR_IGNORECASE(50) DEFAULT '' NOT NULL,
    GROUP_ID INT DEFAULT 0 NOT NULL,
    JS TEXT,
    REPORT BOOLEAN DEFAULT FALSE,
    CONTROL_USER BOOLEAN DEFAULT FALSE,
    CONTROL_GROUP BOOLEAN DEFAULT FALSE,
    CONTROL_ACTIVE BOOLEAN DEFAULT TRUE,
    BIG BOOLEAN DEFAULT FALSE,
    PARENT_ID INT DEFAULT 0
);
-- 5 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_TABLE;
CREATE CACHED TABLE PUBLIC.TRITAO_DESIGN(
    ID INT NOT NULL,
    TABLE_ID INT DEFAULT 0 NOT NULL,
    NAME VARCHAR_IGNORECASE(50) DEFAULT '',
    DISPLAYNAME VARCHAR_IGNORECASE(50) DEFAULT '',
    X INT DEFAULT 0 NOT NULL,
    Y INT DEFAULT 0 NOT NULL,
    TYPE VARCHAR_IGNORECASE(50) DEFAULT '' NOT NULL,
    SIZEX INT DEFAULT 0,
    SIZEY INT DEFAULT 0,
    MAX INT DEFAULT 0,
    MIN INT DEFAULT 0,
    COLSPAN INT DEFAULT 0,
    ROWSPAN INT DEFAULT 0,
    TDWIDTH VARCHAR_IGNORECASE(10) DEFAULT '0',
    TDHEIGHT VARCHAR_IGNORECASE(10) DEFAULT '0',
    NOTNULL BOOLEAN DEFAULT TRUE,
    PRIMARYKEY BOOLEAN DEFAULT FALSE,
    SEARCH BOOLEAN DEFAULT TRUE,
    LINK VARCHAR_IGNORECASE(250) DEFAULT '',
    EXTRA1 TEXT,
    EXTRA2 TEXT
);
-- 23 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_DESIGN;
CREATE CACHED TABLE PUBLIC.TRITAO_GROUP(
    ID INT NOT NULL,
    NAME VARCHAR_IGNORECASE(50) DEFAULT '' NOT NULL,
    TRITAO_GROUP INT DEFAULT 0 NOT NULL,
    ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,
    REPORT TEXT
);
-- 0 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_GROUP;
CREATE CACHED TABLE PUBLIC.TRITAO_USER(
    ID INT NOT NULL,
    NAME VARCHAR_IGNORECASE(50) DEFAULT '' NOT NULL,
    GROUP_ID INT DEFAULT 0 NOT NULL,
    USER VARCHAR_IGNORECASE(15) DEFAULT '' NOT NULL,
    PASS VARCHAR_IGNORECASE(15) DEFAULT '' NOT NULL,
    ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,
    IS_ADMIN BOOLEAN DEFAULT FALSE NOT NULL,
    REPORT TEXT
);
-- 1 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_USER;
CREATE CACHED TABLE PUBLIC.TRITAO_GROUP_RULES(
    ID INT NOT NULL,
    GROUP_ID INT DEFAULT 0 NOT NULL,
    TABLE_ID INT DEFAULT 0 NOT NULL,
    ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,
    CAN_READ BOOLEAN DEFAULT FALSE,
    CAN_WRITE BOOLEAN DEFAULT FALSE,
    CAN_DELETE BOOLEAN DEFAULT FALSE
);
-- 0 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_GROUP_RULES;
CREATE CACHED TABLE PUBLIC.TRITAO_USER_RULES(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0 NOT NULL,
    TABLE_ID INT DEFAULT 0 NOT NULL,
    ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,
    CAN_READ BOOLEAN DEFAULT FALSE,
    CAN_WRITE BOOLEAN DEFAULT FALSE,
    CAN_DELETE BOOLEAN DEFAULT FALSE
);
-- 0 +/- SELECT COUNT(*) FROM PUBLIC.TRITAO_USER_RULES;
CREATE CACHED TABLE PUBLIC.CATEGORIA(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0,
    GROUP_ID INT DEFAULT 0,
    ACTIVE BOOLEAN,
    NOME_PT VARCHAR_IGNORECASE(250),
    NOME_EN VARCHAR_IGNORECASE(250),
    ORDEM INT
);
-- 5 +/- SELECT COUNT(*) FROM PUBLIC.CATEGORIA;
CREATE CACHED TABLE PUBLIC.PRODUTO_IMAGEM(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0,
    GROUP_ID INT DEFAULT 0,
    ACTIVE BOOLEAN,
    PRODUTO_ID INT,
    DESCRICAO_PT VARCHAR_IGNORECASE(250),
    DESCRICAO_EN VARCHAR_IGNORECASE(250),
    FICHEIRO TEXT,
    ORDEM INT
);
-- 145 +/- SELECT COUNT(*) FROM PUBLIC.PRODUTO_IMAGEM;
CREATE CACHED TABLE PUBLIC.DICIONARIO_AREA(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0,
    GROUP_ID INT DEFAULT 0,
    ACTIVE BOOLEAN,
    NOME VARCHAR_IGNORECASE(250),
    CODIGO VARCHAR_IGNORECASE(250)
);
-- 10 +/- SELECT COUNT(*) FROM PUBLIC.DICIONARIO_AREA;
CREATE CACHED TABLE PUBLIC.DICIONARIO(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0,
    GROUP_ID INT DEFAULT 0,
    ACTIVE BOOLEAN,
    DICIONARIO_AREA_ID INT,
    CONTEUDO_PT TEXT,
    CONTEUDO_EN TEXT
);
-- 10 +/- SELECT COUNT(*) FROM PUBLIC.DICIONARIO;
CREATE CACHED TABLE PUBLIC.PRODUTO(
    ID INT NOT NULL,
    USER_ID INT DEFAULT 0,
    GROUP_ID INT DEFAULT 0,
    ACTIVE BOOLEAN,
    CATEGORIA_ID INT,
    NOME_PT VARCHAR_IGNORECASE(250),
    NOME_EN VARCHAR_IGNORECASE(250),
    TITULO_PT VARCHAR_IGNORECASE(250),
    TITULO_EN VARCHAR_IGNORECASE(250),
    THUMBNAIL TEXT,
    DESTAQUE BOOLEAN DEFAULT FALSE,
    CONTEUDO_EN TEXT,
    CONTEUDO_PT TEXT,
    ARQUIVO BOOLEAN DEFAULT FALSE
);
-- 37 +/- SELECT COUNT(*) FROM PUBLIC.PRODUTO;
ALTER TABLE PUBLIC.PRODUTO ADD CONSTRAINT PUBLIC.CONSTRAINT_1 PRIMARY KEY(ID);
ALTER TABLE PUBLIC.TRITAO_GROUP ADD CONSTRAINT PUBLIC.CONSTRAINT_E0 PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.TRITAO_DESIGN ADD CONSTRAINT PUBLIC.CONSTRAINT_2 PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.TRITAO_TABLE ADD CONSTRAINT PUBLIC.CONSTRAINT_E PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.DICIONARIO_AREA ADD CONSTRAINT PUBLIC.CONSTRAINT_D2 PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.PRODUTO_IMAGEM ADD CONSTRAINT PUBLIC.CONSTRAINT_D PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.CATEGORIA ADD CONSTRAINT PUBLIC.CONSTRAINT_3 PRIMARY KEY(ID);
ALTER TABLE PUBLIC.TRITAO_USER_RULES ADD CONSTRAINT PUBLIC.CONSTRAINT_6 PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.TRITAO_GROUP_RULES ADD CONSTRAINT PUBLIC.CONSTRAINT_EC 
PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.TRITAO_USER ADD CONSTRAINT PUBLIC.CONSTRAINT_7 PRIMARY 
KEY(ID);
ALTER TABLE PUBLIC.DICIONARIO ADD CONSTRAINT PUBLIC.CONSTRAINT_F PRIMARY 
KEY(ID);

Original comment by eduv...@gmail.com on 30 Jan 2009 at 11:57

[GoogleCodeExporter]

The bug report says "select tblTest.id "column_id" from tblTest" but there is 
no such
table in your script.

Original comment by thomas.t...@gmail.com on 30 Jan 2009 at 1:30

• Changed state: Invalid

[GoogleCodeExporter]

LoL! Sorry... I said tblTest like a sample!

If I do:

select produto.id "id" from produto

Or any another table/column cause the same bug!

The problem is if I just use "something between quotes" in a query!

Original comment by eduv...@gmail.com on 30 Jan 2009 at 1:52

[GoogleCodeExporter]

And if the table not exists the error is:

Table TBLTEST not found; SQL statement: select tblTest.id "column_id" from 
tblTest 
[42102-79]

And the error is:

Column ID not found [42122-105]

Is when the table and the id are correct and just do a select using quotes.

My scene is with same databases and the queries just changing the H2DataBase.

Original comment by eduv...@gmail.com on 30 Jan 2009 at 2:05

[GoogleCodeExporter]

Hm, I can't reproduce the problem (it works for me). Could you post the stack 
trace
as well please? For both exceptions please.

Original comment by thomas.t...@gmail.com on 30 Jan 2009 at 3:10

[GoogleCodeExporter]

Few days ago I was do updated the server, to new version of CentOS and JDK, and 
now
works fine...

I think never more catch the stack trace again :P

Thank you, any way. And sorry.

Original comment by eduv...@gmail.com on 3 Feb 2009 at 9:37

[GoogleCodeExporter]

Again!... I get the error on new server, with new JDK and CentOS but in another 
DB 
that I not had tested yet.

org.h2.jdbc.JdbcSQLException: Column ID not found [42122-107]
org.h2.message.Message.getSQLException(Message.java:103)
org.h2.message.Message.getSQLException(Message.java:114)
org.h2.message.Message.getSQLException(Message.java:77)
org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:2964)
org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3015)
org.h2.jdbc.JdbcResultSet.getObject(JdbcResultSet.java:480)

Now is with H2DataBase version 1.1.107!

Original comment by eduv...@gmail.com on 5 Feb 2009 at 4:20

[GoogleCodeExporter]

Hi,

It looks like you ran:
select tblTest.id "column_id" from tblTest 
and then called:
rs.getString("ID")

This is not supported by H2, Derby, PostgreSQL, HSQLDB, MySQL.
The test case I used is:

Class.forName("org.h2.Driver");
DeleteDbFiles.execute("data", "test", true);
Connection conn;
Statement stat;
conn = DriverManager.getConnection(
"jdbc:h2:data/test", "sa", "sa");
stat = conn.createStatement();
try {
stat.execute("drop table test");
} catch(SQLException e) {}
stat.execute("create table test(id int)");
stat.execute("insert into test values(1)");
ResultSet rs = stat.executeQuery("select id as \"column_id\" from test");
rs.next();
System.out.println(rs.getString("ID"));
conn.close();

You need to use

select id as "column_id" from test
rs.getString("column_id");

or

select id from test
rs.getString("ID");

Original comment by thomas.t...@gmail.com on 6 Feb 2009 at 4:36