What has option 'string_rtruncation' to do with creating a materialized view?

[reimer_pods]

I'm trying to execute the following statement in ISQL :

CREATE MATERIALIZED VIEW VWBWERTELISTE AS
SELECT WB_WERTEBEREICH, WB_TYP, WB_FORMAT, WL_RANG, WL_ANZEIGE, WL_WERT
FROM WERTEBEREICH WB JOIN WERTELISTE WL ON WB.WB_ID = WL.WB_ID

this error message pops up

Could not execute statement.
Cannot create a materialized view because option 'string_rtruncation'
has an inappropriate setting
SQLCODE=-1051, ODBC 3 State="HY000"
Line 1, column 1

If I set the option to 'On', create the mat.view and set the option back to the old value ('Off), everything is ok and works as supposed to.

I couldn't find anything related in the documentation. It might be a bug, but it's at least an unexpected behavior. Any possible explanations?

[johnsmirnios · Accepted solution]

See http://dcx.sybase.com/index.html#1201/en/dbusage/workingwdb-s-3924160.html

The results of a SELECT statement (or VIEW definition) and the errors reported by it depend on various user options. To use a materialized view, the options in effect at the time the materialized view is used must match the options in effect when it was materialized otherwise it is impossible to know whether the values in the materialized view are meaningful & correct relative to the current option settings. To address that issue, certain option settings are either mandated or stored with the materialized view definition itself.

[Breck_Carter · Accepted solution]

The requirement for some "stored" settings (date_format etc) to match in value is easy to understand and accept.

However, the "mandated" settings (string_rtruncation=On etc) are not so easy to understand... do you know why these settings are mandated?

[reimer_pods · Accepted solution]

Yes, that's exactly what I'd like to know.

[VolkerBarth · Accepted solution]

If the question is "Why do they have a mandatory value instead of having to match in value?", then that's my question, too.

In case those non-mandatory options do not match: Am I right that this prevents the mat. view from being used in optimization (i.e. "in the background") but still does allow it to query the mat. view directly?

[reimer_pods · Accepted solution]

In my case I could select from the mat. view although the option value ist different from the value at creation time. So I'd say that can be answered with "Yes".

[VolkerBarth · Accepted solution]

That makes sense to me: If you select directly from the mat. view, then it's obviously your responsibility to know the particular circumstances. - If the optimizer has to evaluate on view matching, it must decide whether the view's result would semantically fit to the given query...

[johnsmirnios · Accepted solution]

The person with the definitive answer is out of the office for a week or so. From talking with other query-processing engineers, it seems that the mandated options are essentially ones that we wish really didn't exist and they only exist today to mimic old behaviour for compatibility reasons. Settings other than the recommended settings are discouraged even outside the scope of materialized views. The mandated string_rtruncation setting, for example, is ANSI standard behaviour. Other options such as ansinull=off are, well, "evil" and may cause significant complexities in verifying correctness when using materialized views.

I believe you can always reference a materialized view directly; however, it will only be substituted automatically by the optimizer if the current options match the mandated/stored options.

[VolkerBarth]

I can't explain why this option is needed, but the docs do tell that it is needed, like some others:

The following database options must have the specified settings when a materialized view is created; otherwise, an error is returned. These database option values are also required for the view to be used by the optimizer:

ansinull=On
conversion_error=On
divide_by_zero_error=On
sort_collation=Internal
string_rtruncation=On

If you do change the setting back to "invalid" values, the view might not be used in optimization, and the system procedure sa_materialized_view_info might tell you why - cf. the "AvailForOptimization" column.