For the record: it's not the Abstract-ness that leads to the index being ignored, it's the fact that the index is not defined in the primary superclass of Test.NewClass1. From secondary superclasses, only parameters, properties and methods are inherited, but not indices. So you can keep Test.NewClass Abstract, but make it inherit from %Persistent itself and then have Test.NewClass1 point to it as its primary superclass. I'll ask the doc team to make this a little clearer, as it's not obvious from this section of the doc nor the one on indices right now.

This said, @Robert Cemper 's suggested use of NoExtent is likely what you wanted to achieve.

Yes, it always behaved like this. Currently, table stats are stored as part of the class definition, based on the idea that they are often kind of static and belong with the application. That holds for certain types of applications, but in many cases it is a little too static and this is especially true for the Message Bank example here, as TuneTable cannot update class definitions for a read-only system class.

We have a project coming up to move the table stats to live with the data rather than keep them in the class definition and hope to bring that to an IRIS release in the course of 2023. In the meantime, the workaround suggested above, marking ENSLIB as writable, running TT and then marking it read-only again is reasonable (though quite a step from an official recommendation). Note that after upgrades, your ENSLIB will be overwritten and your stats will have been lost.

FYI, the last part of this article also touches on this subject

In the case of JDBC, there's also the getGeneratedKeys() method that you may find useful.

We're looking into support for a RETURNING clause for the INSERT command as a possible enhancement (Postgres' flavour of this nonstandard clause is very nice), but don't hold your breath as we have a long list of such candidate enhancements and a much shorter list of people who can work on the surprising amount of places in our codebase that would need to be updated to accommodate such a syntax extension.

@Vitaliy Serdtsev is right in pointing to the resultset's metadata. That's where IRIS SQL registers column datatypes and he already pointed out that this is obviously not impacted by the presence of an ORDER BY. 

The resultset object returns the SQL-side value to ObjectScript, but on that ObjectScript side, the datatype no longer matters as it isn't strongly typed and still 1="1". Therefore, I don't think this constitutes an error.

FWIW, the reason you're seeing this is that due to the ORDER BY clause we're picking up that id value from the index' subscript rather than from the master map.

Where Oracle uses the FROM DUAL pseudo-table, IRIS will just do without a FROM clause altogether for cases where you just want to select a single row of constant expressions. The following statement should work:

INSERT INTO mytable (column1, column2, column_n)
  SELECT expr1, expr2, expr_n UNION ALL
   SELECT expr1, expr2, expr_n;

Oliver already pointed you at the key setting for this, but I thought I'd include a snippet from and link to the corresponding doc as well

If Source has a foreign key public RowID, and you want Destination to have the same foreign key relationship, you must define Destination with the ALLOWIDENTITYINSERT=1 parameter. When defining Destination as a persistent class, specify Parameter ALLOWIDENTITYINSERT=1;. When using CREATE TABLE to define Destination, specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1. If a table is defined as ALLOWIDENTITYINSERT=1, this setting cannot be changed by the SetOption("IdentityInsert") method.

This is expected behaviour. for a SELECT statement, %Execute() (or %ExecDirect()) will set %SQLCODE to 0 if the statement was successfully executed and ready to return rows, or an error code if the execution went wrong. It's through the use of %Next() that it will figure out whether there are any rows left. The first %Next() call on such a resultset will return 0 and at the same time set %SQLCODE to 100. This behaviour is similar and perhaps even more visible in the case of %ROWCOUNT for SELECT statements, which increases with every call to %Next(). See also the docs for %Next() (second bullet)

The simple reason for this is that we're not building the whole result set upfront, but rather "get ready" to iterate through it with %Next() calls, as users may only be interested in the first few rows. Some call this a lazy execution model, but I prefer the term efficient :-). Of course, for DML statements such as UPDATE and DELETE, the %ROWCOUNT is known immediately upon execution, as there is no result set to scan through

A little more documentation on the SQL projection of collection properties here.

Currently the SQLPROJECTION = table option is only available for "array of" properties. In 2022.1 we intend to make this option also available for "list of" properties.

If you're interested, I can dig up a script I wrote a while ago to create a class that projects a read-only view of your "list of" property. No warranty!

The iFind search portal demo includes a simple class query to find similar documents within a single iFind index. It's only pretty basic and somewhat picky (assuming the demo setup), building on the dominance score for each entity, and may not guard against that difference in length issue you're seeing with BM25. There is a similar method in iKnow when your data would already be in an iKnow domain.

There would indeed be value in providing %SIMILARITY support for iFind indexed fields, leveraging the standard/enhanced algorithm on top of word tokens. I'll log that as an enhancement request and we can follow up internally. Obviously, I'm interested in experiences or advice of other DC members here 

IRIS will automatically create an auto-incrementing RowID and leverage it as the IdKey for you, so unless you want anything other than the default, you shouldn't define such a field or index explicitly. 

If all you want is control the name, take a look at the SqlRowIdName class parameter. If you need control over its behaviour, what you've set up is appropriate and you can leverage SqlRowIdPrivate to get rid of the default additional projection of the RowID. However, unless there's a good reason for controlling the IdKey, you should try to avoid overriding it as it may deprive you of certain practical features and efficiencies such as bitmap indices and an extent index.

You can expose this information through setting the IFINDMAPPINGS parameter to 1:

  • [class_name]_[index_name]_WordRec: stores which words appear in each record in this index. See also %iFind.Index.AbstractWordRec.
  • [class_name]_[index_name]_WordSpread: stores the total number of records in which this word appears in this index. See also %iFind.Index.AbstractWordSpread.
  • [class_name]_[index_name]_WordPos stores which word occurs at which position in a record, so it can be joined to the AttributePos table. See also

So by doing a COUNT() on the WordPos table, you should find what you're looking for IFF it corresponds to an actual word. If you're using wildcards, you might combine with %iFind.FindWords() as a TVF, but that'd still be looking for individual words only:

SELECT COUNT(*) 
FROM Test_IFind.IFind_IF_WordPos 
WHERE WordId IN (SELECT WordId FROM %iFind.FindWords('ab*'))

If you want to count any kind of match, your highlight trick is probably the nicest way to get at it.

(as an aside: with the introduction of the %iFind.Index.Minimal index type in 2020.1, which BTW was Eduard's suggestion ;-), it seems the class reference for the IFINDMAPPINGS projections added by the .Basic class but not in .Minimal got lost. We'll fix that shortly!)