[h2] Can't create full text index (H2: v1.4.200 , lucene: v7.7.2 or v8.2.0)

[Bengui Xie]

Run scripts below in H2 Console:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR 
"org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR, 
LAST_NAME VARCHAR);
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');
INSERT INTO TEST VALUES(1, 'John', 'Wayne');
INSERT INTO TEST VALUES(2, 'Elton', 'John');
SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
CALL FTL_DROP_ALL();

Result:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
Update count: 0
(0 ms)

CALL FTL_INIT();
PUBLIC.FTL_INIT()   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
*null*(1 row, 2 ms)

DROP TABLE IF EXISTS TEST;
Update count: 0
(2 ms)

CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR,
LAST_NAME VARCHAR);
Update count: 0
(1 ms)

CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');
Unique index or primary key violation: "FTL.PRIMARY_KEY_9 ON 
FTL.INDEXES(SCHEMA, ""TABLE"") VALUES 1"; SQL statement:
INSERT INTO FTL.INDEXES(SCHEMA, `TABLE`, COLUMNS) VALUES(?, ?, ?) 
[23505-200] 
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
 23505/23505 (Help) 
<https://h2database.com/javadoc/org/h2/api/ErrorCode.html#c23505>


INSERT INTO TEST VALUES(1, 'John', 'Wayne');
Update count: 1
(0 ms)

INSERT INTO TEST VALUES(2, 'Elton', 'John');
Update count: 1
(1 ms)

SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SCHEMA   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
TABLE   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
COLUMNS   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
KEYS   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
SCORE   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>(no
 
rows, 221 ms)

SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
SCHEMA   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
TABLE   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
COLUMNS   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
KEYS   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
SCORE   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>(no
 
rows, 1 ms)

CALL FTL_DROP_ALL();
PUBLIC.FTL_DROP_ALL()   
<http://10.0.75.1:8082/query.do?jsessionid=c2c4b061998d61051c68ec4841e4bc86#>
*null*(1 row, 43 ms)

-- 
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/487e0f45-fb70-4ad8-b0c8-14d057d47180%40googlegroups.com.