Known Issues and Notes

Stored Procedures Do Not Support Common Table Expressions (CTEs)

InterSystems SQL supports both stored procedures and Common Table Expressions (CTEs). However, attempting to create a stored procedure that contains a CTE causes an error that prevents the system from creating the stored procedure. For example, the following example demonstrates a stored procedure that raises and error that prevents its creation:

CREATE PROCEDURE test.sp_test_cte(in inpOwner VARCHAR(128))
   BEGIN
   WITH cte1 AS (SELETE * FROM INFORMATION_SCHEMA.VIEWS)
   SELECT * FROM cte1 WHERE cte1.owner = 'jsmith'
   ;
   END

Partitioned Table Conversion Failures If Partition Key Field Is Not Lowercase

With the experimental table partitioning feature, you can convert an existing nonpartitioned table into a key-partitioned table. However, such a conversion fails if the partition key was created on a field whose name contains uppercase letters. There are no adverse effects on the table when the conversion fails.

Inconsistent Results on Partitioned Tables with a WHERE Clause on ID

With the experimental table partitioning feature, ID values in a key-partitioned table are a composite create by the partition key, such as '20230501000||12' or '1||8||5||87'. Queries that use the ID value in a WHERE or ORDER BY clause can produce inconsistent results. For example, the following query may return no results even when an entry with the specified ID exists:

SELECT * FROM demo.log WHERE ID = '1||9||5||87'

Similiarly, the following example may also return no results:

SELECT * FROM demo.log ORDER BY ID

OR Predicates on Partitioned Table May Return Incorrect Results

In the experimental table partitioned feature, queries against key-partitioned tables that include an OR condition involving multiple partition key fields can return zero results. For example, on a table that is key-partitioned on fields f1 and f2, queries like the following can return no results, even when such rows do exist:

SELECT * FROM demo.t WHERE (f1=1) OR (f2>8)

Interoperability UI Performance Issues

InterSystems IRIS 2025.2 introduced two changes that bring performance improvements to most use cases: smarter table statistics and enabling BiasQueriesAsOutlier by default. However, there are a small subset of queries that can be slower than before. The following components of the Interoperability user interface have queries that can fall into this subset:

• The Production UI

• The Message Viewer

• The Message Bank Message Viewer

• The Event Log Viewer

• The Business Rule Log Viewer

• The Production Monitor

These user interfaces can be slow when all three of the following conditions are true:

1. There are greater than 15 million entries in the Messages or Event Log table for the given namespace.

2. Some business components have significantly more activity than the others.

3. Activity on the business components is not evenly distributed over time (for example, if large batches of messages are processed in bulk or is some business components rarely see activity).

If your Interoperability user interface has significantly slowed upon upgrade to this version, you cam employ either of the following workarounds:

1. Use the Purge Management Data feature to confirm that the Message and Event Log have fewer that 15 million entries. If so, purge old data as needed to bring the counts down.

2. In any namespace, manually set the following global:

   set ^%SYS("sql","sys","rtpc","planCostThreshold") = 10  
   

   

   This value is undefined by default. If you wish to revert this behavior, kill the global subnode as follows:

   kill ^%SYS("sql","sys","rtpc","planCostThreshold")
   

   

LDAP Authorization Always Requires User's Domain

When you use O/S Authentication with LDAP Authorization, you must provide your fully qualified username (for example, EndUser@example.com) to successfully log in, even in cases where you only use a single security domain.

Previously, you only needed to provide a fully qualified username if you used multiple domains, as InterSystems IRIS could infer the domain in single-domain contexts. This behavior will be restored in a future version.