Table of Contents

Datasource Limitations

Velocity strives to provide a consistent cross-platform database interface, but certain databases lack functionality available in others. Rather than restricting Velocity to only the lowest common denominator, we've documented each database's limitations.

This allows you to:

  • Use advanced features when targeting databases that support them
  • Avoid features incompatible with your target databases
  • Make informed decisions about which databases to support in your application

DB2

Data Types:

  • Timestamp: The Velocity Timestamp DataType (which stores timezone information alongside date/time values) is not supported. Using Timestamp will fall back to DateTime, and timezone information will not be accurately stored or retrieved.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like TODAY() or NOW().

Queries:

  • GROUP BY a CASE expression — use the subquery overload. Grouping by a case expression is fully supported and returns the same results as on any other datasource; it just has to be written with the GroupBy overload that takes the subquery, which computes the expression in a derived table and groups by its output column. The single-argument GroupBy repeats the expression in the GROUP BY clause, and DB2 rejects that with SQL0119N: it enforces the strict SQL-92 rule that a GROUP BY expression be provably identical to the selected one, and will not make that guarantee for an expression containing parameter markers, whose values are unknown when the statement is prepared. (Neither GROUP BY by ordinal position nor by select alias is accepted by DB2 as an alternative.) See Conditional Expressions.

Geospatial:

  • Self-intersecting polygons: Cannot be added to DB2, whereas other databases allow adding them and testing validity with ST_IsValid().
  • Interior ring ordering: Polygon interior rings are ordered by an undisclosed DB2 algorithm, not by the order specified in code. This makes ST_InteriorRingN() non-deterministic regarding ring order.

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.

Date/Time Functions:

  • UTC not supported for NOW()/TODAY(): The Velocity functions NOW() and TODAY() in DEFAULT constraints return the current date/time in the DB2 server's local timezone, not UTC. Workaround: Run the DB2 server in UTC timezone.

MySQL

Data Types:

  • Timestamp: Limited support for the Velocity Timestamp DataType. Timestamps are converted to UTC and timezone information is discarded.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like TODAY() or NOW().

Queries:

  • Full outer joins: Not supported. Full outer joins can be simulated using UNION of left and right joins, but there is no direct syntax support.

Oracle

Data Types:

  • Integer types: Oracle uses the NUMBER data type with scale and precision instead of traditional integer types. Velocity maps standard integer types to NUMBER configurations that can hold larger values than typical, ensuring values are stored correctly. However, this means returned values may exceed the original data type's range. Best practice: Use Long for all whole number types when Oracle is in your database mix.
  • Time: Oracle has no native time-of-day type. Velocity maps DataType.Time to INTERVAL DAY(0) TO SECOND, which stores the time as a zero-day interval. Values round-trip correctly, but the underlying Oracle column type differs from all other supported databases.

Constraints:

  • Non-deterministic check constraints: Check constraints cannot contain non-deterministic functions like RANDOM().

Geospatial:

  • Spatial indexes (Oracle 19c): Cannot create spatial indexes on tables or columns using escaped quote-style identifiers. Workaround: Set the Velocity context setting EscapeIdentifiers to false.
Warning

Use this setting with extreme caution. Changing it after schema creation will cause significant issues. This setting helps with Geospatial Indexes if Oracle 19 is being used.

PostgreSQL

Data Types:

  • Timestamp: Not fully supported. Timestamps are converted to UTC with the timezone offset shown relative to the current client session. To maintain the actual timezone, use DateTime DataType and store the timezone in a separate column.

Geospatial:

  • SpatialDWithin is PostgreSQL-only: SpatialDWithin (ST_DWITHIN) has no equivalent on any other supported database, since it depends on PostgreSQL's ability to push a distance threshold through a spatial index rather than computing an exact distance for every row. Calling it against any other connection throws NotSupportedException at query-build time. See Geospatial Support for the portable alternative.

SQL Server

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.

Functions:

  • GREATEST/LEAST require SQL Server 2022: Greatest and Least map to the native GREATEST/LEAST functions, which SQL Server only gained in version 2022 (16.x). Earlier versions will reject the generated SQL. Workaround: express the comparison as a searched Case instead.

SQLite

Schema Operations:

  • Multi-schema support: Velocity supports connecting to multiple schemas (databases in SQLite terminology) simultaneously using ATTACH DATABASE. However, SQLite does not support foreign keys across different schemas—any cross-schema foreign key constraints will fail.

Constraints:

  • Post-creation constraints: Cannot create or drop constraints after table creation. All constraints must be defined when creating the table.

Table Alterations:

  • ALTER TABLE limitations: SQLite has very limited ALTER TABLE support:
    • Cannot change column DataType directly—requires table recreation
    • Cannot add default constraints to existing columns
    • Cannot modify most column properties after creation
    • Workaround: Velocity can recreate tables when necessary, but this is more complex than standard ALTER operations.

Teradata

Queries:

  • GROUP BY a CASE expression — use the subquery overload. Grouping by a case expression is fully supported and returns the same results as on any other datasource; it just has to be written with the GroupBy overload that takes the subquery, which computes the expression in a derived table and groups by its output column. The single-argument GroupBy repeats the expression in the GROUP BY clause, and Teradata rejects that with error 3504, "Selected non-aggregate values must be part of the associated group" — a parameter marker inside a case expression in both clauses defeats its equivalence check, which Teradata documents as a known cause of 3504 for parameterized queries. See Conditional Expressions.

Full-Text Search:

  • Supported with extension. Velocity includes full-text search support for Teradata, but requires the Teradata Full-Text Search (TDFTS) extension to be installed and configured on your Teradata instance. See Teradata Full-Text Search for detailed implementation and usage information.

Date/Time Functions:

  • UTC not supported for NOW()/TODAY(): The Velocity functions NOW() and TODAY() in DEFAULT constraints return the current date/time in the Teradata server's local timezone, not UTC. Workaround: Run the Teradata server in UTC timezone.

Schema Operations:

  • Drop schema: Cannot drop schemas that contain objects. Use EmptySchema first, then drop the schema.

Function Behaviour Differences

Some functions exist on every supported datasource but do not behave identically. Velocity passes these through rather than normalizing them: emulating one database's semantics everywhere would make the function mean something other than what each vendor documents, and a silent divergence is worse than a stated one.

GREATEST and LEAST with NULL arguments

Greatest and Least disagree on what a NULL argument means:

Behaviour Datasources
NULL arguments are ignored, and the remaining values compared PostgreSQL, SQL Server
Any NULL argument makes the whole result NULL Oracle, MySQL, DB2, SQLite, Teradata

This matters most when the result feeds a Case. On the second group a single missing value yields NULL, so no WHEN branch matches and the row silently lands on ELSE.

Workaround: where a missing value has a defined meaning, state it with Coalesce. That produces identical results everywhere:

var counts = raceColumns
    .Select(c => (IElement)new Coalesce([ c, new Literal<int>(0) ]))
    .ToList();

var greatest = new Greatest(counts);

Note also that SQLite has no GREATEST/LEAST at all — Velocity emits its multi-argument scalar MAX/MIN, which is the same operation.