Re: [h2] Re: Duplicate records when order by date?

[Evgenij Ryazanov]

> Are they really peers if there's a unique primary key?

Yes, they are if this primary key isn't included into sort specification. 
If rows aren't distinct with respect to the sort specification, they are 
peers of each other. Columns aren't included into sort specification don't 
matter here.

Database systems don't shuffle rows intentionally, but ordering of peers 
depends on index construction, query execution plan (it can be changed even 
for the same query with the same parameters in some cases), sometimes on 
row insertion order and other data modification operations. There are too 
many unknown variables. You can't rely on them.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/53ab2276-4ef2-4134-b4d7-07b2a6133628n%40googlegroups.com.

Re: [h2] Re: Duplicate records when order by date?

[Evgenij Ryazanov]

On Tuesday 16 April 2024 at 17:18:41 UTC+8 Evgenij Ryazanov wrote:

These rows are called *pears* in the SQL Standard.

I meant *peers*, of course. 

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ba484884-f94b-4f6a-92aa-3721fa4bcaa9n%40googlegroups.com.

Re: [h2] Re: Duplicate records when order by date?

[Evgenij Ryazanov]

These rows are called *pears* in the SQL Standard. The SQL Standard doesn't 
require any exact ordering of peers and doesn't require any stable ordering 
rules for them. These things are implementation-dependent.

If some particular DBMS doesn't have own documented ordering rules for 
pears (I never saw such rules anywhere), your queries rely on undefined 
behavior.

PostgreSQL may and can return peers in any order:

# create table test(a int, b int);
# create index test_b on test(b);
# insert into test values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (0, 4), 
(-1, 4);
# select * from test order by a offset 0 rows;
a  | b
---+---
-1 | 4
 0 | 4
 1 | 3
 1 | 2
 2 | 4
 2 | 3
 3 | 4
(7 rows)

# select * from test order by a offset 0 rows fetch first 3 rows only;
a  | b
---+---
-1 | 4
 0 | 4
 1 | 2
(3 rows) 

# select * from test order by a offset 3 rows fetch first 3 rows only;
a | b
--+---
1 | 2
2 | 4
2 | 3
(3 rows)

Row (1, 3) is missing.

Results in H2 are slightly different:

# select * from test order by a offset 0 rows fetch first 3 rows only;
A  | B
-- | -
-1 | 4
 0 | 4
 1 | 3
(3 rows) 

# select * from test order by a offset 3 rows fetch first 3 rows only;
A | B
- | -
1 | 3
2 | 4
2 | 3

In H2 row (1, 2) is missing.

But there is no bug in PostgreSQL or H2.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5c04695c-ea06-4a3d-b0d7-ad07190738c9n%40googlegroups.com.

[h2] Re: Duplicate records when order by date?

[Evgenij Ryazanov]

Hello!

Try the following query: SELECT RANK() OVER (ORDER BY start_date DESC NULLS 
FIRST) R, * FROM test ORDER BY 1; Some rows have the same rank:
149 130 2019-01-11 00:00:00-05
150 217 2019-01-02 00:00:00-05
150 218 2019-01-02 00:00:00-05
152 164 2019-01-01 00:00:00-05
152 166 2019-01-01 00:00:00-05
154 165 2018-11-16 00:00:00-05
That means rows with ID 217 and 218 may be returned every time in any 
order. It isn't a bug, it is a valid standard-compliant behavior.

Offset pagination a bad thing, but if you really need to use it, there are 
two common solutions: 
1. You can add a unique non-null column to the ORDER BY clause, such as 
primary key column(s):
SELECT * FROM test ORDER BY start_date DESC NULLS FIRST, id OFFSET 100 ROWS 
FETCH NEXT 50 ROWS ONLY;
2. You can use standard WITH TIES clause:
SELECT * FROM test ORDER BY start_date DESC NULLS FIRST OFFSET 100 ROWS 
FETCH NEXT 50 ROWS WITH TIES; 
This query returns 51 rows, because after 50th row there is an additional 
row with the same rank. These possible additional returned rows inform your 
application about this situation and it can handle it somehow:
217 2019-01-02 00:00:00-05
218 2019-01-02 00:00:00-05
For example, it can process all of them and run next query with OFFSET 151 ROWS 
FETCH NEXT 50 ROWS WITH TIES.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/9a785bc4-7faa-4a70-8e03-f37c4bf76c63n%40googlegroups.com.

[h2] Re: H2 does not use SYSTEM TABLE in table types

[Evgenij Ryazanov]

H2 returns table types exactly as required by the SQL Standard. JDBC 
doesn't have any own requirements.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e900886f-99ae-4121-9884-f6d9d16ee7f2n%40googlegroups.com.

[h2] Re: H2 does not use SYSTEM TABLE in table types

[Evgenij Ryazanov]

Hi!

> Why H2 does not use SYSTEM TABLE in the table types returned by the 
DatabaseMetaData.getTableTypes() method

Because there is no such thing as system table in the SQL Standard. JDBC 
metadata is an entirely unreliable thing, you can't construct anything 
serious on top of it.

There is a difference between type of a table (base table, view, local 
temporary table, global temporary table and so on) and its actual 
implementation.

In modern versions of H2 you can use a query like that to distinguish 
user-defined tables and views from system tables and views:

SELECT *, DB_OBJECT_ID('TABLE', TABLE_SCHEMA, TABLE_NAME) < 0 IS_SYSTEM FROM 
INFORMATION_SCHEMA.TABLES;

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4464e30e-e247-4946-86b9-b2793988485dn%40googlegroups.com.

[h2] Re: how much indexes are used for optimization

[Evgenij Ryazanov]

Hello!

Currently only different primary queries can use different indexes, nested 
derived tables and subqueries can also use own indexes.

SELECT * FROM TEST WHERE NAME = 'A' AND FIRSTNAME = 'B' can use only one 
index and it needs an index on (NAME, FIRSTNAME, …) or on (FIRSTNAME, NAME, 
…) columns for optimal performance.

H2 doesn't have a bitmap index scan optimization, so if you have only 
separate indexes, only one index will be used. Some database systems can 
use two indexes on the same time in such queries, but even in them a 
two-column index should be significantly faster than bitmap index scan 
optimization on two indexes.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7c9bc87e-8899-4dc4-bb64-a094959fb1ben%40googlegroups.com.

[h2] Re: java.lang.NoClassDefFoundError: org/h2/command/dml/SetClauseList

[Evgenij Ryazanov]

Hello!

Make sure you have only one version of H2 in classpath of your application.

If you have a standalone H2 Server process, check its classpath instead. If 
you use auto-server mode, check classpaths of all involved applications.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e5f9b4c1-c7a0-489c-8ca1-34a1052cf82fn%40googlegroups.com.

[h2] Re: Get user's roles and role's users

[Evgenij Ryazanov]

Hi!

Take a look on GRANTEE and GRANTEDROLE columns in non-standard 
INFORMATION_SCHEMA.RIGHTS table where GRANTEETYPE = 'USER' and GRANTEDROLE 
IS NOT NULL.

Please note that a role can be granted to another role. These records have 
GRANTEETYPE 
= 'ROLE' and GRANTEDROLE IS NOT NULL. Users have transitive permissions 
from these roles.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/66a8df78-5830-436d-8c9e-c890e8cf4a27n%40googlegroups.com.

Re: [h2] Re: Auto Increment statement is giving an error when passing null with H2DB 2.2.224 version

[Evgenij Ryazanov]

When you use Statement.RETURN_GENERATED_KEYS, driver may choose what it 
should return by itself, see JDBC™ 4.3 Specification, section 13.6. Modern 
versions of H2 return primary key columns, identity columns, and columns 
with non-constant default expression (including expressions inherited from 
domains).

But you can pass an array of column names or column indexes instead of that 
constant to request the exact columns.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e2b9ff41-9cd6-48fb-871f-1d8f73ff095cn%40googlegroups.com.

[h2] Re: Auto Increment statement is giving an error when passing null with H2DB 2.2.224 version

[Evgenij Ryazanov]

Hi!

You shouldn't try to insert NULL into columns with NOT NULL constrains. 
Only non-null values or DEFAULT may be inserted into them.

H2 allows such incorrect insertions into identity columns only in Legacy, 
HSLQDB, MariaDB, and MySQL compatibility modes.

You can also enable this feature for any column in any compatibility mode 
with ALTER TABLE tableName ALTER COLUMN columnName SET DEFAULT ON NULL

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0f828877-acab-49eb-a39a-8df33b23d309n%40googlegroups.com.

[h2] Re: Updating a current column with an old column using triggers without exception StackOverflowError

[Evgenij Ryazanov]

Hello!

If you need to modify this row in your trigger, you must change values in 
the newRow array directly:

newRow[/* 0-based column index */] = OffsetDateTime.now();

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/136b7d23-0894-4e1a-b913-a51a5553bf3dn%40googlegroups.com.

[h2] Re: create domain text[]

[Evgenij Ryazanov]

Hello!

It isn't going to work, text[] is not an identifier and it cannot be used 
as domain name.

PostgreSQL supports standard arrays for more than 20 years, just use a 
standard definition in both PostgreSQL and H2.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b3c906ae-2836-4792-b1e3-fcf5266977d2n%40googlegroups.com.

[h2] Re: concurrent queries

[Evgenij Ryazanov]

Hi!

All 1.*.* versions of H2 are entirely outdated and aren't supported in any 
way.

1. H2 1.4.* has two different storage engines.
2. H2 1.4.199 and older versions have different synchronization models for 
every engine (1.4.200 uses multi-threaded mode for MVStore and 
single-threaded mode for PageStore unconditionally).
3. H2 1.4.197 and older versions can use either table-level locks or 
row-levels locks when legacy PageStore engine is used (since 1.4.198 
PageStore uses table-level locks unconditionally).

So possibility of concurrent execution depends on your setup. Most likely 
you use a legacy PageStore engine and therefore database works in 
single-threaded mode with table-level locks.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/58011bc2-8302-491e-b94f-454ebe1759c2n%40googlegroups.com.

Re: [h2] Year alias in select statement

[Evgenij Ryazanov]

Hello!

YEAR is a reserved word even in archaic SQL-92, so it was a bad idea to use 
it as unquoted identifier. BTW, DATE and TRANSACTION are reserved words 
too, but H2 allows their usage as identifiers. Also there is no YEAR 
function in the Standard, correct syntax is EXTRACT(YEAR FROM someValue).

Newer versions of H2 may have more keywords than old versions and reserved 
words from the latest version of the SQL Standard (from SQL:2023 for now) 
are potential candidates.
 
Anyway, the following test case prints 2024 as expected, so NON_KEYWORDS 
settings works well and you have some other problem in your application, 
most likely your query is executed from a connection with different 
settings.

Properties p = new Properties();

p.put("MODE", "PostgreSQL");

p.put("DATABASE_TO_LOWER", "TRUE");

p.put("DEFAULT_NULL_ORDERING", "HIGH");

p.put("NON_KEYWORDS", "YEAR");

try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:", p)) 
{

Statement s = connection.createStatement();

s.execute("CREATE TABLE transaction(date DATE) AS VALUES CURRENT_DATE");

ResultSet rs = s.executeQuery("SELECT year(date) AS year FROM transaction");

rs.next();

System.out.println(rs.getInt(1));

}

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/fc4ea7a4-c9f8-47a1-89be-191e9d807f15n%40googlegroups.com.

[h2] Re: UNIQUE KEY constraint in H2-1.4.199 is not working with H2-2.2.224

[Evgenij Ryazanov]

Hi!

Your table definition has non-portable MySQL-specific syntax. If this is 
not your intention, you should use standard CONSTRAINT mdrIdProeprtyName 
UNIQUE(mdr_id, property_name) instead.

MySQL-specific features are only allowed in MySQL and MariaDB compatibility 
modes:
https://h2database.com/html/features.html#compatibility
If you use H2 for unit tests with Spring Boot, you also need to disable 
automatic configuration of databases with @AutoConfigureTestDatabase(replace 
= AutoConfigureTestDatabase.Replace.NONE) annotation or in any other way.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b82e8e0c-ab92-417d-9f40-58038c008bf0n%40googlegroups.com.

[h2] Re: Syntax error with ON CONFLICT

[Evgenij Ryazanov]

Hello!

This PostgreSQL-specific clause is not supported by H2. You need to use the 
MERGE command from the SQL Standard:
https://www.postgresql.org/docs/current/sql-merge.html
https://h2database.com/html/commands.html#merge_using

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d286adbb-cc3a-4787-8fff-88478b8bd6b0n%40googlegroups.com.

[h2] Re: Infinite looping when trigger updates same table

[Evgenij Ryazanov]

Hi!

Is it your question? Anyway, take a look on answers:
https://stackoverflow.com/questions/77754877/h2-triggers-behaving-in-infinite-loop

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a279973f-696c-4070-a2f4-6eabd611853an%40googlegroups.com.

[h2] Re: is there any command line interface can interact with h2 in shell

[Evgenij Ryazanov]

Hi!

You can use the shell tool:
https://h2database.com/html/tutorial.html#shell_tool

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4941e43c-ac24-4c16-a463-e849f80c1b94n%40googlegroups.com.

[h2] Re: Batch insert on key violation

[Evgenij Ryazanov]

Hello!

When you use JDBC directly, H2 throws BatchUpdateException in case of 
failure, but only after execution of all commands in the batch (other 
drivers may stop after a first failure, but every driver must always 
continue processing or always stop processing). getUpdateCounts() and 
modern getLargeUpdateCounts() methods of this exception return an array 
with update counts or statuses.

For example, attempt to insert values 1, 1, 2, and 1 into a unique column 
throws this exception with [1, -3, 1, -3] result. It means that first and 
third insertions were successful, but second and fourth weren't.

If there were no failures, executeBatch() and modern executeLargeBatch() 
methods return the same array with update counts.

When you use third-party wrappers on top of JDBC you need to check their 
documentation, because their behavior may be different from your 
expectations.

Also you can use a MERGE 
 command for 
conditional insertions instead of INSERT to avoid any exceptions here.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7092b867-c5e5-48de-a261-45c98e5b3c24n%40googlegroups.com.

[h2] Re: Lock file absent by default ?

[Evgenij Ryazanov]

Hello!

1. H2 uses file system locks by default for a very long time, so there is 
no additional file. In few special cases file systems (usually virtual or 
remote ones) don't support locks well. In these cases you can add 
;FILE_LOCK=FILE to JDBC URL to use that old method instead of modern one.
2. Operating systems usually can rename files even when they are accessed, 
if application doesn't try to re-open these files under old names it 
shouldn't produce any problems. But this is a bad idea anyway, it still can 
break some commands, such as SHUTDOWN COMPACT and others.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b22c6d38-b8a4-40bd-9d74-a1df1b5553ecn%40googlegroups.com.

[h2] Re: Unnesting multiple array columns

[Evgenij Ryazanov]

Hello!

Yes, it is possible, but with valid SQL only.

Subqueries must be enclosed in parentheses, all your queries are incorrect. 
Valid queries are

select * from unnest((select bar from foo));
select * from unnest((select baz from foo));
select * from unnest((select bar from foo), (select baz from foo));

In some cases H2 allows subqueries without parentheses due to historic 
reasons, but this undocumented syntax was implemented very inconsistently 
and actually it isn't possible to implement it in reliable way due to 
syntax conflicts. Don't use it, it is not supported and it may not work in 
future versions of H2 in places where it works in 2.2.222.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/205acd8a-9de4-45d2-a894-bee45ca2faf5n%40googlegroups.com.

[h2] Re: Use of COMMIT TRANSACTION

[Evgenij Ryazanov]

Hello!

These commands aren't going to work. 

You need to disable auto-commit, execute your commands and commit or 
rollback your transaction.
In JDBC, you can use Connection.setAutoCommit(false), Connection.commit(), 
and Connection.rollback().
In SQL you can use SET AUTOCOMMIT OFF, COMMIT, and ROLLBACK.
After commit or rollback a new transaction will be started automatically.

Don't call PREPARE COMMIT name, COMMIT TRANSACTION name, ROLLBACK 
TRANSACTION name etc, these commands are used by more expensive two-phase 
commit protocol. Most likely you don't need it here. You can use it, of 
course, but in that case PREPARE COMMIT must be executed after data 
modification commands.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4821816e-3cb1-43a5-be08-7b8b6efb7d8bn%40googlegroups.com.

[h2] Re: Support returning ID of inserted row right from the INSERT INTO statement

[Evgenij Ryazanov]

Hello!

H2 supports standard data change delta tables:
https://h2database.com/html/grammar.html#data_change_delta_table

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e5c59392-39cf-4957-a0db-bea8d137db4fn%40googlegroups.com.

[h2] Re: like 'test%' with escape '' not working in MODE=ORACLE

[Evgenij Ryazanov]

Hello!

This issue cannot be resolved on H2 side.

ESCAPE '' is not valid according to the SQL Standard:
> If the length in characters of ECV is not equal to 1, then an exception 
condition is raised:
> data exception — invalid escape character.
Oracle throws an exception as expected:
ORA-01425: escape character must be character string of length 1

H2 allows ESCAPE '' with a special meaning, but this extension is not 
portable across all compatibility modes, because in Oracle compatibility 
mode (just like in the real Oracle) an empty character string and NULL 
aren't distinct from each other and it isn't possible to distinguish them 
here.

This issue was already reported in bugtracker of Hibernate ORM:
https://hibernate.atlassian.net/browse/HHH-16277
It was closed as a duplicate of another issue, but I think it isn't a 
duplicate and should be re-opened.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/562bdf13-b6a9-4a76-92bd-134b6d3e7377n%40googlegroups.com.

[h2] Re: Version 2.2.222 still not available in the Maven repository

[Evgenij Ryazanov]

H2 2.2.222 has a known regression: it doesn't perform automatic calculation 
of statistics.

You need to run the ANALYZE command by yourself. In real-words applications 
it can be enough to call its once after insertion of enough amount of data, 
in unit tests you may need to call it in every test that inserts many rows.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f341fb73-30e5-48ff-9acb-73596f333d07n%40googlegroups.com.

[h2] Re: H2 mem db failing with LocalDateTime

[Evgenij Ryazanov]

Hello!

You cannot use MySQL/MariaDB-specific features without MySQL or MariaDB 
compatibility mode:
https://h2database.com/html/features.html#compatibility

If you use H2 in unit tests you also need to disable their automatic 
configuration by Spring Boot, otherwise your custom JDBC URL with all these 
settings will be ignored.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f24bd579-2db3-476f-b4d0-372ccec0836dn%40googlegroups.com.

Re: [h2] Comparing NULL able columns --> IS DISTINCT FROM

[Evgenij Ryazanov]

Hello!

SQL actually has three boolean values: TRUE, FALSE, and UNKNOWN (UNKNOWN in 
the same thing as NULL). Comparison operators can return all these values. 
NULL is neither equal nor not equal to NULL or any other value, results of 
all six comparison operators (=, <>, <, >, <=, >=) return UNKNOWN if either 
operand is null (and in some other cases), they don't return FALSE here.

Distinct predicate is different, it always returns TRUE or FALSE, even when 
comparison operation between operands cannot determine their equality or 
not equality. In this predicate NULL is not distinct from other NULL. Equal 
values are not distinct from each other. Non-equal values are distinct from 
each other and from NULL. IS NOT DISTINCT FROM is a null-safe equivalent of 
=, IS DISTINCT FROM is a null-safe equivalent of <>.

Various filtration clauses (WHERE, HAVING, etc.) preserve rows where 
expression evaluates to TRUE only. CHECK and domain constraints allow rows 
/ values where expression evaluates to TRUE or UNKNOWN.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/dbc36bc6-feb5-4feb-bb93-4f2c9429460fn%40googlegroups.com.

[h2] Re: Create Table Problem (Upgrading from 1.4.x)

[Evgenij Ryazanov]

Hello.

In modern versions of the SQL Standard KEY is really a non-reserved word, 
but it was a mistake to exclude it from the list of reserved words, because 
in some contexts you still cannot use it as identifier, even the SQL 
Standard itself has at least one such documented exclusion appeared in 
ISO/IEC 9075-2:2016 TECHNICAL CORRIGENDUM 2.

Definition of your table also has a VALUE column, but VALUE is a reserved 
word in the SQL Standard and it is a keyword in H2.

If you cannot rename these columns, you can add `;NON_KEYWORDS=KEY,VALUE` 
to JDBC URL. It also should have `
;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH` if you 
want a better compatibility with PostgreSQL. Alternatively you can pass 
these settings in the map with properties.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7756da35-d074-439e-af77-33adc7a4bbd6n%40googlegroups.com.

[h2] Re: Is there a way to quote columns ?

[Evgenij Ryazanov]

With H2 version *2.1.214* and the JDBC URL 
*jdbc:h2:tcp://localhost/MY_DB;AUTO_SERVER=TRUE;IGNORECASE=TRUE;NON_KEYWORDS=INTERVAL,VALUES*
I get this Exception:

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported 
connection setting "NON_KEYWORDS" [90113-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:622)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.engine.ConnectionInfo.readSettingsFromURL(ConnectionInfo.java:269)
at org.h2.engine.ConnectionInfo.(ConnectionInfo.java:78)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:152)
at org.h2.Driver.connect(Driver.java:69)

You need to use some modern version of H2 on client side too.

I start H2 over TCP with this:

java -cp /opt/h2-2.1.214/bin/h2-2.1.214.jar org.h2.tools.Server 
-baseDir $HOME/my_db -ifNotExists -tcp -tcpAllowOthers -tcpPort 9092 
1>my_db.log 2>&1

Documentation of H2 has clear warning about combination of -ifNotExists 
with -tcpAllowOthers, this combination of settings effectively creates a 
remote security hole on you system unless your ports are guarded somehow.
https://h2database.com/html/tutorial.html#creating_new_databases

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d44ce858-8fb4-4f5d-bdb3-948a638c93d6n%40googlegroups.com.

[h2] Re: Is there a way to quote columns ?

[Evgenij Ryazanov]

Hello!

You need to quote column names in your application, there is nothing to do 
on H2 side. If you use Hibernate ORM, you can force quotation of all 
identifiers with hibernate.globally_quoted_identifiers setting.

In the worst case you can add ;NON_KEYWORDS=INTERVAL to JDBC URL, but this 
setting doesn't cover all possible cases and it should be considered only 
as a possible temporary workaround.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f18348fb-3290-498e-a704-a01618434e98n%40googlegroups.com.

[h2] Re: When was REGEXP_SUBSTR added to H2?

[Evgenij Ryazanov]

Hello!

It was added in H2 2.0.202.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/01e4ae58-f596-45c1-86c5-b85d17ad2dadn%40googlegroups.com.

Re: [h2] How to differentiate system tables from others

[Evgenij Ryazanov]

Do you still have an own special driver for LibreOffice?

If so, you can assume that rows with TABLE_SCHEM = 'INFORMATION_SCHEMA' 
describe system tables.

(Actually they aren't tables, but it doesn't matter.)

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5afd25b8-dcf5-433f-9c72-e31e4ac0fb5dn%40googlegroups.com.

Re: [h2] How to differentiate system tables from others

[Evgenij Ryazanov]

The SQL Standard describes 'BASE TABLE', 'VIEW', 'GLOBAL TEMPORARY', 'LOCAL 
TEMPORARY', and 'SYSTEM VERSIONED' table types. H2 doesn't support system 
versioned tables, but supports all other types.

The JDBC Specification doesn't describe (and shouldn't describe) any table 
types.

Javadoc of DatabaseMetaData.getTables() also doesn't describe any table 
types, but it provides a list of *possible examples* ("TABLE", "VIEW", "SYSTEM 
TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"). The 
*actual* table types available in database are returned by 
DatabaseMetaData.getTableTypes(), this method should return what database 
is actually has and returned list of actual table types is not required to 
have mentioned example table types. This area is database and 
driver-specific.

Some parts of JDBC metadata look like they were written for MySQL and 
partially for Oracle, because they include some exotic features of these 
systems, but don't include many common standard features.

It means you can get some basic information from DatabaseMetaData, but 
there are no unified ways for other features. The INFORMATION_SCHEMA from 
the SQL Standard is designed significantly better, but Standard also cannot 
describe all possible features of all database systems. Unfortunately, some 
database systems don't have this schema, but have some own non-standard and 
non-portable sources of information. Various applications and libraries use 
different queries for different database systems due to all these problems.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a8537dfe-cc21-45c1-96f3-c6d7e04be43bn%40googlegroups.com.

Re: [h2] How to differentiate system tables from others

[Evgenij Ryazanov]

Hi!

The latest version of H2 is 2.1.214, there is no such version as 2.2.219.

You can detect types of tables with the following query in modern versions 
of H2:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, STORAGE_TYPE, 
DB_OBJECT_SQL('TABLE', TABLE_SCHEMA, TABLE_NAME) IS NULL IS_SYSTEM FROM 
INFORMATION_SCHEMA.TABLES;

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0c9892ec-4a71-4802-9f21-9ee9fc42da8an%40googlegroups.com.

[h2] Re: [Spring bot3 + h2 v.2.1.214] H2 failing to fetch Java Instant.

[Evgenij Ryazanov]

MySQL mentioned in your question on StackOverflow doesn't have the TIMESTAMP 
WITH TIME ZONE data type. It means your application uses different data 
types with different database systems.

JDBC drivers by default return TIMESTAMP values as java.sql.Timestamp 
(R2DBC returns them as java.time.LocalDateTime).

Both JDBC and R2DBC drivers return TIMESTAMP WITH TIME ZONE values as 
java.time.OffsetDateTime.

spring-data-commons is able to convert LocalDateTime to Instant, but it 
doesn't have a built-in converter from OffsetDateTime to Instant.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7ea3c19b-1c56-4148-a690-b9d2b66520aen%40googlegroups.com.

[h2] Re: [Spring bot3 + h2 v.2.1.214] H2 failing to fetch Java Instant.

[Evgenij Ryazanov]

Hello!

This problem is not related to H2 at all.

H2 by itself can return TIMESTAMP WITH TIME ZONE and compatible values as 
java.time.Instant from its JDBC driver if it is explicitly requested.

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:")) {

ResultSet rs = c.createStatement().executeQuery("VALUES TIMESTAMP WITH TIME 
ZONE '2023-01-02 03:04:05.123456789+00'");

rs.next();

System.out.println(rs.getObject(1, Instant.class));

}

But when you use some library on top of JDBC, you need to ensure that this 
library also supports java.time.Instant values. This Java type is not a 
part of JDBC specification and only few drivers (including driver of H2) 
support it natively. Java persistence libraries also aren't required to 
know this data type. It looks like you need to write an own data type 
converter. For example, in JPA, converters implement 
jakarta.persistence.AttributeConverter or 
javax.persistence.AttributeConverter depending on version of JPA 
implementation. In your case an implementation of 
org.springframework.core.convert.converter.Converter seems to be required. 
spring-data-commons project has 
org.springframework.data.convert.Jsr310Converters class with some 
converters for java.time.Instant type, but it doesn't have a converter 
between java.time.OffsetDateTime (JDBC data type for TIMESTAMP WITH TIME 
ZONE SQL data type) and java.time.Instant.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5aeff1b3-0650-4243-b9a7-4036c9fd3f89n%40googlegroups.com.

Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)

[Evgenij Ryazanov]

Hello!

JDBC parameters can only be used to specify values (literals). They cannot 
be used to specify identifiers, identifiers (including names of columns) 
aren't parameterizable.

Also your decision to create an own column for each user looks like a bad 
idea, usually you shouldn't store data in that way in relational databases.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/437ce411-0a4f-4003-98bf-3927e7df04a6n%40googlegroups.com.

Re: [h2] Problems with DATEADD, DATEDIFF and daylight savings time

[Evgenij Ryazanov]

Hello!

I have tests that use a H2 db, and the data set is populated with 
timestamps relative to the current timestamp, using expressions such as 
DATEADD(DAY, -3, CURRENT_TIMESTAMP).

 

Let's say current timestamp is 2023-03-27 18:00:00

CURRENT_TIMESTAMP can't return this value, it returns a TIMESTAMP WITH TIME 
ZONE value. Most likely 2023-03-24 18:00:00+02 was returned.
 

, and assertion expects a query to return a timestamp at same time of day 3 
days ago, then the (computed) expected timestamp in Java would correctly be 
*2023-03-24 
18:00:00*

DATEADD with TIMESTAMP WITH TIME ZONE argument also returns a TIMESTAMP 
WITH TIME ZONE value with the same time zone offset.

DATEADD(DAY, -3, TIMESTAMP WITH TIME ZONE '2023-03-27 18:00:00+02' returns 
2023-03-24 
18:00:00+02 (and this value is actually equal to 2023-03-24 17:00:00+01)

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("SET TIME ZONE 'Europe/Paris'");

ResultSet rs = s.executeQuery("VALUES DATEADD(DAY, -3, TIMESTAMP WITH TIME 
ZONE '2023-03-27 18:00:00+02')");

rs.next();

System.out.println(rs.getObject(1)); // Returns OffsetDateTime

System.out.println(rs.getObject(1, LocalDateTime.class)); // Implicit 
conversion to TIMESTAMP data type

System.out.println(rs.getTimestamp(1)); // Legacy method, shouldn't be used 
in modern applications, especially for TIMESTAMP WITH TIME ZONE data type

}

2023-03-24T18:00+02:00
2023-03-24T17:00
2023-03-24 17:00:00.0

If you want to perform date-time arithmetic with your local time zone, you 
need to use the TIMESTAMP data type instead. Current value of this data 
type is returned by the standard LOCALTIMESTAMP function.

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("SET TIME ZONE 'Europe/Paris'");

ResultSet rs = s.executeQuery("VALUES DATEADD(DAY, -3, TIMESTAMP 
'2023-03-27 18:00:00')");

rs.next();

System.out.println(rs.getObject(1, LocalDateTime.class));

System.out.println(rs.getTimestamp(1)); // Legacy method, shouldn't be used 
in modern applications

}

2023-03-24T18:00
2023-03-24 18:00:00.0

Please note that TIMESTAMP data type has a natural limitation, it cannot 
distinguish 2023-10-29 02:00:00 CEST and 2023-10-29 02:00:00 CET, because 
it doesn't have any time zone information.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7a98b52c-dc29-4ea4-90b1-be40f3aa8886n%40googlegroups.com.

Re: [h2] Re: Cannot find symbol NUMERIC

[Evgenij Ryazanov]

With JDBC, you can call your function either with PreparedStatement or with 
CallableStatement:

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("CREATE ALIAS my_stored_proc AS 'BigDecimal send() { return 
BigDecimal.ONE; }'");

// With PreparedStatement

PreparedStatement ps = c.prepareStatement("CALL my_stored_proc()");

ResultSet rs = ps.executeQuery();

rs.next();

System.out.println(rs.getBigDecimal(1));

// With CallableStatement

CallableStatement cs = c.prepareCall("{? = CALL my_stored_proc()}");

cs.registerOutParameter(1, Types.NUMERIC);

cs.execute();

System.out.println(cs.getBigDecimal(1));

}

In case of callable statement you should register the output parameter 
only, because your function doesn't have any input parameters. It is also 
possible to use it without registration of output parameter, in that case 
result can be read from it in the same way as from prepared statement.

You use a wrapper over JDBC, it has a different API, but situation in the 
same. Your function doesn't have any input parameters, it means you 
shouldn't try to pass them here, hypothetically SimpleJdbcCall can be 
confused by that unexpected parameter. You can try to run it without 
parameters. But I never used that library, so I can't be sure.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/927ea4e9-60d0-41ad-b186-95202b3bdcfan%40googlegroups.com.

[h2] Re: Cannot find symbol NUMERIC

[Evgenij Ryazanov]

Hello!

The valid syntax is

CREATE ALIAS MY_STORED_PROC AS '
BigDecimal envia() {
return BigDecimal.ONE;
}
';

because SQL NUMERIC data type is mapped to BigDecimal data type in Java.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/92fde29d-e26e-4816-b752-d7e568b971c1n%40googlegroups.com.

[h2] Re: Timestamp's value is different in different time zones

[Evgenij Ryazanov]

Take a look on file name extension, if it is .h2.db, this is a file from 
PageStore, if it is .mv.db a modern MVStore backend is used.

H2 1.4.192 is too old and doesn't support JSR-310 data types yet, their 
initial support was added only in H2 1.4.193.

If your file is in MVStore format, you can read the original datetime value 
with ResultSet.getString(column) in 1.4.192.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ae329b60-ffa7-4115-a9b9-825f68e513d2n%40googlegroups.com.

[h2] Re: Timestamp's value is different in different time zones

[Evgenij Ryazanov]

> Unfortunately, I should be using an old version of jdbc driver

If you use some old unsupported version of H2 with PageStore backend, you 
need to set the same JVM time zone as it was on system where database file 
was created. This backend had very problematic storage format for datetime 
values. It cannot read persisted timestamps back properly if there is any 
difference between DST transition rules. MVStore backend isn't affected by 
that problem.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2f772404-740a-410c-992b-c048b8ec2430n%40googlegroups.com.

[h2] Re: Unique index or primary key violation

[Evgenij Ryazanov]

Hi!

It doesn't look like an original error from H2, H2 produces messages like 
Unique 
index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.AD(ACCU_ID, 
TARGET_ID, REPLAY_ID) VALUES ( /* key:1 */ 2, 3, 4), where 1 is an internal 
row number (_ROWID_) and other values are values of primary key columns.

Please note that H2 1.4.192 is a very old unsupported version of H2 
database.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/af3fee93-0f96-4c7b-8886-c49a8bcac386n%40googlegroups.com.

[h2] Re: Timestamp's value is different in different time zones

[Evgenij Ryazanov]

Hi!

There are two different timestamp data types in the SQL Standard: TIMESTAMP 
(TIMESTAMP WITHOUT TIME ZONE) and TIMESTAMP WITH TIME ZONE.

TIMESTAMP [ WITHOUT TIME ZONE ] has YEAR, MONTH, DAY, HOUR, MINUTE, and 
SECOND fields.
TIMESTAMP WITH TIME ZONE additionally has TIMEZONE_HOUR and TIMEZONE_MINUTE 
fields.

The SQL Standard determines their relation to local time or UTC time in the 
following way:

A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT 
TIME ZONE,
may represent a local time, whereas a datetime value of data type TIME WITH 
TIME ZONE or TIMESTAMP
WITH TIME ZONE represents UTC.

H2 strictly follows the SQL Standard here. The TIMESTAMP data type in H2 
represents local date and time, the TIMESTAMP WITH TIME ZONE represents a 
timestamp with some exactly known UTC offset and this offset is preserved. 
(Actually H2 additionally supports time zones with seconds in their 
offsets, but it doesn't matter here.)

SET TIME ZONE 'Europe/Paris';
CREATE TABLE TEST(T1 TIMESTAMP, T2 TIMESTAMP WITH TIME ZONE);
INSERT INTO TEST VALUES(TIMESTAMP '2023-02-01 01:00:00', TIMESTAMP WITH 
TIME ZONE '2023-02-01 01:00:00+01:00');
TABLE TEST;
> H2: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01
> PostgreSQL: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01
SET TIME ZONE 'Asia/Tokyo';
TABLE TEST;
> H2: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01
> PostgreSQL: 2023-02-01 01:00:00 | 2023-02-01 09:00:00+09

You can see that H2 and PostgreSQL work more or less in the same way, but 
PostgreSQL doesn't preserve time zone offset in its TIMESTAMP WITH TIME ZONE 
data type and converts UTC to local time zone instead. Anyway, 2023-02-01 
01:00:00+01 and 2023-02-01 09:00:00+09 represent the same UTC value.

So you need to choose a proper data type depending on your needs. If you 
need to store local values and they should stay the same when time zone is 
changed, use the TIMESTAMP data type, but beware of DST transitions. If you 
need to hold exact absolute values, use TIMESTAMP WITH TIME ZONE.

TIMESTAMP values should be read and set as java.time.LocalDateTime.
TIMESTAMP WITH TIME ZONE values should be read and set as 
java.time.OffsetDateTime, but H2 also supports java.time.Instant and 
java.time.ZonedDateTime.

Never use java.sql.Timestamp, this defective by design class represents a 
local datetime value, but it holds it internally in UTC and it doesn't know 
the exact time zone to display it properly. It also has other problems with 
historic dates.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7521d99d-64d3-48f1-9db8-a79a73214d69n%40googlegroups.com.

[h2] Re: "SELECT .. WHERE ... IN ... OR ... IN" on large tables

[Evgenij Ryazanov]

Hello!

H2 is unable to optimize such OR conditions when multiple columns are 
involved.

To make your query faster, you need to rewrite it with a UNION between two 
inner queries, one with filtration by first column and second with 
filtration by second one.
These inner queries should be able to use indexes on these columns.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2d90abe8-3ee5-441c-a695-bc1f7ec28de8n%40googlegroups.com.

[h2] Re: Index not found error while using index hints

[Evgenij Ryazanov]

Hi!

I think there are only two possible reasons for that failure.
1. Index belongs to another table (possibly with the same name, but in 
different schema).
2. Index has another name, for example, "my_table_index" (quoted 
identifiers are case sensitive by default).

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7023fc93-ee2e-49c7-8968-779c0a36d571n%40googlegroups.com.

[h2] Re: Asking for help

[Evgenij Ryazanov]

Hello!

In H2 2.1.210 an old bug with incorrectly parsed join conditions was fixed. 
In earlier version of H2 in some cases they were applied to the wrong joins.

Few applications use workarounds for this bug and these workarounds need to 
be removed from them during upgrade to H2 2.1.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/58874403-1377-4654-a95e-9c651f5c5c47n%40googlegroups.com.

[h2] Re: Problem updating from 1.4.199 to 2.1.214

[Evgenij Ryazanov]

Hello!

2.5.5 is too old and it has hibernate-core 5.4.32.Final in its 
dependencies, this version doesn't support any new versions of H2. You need 
to check version of Hibernate ORM actually used by your application and 
upgrade it to 5.6.15.Final or 6.1.7.Final if necessary.

You also need to check SQL data types of columns with UUID values if you 
have them, because Hibernate ORM can incorrectly choose some wrong data 
type such as BINARY(255) for them, in historic versions of H2 it was 
acceptable, because it was incorrectly implemented as BINARY VARYING(255), 
but in new versions of H2 these data types have different 
standard-compliant implementations. The best data type for H2 is UUID, but 
BINARY(16) can also be used. Attempts to use BINARY(255) cause various 
problems, such as https://hibernate.atlassian.net/browse/HHH-15373

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/861c9172-d9c7-4cfb-8724-5b822d320fa9n%40googlegroups.com.

[h2] Re: Error converting value

[Evgenij Ryazanov]

Hello!

In the SQL Standard all character strings with universal character set (H2 
doesn't have any other character sets) are comparable with each other and 
all numbers are comparable with each other. Comparison operations between 
different groups of data types aren't described, so there is no required 
behavior and you cannot assume anything about them, for example, database 
may reject them all.

De-facto database systems usually allow this comparison and convert 
character strings to numbers, so there is nothing special in behavior of 
H2. This behavior is actually useful for the most of cases.

In your case you need to use '12345' instead of 12345 to avoid failures and 
to allow usage of index (if this column has an index or a primary key or 
unique constraint).

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/156fc3f2-f63e-4a81-953d-5d96e74e7d3fn%40googlegroups.com.

[h2] Re: Enquiry for New version release

[Evgenij Ryazanov]

Hello!

What exactly do you mean? There are no known vulnerabilities in this 
version.

CVE-2022-45868 / sonatype-2022-624. is a fake security report and there is 
nothing to fix on H2 side. If some code checking tools reports it to you, 
let it vendor know about that, usually such tools have lists of false 
positives due to low quality of security databases.

https://github.com/h2database/h2database/issues/3686

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ac971fdb-6832-4913-8d54-823fdb9fa5c0n%40googlegroups.com.

[h2] Re: Error on NaN literal in SQL statement

[Evgenij Ryazanov]

Hello!

NaN is not a literal, it's just an identifier.

In this case you can pass it as a character string literal:
INSERT INTO "TABLE"(ID, DOUBLE_COLUMN) VALUES (10, 'NaN');

In more complex cases where data type cannot be determined automatically a 
cast is needed:
CAST('NaN' AS DOUBLE PRECISION)

There are two other special values: CAST('Infinity' AS DOUBLE PRECISION) and 
CAST('-Infinity' AS DOUBLE PRECISION).

REAL and DECFLOAT data types also have these three special values in H2, 
but all other numeric data types (TINYINT, SMALLINT, INTEGER, BIGINT, and 
NUMERIC) don't support them.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/97b9ab12-90a9-4b15-90a3-586e55f8ff96n%40googlegroups.com.

Re: [h2] Re: show hex value

[Evgenij Ryazanov]

Hello!

There is a RAWTOHEX  
function, you can convert this integer number to a BINARY(4) data type and 
pass the result to this function:
SELECT RAWTOHEX(CAST(1234567890 AS BINARY(4)));

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/c06add93-e74c-4aba-8269-ff5bfcbcaa34n%40googlegroups.com.

Re: [h2] index doesn't help

[Evgenij Ryazanov]

Hello!

WHERE boolean and WHERE boolean = TRUE have no difference.

The actual problem is usage of OR, H2 is unable to use indexes in such 
conditions. Take a look on execution plan produced by the EXPLAIN 
 command:
SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.DATA.tableScan */
WHERE "MEM"
OR "IRQREQUEST"
ORDER BY 1

So you need to convert this query into query with a UNION:
(select sequence, mem, memaddr, memvalue, memRead from data where mem union 
select sequence, mem, memaddr, memvalue, memRead from data where 
irqRequest) order by sequence;
This query can use indexes, see its execution plan:

(SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.MEM: MEM = TRUE */
WHERE "MEM")
UNION
(SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.IRQREQUEST: IRQREQUEST = TRUE */
WHERE "IRQREQUEST")
ORDER BY 1

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/46d20b0f-f1c0-473a-b1d9-2993c0d8b1f2n%40googlegroups.com.

[h2] Re: questions about case sensitivity

[Evgenij Ryazanov]

Hello!

These settings aren't related to each other.

IGNORE_CASE is about *data types* and their *values*. It replaces CHARACTER 
VARYING (VARCHAR) columns in DDL commands with VARCHAR_IGNORECASE data 
type. This legacy setting should never be used, it is much better to 
specify some case-insensitive database collation if you need it.

DATABASE_TO_UPPER, DATABASE_TO_LOWER, and CASE_INSENSITIVE_IDENTIFIERS 
settings change treatment of *identifiers*. H2 by default is fully 
compliant with the SQL Standard here. It means all unquoted identifiers are 
converted to upper case. id, ID, Id, "ID" (and also non-standard 
MySQL-style `ID` and `id`) are equal to each other, but "id" is an another 
identifier. Some other database systems historically process identifiers in 
their own special way and sometimes this way also depends on their 
environment. That's why these settings can be set separately from 
compatibility modes. Values of these settings, however, aren't persisted 
and you must always specify them with the same values in JDBC URL.

It is possible to specify them in the default Regular compatibility mode 
too, but, again, the default behavior is already correct and compliant with 
the Standard.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a01867ed-f497-4a74-a5f1-042f2315e2c3n%40googlegroups.com.

[h2] Re: How to back up the data while the database and program is running

[Evgenij Ryazanov]

Hello!

You can execute the SCRIPT command:
https://h2database.com/html/commands.html#script

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/47524c29-6f36-4b96-af7f-1bce92a4f03cn%40googlegroups.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Evgenij Ryazanov]

Scripts generated by this tool aren't really suitable for data migration.

It will be better to use the SCRIPT 
 command or the Script 
tool to produce an SQL without all this messy temporary tables and edit 
their resulting script as necessary. They also can be used to export 
different tables separately.

Unfortunately, different database systems aren't really compatible with 
each other even in their basic functionality, so usually you cannot execute 
an SQL dump from one database system in another database system without 
some modifications, such as replacements of data types and other.

There are some third-party tools for such migrations, but usually they 
support only few database systems and I don't know whether some of them 
supports migrations from H2 to MS SQL or not.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2ec41a0a-b297-4d62-becd-2b4cac94cc38n%40googlegroups.com.

[h2] Re: Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

[Evgenij Ryazanov]

Hello!

Resolution of system timestamps in JVM depends on Java version and 
operating system. Old versions of Java (Java 8 and older) provide only 3 
digits. Resolution was improved in Java 9, this version provides 7 digits 
on Windows, but only 6 digits on Linux, because it uses an old system 
function with this limitation. Java 15 and newer versions use a modern 
function and provide 9 digits instead of 6 on Linux.

There are two data types in the SQL Standard and H2 for datetime values 
with both date and time parts: TIMESTAMP 
 and TIMESTAMP 
WITH TIME ZONE 
. 
Both data types have default fractional seconds precision of 6 as required 
by the Standard and some database systems, including the H2, support larger 
precision.

(DATETIME is silently replaced with standard TIMESTAMP data type, actually 
you normally should use TIMESTAMP(9) WITH TIME ZONE for Instant values in 
H2 to avoid issues on DST or other time zone transitions.)

Instant values in Java also support up to 9 fractional digits. When you 
insert a value with non-zero nanoseconds into a column with lower 
fractional seconds precision, this value is rounded to that precision by H2 
(the SQL Standard doesn't specify an exact behavior, it only requires an 
implementation-defined rounding or truncation). When Hibernate reads it 
back it gets a rounded value instead of expected original one. To avoid it, 
you must define an explicit fractional seconds precision of 9 for this 
column.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a73f0faa-d9ae-4e2b-a408-9603c4efd0ban%40googlegroups.com.

[h2] Re: show hex value

[Evgenij Ryazanov]

Hi!

H2 Console already displays values of binary strings in hexadecimal format, 
if you need a similar feature in other tool you should ask its developers 
instead.

There is also a RAWTOHEX 
 function, you can use 
it to convert a binary string to a character string with hexadecimal 
representation.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/31e4a71e-b5e7-4f28-a405-f99c6ec23df2n%40googlegroups.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Evgenij Ryazanov]

Hi!

Table definition and table data are stored separately and they are 
recovered separately too.

The recovery tool works in the following way (I skipped unrelated steps for 
simplicity):
1. It constructs a temporary table with LOB chunks from all tables first.
2. Then, it recovers data from tables, it creates a new temporary table for 
each found table for that purpose. LOB values for them are constructed from 
chunks from (1). Actual table names, column names, data types and other 
attributes aren't yet known at that moment.
3. Next, it tries to recover metadata of database, including definitions of 
actual tables.
4. After that, If metadata of some table was recovered on step (3) and data 
for that table was recovered on step (2) data is copied from the temporary 
table into actual one.
5. Finally, all temporary tables are dropped.

This strategy isn't perfect and can potentially be improved, at least for 
some cases when both data and metadata are recoverable.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a60484f7-f4c2-456a-8967-d4644854dd48n%40googlegroups.com.

[h2] Re: Getting names of unique constraints via hibernate

[Evgenij Ryazanov]

Hello!

H2 doesn't return names of constrains in these exceptions (maybe it will in 
future versions). If you need to find name of constraint, you need to check 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS table. You need only rows where 
TABLE_SCHEMA and TABLE_NAME match schema and name of updated table.

* For primary key violations situation is simple, because a table may have 
only one such constraint, so you need a row with CONSTRAINT_TYPE = 'PRIMARY 
KEY'.

* For unique index violations you need to read index name from exception 
and find a row with matched names in INDEX_SCHEMA and INDEX_NAME columns. *If 
there is no such row, it isn't a violation of a unique constraint, but it 
is a violation of unique index (and you already know name of this index), 
unique indexes may exist without constraints*. Alternatively you can parse 
column names from exception and search them in 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but such check will be significantly 
more complicated than check by index name.

Names of both primary key and unique constraints can be read from 
CONSTRAINT_SCHEMA and CONSTRAINT_NAME columns in matched rows.

Indexes used by constraint may have any names, there is no mandatory 
suffix. Constraints can and will use any compatible existing index instead 
of own one if such index already exist, so you cannot determine name of 
constraint from name of its index without 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.

There is also a special case for names in exceptions. It is possible to 
create an index with non-ASCII or non-printable characters. Index PUBLIC.Ä 
(or PUBLIC.U&"\00c4", both names are equivalent in SQL) will be reported as 
"PUBLIC.U&""\\00c4"" in exception, but you need to find a row with INDEX_NAME 
= 'Ä' (or INDEX_NAME = U&'\00c4').

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/c3d147b9-0339-4fd7-8113-f115068b144bn%40googlegroups.com.

[h2] Re: int not 100% continuous

[Evgenij Ryazanov]

Hi!

If you don't care about exact ordering, the fastest way is
UPDATE tableName SET columnName = ROWNUM();

If you want to preserve it, a slower command is needed:
MERGE INTO tableName USING
(SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) 
T(columnName, R)
ON tableName.columnName = T.columnName
WHEN MATCHED THEN UPDATE SET columnName = T.R;
This command also requires a lot of memory (because H2 cannot buffer window 
functions on disk).

If you have an ascending index on tableName(columnName), you can try to use 
more efficient version without window functions:
MERGE INTO tableName USING
(SELECT columnName, ROWNUM() FROM tableName ORDER BY columnName) 
T(columnName, R)
ON tableName.columnName = T.columnName
WHEN MATCHED THEN UPDATE SET columnName = T.R;
Without a compatible index ROWNUM() can number rows in order different from 
specified in ORDER BY clause.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d1add6b0-1d5e-4194-b3fd-2f4ff96a90b0n%40googlegroups.com.

Re: [h2] H2 PreparedStatement and Arrays

[Evgenij Ryazanov]

Your code actually constructs something like mstb_etypenum IN (ARRAY[2, 3, 
4, 5]) instead of mstb_etypenum IN (2, 3, 4, 5). 

In SQL statements from JDBC (unlike in JPQL from JPA) you cannot pass 
parameters in that way. One parameter always creates one value.

You can use
mstb_etypenum = ANY(?)
instead, see *Prepared Statements and IN(...)* section in documentation for 
an example:
https://h2database.com/html/performance.html#database_performance_tuning

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/3bd02bc0-5625-4bc7-9045-a12caeb7d16fn%40googlegroups.com.

[h2] Re: How to modify SELECT LOB_TIMEOUT

[Evgenij Ryazanov]

Yes, execution of any SQL command and also actions on toolbar (refresh, 
commit, rollback, command history, etc.) reset the timeout.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b7b8e59f-adda-4f14-9d51-0635d4be23f2n%40googlegroups.com.

[h2] Re: How to modify SELECT LOB_TIMEOUT

[Evgenij Ryazanov]

Yes, this setting is not related to timeout of web sessions.

You need
java -Dh2.consoleTimeout=360 …

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e2b69941-e666-4f5c-8f5c-6a92f0cfcb36n%40googlegroups.com.

[h2] Re: How to modify SELECT LOB_TIMEOUT

[Evgenij Ryazanov]

You cannot specify any connection options in this file.

You need to change JDBC URL in Login window of H2 Console itself.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7b14a71d-8bd7-470c-9266-24033662e3e7n%40googlegroups.com.

[h2] Re: How to modify SELECT LOB_TIMEOUT

[Evgenij Ryazanov]

Hello.

You can add ;LOB_TIMEOUT=your_value to JDBC URL.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4a639077-78e4-44f2-965f-d86965d501cfn%40googlegroups.com.

[h2] Re: ERROR SqlExceptionHelper: Unknown data type: "unsigned"

[Evgenij Ryazanov]

Hello.

CAST in MySQL and its forks is not compliant with cast specification from 
the SQL Standard and is not compatible with other database systems. H2 
cannot emulate this deviation.

You can replace it with something like
case when sequence >= 0 then sequence else sequence + 18446744073709551616 
end
It should work in almost all database systems, including MySQL and H2.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/095d1d69-f907-47d1-87d9-b6a2cdf68af9n%40googlegroups.com.

Re: [h2] Can't insert SHA3-256 from runscript

[Evgenij Ryazanov]

H2 1.4.200 supports only SHA-256 and it needs to be specified as SHA256 
(without dash) in this outdated version.

Please note that passwords should normally be hashed with a random data 
(salt) to prevent some common attacks.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5c012fab-b4e3-4db5-b6f8-2cef68a2fd5bn%40googlegroups.com.

[h2] Re: Can't insert SHA3-256 from runscript

[Evgenij Ryazanov]

Hello.

SHA3-256 is only supported by H2 2.*.*. Error code 90008-200 was produced 
by H2 1.4.200, it doesn't support that algorithm. You need to upgrade H2 
used by your application to a some recent version.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f2e61cc4-f44a-4db3-9a46-6a594b18a4a5n%40googlegroups.com.

[h2] Re: JDBC getIndexInfo for large table is very slow

[Evgenij Ryazanov]

Hello!

You need to pass true as the last argument (approximate). It allows to 
return a fast approximation instead of exact number of rows in the index.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/29742cd3-9bed-4c9e-b8c2-619c1f162cc2n%40googlegroups.com.

[h2] Re: Help needed for COLUMN option in SCRIPT

[Evgenij Ryazanov]

Hello!

Unfortunately, this tool doesn't support all clauses of the SCRIPT command. 
You need to use the Shell tool or H2 Console or JDBC connection to the 
database.

Valid SQL command will be SCRIPT COLUMNS TO 'backup.sql'

https://h2database.com/html/commands.html#script

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/153851f8-2f95-40f6-af28-ad792ab9179fn%40googlegroups.com.

[h2] Re: Cant get VARCHAR column to maximum size

[Evgenij Ryazanov]

Hello.

Online documentation is actual only for the latest released version, 
currently it is 2.1.214. If you use some older version, you need to 
download its complete distribution in a ZIP archive, it includes 
documentation in HTML and PDF formats:
https://h2database.com/html/download-archive.html

In H2 2.0.202–2.1.210 limit was 1,048,576 characters.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/efc8e42b-9f14-4aa9-a238-91727e229e1dn%40googlegroups.com.

Re: [h2] Management of users, roles and privileges in LibreOffice Base

[Evgenij Ryazanov]

> Can you confirm that the names of users and roles only support uppercase 
characters without double quotes in all the commands where they appear.

It is possible to use unquoted (NAME), quoted ("NAME"), or Unicode 
(U&"NAME") identifiers for names of users and roles in H2, but these names 
are always converted to the upper case, they are case insensitive, unlike 
other names.

If DATABASE_TO_LOWER=TRUE setting was used (usually together with 
PostgreSQL, MySQL, or MariaDB compatibility mode), they are reported in the 
INFORMATION_SCHEMA in the lower case, by default they are in the upper case.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/adfb05a2-b71c-4614-9159-f5362b1454b0n%40googlegroups.com.

[h2] Re: SELECT command that returns a strange RESULSET

[Evgenij Ryazanov]

Hi!

USER is not an identifier. It is a reserved word in the SQL Standard. It 
has exactly the same meaning as CURRENT_USER in the SQL Standard and in H2.
https://h2database.com/html/functions.html#current_user

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a6b70b07-671c-4e3b-8603-20a9effa6e43n%40googlegroups.com.

Re: [h2] Management of users, roles and privileges in LibreOffice Base

[Evgenij Ryazanov]

Database administration is out of scope of the SQL Standard and there are 
no predefined roles is the Standard. In the Standard, every schema has an 
owner and this owner may perform DDL commands within this schema. Anything 
else is database-specific.

In H2, the most of DDL commands require schema owner privileges. Schema 
owner can be specified with standard command:
https://h2database.com/html/commands.html#create_schema
And it can be read from standard INFORMATION_SCHEMA.SCHEMATA.SCHEMA_OWNER.

Few commands in H2 need access to JVM, they require non-standard ADMIN 
privileges due to security reasons.
Users with these privileges can be created with non-standard CREATE USER 
command with ADMIN clause:
https://h2database.com/html/commands.html#create_user
You can check whether some user is ADMIN or not in non-standard 
INFORMATION_SCHEMA.USERS.IS_ADMIN column.
When database is created, user gets ADMIN privileges automatically. If they 
aren't required for normal operations, it is recommended to create and use 
a user with lower privileges.

H2 also has special non-standard ALTER ANY SCHEMA privileges, they give 
access to commands with required schema owner privileges in any schema.
They can be granted or revoked with non-standard commands:
https://h2database.com/html/commands.html#grant_alter_any_schema
https://h2database.com/html/commands.html#revoke_alter_any_schema
You can check non-standard INFORMATION_SCHEMA.RIGHTS for presence of these 
privileges.
If 'RIGHTS' = 'ALTER ANY SCHEMA', GRANTEE has them.
These privileges don't provide access to JVM, unlike ADMIN privileges.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0e7d8b3d-0c2b-42a3-ab97-a8affc059b35n%40googlegroups.com.

Re: [h2] Management of users, roles and privileges in LibreOffice Base

[Evgenij Ryazanov]

Hi!

On Friday, 2 September 2022 at 23:40:02 UTC+8 prrvchr wrote:

>
>- java.sql.DatabaseMetaData.getUserName() should return the user who 
>is logged in (current user).
>
> This method is implemented in H2 itself properly. You can also use 
standard CURRENT_USER expression in SQL.


>- In order to obtain all the users declared on the database the 
>INFORMATION_SCHEMA.SYSTEM_USERS table is used. This table normalized 
>by JDBC must list all the users whose current user... The query is: SELECT 
>USER_NAME FROM INFORMATION_SCHEMA.SYSTEM_USERS
>
> There is no such table or view in the SQL Standard. INFORMATION_SCHEMA is 
not related to JDBC in any way, it is covered only by the SQL Standard, 
Part 11: Information and Definition Schemas (SQL/Schemata). H2 has own 
non-standard table INFORMATION_SCHEMA.USERS, you need to use it instead.
 

>
>- In order to obtain all the roles declared on the database the 
>INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS table is used. This 
>table normalized by JDBC should list all roles, including default system 
>roles... The query is: SELECT ROLE_NAME FROM 
>INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS
>
> This view is a part of the SQL Standard. Database systems with optional 
features T331, “Basic roles” and F391, “Long identifiers” should also 
provide this view. We can add this view to H2.

>
>- in order to obtain the hierarchy of roles as well as the users 
>assigned to roles since there is no standardized table in JDBC, I need 
>a table in INFORMATION_SCHEMA allowing to establish these relations.  With 
>HsqlDB, fredt provided me with the 
>INFORMATION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS table which lists all 
> the 
>GRANTEE and ROLE_NAME and allows me to establish the relationships...
>
> There is no such table or view is the SQL Standard. There is a 
DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS table, but according to 
the Standard, DEFINITION_SCHEMA is not accessible to applications even if 
it exists. Applications should use views in INFORMATION_SCHEMA based on 
this table, such as INFORMATION_SCHEMA.APPLICABLE_ROLES, 
INFORMATION_SCHEMA.ENABLED_ROLES and others, but they don't provide 
complete information. We can add these standard views to H2, but we 
shouldn't add ROLE_AUTHORIZATION_DESCRIPTORS.

In the current H2 all available information is provided 
in INFORMATION_SCHEMA.USERS, INFORMATION_SCHEMA.ROLES, and 
INFORMATION_SCHEMA.GRANTS. These tables are non-standard and non-portable.

https://h2database.com/html/systemtables.html#information_schema_users
https://h2database.com/html/systemtables.html#information_schema_roles
https://h2database.com/html/systemtables.html#information_schema_rights

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/632e9586-ad71-4180-9601-1e26a3c0b286n%40googlegroups.com.

[h2] Re: Database hangs when the query contains multibyte space.

[Evgenij Ryazanov]

Hello!

This issue was fixed, the fix will be included into the next version.

If you need this fix right now, you can compile H2 from its current sources:
https://github.com/h2database/h2database
Building instructions are here:
https://h2database.com/html/build.html#building

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/3792ee2c-5e0e-4c81-b1f4-f624ca8159fdn%40googlegroups.com.

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

[Evgenij Ryazanov]

You need to download the complete distribution of the version you need in a 
ZIP archive:
https://h2database.com/html/download-archive.html
They contain documentation in PDF and HTML format.

But INFORMATION_SCHEMA wasn't documented properly in old versions, so you 
cannot compare old and new implementation in that way.

You can try to compare new and old implementations in the sources:
https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTable.java
https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTableLegacy.java

Legacy implementation is provided by H2 Server to client processes with 
outdated drivers.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f541fabc-8f8e-4a18-975d-e910dcb004ffn%40googlegroups.com.

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

[Evgenij Ryazanov]

You can read data type name and other parameters from the COLUMNS table:
https://h2database.com/html/systemtables.html#information_schema_columns
DATA_TYPE column now contains the name as required by the SQL Standard.

For ARRAY data types there is an additional table with definitions of 
elements:
https://h2database.com/html/systemtables.html#information_schema_element_types

For row value data types you need another additional table with definitions 
of fields:
https://h2database.com/html/systemtables.html#information_schema_fields

All these tables in H2 2.*.* are compliant with the SQL Standard.

Non-standard ENUM data types have an own non-standard table with possible 
values:
https://h2database.com/html/systemtables.html#information_schema_enum_values

In old versions of H2 INFORMATION_SCHEMA.COLUMNS.DATA_TYPE was incorrectly 
used for JDBC type code. H2 2.*.* doesn't report JDBC-specific information 
in the INFORMATION_SCHEMA, but you can read it from JDBC database metadata, 
if you wish.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e87f345e-df94-4609-a868-dfd77faa3aa8n%40googlegroups.com.

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

[Evgenij Ryazanov]

Hi!

DatabaseMetaLocal is not related to INFORMATION_SCHEMA. It is a data source 
for implementation of java.sql.DatabaseMetaData.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/55755d0b-d097-4aa1-baff-d61ea81d6a85n%40googlegroups.com.

[h2] Re: getPlanSQL response

[Evgenij Ryazanov]

Hello.

org.h2.command.Prepared is not a part of API and it should never be used 
directly by application.

If you need to get execution plan of some query or DML command, you should 
use the EXPLAIN command:
https://h2database.com/html/commands.html#explain

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/41ceb8a2-f2a5-4048-a740-787065c69fa3n%40googlegroups.com.

[h2] Re: Question about async: filesystem

[Evgenij Ryazanov]

Web application with embedded database managed by this application must 
have a ServletContextListener. In its contextDestroyed() method it must 
shutdown the database properly.

For example, application can use a org.h2.jdbcx.JdbcConnectionPool to 
allocate its connections. It can be constructed with separately created 
org.h2.jdbcx.JdbcDataSource.

contextDestroyed() can execute everything you need during unloading of your 
application first, then it should dispose the connection pool to prevent 
allocations of new connections by some leftover parts of your application, 
and finally it must execute the SHUTDOWN command, it can allocate an own 
connection directly from the data source for this command or use some 
previously created connection. Possible exceptions from this special 
connection should normally be ignored.

For Apache Tomcat running as a system service that logic should provide 
enough safety by itself. But there are other servers and it is possible to 
launch them in various ways, so in some cases your application may need 
some additional protection.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e5cf8dda-1d7a-4c21-919b-7cfa923d0df6n%40googlegroups.com.

[h2] Re: Question about async: filesystem

[Evgenij Ryazanov]

Hello!

file: and nio: have no special meaning in modern versions of H2, 
jdbc:h2:file:SOME_PATH, jdbc:h2:nio:SOME_PATH, and plain jdbc:h2:SOME_PATH 
have exactly the same meaning.
Unfortunately, invocation of Thread.interrupt() during disk I/O closes the 
underlying file channel, so it isn't safe to interrupt a thread that 
executes some database command in embedded persistent database. If you use 
a separate server process you can interrupt client threads safely.

async: file system uses asynchronous I/O on Windows and it may work a 
little bit faster, but better performance is not guaranteed for all cases.
On POSIX systems in simply performs I/O in separate threads, so performance 
may be slightly reduced. These systems, unlike Windows, don't need any 
special optimizations for multi-threaded access to the same file.
In both cases case Thread.interrupt() on thread that works with database 
doesn't perform disk I/O by itself, so if you cannot prevent interrupts and 
cannot use a separate server, you need to use this file system abstraction 
layer.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8de232b0-4f98-4ae1-921c-68379c9ae3f7n%40googlegroups.com.

[h2] Re: question about to_char

[Evgenij Ryazanov]

Hello!

Yes, it's a bug. Implementation of this Oracle compatibility function 
doesn't handle DECFLOAT arguments properly, but you can cast them to a some 
other numeric types as a workaround.

TO_CHAR(CAST(2E+1 AS NUMERIC(6, 2)), 'D00')

Approximate numeric literals (with exponent) have DECFLOAT data type in new 
versions of H2.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6eedc3f0-d6ba-4735-82a2-916e04466474n%40googlegroups.com.

[h2] Re: Revisiting manipulating CURRENT_TIMESTAMP

[Evgenij Ryazanov]

Hello!

You can only add ;BUILTIN_ALIAS_OVERRIDE=TRUE to JDBC URL and create an own 
user-defined function with that name (CREATE ALIAS "CURRENT_TIMESTAMP" …):
https://h2database.com/html/commands.html#create_alias

But usually it is better to move that logic from database level to Java 
persistence level or application level.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a4fcdd8d-73ac-4f7c-8cb3-012f8dfd918an%40googlegroups.com.

[h2] Re: UNSIGNED and array problem in H2 query

[Evgenij Ryazanov]

Hello.

Where did you find that syntax? There are no unsigned numeric data types in 
the SQL Standard and in the most of database systems including the H2. This 
is a feature of MySQL. H2 silently accepts UNSIGNED only in MySQL and 
MariaDB compatibility modes, but this declaration is ignored by H2 anyway.
https://h2database.com/html/features.html#compatibility
Also the real MySQL doesn't have the ARRAY data type, so if you use H2 as 
an replacement of MySQL for unit tests, you shouldn't try to use it.

If you use H2 as a primary database, you can use standard-compliant ARRAY 
data type safely:
https://h2database.com/html/datatypes.html#array_type
You cannot declare your column as UNSIGNED, but you can add a check 
constraint to it if you wish.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/73bf3449-81ac-415e-b749-7ffdef344489n%40googlegroups.com.

Re: [h2] Exception from PgServerThread.initDb

[Evgenij Ryazanov]

Hello.

> Will the database files dbName.h2.db , dbName.lock.db, dbName.mv.db and 
dbName.track.db work in the latest version
No, you need to export your databases to SQL scripts with old version of H2 
and create new databases with the new version and execute these scripts 
(this procedure was always required to upgrade between different versions):
https://h2database.com/html/tutorial.html#upgrade_backup_restore
You may need to add FROM_1X to import options, documentation suggests 
VARIABLE_BINARY, but FROM_1X can handle incorrect grammar used by old 
versions of H2 much better, unfortunately, documentation isn't up to date.

There is also a org.h2.tools.Upgrade utility in H2 2.*.*, you can try to 
use it instead (it requires access to Internet to download an old version).

You can also try a third-party upgrade tool:
https://github.com/manticore-projects/H2MigrationTool

> or I will have to change those files and Queries as well?
It depends on their correctness, new versions of H2 are more restrictive 
and many bugs were fixed, there is no bug-for-bug emulation for older 
versions.

> How Session has changed ?
It is not a part of API, applications should never try to use it directly. 
What exactly are you trying to do with it?

> I was lookinginto the code and I saw that the object of SessionLocal in 
the PgServerThread.initDb() is null.
PgServerThread should never be used by applications and librarires. If you 
need to start the PG server to allow connections from ODBC drivers for 
PostgreSQL, you should use
org.h2.tools.Server.createPgServer(optional_arguments).start();
or command-line options.
This server is also started automatically when you start H2 as a service 
without parameters:
java -jar h2-*.jar

> Engine.createSession(ci) is returning null
This method never returns null in H2 itself, it either returns an instance, 
or throws an exception. If you use modified sources, reflection, or some 
code injection and modification libraries everything in possible, but it 
that case it isn't a problem of H2.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/85462e4d-3231-4528-85e0-ac1eaf02c5b0n%40googlegroups.com.

[h2] Re: WriteValue Function replacement

[Evgenij Ryazanov]

New versions of H2 don't have custom data types, they had significant 
design flaws, security problems, missing basic functionality, and it wasn't 
possible to maintain any stable API for them.

New versions of H2 support standard ROW value data type and standard ARRAY 
data type. It is possible to define complex data types with them:
CREATE DOMAIN MY_TYPE AS ROW(NAME CHARACTER VARYING(100), POINTS ROW(X 
INTEGER, Y INTEGER) ARRAY[1000]);
You can also use the non-standard JSON data type.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/c9bc95dd-2752-4977-af94-8a4030563483n%40googlegroups.com.

[h2] Re: WriteValue Function replacement

[Evgenij Ryazanov]

Hello.

No, there are no direct replacements. You cannot rely on internals of H2, 
they aren't expected to be used by applications.

This method was used internally by removed PageStore backend. MVStore 
backend uses ValueDataType instead (and it also isn't a part of API). 
ValueDataType uses different encoding, so it cannot read or write values in 
format used by PageStore.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b3e09598-72a6-4724-b335-765602ba17b3n%40googlegroups.com.

[h2] Re: MV_Store

[Evgenij Ryazanov]

Hello.

Legacy PageStore backend was removed from H2, so you cannot specify 
MV_STORE=FALSE any more. This backend is only available in old unsupported 
versions, it was introduced somewhere in 1.1.* releases and the last 
release with this backend is 1.4.200.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/96ff2095-a7ac-43c3-b6ad-1638fdf8e8c1n%40googlegroups.com.

[h2] Re: LOWER function does not work as expected in v 2.1.214

[Evgenij Ryazanov]

Because when you use the LOWER or UPPER function, its result doesn't have 
CHARACTER(255) data type any more, it has CHARACTER VARYING data type. 
Character string literals also have CHARACTER VARYING data type in H2.
So a comparison between two CHARACTER VARYING values is actually performed 
and this comparison returns FALSE in H2 when strings have different length.

Without this function a comparison between CHARACTER(255) and CHARACTER 
VARYING values is performed and this comparison ignores trailing spaces in 
H2.

LOWER and UPPER functions should return result of the same data type as 
their argument according to the SQL Standard, but H2 currently cannot 
satisfy that requirement, because H2 doesn't have warnings. Some strings in 
some locales have different lengths after conversion to upper or lower 
case. If this length is larger than length of original data type, 
standard-compliant database should truncate the result to the declared 
length and raise a warning. But H2 cannot warn you about truncation and it 
is a bad idea to perform this truncation silently, so it isn't performed at 
all. But to return values longer than argument H2 declares result of these 
functions as CHARACTER VARYING with the maximum length.

Take a look on length and columnDefinition attributes of @Column 
annotation. You can adjust SQL data type produced by JPA with them.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/62d83651-b378-49e9-9aed-e0bf4b3bf52en%40googlegroups.com.

Re: [h2] ALTER VIEW PUBLIC."View1" AS SELECT ....

[Evgenij Ryazanov]

Hello!

The SQL Standard doesn't have any commands for view modification, so the 
only portable way is to drop an old view and create a new one.

In H2 you can use CREATE OR REPLACE VIEW viewName AS …

https://h2database.com/html/commands.html#create_view

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6b7abe0b-8ebd-485a-885a-8b780a226c39n%40googlegroups.com.

[h2] Re: LOWER function does not work as expected in v 2.1.214

[Evgenij Ryazanov]

CHARACTER is a fixed-width data type. Columns of CHARACTER data type always 
have exactly one character. Columns of CHARACTER(255) data type always have 
exactly 255 characters, shorted values are right-padded with spaces. If you 
need to store strings of different length, you should always use CHARACTER 
VARYING / VARCHAR data type instead. If you need to store exactly one 
character, you can use CHARACTER or CHARACTER(1); but don't use 
CHARACTER(255) for this purpose, such choice is obliviously wrong.

More details are described here:
https://github.com/h2database/h2database/issues/3385



-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/04ff2b2c-b29f-4635-8b71-48d46b348dddn%40googlegroups.com.

[h2] Re: LOWER function does not work as expected in v 2.1.214

[Evgenij Ryazanov]

Hello!

You need to provide a complete test case. Something like

CREATE TABLE XXX(XYZ VARCHAR(100), …);
INSERT INTO XXX(XYZ, …) VALUES (…);
SELECT * FROM XXX WHERE LOWER(XYZ) = 'somevalue';

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/31c23582-e45e-4ae6-8d5c-bb6323c431f8n%40googlegroups.com.

[h2] Re: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

[Evgenij Ryazanov]

Hello.

Identity columns don't have any dedicated data types in the SQL Standard 
and it the most of database systems. When you declare an identity column 
you must specify both data type (BIGINT or INTEGER, for example) and 
identity clause (GENERATED BY DEFAULT AS IDENTITY etc.)

Few database systems have special data types with implicit identity 
characteristics due to historic reasons. PostgreSQL has serial data types (
smallserial = serial2, serial = serial4, bigserial = serial8). Normally 
identity columns should be used in modern versions of PostgreSQL instead of 
these legacy types, but at least JDBC drivers have something to report 
here. PgJDBC reports only smallserial, serial, and bigserial data types in 
getTypeInfo() with TRUE in AUTO_INCREMENT column.

H2 doesn't have special types (compatibility modes have some) and returns 
FALSE for all data types.

HSQLDB doesn't have special types, but it returns TRUE for TYNYINT, SMALLINT, 
INTGER, BIGINT, DECIMAL and NUMERIC. These data types may be used for 
identity columns, but an additional clause must be specified in their 
definitions.

Derby doesn't have special types, it returns TRUE for SMALLINT, INTEGER, 
and BIGINT, so situation is the same. It also returns NULL for LOB data 
types, it looks like a bug.

It means some drivers (PgJDBC, H2) assume that TRUE in this column 
indicates a special data type with implicit identity characteristics, some 
other drivers (HSQLDB, Derby) assume that TRUE indicates a data type that 
may be used for an identity column with additional identity generation 
clauses.

Why this deviation exists? In the SQL Standard, INFORMATION_SCHEMA is 
described in a separate book with more than 300 pages and we usually can 
say where it was implemented correctly and where it isn't correct. But JDBC 
metadata is only described in its own Javadoc and these descriptions are 
too brief and obscure. In many cases it isn't possible to determine what 
they exactly mean, so developers of JDBC drivers need to guess or they can 
try to copy behavior of some other driver.

I think we can change our implementation to return TRUE for TINYINT, 
SMALLINT, INTEGER, BIGINT, and NUMERIC data types.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e009e47a-381a-4c49-b909-dc039a7c34a9n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[Evgenij Ryazanov]

I forgot to add TABLE_TYPE alias after CASE … END.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/95761609-b5d4-4ab4-8ae7-5bd80e21c5b7n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[Evgenij Ryazanov]

You can use something like that:

SELECT
TABLE_CATALOG TABLE_CAT,
TABLE_SCHEMA TABLE_SCHEM,
TABLE_NAME,
CASE WHEN TABLE_SCHEMA = 'INFORMATION_SCHEMA' THEN 'SYSTEM TABLE' WHEN 
TABLE_TYPE = 'BASE TABLE' THEN 'TABLE' ELSE TABLE_TYPE END,
REMARKS,
CAST(NULL AS VARCHAR) TYPE_CAT,
CAST(NULL AS VARCHAR) TYPE_SCHEM,
CAST(NULL AS VARCHAR) TYPE_NAME,
CAST(NULL AS VARCHAR) SELF_REFERENCING_COL_NAME,
CAST(NULL AS VARCHAR) REF_GENERATION
FROM INFORMATION_SCHEMA.TABLES;

You need to add WHERE clause when DatabaseMetaData.getTables() was called 
with filtration parameters.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7addbc0a-3f13-498b-a3cb-a99c3195f8b0n%40googlegroups.com.

[h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[Evgenij Ryazanov]

Hello.

JDBC doesn't have any actual requirements for returned table types, the 
provided list is only an example.
H2 returns table types as required by the SQL Standard for 
INFORMATION_SCHEMA.TABLES.TABLE_TYPE column.

But each JDBC driver should return its own table types from 
DatabaseMetaData.getTableTypes() and H2 does it correctly.

I don't know what do you mean by system table and why you need to 
distinguish them from normal tables and views. The INFORMATION_SCHEMA and 
pg_catalog (it exists only when PostgreSQL compatibility mode was enabled) 
normally have only very special virtual tables, but they aren't expected to 
be found in other places.

You can use non-standard DB_OBJECT_SQL function, it definitely returns NULL 
for various special tables.

SELECT TABLE_SCHEMA, TABLE_NAME, DB_OBJECT_SQL('TABLE', TABLE_SCHEMA, 
TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

https://h2database.com/html/functions.html#db_object_sql

These is also non-standard INFORMATION_SCHEMA.TABLES.TABLE_CLASS column, 
but you should understand that names of some implementations may be changed 
at any moment, even it patch release, some implementations may appear only 
under certain conditions and it is possible to add an own implementation of 
a table with a custom table engine. 

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/fa165739-0914-416f-8f17-de35cfd18039n%40googlegroups.com.

[h2] Re: Strange order varchar column

[Evgenij Ryazanov]

Hello.

The default sort order when database collation wasn't specified or was 
specified as OFF is
LUTZ, JOSEF
LUTZ, JOSEF ANTON
LUTZ, JOSEFA
because this is how java.lang.String.compareTo() works.

If database collation was specified, for example, as EN, the sort order is
LUTZ, JOSEF
LUTZ, JOSEFA
LUTZ, JOSEF ANTON
because this is how collators (java.text.Collator) work and strength 
setting doesn't affect this order.

H2 doesn't provide possibility to add own rules to collators, so this 
behavior cannot be currently changed.

H2 also supports collators from third-party ICU4J library, but I never used 
it and I don't know how these collators work.

Don't try to use CHAR / CHARACTER / etc. for variable-length strings, these 
data types can only create additional problems, they are suitable only for 
fixed-length strings.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/693e066f-fd9c-40a6-b325-3620bdc34c77n%40googlegroups.com.