Question
· May 27

SQL Query returns <UNDEFINED> with OFFSET

I am testing vectorsearch, while doing so I am trying to paginate my resultset for a "next page" function to give me the first, second, third 15 entries within a table. 

For this I have two embedding classes. One with a HNSW Index (vectornomicembedtextlatest) , and one without (vectornomicembedtexttest).

Calling SELECT ID,PRIMKEY FROM SQLUser.vectornomicembedtexttest LIMIT 5 OFFSET 1 works fine with the first entry having the rowID of 486448. (I deleted old entries in the beginning and reused the table)

SELECT ID,PRIMKEY FROM SQLUser.vectornomicembedtexttest ORDER BY PRIMKEY DESC LIMIT 5 OFFSET 1  -> This works fine.

Calling my Vectorsearch SQL command however gives an <UNDEFINED> error: 
SELECT Primkey FROM SQLUser.vectornomicembedtexttest WHERE Mandant = '0' AND Klasse = 'CRMSHOPARTIKEL' LIMIT 3 OFFSET 1 ORDER BY VECTOR_DOT_PRODUCT(Embedding, TO_VECTOR('-.3707275390625,-.092041015625,-3.88720703125,... ,.298583984375', DOUBLE, 768)) DESC 

SQLCODE 400
[%msg: <ObjectScript-Fehler: <UNDEFINED>%0Afirst+3^%sqlcq.PHARMBUY.cls113.HWwDnTI0TjK.1>]

If I do the same for my indexed table everything works fine
SELECT Primkey FROM SQLUser.vectornomicembedtextlatest WHERE Mandant = '0' AND Klasse = 'CRMSHOPARTIKEL' LIMIT 3 OFFSET 1 ORDER BY VECTOR_DOT_PRODUCT(Embedding, TO_VECTOR('-.3707275390625,-.092041015625,-3.88720703125,... ,.298583984375', DOUBLE, 768)) DESC 

This is the Index
Index HNSWIndex On (Embedding) As %SQL.Index.HNSW(Distance = "DotProduct") [ SqlName = HNSWIndex, Type = index ];

However vectornomicembedtextlatest also starts with a rowID of 1 instead of 486448 because I haven't deleted any entries. Not sure if that is the problem here. 

Product version: IRIS 2025.1
$ZV: IRIS for Windows (x86-64) 2025.1 (Build 223U) Tue Mar 11 2025 18:14:42 EDT
Discussion (0)1
Log in or sign up to continue