See case 133.
Hi,
We are hitting a bug in Aqua extracting or viewing a float(16) user defined datatype this is shown in the GUI as float(8):
[cid:image001.png@01D4DF03.48AAB530]
If this is then extracted out to SQL and re-run against a Sybase instance, due to Sybase checking the length being less than 16 this is re-created as a real datatype instead of a float, thus losing precision in the datatype:
[cid:image002.png@01D4DF03.48AAB530]
Product Info below:
License Key: E1890A59B47A025A832696B5965FD609A8D53AB5C1CDCB08****************
Product: Aqua Data Studio
Version: 18.5.0-12
Build #: 55751
Build Date: 2018-Mar-08 10:33:23 AM
Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / en / GB / Oracle Corporation 1.8.0_102-b14
Memory: Max=954,728,448; Total=881,852,416; Free=585,559,944; CPUs=8
In-Window Graphics Capabilities
Graphics Vendor: Microsoft Corporation
OpenGL Renderer: GDI Generic
OpenGL Version: 1.1.0
Double-Buffering: Disabled
Anti-Aliasing: Disabled
Anti-Aliasing Sample Count: 0
Hardware Acceleration: Disabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 0
Depth Bits: 16
Accumulation Buffer Bits: Red: 16 Green: 16 Blue: 16
Initialization Time: 1806 ms
Offscreen Graphics Capabilities
Graphics Vendor: Brian Paul
OpenGL Renderer: Mesa OffScreen
OpenGL Version: 2.1 Mesa 7.8.2
Double-Buffering: Disabled
Anti-Aliasing: Disabled
Anti-Aliasing Sample Count: 0
Hardware Acceleration: Disabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 8
Depth Bits: 16
Accumulation Buffer Bits: Red: 16 Green: 16 Blue: 16
Initialization Time: 58 ms
Hardware PBuffer Available: No
Using PBuffer: No
Using Ram Buffer: Yes
Offscreen Rendering: Enabled
Offscreen Buffer Size: 800x600
From Tom:
Hi John,
We extracted the information that Sybase stores. We are assuming that they are using ASE.
I created these two types:
sp_addtype N'price_ut8', N'float(8)', N'null'
GO
sp_addtype N'price_ut16', N'float(16)', N'null'
GO
When I extract the types that are stored, I get this.
select
s.usertype,
s.name typename,
u.name username,
s.type,
s.allownulls,
s.length,
s.prec,
s.scale,
s.ident,
ut.name
from
dbo.systypes s,
dbo.systypes ut,
dbo.sysusers u
where
(s.type = ut.type and
ut.usertype < 100) and
s.uid = u.uid and
s.usertype > 99 and
ut.name not in ('sysname',
'longsysname',
'nchar',
'nvarchar') and s.name in ('price_ut8', 'price_ut16')
usertype typename username type allownulls length prec scale ident name
104 price_ut16 dbo 62 1 8 (null) (null) 0 float
103 price_ut8 dbo 59 1 4 (null) (null) 0 real
Notice the size and the type name. This has to do with the way Sybase stores the information based on the precision.(mantissa). Go to the below link for additional information.
http://dcx.sybase.com/1200/en/dbreference/float.html
From User:
Yes it stores it as length 8 however that applies to any float from 16 to 48 precision. The problem is Aqua extracts them as float(8) which then creates a real datatype not float when you re-run it into Sybase. If you check the sp_addtype proc you'll see anything less than float(16) is actually created a real since the precision is not required.
This does not happen with Sybase's own ddlgen tool which extracts all floats as float(16) thus ensuring there is no change of datatype when you re-run the SQL back into another database so it would seem sensible for Aqua to do the same.
From Tom:
This is what Sybase gives us back and how it is stored in their system tables. We simply pass this query into Sybase and what it produces is what is in the system table.
108 KB
126 KB
This seems like a bug in sp_addtype in Sybase ASE. For example, creating a "float(15)" is stored as "real". According to their documentation, a "real" is stored using 4 bytes, which guarantees precision up to 6 digits, which is definitely less that the 15 digits that the user asks for. This we cannot address in ADS.
Additionally, creating a "float(n >= 16)" is stored as "float" of "length=8", where "length=8" seems to indicate the number of bytes required to store the data rather than the digit precision. Thus the precision passed to sp_addtype is a different unit than what is stored in the "length" column in "dbo.systypes".
Thus in this issue, when generating DDL in ADS, the length column in "dbo.systypes" should be ignored for float types, and float types should be generated as float(16) rather than float(8).
Hi Komal,
When we Script Object to Window then float types should be generated as float(16) rather than float(8).
Hi Komal,
When we Script Object to Window then float types should be generated as float(16) rather than float(8).
Sybase float data types are being extracted correctly.
Testcase updated in testrail:
https://idera.testrail.net/index.php?/suites/view/3860&group_by=cases:section_id&group_order=asc
Sybase float data types are being extracted correctly.
Testcase updated in testrail:
https://idera.testrail.net/index.php?/suites/view/3860&group_by=cases:section_id&group_order=asc
The automation test case did not complete successfully for me. It was stuck while trying to close one of the tabs. See automation.png. Note that there are 2 open tabs at this point; not sure whether or not it's intentional.
The automation test case did not complete successfully for me. It was stuck while trying to close one of the tabs. See automation.png. Note that there are 2 open tabs at this point; not sure whether or not it's intentional.
It still failed for me due to some random timing failure in Ranorex. I've checked in my suggested fix in SVN #24104. Please review and make any necessary changes as you see fit.
It still failed for me due to some random timing failure in Ranorex. I've checked in my suggested fix in SVN #24104. Please review and make any necessary changes as you see fit.
Verifed on v20.6.0-rc-2 build with sybase ASE 15.7 and 16
on windows 10
mac
unbuntu 18.0.4
Verified creating of user defined datatype and table with column having float 16
generated script for the dataype float 16 and float8 both are working fine
Verifed on v20.6.0-rc-2 build with sybase ASE 15.7 and 16
on windows 10
mac
unbuntu 18.0.4
Verified creating of user defined datatype and table with column having float 16
generated script for the dataype float 16 and float8 both are working fine
Issue #15700 |
Verified |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build ADS 20.6.0-dev-40-no-ofsc |
No time estimate |
This seems like a bug in sp_addtype in Sybase ASE. For example, creating a "float(15)" is stored as "real". According to their documentation, a "real" is stored using 4 bytes, which guarantees precision up to 6 digits, which is definitely less that the 15 digits that the user asks for. This we cannot address in ADS.
Additionally, creating a "float(n >= 16)" is stored as "float" of "length=8", where "length=8" seems to indicate the number of bytes required to store the data rather than the digit precision. Thus the precision passed to sp_addtype is a different unit than what is stored in the "length" column in "dbo.systypes".
Thus in this issue, when generating DDL in ADS, the length column in "dbo.systypes" should be ignored for float types, and float types should be generated as float(16) rather than float(8).