New issue with numeric scale in decimal data type

[Dario Fassi]

Hi,
Testing with version 1.3.155 I found and issue related to recent changes in numeric scale of decimal fields.
Simple test case:

create table t1 as (select cast( 0 as dec(15,2) ) as salary );
alter table t1 add column f1 int;

Error: Invalid value "32767" for parameter "scale (precision = 1)"; SQL statement:
CREATE CACHED TABLE PUBLIC.T1_COPY_2_4(
SALARY DECIMAL(1, 32767),
F1 INT
) AS SELECT SALARY, NULL FROM PUBLIC.T1 [90008-155]
SQLState: 90008
ErrorCode: 90008

After that any try to connect to this db fail with message:

TestDb: Invalid value "32767" for parameter "scale (precision = 15)"; SQL statement

regards,
Dario.

[Thotheolh]

Here's a complete trace from my 1.3.155 Web Console:

JDBC Path: jdbc:h2:mem:

Results:

create table t1 as (select cast( 0 as dec(15,2) ) as salary );

Update count: 0
(8 ms)

alter table t1 add column f1 int;

Invalid value "32767" for parameter "scale (precision = 1)"; SQL
statement:

CREATE MEMORY TABLE PUBLIC.T1_COPY_2_0(

SALARY DECIMAL(1, 32767),
F1 INT

) AS SELECT SALARY, NULL FROM PUBLIC.T1 [90008-155] 90008/90008 (Help)
org.h2.jdbc.JdbcSQLException: Invalid value "32767" for parameter

"scale (precision = 1)"; SQL statement:

CREATE MEMORY TABLE PUBLIC.T1_COPY_2_0(

SALARY DECIMAL(1, 32767),
F1 INT
) AS SELECT SALARY, NULL FROM PUBLIC.T1 [90008-155]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.command.Parser.parseColumnWithType(Parser.java:3689)
at org.h2.command.Parser.parseColumnForTable(Parser.java:3480)
at org.h2.command.Parser.parseCreateTable(Parser.java:5133)
at org.h2.command.Parser.parseCreate(Parser.java:3774)
at org.h2.command.Parser.parsePrepared(Parser.java:320)
at org.h2.command.Parser.parse(Parser.java:275)
at org.h2.command.Parser.parse(Parser.java:247)
at org.h2.command.Parser.prepare(Parser.java:201)
at org.h2.engine.Session.prepare(Session.java:400)
at org.h2.engine.Session.prepare(Session.java:387)
at
org.h2.command.ddl.AlterTableAlterColumn.execute(AlterTableAlterColumn.java:
396)
at
org.h2.command.ddl.AlterTableAlterColumn.cloneTableStructure(AlterTableAlterColumn.java:
298)
at
org.h2.command.ddl.AlterTableAlterColumn.copyData(AlterTableAlterColumn.java:
181)
at
org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:
122)
at org.h2.command.CommandContainer.update(CommandContainer.java:
71)
at org.h2.command.Command.executeUpdate(Command.java:212)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
177)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
at org.h2.server.web.WebApp.getResult(WebApp.java:1304)
at org.h2.server.web.WebApp.query(WebApp.java:994)
at org.h2.server.web.WebApp$1.next(WebApp.java:957)
at org.h2.server.web.WebApp$1.next(WebApp.java:960)
at org.h2.server.web.WebThread.process(WebThread.java:166)
at org.h2.server.web.WebThread.run(WebThread.java:93)
at java.lang.Thread.run(Thread.java:662)

[Thomas Mueller]

Hi,

This is a bug, I will fix it. As a workaround, use:

create table t1(salary dec(15, 2)) as select  0;

Regards,
Thomas

[apfe...@gmail.com]

Hello, 

I am aware this is an old post, but I am seeing the same error on the latest 1.4.187 v of H2.

  CREATE TABLE "TABLE1" 

   (

"FAIL" NUMBER(6,24)

    )  as select  0;

Invalid value "24" for parameter "scale (precision = 6)"

Artur

[Thomas Mueller]

Hi,

By the way, your problem and the original problem are different, see the first mail in this thread. The original problem was:

  create table t1 as (select  cast( 0 as dec(15,2) ) as salary );
  alter table t1 add column f1 int;

The "alter table" didn't work originally, and this is fixed now.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Thomas Mueller]

Hi,

This is not a bug. The scale must be larger than the precision.

Regards,

Thomas

On Tuesday, June 16, 2015, <apfe...@gmail.com> wrote:

--