Database Variances

Overview

In Innoslate v4.15 and later, search functionality across PostgreSQL 18 and Microsoft SQL Server 2022 is functionally unified. Prior releases exhibited dialect variances between database engines for full-text search, stop words, and wildcard evaluation. These behaviors now produce identical results across both supported engines.

Core Query Parity (PostgreSQL 18 & SQL Server 2022)

Across all qualified search fields (Name:, Number:, Description:, and custom schema attributes), both database engines enforce identical rules:

• Whole-Value Anchoring: Qualified searches compare against the complete field value. An unqualified exact phrase match (such as Name:Innoslate or Description:Innoslate) matches only when the stored field equals that exact string.

• Wildcard Semantics:

  • Asterisk (*) and percent (%) function interchangeably as multi-character wildcards.
  • Underscore (_) functions as a single-character wildcard (including punctuation like .).
  • Substring matches require leading and trailing wildcards (e.g., *MBSE* or %MBSE%).
• Stripped Markup for Descriptions: Rich-text descriptions evaluate against raw text (description_text). Search terms match rendered content rather than HTML tags.
• Stop Words: Field-specific qualified searches (Name:, Number:, Description:) execute directly against database columns and bypass stop-word filters entirely. Searching Name:is is treated as a literal search for the exact value "is".

Index Timing Variance: Asynchronous Crawl (SQL Server Only)

The only functional difference between PostgreSQL and Microsoft SQL Server is the index update mechanism for unqualified global full-text queries.

Operational Impact on SQL Server: Wildcard-free, unqualified searches rely on SQL Server's full-text catalog. When creating or modifying entities in SQL Server environments, these specific queries will not return newly saved entities until the asynchronous crawl cycle completes. If instant query evaluation is required immediately after saving, use qualified searches (Name:, Number:, Description:) or include wildcard syntax.

Verified Search Behavior Matrix (93 Test Suite)

All 93 standard search permutations yield matching results on PostgreSQL 18 and Microsoft SQL Server 2022:

• Entity A Test Value: Name / Number / Description = "Innoslate is a MBSE tool" (Rows 1–67)
• Entity B Test Value: Number = "I.1.12" (Rows 68–93)

1. Unqualified Searches (Rows 1–17)

Searches across Name, Number, and Description simultaneously.

2. Qualified Name: Searches (Rows 18–34)

3. Qualified Number: Searches (Rows 35–51)

4. Qualified Description: Searches (Rows 52–67)

Evaluates against description_text with HTML markup removed.

5. Hierarchical Number Matching: I.1.12 (Rows 68–93)

Overview

Innoslate is compatible with both SQL and PostgreSQL databases.

While Innoslate supports multiple database backends, each handles full-text search and stop words slightly differently. The table below highlights these behavioral differences.

Search Behavior Example

To demonstrate these differences, we created four test entities:

Entity 1: Name = "Innoslate is an MBSE tool"
Entity 2: Number = "Innoslate is an MBSE tool"
Entity 3: Description = "Innoslate is an MBSE tool"
Entity 4: Number = I.1.12

Table illustrating search results across databases follows below