Как создать столбец с вычисляемым значением в HSQLDB?
В jUnit тестах используется HSQLDB версии 2.7.2 для создания DB в памяти (in-memory DB).
Для создания таблицы используется SQL скрипт, примерное содержимое которого приведено ниже. Проблема заключается в том, что определённый столбец использует вычисляемое значение (computed) используя значения нескольких других столбцов, которые могут содержать NULL значения. Это вычисляемое значение используется как некий ключ (даже не спрашивайте меня почему так - кровавый энтерпрайз) и использует разделитель между значениями. Ожидаемое/требуемое поведение таково, что при NULL-значении разделитель всё равно должен присутствовать.
-- SQL скрипт src/test/resources/t.sql
CREATE TABLE t(
id int GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL,
first_name varchar(50) NULL,
middle_name varchar(50) NULL,
last_name varchar(50) NULL,
full_name varchar(150) GENERATED ALWAYS AS (first_name || ',' || middle_name || ',' || last_name)
-- так тоже пробовал
-- full_name varchar(150) GENERATED ALWAYS AS (CONCAT(first_name, ',', middle_name, ',', last_name))
-- full_name varchar(150) GENERATED ALWAYS AS (CONCAT_WS(first_name, ',', middle_name, ',', last_name))
);
COMMIT;
INSERT INTO t (first_name, middle_name, last_name) VALUES ('a', NULL, 'b');
INSERT INTO t (first_name, middle_name, last_name) VALUES ('c', 'd', 'e');
INSERT INTO t (first_name, middle_name, last_name) VALUES ('f', NULL, 'g');
COMMIT;
Пример создания вычисляемого столбца приведён в документации.
Также пробовал использовать CONCAT и дополнительно указывал в строке подключения параметр sql.concat_nulls=false и sql.concat_nulls=true, так как согласно документации при наличии NULL хотя бы у одного конкатенируемого значения CONCAT вернёт NULL, но никакого эффекта этот параметр строки подключения не принёс. В приведённом примере вычисляемые значения (full_name) будут a,b, c,d,e и ``, так как ни одно из используемых значений не содержит NULL, а необходимые значения должны быть a,,b, c,d,e и f,,g.
Другими словами, оба приведённых варианта создания столбца с вычисляемым значением не работают с NULL.
Каким образом создать вычисляемый столбец используя значения других столбцов содержащих NULL в HSQLDB?
Использование CONCAT_WS
Akina предложил использовать CONCAT_WS, и да это работает, но в этом случае NULL-значениях игнорируются и вместо a,,b или f,,g мы получаем a,b и f,g соответственно (вне зависимости от параметра sql.concat_nulls в строке подключения).
Примерное использование
<!-- фрагмент pom.xml -->
<!-- https://mvnrepository.com/artifact/org.hsqldb/hsqldb -->
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>${hsqldb}</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.hsqldb/sqltool -->
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>sqltool</artifactId>
<version>${hsqldb}</version>
<scope>test</scope>
</dependency>
// фрагмент jUnit теста
static Connection getConnection() throws SQLException
{
return DriverManager.getConnection("jdbc:hsqldb:mem:testdb;"
+ "sql.lowercase_ident=true;"
+ "sql.enforce_names=true;"
+ "sql.enforce_refs=true;"
+ "sql.truncate_trailing=false;"
+ "get_column_name=true"
+ "shutdown=false"
+ "sql.syntax_mss=true"
+ "hsqldb.applog=3"
+ "hsqldb.sqllog=3"
// хоть true, хоть false - одно и то-же поведение
// + "sql.concat_nulls=true"
+ "sql.concat_nulls=false", "SA", "");
}
@Test
void computedColumnTest() throws SQLException, IOException, SqlToolError
{
try (Connection _con = getConnection())
{
java.sql.Statement _stmt = _con.createStatement();
// так выбрасывает исключение
// java.sql.SQLSyntaxErrorException: unexpected token: CONCAT
// _stmt.execute("SET DATABASE SQL SYNTAX CONCAT NULLS FALSE");
var _file = new File(this.getClass().getResource( "/t.sql" ).getFile());
var _sqlFile = new SqlFile(_file);
_sqlFile.setConnection( _con );
_sqlFile.execute();
String _sql = "SELECT id, first_name, middle_name, last_name, full_name FROM t";
ResultSet _resultSet = _stmt.executeQuery( _sql );
while(_resultSet.next())
{
int _id = _resultSet.getInt( 1 );
String _firstName = _resultSet.getString( 2 );
String _middleName = _resultSet.getString( 3 );
String _lastName = _resultSet.getString( 4 );
String _fullName = _resultSet.getString( 5 );
}
_stmt.execute( "SHUTDOWN" );
}
}