Vector and Ingres
Compatibility with Ingres Database
Vector is built upon an Ingres framework and uses many Ingres facilities and utilities.
Note: Vector does not necessarily support all the functionality described in the Ingres documentation. Features not listed or described in the Vector documentation cannot be assumed to be supported or work as described in the Ingres documentation.
Incompatibilities with Ingres Database
The following sections describe some of the major restrictions and incompatibilities between Vector and traditional Ingres.
SQL Cursors
Vector supports read-only scrollable cursors. Cursors on Vector queries cannot be used for update. Only one cursor can be open per connection.
Some Subqueries Do Not Work
Some specific complex forms of subqueries that are supported in traditional Ingres are not supported. Subqueries that do not successfully flatten and compile to a valid Vector syntax produce the E_OP08C2 error. Try to rewrite these queries.
Scalar Subquery Cardinality Check
Vector does not perform a cardinality check on scalar subqueries. Make sure the scalar subquery returns no more than one row.
Cross-system Queries
SELECT queries that access both Vector and traditional Ingres tables cannot execute. To cross join data in traditional Ingres and Vector you must move the data either into traditional Ingres or into Vector using INSERT…SELECT or CREATE TABLE AS SELECT.
SQL Data Type Incompatibilities
The following incompatibilities between Vector and traditional Ingres are known:
• Vector does not check for overflow on operations on floating point types. (Operations on integer types and decimal types are checked for overflow.)
• TIMESTAMP and INTERVAL DAY TO SECOND can only store 6 decimal places (fractions of seconds), compared to 9 in traditional Ingres.
• Vector supports only the YYYY-MM-DD date format and the YYYY-MM-DD hh:mm:ss.[fffff] timestamp format in SQL. (The vwload utility, however, supports all date formats supported by traditional Ingres.) Use DATE_FORMAT() or TO_CHAR() to change the display of date and time values; use STR_TO_DATE() or TO_DATE() or TO_TIMESTAMP() to convert strings into date and time values.
• Conversion from floating point types to character types can yield slightly different results than in traditional Ingres.
• The MONEY currency symbol is fixed to '$'.
• The ASCII null character ('\0\') is not supported in char and varchar data types.
SQL Function Incompatibilities
The following restrictions or incompatibilities between Vector and traditional Ingres are known:
• Vector supports constant LIKE patterns only. The LIKE predicate does not support a range of characters (separated by a dash within escaped brackets).
• The BEGINNING, CONTAINING, and ENDING predicates do not support the ESCAPE clause.
• The HASH function returns different results in Vector than in traditional Ingres due to a different hash computation algorithm.
• Using the RANDOM function after issuing SET RANDOM_SEED will not return the same results for Vector and Ingres tables. Furthermore, it may not return the same results for every Vector query.
Differences in Constraint Checking
Constraint checking in Vector differs from traditional Ingres in the following ways:
• When loading data with COPY, if a constraint violation is detected, the entire dataset is rejected.
• When loading data with COPY, Vector detects foreign key (referential) constraint violations, unlike traditional Ingres.
• Vector does not handle cyclic constraints.