Subquery Behavior Change due to Scalar Subquery Feature
The scalar subquery feature introduced in Ingres 10 can cause certain existing subqueries to return an error.
In previous releases, the following two forms of syntax were equivalent:
SELECT col1_int, col2_float FROM session.vps18_gtt2
WHERE col1_int = (SELECT col1_int FROM session.vps18_gtt1
WHERE (col1_int>=11100 AND col1_int<=11115))
ORDER BY col1_int;
SELECT col1_int, col2_float FROM session.vps18_gtt2
WHERE col1_int IN (SELECT col1_int FROM session.vps18_gtt1
WHERE (col1_int>=11100 AND col1_int<=11115))
ORDER BY col1_int;
With the introduction of scalar subqueries, the first form will fail with E_US1196 (cardinality error) if the subquery returns more than one row. In previous releases, the ANY operator was implied in such scalar subqueries and the cardinality violation was not raised; in Ingres 10, the cardinality violation is flagged by default.
When migrating to Ingres 10, you can use the parameter cardinality_check under the DBMS Server component in CBF to revert to the legacy behavior if your data or applications exhibit this new behavior. You can also control the behavior at the session level with SET [NO]CARDINALITY_CHECK, or change your SQL to use the explicit ANY operator, if appropriate:
SELECT col1_int, col2_float FROM session.vps18_gtt2
WHERE col1_int = ANY(SELECT col1_int FROM session.vps18_gtt1
WHERE (col1_int>=11100 AND col1_int<=11115))
ORDER BY col1_int;
Last modified date: 01/30/2023