I would suspect that since you're asking the FTL_SEARCH_DATA method to
do the pagination for you, that you are at the whim of whatever the
default ordering sematics are for that callout. Not being familiar
with the method myself, I would suspect it's either:
1) returning the 5 most relevant hits (think of it as an implicit
ordering by the search score), or
2) returning 5 hits that it happened to have at the beginning of the
result list (no explicitly ordering at all)
Considering this is Lucene, I'm more inclined to believe it's probably
#1 but, in any event, if you want the first 5 results that match your
search ordered by the name, I would try:
SELECT T.*
FROM FTL_SEARCH_DATA('a*', 0, 0) FT, TEST_DB T
WHERE FT.TABLE='TEST_DB' AND T.ID=FT.KEYS[0]
ORDER BY T.WORD
LIMIT 5
OFFSET 0
So, you're getting all of the results from the search (by passing 0,0
to FTL_SEARCH_DATA), and then explicitly applying your preferred
pagination/sorting after the key results have been joined to your
table ids.
On Oct 3, 6:22 am, Buzz <[email protected]> wrote:
> Hi
>
> I'm not too sure whether this problem is due to my inexperience with
> lucene or whether there is an issue with H2.
>
> I have a simple create table, insert strings, search for words with
> wildcard.
>
> The data:
> a
> ab
> abc
> abcd
> abcde
> abb
> acc
> add
> aee
>
> If I do a search for 'ab*' (limit 5) then I get ab .. abb
> If I do a search for 'ac*' (limit 5) then I get acc
>
> However, if I do a search for 'a*' (limit 5) then I get ab .. abb ->
> ie no 'a'
>
> Is my seach expression wrong?
>
> Nick
>
> Class.forName("org.h2.Driver");
>
> Connection conn = DriverManager.getConnection
> ("jdbc:h2:file:db_test/db_test", "sa", "");
> Statement stat = conn.createStatement();
>
> stat.execute("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
> \"org.h2.fulltext.FullTextLucene.init\"");
> stat.execute("CALL FTL_INIT()");
> stat.execute("CREATE TABLE TEST_DB(ID INT PRIMARY KEY
> AUTO_INCREMENT, KEY INT, WORD VARCHAR, TYPE CHAR, DESC VARCHAR)");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('a')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('ab')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('abc')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('abcd')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('abcde')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('abb')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('acc')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('add')");
> stat.execute("INSERT INTO TEST_DB (WORD) VALUES('aee')");
> stat.execute("CALL FTL_CREATE_INDEX('PUBLIC', 'TEST_DB',
> NULL)");
>
> ResultSet rs = stat.executeQuery("SELECT T.* FROM
> FTL_SEARCH_DATA('a*', 5, 0) FT, TEST_DB T WHERE FT.TABLE='TEST_DB' AND
> T.ID=FT.KEYS[0]");
>
> while (rs.next()) {
> String word = rs.getString("WORD");
> System.out.println("word " + word);
> }
>
> conn.close();
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
