Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere 11 - Changes and Upgrading » What's new in version 11.0.1


SQL Anywhere behavior changes and deprecated features

Following is a list of changes to SQL Anywhere databases and database servers introduced in version 11.0.1, grouped by category.

Behavior changes
  • Full text searching   The following behavior changes have been made to full text searching:

    • Operator precedence is now applied   Previously, no precedence was applied to operators in a query string. Now, the following operator precedence is applied:

      • NEAR, FUZZY operators
      • AND NOT operator
      • AND operator
      • OR operator

      See Operator precedence in a CONTAINS search condition.

    • NEAR clause arguments must be terms or prefix terms   When you perform a proximity search, the arguments to the NEAR clause must be terms or prefix terms. See CONTAINS search condition, and Proximity searching.

    • Use of hyphen and AND NOT clause   Within a phrase, a hyphen is treated as a term breaker, not a special character. Outside of a phrase, the treatment of a hyphen depends on the syntax surrounding the hyphen. See Allowed syntax for hyphen (-), and Using the AND NOT operator in full text searches.

    • Use of asterisk and prefix searching   When performing a prefix search, the asterisk must be appended to a term and followed immediately by a space, or by the end of query string, or by one of the allowed special characters. See Allowed syntax for asterisk (*), and Prefix searching.

    • Creating a duplicate text index now returns an error   You can no longer create duplicate text indexes. A text index is considered a duplicate if the following settings are identical to those of an existing text index:

      • the base table being referenced

      • the columns to be indexed (order does not matter)

      • the settings for the configuration object used (TERM BREAKER, MINIMUM TERM LENGTH, MAXIMUM TERM LENGTH, STOPLIST, collation information)

      Duplicate text indexes created before SQL Anywhere 11.0.1 can remain in the database and do not cause errors when started with a 11.0.1 database server. However, if a database that contains duplicate text indexes is reloaded on version 11.0.1 or later, an error is returned.

      To identify duplicate text indexes in an existing database, execute the following query:

      SELECT LIST( i.index_name )
          JOIN SYS.SYSTEXTIDX t ON i.object_id = t.index_id AND t.sequence = 1
          JOIN SYS.SYSTEXTCONFIG F ON F.object_id = t.text_config
          JOIN ( 
      	SELECT table_id, index_id, LIST( column_id, ', ' ORDER BY column_id ) col_id
      	GROUP BY table_id, index_id) x 
      	ON x.table_id = i.table_id AND x.index_id = i.index_id
      WHERE i.index_category=4
      GROUP BY i.table_id, f.term_breaker, f.min_term_length, f.max_term_length, 
          f.collation, ISNULL( f.char_stoplist, '-' ), 
          ISNULL( f.nchar_stoplist, '-' ), x.col_id
      HAVING count(*) > 1

      This query works only if the strings representing STOPLIST are exactly the same or if NO STOPLIST is specified. For example, stoplists 'a b c' and 'a - b c' are not considered the same stoplist by this query, but would be considered the same during a check for duplicates during text index creation.

  • Regular expressions   Changes have been made to the behavior for the SIMILAR TO and REGEXP search conditions, and the REGEXP_SUBSTR function. The overall intention of the changes is to continue to have SIMILAR TO be consistent with the ANSI/SQL standard, while making the behavior of REGEXP and REGEXP_SUBSTR consistent with Perl.

    • Database collations and matching   Previously, REGEXP and REGEXP_SUBSTR determined if a literal or character class range in the pattern matched the string by using the collation equivalence and sort order. Now, REGEXP and REGEXP_SUBSTR use binary comparisons of code point values for matching and evaluation of ranges. The change was made to make the behavior consistent with Perl 5.0.

      SIMILAR TO still uses the database collation for matching and range evaluation. See LIKE, REGEXP, and SIMILAR TO search conditions.

    • Database case sensitivity and [[:upper:]] and [[:lower:]] sub-character classes   SIMILAR TO and REGEXP [[:upper:]] and [[:lower:]] sub-character classes were case-insensitive on a case-insensitive database. This has been changed so that [[:upper:]] only matches upper case characters and [[:lower:]] only matches lower case characters, regardless of the database case sensitivity.

    • Treatment of caret (^), underscore (_), and percent sign (%) as metacharacters   The following table explains the previous and new treatment of these characters as metacharacters:

      Character Previous behavior New behavior
      _ (underscore) For SIMILAR TO, REGEXP, and REGEXP_SUBSTR, an underscore was treated as a metacharacter: it matched any single character.

      For SIMILAR TO, an underscore is treated as a metacharacter: it matches any single character.

      For REGEXP and REGEXP_SUBSTR, an underscore is not treated as a metacharacter. Instead, REGEXP, and REGEXP_SUBSTR use a period (.) to match any single character.

      % For SIMILAR TO, REGEXP, and REGEXP_SUBSTR, a percent sign was treated as a metacharacter: it matched any number of any characters.

      For SIMILAR TO, a percent sign is treated as a metacharacter: it matches any number of any characters.

      For REGEXP and REGEXP_SUBSTR, a percent sign is not treated as a metacharacter. Instead, REGEXP, and REGEXP_SUBSTR use dot-asterisk (.*) to match any number of any characters.

      ^ For SIMILAR TO, REGEXP, and REGEXP_SUBSTR, a caret inside a character class was treated as a negation or subtraction character for anything to the right of it: it was interpreted as NOT matches.

      For SIMILAR TO, a caret is treated as a negation or subtraction character for characters to the right of it. For example, SIMILAR TO [a-d^c] matches a, b, d, but not c.

      For REGEXP and REGEXP_SUBSTR, the caret is only treated as a metacharacter if it is in the first position inside a character class: it is interpreted as a negation of the character class. For example, REGEXP [^abc] matches any single character that is not a, b, or c, while REGEXP [a-d^c] matches a, b, c, d, and ^.

  • Mac OS X no longer requires dbmodenv   In previous releases, to use the graphical administration tools on Mac OS X, the user's $HOME/.MacOSX/environment.plist file had to have the SQL Anywhere binary and library location added to PATH and DYLD_LIBRARY_PATH. You could do this using the dbmodenv tool. SQL Anywhere no longer depends on the settings in $HOME/.MacOSX/environment.plist, and you no longer need to run dbmodenv or log out and log in again after installing SQL Anywhere.

  • Default changed for null values returned from the dbisqlc OUTPUT statement   In previous releases, if you used the OUTPUT statement from dbisqlc, the statement returned the value (NULL) for null values. Now, the statement returns an empty string by default for null values. You can change the way NULL values are exported by setting the output_nulls option. See output_nulls option [Interactive SQL].

  • Endian support   After upgrading, pre-11.0.1 text indexes created on a big-endian computer need to be truncated and refreshed (for MANUAL REFRESH and AUTO REFRESH text indexes) or recreated (for IMMEDIATE REFRESH indexes).

  • Administration tools on Mac OS X   On Mac OS X, the SQL Anywhere administration tools now use the 64-bit JDK 1.6. The administration tools only run on Intel Macintoshes with 64-bit processors supported by the Apple JDK 1.6 (Mac OS X 10.5.2 or later). If you are deploying the administration tools for Mac OS X, the native libraries are located in $SQLANY11/System/lib64.v. See Deploying administration tools on Linux, Solaris, and Mac OS X.

  • New default size for chunked transfer-coding for HTTP clients   Previously, if an HTTP client sent data greater than 2048 bytes, chunked transfer-coding was attempted by default (or if the user specifies CREATE PROCEDURE ... SET 'HTTP(CH=auto)'. The default size has been changed from 2048 to 8196 bytes. Also, a new status, 411 Length Required, has been added to the criteria for re-issuing the request without using chunked transfer-coding. See CREATE PROCEDURE statement (web services).

Deprecated and discontinued features
  • COMMENT ON EXTERNAL ENVIRONMENT OBJECT object-name   The syntax has been changed to COMMENT ON EXTERNAL OBJECT object-name. Currently, the old syntax is still accepted but may not be supported in a future release. See COMMENT statement.

  • ansi_substring option support   The ansi_substring option was deprecated in version 11.0.0, but is now supported for version 11.0.1. See ansi_substring option [compatibility].