Question on conversion_error option

#11

Since data exceptions are run-time errors, differences in execution
plans can yield different results.

Given the offending query

FROM fmds.FMMC_EMPLOYEES E
INNER JOIN fmds.INDIRECT_LABOR_HOURS I
ON E.Facility = I.WorkDate AND E.Labor_Category =
I.LaborCategory
WHERE (((E.Facility = 56) AND (Is.WorkDate BETWEEN '2009-08-01' AND
'2009-08-05')))

what is the size of the result set if the query is modified to omit the
integer-date comparison? That is,

FROM fmds.FMMC_EMPLOYEES E
INNER JOIN fmds.INDIRECT_LABOR_HOURS I
ON E.Labor_Category = I.LaborCategory
WHERE (((E.Facility = 56) AND (Is.WorkDate BETWEEN '2009-08-01' AND
'2009-08-05')))

Glenn

Wing Dinanto wrote:

Quote:

Glen,

Yes the databases on the two machine are identical. In
fact, to make sure this identical, we just copy the database
that shown the conversion error on the first machine to the
other machine. The database has PUBLIC option
'conversion_error' turning ON.

We also noticed from the 'Connected Users' tab that the
server open two more connection id and try to fetching the
result set and we could not disconnect the connection from
'Connected Users' tab.

Please advise if you have any idea where we should looking
for.

Wing Dinanto

OK - are the databases on the two machine identical in
content?

Glenn

Rachan Terrell wrote:
Glenn,

Thank you very much pointing this out and providing us
with alternative for checking conversion_error. However
, we do not use SET TEMPORARY OPTION and we did verify
this with:

select * from sa_conn_properties() where Propname like
'%conversion_error%'
and all the connection have conversion_error set to 'ON'

Best regards,

Rachan Terrell

"Glenn Paulley [Sybase iAnywhere]"
paulley (AT) ianywhere (DOT) com> wrote in message
news:4a9ece06$1 (AT) forums-1-dub (DOT) .. >> Temporarily setting the
conversion_error option, ie
SET TEMPORARY OPTION "DBA".conversion_error = 'OFF'

will not show up in the SYSOPTIONS catalog table - the
value is retained >> in memory for that connection only,
and will disappear upon disconnect. >> The catalog table
contains only permanent settings.
Instead, you can use

select * from sa_conn_properties() where Propname like
'%conv%'
to determine what the actual property is at runtime for
this connection. >> Perhaps that will show a difference
in settings.
Glenn

Rachan Terrell wrote:
Glenn,

As I mention earlier that we verified that
PUBLIC.conversion_error is >>> 'ON' by executing this
TSQL:
select * from sys.sysoptions where "option" like
'%conversion_error%'
We got:

user_name,option,setting
'PUBLIC','conversion_error','*ON*'

When we executed the statement that contains the join
between integer >>> and date database the session
hung...but when we set >>> PUBLIC.conversion_error is
'OFF' and re-execute the TSQL the session did >>> NOT
hung and no warning or any error occurred. We anticipate
the >>> contradictory. We still does not know why it act
as it does.
SELECT ...........etc.

FROM fmds.FMMC_EMPLOYEES E
INNER JOIN fmds.INDIRECT_LABOR_HOURS I
ON *E.Facility = I.WorkDate*

Are there other setting we should be looking beside
the one that are in >>> sys.sysoptions?
Best regards,

Rachan Terrell

Here are the differant between *Wing setup* vs Our
*Production Server*
FILE COMPARISON
Produced: 9/2/2009 1:59:02 PM

Mode: Just Differences

Left file: *Production Server*

Right file: *Wing setup*

----------------------------------------------------------
-------------- >>> 2 'PUBLIC','allow_read_client_file'
,'Off' +-
----------------------------------------------------------
--------------
----------------------------------------------------------
-------------- >>> 4 'PUBLIC','allow_write_client_file'
,'Off' +-
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 8 >>> 'PUBLIC','ansi_integer_overflow','On'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 16 >>> 'PUBLIC','automatic_timestamp','Off'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 44 >>> 'PUBLIC','divide_by_zero_error','On'
45
'PUBLIC','encrypt_aes_random_iv','On'

----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 52 >>> 'PUBLIC','float_as_double','Off'
----------------------------------------------------------
--------------
----------------------------------------------------------
-------------- >>> 65 'PUBLIC','max_priority','normal'
+-
----------------------------------------------------------
--------------
----------------------------------------------------------
-------------- >>> 71 'PUBLIC','ml_remote_id',''
+-
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 83 >>> 'PUBLIC','optimistic_wait_for_commit','Off'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 87 >>> 'PUBLIC','percent_as_comment','On'
----------------------------------------------------------
--------------
----------------------------------------------------------
-------------- >>> 88 'PUBLIC','priority','normal'
+-
----------------------------------------------------------
--------------
----------------------------------------------------------
-------------- >>> 90 'PUBLIC','query_mem_timeout','-1'
95 >>> 'PUBLIC','query_plan_on_open','Off'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 106 >>> 'PUBLIC','ri_trigger_time','After'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 112 >>> 'PUBLIC','sort_collation','Internal'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 125 >>> 'PUBLIC','Thread_count','0'
----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 130 >>> 'PUBLIC','truncate_with_auto_commit','On'
131
'PUBLIC','tsql_hex_constant','On'

----------------------------------------------------------
--------------
----------------------------------------------------------
--------------
-+ 137 >>> 'PUBLIC','uuid_has_hyphens','On'
----------------------------------------------------------
--------------
"Glenn Paulley [Sybase iAnywhere]"
paulley (AT) ianywhere (DOT) com
mailto

aulley (AT) ianywhere (DOT) com>> wrote in message
news:4a9d7d31$1 (AT) forums-1-dub (DOT) .. >>> >A couple of points:

1) I was incorrect in my initial response to Rachan
- in her example, >>> > the INTEGER would be cast to a
DATE, not the other way round. Invalid >>> > dates that
result from the attempted conversion will yield a data
exception error (-157), as you have seen. This is
outlined in section >>> > 2.4.1 in the whitepaper I
mentioned.
2) NOW(*) is not a date, but a timestamp. If you
want to know what the >>> > domain of an expression is,
you can use the EXPRTYPE function, as >>> follows:

SELECT EXPRTYPE( 'SELECT * FROM T_TABLE1', 1 )

which will display the type of the first element in
the SELECT list.
3) Data exceptions are a runtime thing. Consider
the following:
create table foo (x integer, y date )
insert into foo values( 56, '2009-01-27');
insert into foo values( 20090127, '2009-01-27');
commit

With CONVERSION_ERROR = ON, this statement gets an
error:
select * from foo where x = y

However, the following statement

select * from foo where x = y and x > 56

does NOT get an error, as rows that would cause the
error are rejected >>> > before any conversion is
attempted - the optimizer may order >>> predicates
in the execution plan to be evaluated in any order.

4) Setting the option CONVERSION_ERROR to OFF will
certainly cause the >>> > data exception to be ignored,
and the value NULL will be returned >>> > instead. Has
Rachan modified her own setting of CONVERSION_ERROR, even
though the PUBLIC option remains ON?
Glenn

Wing Dinanto wrote:
Glen,

Thank you for your technical explanation. Rachan
Terrel, >>> >> and I are on the same team. What we
happen here is that the >>> >> database option is the
same as follows:
user_name,option,setting
'PUBLIC','conversion_error','On'
'PUBLIC','on_tsql_error','Conditional'
'PUBLIC','sql_flagger_error_level','W'
'PUBLIC','divide_by_zero_error','On'
'PUBLIC','continue_after_raiserror','On'

Do the following:

create table t_table1(
a int
);

insert into t_table1(a)
values(1);

select * from t_table1
where a = now(*);

The above statement on my local machine throw an
error:
There was an error reading the results of the SQL
statement. >>> >> The displayed results may be incorrect
or incomplete. >>> >> Cannot convert 1 to a date
SQLCODE=-157, ODBC 3 State="07006"

However on Rachan machine it does not throw this
error and >>> >> still try to fetch the result set. Do
you what can cause >>> >> this and how to resolve this
issue?

Thank you,

Wing Dinanto

SQL Anywhere will implicitly cast a date to an
integer >>> >>> without error. For example, the date
'2009-01-27' will >>> >>> become the integer value
20,090,127.
Why the server appeared to "hang" is likely
unrelated to >>> >>> this specific conversion, and may
be due to other >>> >>> factors.

For further information on data type conversions,
please >>> >>> see the following whitepaper:

http://www.sybase.com/detail?id=1027471

Glenn

Rachan Terrell wrote:
SQL Anywhere Network Server Version 11.0.1.2250
Advanced >>> >>>> edition 16 logical processor(s) on 4
physical >>> >>>> processor(s) detected. Running Windows
2003 Build 3790 >>> >>>> Service Pack 2 on X86 (X86_64)
Server built for X86 >>> >>>> processor architecture
25165824K of memory used for >>> >>>> caching Physical
memory allocated for images: 24883928K >>> >>>> Address
space allocated: 1237664K >>> >>>> Using a maximum page
size of 8192 bytes
================================================== ==
This option controls whether data type
conversion >>> >>>> failures, when data is fetched from
the database or >>> >>>> inserted into the database, are
reported by the >>> >>> database as errors
(conversion_error set to On) or as a >>> >>>> warning
(conversion_error set to Off).
When conversion_error is set to On, the
SQLE_CONVERSION_ERROR error is generated. If
the option >>> >>>> is set to Off, the warning
SQLE_CANNOT_CONVERT is >>> >>>> produced.
We verify that the default which is 'ON' is
correct:
select * from sys.sysoptions
where "option" like '%conversion_error%'

We got:

user_name,option,setting
'PUBLIC','conversion_error','ON'

We have set up an event TYPE "DatabaseStart"
which will >>> >>>> set request_timeout and blocking_
timeout:
set option PUBLIC.blocking_timeout =
300000; -- >>> >>>> blocking timeout set to 5 minutes =
300000 miliseconds >>> >>>> set option
PUBLIC.request_timeout = 300; -- >>> >>>> request
timeout set to 5 minutes = 300 seconds
Then we run this TSQL using Interactive SQL:

SELECT E.Facility,
E.Employee_No,
E.Full_Name,
E.Shop,
I.WorkDate,
I.MH_Regular,
I.MH_Overtime,
I.LaborCategory,
I.LaborDistribution,
I.OvertimeCost,
I.RegularCost
FROM fmds.FMMC_EMPLOYEES E
INNER JOIN fmds.INDIRECT_LABOR_HOURS I
ON E.Facility = I.WorkDate AND
E.Labor_Category = I.LaborCategory WHERE
(((E.Facility = 56) AND (Is.WorkDate BETWEEN
'2009-08-01' >>> >>>> AND '2009-08-05')))
ORDER BY E.Employee_No ASC;

Please note that on INNER JOIN we have
E.Facility >>> >>>> (Interger) = I.WorkDate (Date) which
should given us an >>> >>>> error.....
We did not get any error and we were not
timeout. We >>> >>>> noticed that there were two other
connection created by >>> >>>> the system when the TSQL
was executing. We have to >>> >>> teminate the Server's
service because we could not >>> >>>> terminate the
connection that is running the above >>> >>>> TSQL. After
we re-start the server service we then set >>> >>>> the
PUBLIC.conversion_error to 'OFF' and run the above
TSQL again this time it did not give any error or
warning >>> >>>> at all but the query run and not hung
like before.
Thanks in advance of your help.

Best regards,
Rachan Terrell

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time
frame' to all
To Submit Bug Reports:
http://case-express.sybase.com
SQL Anywhere Studio Supported Platforms and
Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all
available >>> >>> through the Sybase iAnywhere pages at

http://www.sybase.com/products/datab...chnicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame'
to all
To Submit Bug Reports:
http://case-express.sybase.com
SQL Anywhere Studio Supported Platforms and Support
Status >>> > http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all
available through the >>> > Sybase iAnywhere pages at

http://www.sybase.com/products/datab...chnicalsupport
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to
all
To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support
Status >> http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available
through the >> Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available
through the Sybase iAnywhere pages at

http://www.sybase.com/products/datab...chnicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Question on conversion_error option

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: Question on conversion_error option - 09-04-2009 , 01:30 PM