I9996 value limits it fails oracle (#10027)

* Creating OR predicate to reduce length of string literals CHANGELOG_BEGIN CHANGELOG_END * removing unnecessary thrown exception CHANGELOG_BEGIN CHANGELOG_END * switching to camel case for constants CHANGELOG_BEGIN CHANGELOG_END * run format CHANGELOG_BEGIN CHANGELOG_END * remove tolist conversion CHANGELOG_BEGIN CHANGELOG_END * scalafmt Co-authored-by: Brian Healey <brian.healey@digitalasset.com>
digital-asset · Jun 21, 2021 · 828827b · 828827b
1 parent a790958
commit 828827b
Showing 1 changed file with 25 additions and 2 deletions.
diff --git a/...r/participant-integration-api/src/main/scala/platform/store/dao/events/SqlFunctions.scala b/...r/participant-integration-api/src/main/scala/platform/store/dao/events/SqlFunctions.scala
@@ -62,8 +62,31 @@ private[dao] object SqlFunctions {
   object OracleSqlFunctions extends SqlFunctions {
     // TODO https://github.com/digital-asset/daml/issues/9493
     // This is likely extremely inefficient due to the multiple full tablescans on unindexed varray column
-    override def arrayIntersectionWhereClause(arrayColumn: String, parties: Set[Party]): String =
-      s"""JSON_EXISTS($arrayColumn, '$$[*]?(@ in ("${parties.mkString("""", """")}"))')"""
+    override def arrayIntersectionWhereClause(arrayColumn: String, parties: Set[Party]): String = {
+      val NumCharsBetweenParties = 3
+      val NumExtraChars = 20
+      val OracleMaxStringLiteralLength = 4000
+
+      val groupedParties =
+        parties.foldLeft((List.empty[List[String]], 0))({ case ((prev, currentLength), party) =>
+          if (
+            currentLength + party.length + NumCharsBetweenParties > OracleMaxStringLiteralLength
+          ) {
+            (List(party) :: prev, party.length + NumExtraChars)
+          } else {
+            prev match {
+              case h :: tail =>
+                ((party :: h) :: tail, currentLength + party.length + NumCharsBetweenParties)
+              case Nil => (List(party) :: Nil, party.length + NumExtraChars)
+            }
+          }
+        })
+      "(" + groupedParties._1
+        .map { listOfParties =>
+          s"""JSON_EXISTS($arrayColumn, '$$[*]?(@ in ("${listOfParties.mkString("""","""")}"))')"""
+        }
+        .mkString(" OR ") + ")"
+    }
 
     override def arrayIntersectionValues(arrayColumn: String, parties: Set[Party]): String =
       s"""(select json_arrayagg(value) from (select value