|
Home > Archive > ASE Database forum > April 2005 > changes in SQL interpretting 12.5.0.3 and 12.5.1
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
changes in SQL interpretting 12.5.0.3 and 12.5.1
|
|
| Adam Leszczyński 2005-04-12, 11:25 am |
| Hi all,
I've had some bad expierience while upgrading a product from ASE 12.5.0.3 to
12.5.1 (both Linux).
The application I've had had some real nasty SQL's, which worked on both
versions of ASE, but completely in a different way. For the following table:
create table1 (
id numeric(8),
Brother numeric(8),
Parent numeric(8),
Firstchild numeric(8)
)
was executed the following SQL:
SELECT
table1.id AS id,
ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null') AS 'parent',
ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null') AS 'brother',
ISNULL(CONVERT(VARCH
AR, table1.FirstChild), 'null') AS 'firstChild'
FROM
table1
ORDER BY
table1.Parent,
table1.Brother DESC
Depending on the version of ASE the data was sorted different:
on 12.5.0.3: according to columns: table1.Parent, table1.Brother
on 12.5.1: according to: ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null'),
ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null')
Actually in 12.5.1 I've had to rewrite the code to something like this:
SELECT
table1.id AS 'id',
ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null') AS 'parent',
ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null') AS 'brother',
ISNULL(CONVERT(VARCH
AR, table1.FirstChild), 'null') AS 'firstChild',
table1.Parent AS 'aaa',
table1.Brother AS 'bbb'
FROM
table1
ORDER BY
aaa,
bbb DESC
to work fine on both versions of ASE
Apart of the bad style SQL (I wasn't the author) I have an important
question. How can I avoid such errors in the future. I couldn't find
anything in the 12.5.1 what's new document. Can the behavior of this change
in the newer versions of ASE? Or is this a matter of database settings (both
ASE servers ware had same configuration). Actually if the order of the
columns didn't exploit my system I wouldn't probably notice the change. On
both versions the code works. It was executed in a stored procedure and
produced no warning/error messages.
TIA,
Adam
ps.
the code was stripped to make it more readable
| |
| Rob Verschoor 2005-04-13, 3:24 am |
| Strange - as far as I know nothing was changed in this area between these
versions.
Can you reproduce it, i.e. show this effect with a limited set of rows?
HTH,
Rob
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
"Adam Leszczyński" <adam_leszczynski.at.wp.pl@no.spam> wrote in message
news:425be204$2@foru
ms-1-dub...
> Hi all,
>
> I've had some bad expierience while upgrading a product from ASE 12.5.0.3
to
> 12.5.1 (both Linux).
>
> The application I've had had some real nasty SQL's, which worked on both
> versions of ASE, but completely in a different way. For the following
table:
>
> create table1 (
> id numeric(8),
> Brother numeric(8),
> Parent numeric(8),
> Firstchild numeric(8)
> )
>
> was executed the following SQL:
>
> SELECT
> table1.id AS id,
> ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null') AS 'parent',
> ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null') AS 'brother',
> ISNULL(CONVERT(VARCH
AR, table1.FirstChild), 'null') AS 'firstChild'
> FROM
> table1
> ORDER BY
> table1.Parent,
> table1.Brother DESC
>
>
> Depending on the version of ASE the data was sorted different:
>
> on 12.5.0.3: according to columns: table1.Parent, table1.Brother
> on 12.5.1: according to: ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null'),
> ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null')
>
>
> Actually in 12.5.1 I've had to rewrite the code to something like this:
>
> SELECT
> table1.id AS 'id',
> ISNULL(CONVERT(VARCH
AR, table1.Parent), 'null') AS 'parent',
> ISNULL(CONVERT(VARCH
AR, table1.Brother), 'null') AS 'brother',
> ISNULL(CONVERT(VARCH
AR, table1.FirstChild), 'null') AS 'firstChild',
> table1.Parent AS 'aaa',
> table1.Brother AS 'bbb'
> FROM
> table1
> ORDER BY
> aaa,
> bbb DESC
>
> to work fine on both versions of ASE
>
>
> Apart of the bad style SQL (I wasn't the author) I have an important
> question. How can I avoid such errors in the future. I couldn't find
> anything in the 12.5.1 what's new document. Can the behavior of this
change
> in the newer versions of ASE? Or is this a matter of database settings
(both
> ASE servers ware had same configuration). Actually if the order of the
> columns didn't exploit my system I wouldn't probably notice the change. On
> both versions the code works. It was executed in a stored procedure and
> produced no warning/error messages.
>
> TIA,
>
> Adam
>
> ps.
> the code was stripped to make it more readable
>
>
| |
| Adam Leszczyński 2005-04-13, 3:24 am |
| I have the whole system archived somewhere with all the settings but it'l
take me a few days
to install it on my PC with exact architecture. I can try to reproduce it on
ASE / Windows system faster.
Adam
Użytkownik "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
napisał w wiadomości news:425ccbb3@forums
-2-dub...
> Strange - as far as I know nothing was changed in this area between these
> versions.
> Can you reproduce it, i.e. show this effect with a limited set of rows?
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE"
> "The Complete Sybase Replication Server Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
> "Adam Leszczyński" <adam_leszczynski.at.wp.pl@no.spam> wrote in message
> news:425be204$2@foru
ms-1-dub...
> to
> table:
> change
> (both
>
>
| |
| Adam Leszczyński 2005-04-13, 7:24 am |
| Użytkownik "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
wrote in news:425ccbb3@forums
-2-dub...
> Strange - as far as I know nothing was changed in this area between these
> versions.
> Can you reproduce it, i.e. show this effect with a limited set of rows?
Here are the results. I don't have the 12.5.0.3 environment currently but I
have 12.5.3 instead which works... same.
Maybe there was a bug in 12.5.1 ????
Adam
create table #table1 (
id numeric(2) null,
Brother numeric(2) null,
Parent numeric(2) null,
FirstChild numeric(2) null
)
go
insert into #table1(id, Brother, Parent, FirstChild) values (1, null, null,
2)
insert into #table1(id, Brother, Parent, FirstChild) values (2, 3, 1, 6)
insert into #table1(id, Brother, Parent, FirstChild) values (3, 4, 1, 10)
insert into #table1(id, Brother, Parent, FirstChild) values (4, 5, 1, null)
insert into #table1(id, Brother, Parent, FirstChild) values (5, null, 1,
null)
insert into #table1(id, Brother, Parent, FirstChild) values (6, 7, 2, null)
insert into #table1(id, Brother, Parent, FirstChild) values (7, 8, 2, null)
insert into #table1(id, Brother, Parent, FirstChild) values (8, 9, 2, null)
insert into #table1(id, Brother, Parent, FirstChild) values (9, null, 2,
null)
insert into #table1(id, Brother, Parent, FirstChild) values (10, 11, 3,
null)
insert into #table1(id, Brother, Parent, FirstChild) values (11, null, 3,
null)
go
test1:
select
#table1.id AS id,
ISNULL(CONVERT(VARCH
AR(5), #table1.Parent), 'null') AS 'parent',
ISNULL(CONVERT(VARCH
AR(5), #table1.Brother), 'null') AS 'brother',
ISNULL(CONVERT(VARCH
AR(5), #table1.FirstChild), 'null') AS 'firstChild'
FROM
#table1
ORDER BY
#table1.Parent,
#table1.Brother DESC
go
test2:
select
#table1.id AS 'id',
ISNULL(CONVERT(VARCH
AR(5), #table1.Parent), 'null') AS 'parent',
ISNULL(CONVERT(VARCH
AR(5), #table1.Brother), 'null') AS 'brother',
ISNULL(CONVERT(VARCH
AR(5), #table1.FirstChild), 'null') AS 'firstChild',
#table1.Parent AS 'aaa',
#table1.Brother AS 'bbb'
FROM
#table1
ORDER BY
aaa,
bbb DESC
go
machine1:
select @@version
Adaptive Server Enterprise/12.5.1/EBF 11429/P/Linux Intel/Enterprise
Linux/ase1251/1823/32-bit/OPT/Tue Sep 16 23:43:54 2003
test1:
id parent brother firstChild
----- ------ ------- ----------
5 1 null null
4 1 5 null
3 1 4 10
2 1 3 6
9 2 null null
8 2 9 null
7 2 8 null
6 2 7 null
11 3 null null
10 3 11 null
1 null null 2
test2:
id parent brother firstChild aaa bbb
----- ------ ------- ---------- ----- -----
1 null null 2 NULL NULL
4 1 5 null 1 5
3 1 4 10 1 4
2 1 3 6 1 3
5 1 null null 1 NULL
8 2 9 null 2 9
7 2 8 null 2 8
6 2 7 null 2 7
9 2 null null 2 NULL
10 3 11 null 3 11
11 3 null null 3 NULL
machine2: select @@version
Adaptive Server Enterprise/12.5.3/EBF 12338 ESD#1/P/NT (IX86)/OS
4.0/ase1253/1900/32-bit/OPT/Mon Jan 24 23:52:28 2005
test1:
id parent brother firstChild
---- ------ ------- ----------
1 null null 2
4 1 5 null
3 1 4 10
2 1 3 6
5 1 null null
8 2 9 null
7 2 8 null
6 2 7 null
9 2 null null
10 3 11 null
11 3 null null
test2:
id parent brother firstChild aaa bbb
---- ------ ------- ---------- ---- ----
1 null null 2 NULL NULL
4 1 5 null 1 5
3 1 4 10 1 4
2 1 3 6 1 3
5 1 null null 1 NULL
8 2 9 null 2 9
7 2 8 null 2 8
6 2 7 null 2 7
9 2 null null 2 NULL
10 3 11 null 3 11
11 3 null null 3 NULL
| |
| Rob Verschoor 2005-04-19, 7:24 am |
| I cannot reproduce your problem. When I run it, the results are exactly
identical in 12.5.1 and 12.5.3 and they're also identical for both queries.
This is what I would expect. It is really strange that you should see a
different order.
The only thing you could do is to submit a repro to Sybase TechSupport and
see if they can repro it too.
HTH,
Rob
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
"Adam Leszczyński" <adam_leszczynski.at.wp.pl@no.spam> wrote in message
news:425cdb46$1@foru
ms-2-dub...
> Użytkownik "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
> wrote in news:425ccbb3@forums
-2-dub...
these[color=darkred]
>
> Here are the results. I don't have the 12.5.0.3 environment currently but
I
> have 12.5.3 instead which works... same.
> Maybe there was a bug in 12.5.1 ????
>
> Adam
>
>
>
> create table #table1 (
> id numeric(2) null,
> Brother numeric(2) null,
> Parent numeric(2) null,
> FirstChild numeric(2) null
> )
> go
>
> insert into #table1(id, Brother, Parent, FirstChild) values (1, null,
null,
> 2)
> insert into #table1(id, Brother, Parent, FirstChild) values (2, 3, 1, 6)
> insert into #table1(id, Brother, Parent, FirstChild) values (3, 4, 1, 10)
> insert into #table1(id, Brother, Parent, FirstChild) values (4, 5, 1,
null)
> insert into #table1(id, Brother, Parent, FirstChild) values (5, null, 1,
> null)
> insert into #table1(id, Brother, Parent, FirstChild) values (6, 7, 2,
null)
> insert into #table1(id, Brother, Parent, FirstChild) values (7, 8, 2,
null)
> insert into #table1(id, Brother, Parent, FirstChild) values (8, 9, 2,
null)
> insert into #table1(id, Brother, Parent, FirstChild) values (9, null, 2,
> null)
> insert into #table1(id, Brother, Parent, FirstChild) values (10, 11, 3,
> null)
> insert into #table1(id, Brother, Parent, FirstChild) values (11, null, 3,
> null)
> go
>
>
>
> test1:
> select
> #table1.id AS id,
> ISNULL(CONVERT(VARCH
AR(5), #table1.Parent), 'null') AS 'parent',
> ISNULL(CONVERT(VARCH
AR(5), #table1.Brother), 'null') AS 'brother',
> ISNULL(CONVERT(VARCH
AR(5), #table1.FirstChild), 'null') AS 'firstChild'
> FROM
> #table1
> ORDER BY
> #table1.Parent,
> #table1.Brother DESC
> go
>
>
> test2:
>
> select
> #table1.id AS 'id',
> ISNULL(CONVERT(VARCH
AR(5), #table1.Parent), 'null') AS 'parent',
> ISNULL(CONVERT(VARCH
AR(5), #table1.Brother), 'null') AS 'brother',
> ISNULL(CONVERT(VARCH
AR(5), #table1.FirstChild), 'null') AS 'firstChild',
> #table1.Parent AS 'aaa',
> #table1.Brother AS 'bbb'
> FROM
> #table1
> ORDER BY
> aaa,
> bbb DESC
> go
>
>
>
>
>
> machine1:
> select @@version
> Adaptive Server Enterprise/12.5.1/EBF 11429/P/Linux Intel/Enterprise
> Linux/ase1251/1823/32-bit/OPT/Tue Sep 16 23:43:54 2003
>
> test1:
> id parent brother firstChild
> ----- ------ ------- ----------
> 5 1 null null
> 4 1 5 null
> 3 1 4 10
> 2 1 3 6
> 9 2 null null
> 8 2 9 null
> 7 2 8 null
> 6 2 7 null
> 11 3 null null
> 10 3 11 null
> 1 null null 2
>
> test2:
> id parent brother firstChild aaa bbb
> ----- ------ ------- ---------- ----- -----
> 1 null null 2 NULL NULL
> 4 1 5 null 1 5
> 3 1 4 10 1 4
> 2 1 3 6 1 3
> 5 1 null null 1 NULL
> 8 2 9 null 2 9
> 7 2 8 null 2 8
> 6 2 7 null 2 7
> 9 2 null null 2 NULL
> 10 3 11 null 3 11
> 11 3 null null 3 NULL
>
>
>
>
> machine2: select @@version
> Adaptive Server Enterprise/12.5.3/EBF 12338 ESD#1/P/NT (IX86)/OS
> 4.0/ase1253/1900/32-bit/OPT/Mon Jan 24 23:52:28 2005
>
> test1:
> id parent brother firstChild
> ---- ------ ------- ----------
> 1 null null 2
> 4 1 5 null
> 3 1 4 10
> 2 1 3 6
> 5 1 null null
> 8 2 9 null
> 7 2 8 null
> 6 2 7 null
> 9 2 null null
> 10 3 11 null
> 11 3 null null
>
> test2:
> id parent brother firstChild aaa bbb
> ---- ------ ------- ---------- ---- ----
> 1 null null 2 NULL NULL
> 4 1 5 null 1 5
> 3 1 4 10 1 4
> 2 1 3 6 1 3
> 5 1 null null 1 NULL
> 8 2 9 null 2 9
> 7 2 8 null 2 8
> 6 2 7 null 2 7
> 9 2 null null 2 NULL
> 10 3 11 null 3 11
> 11 3 null null 3 NULL
>
>
>
| |
| Bret Halford 2005-04-19, 9:24 am |
| If you haven't done so already, run dbcc checktable to make sure there
isn't a problem with the table or indexes.
Rob Verschoor wrote:
[color=darkred]
> I cannot reproduce your problem. When I run it, the results are exactly
> identical in 12.5.1 and 12.5.3 and they're also identical for both queries.
> This is what I would expect. It is really strange that you should see a
> different order.
> The only thing you could do is to submit a repro to Sybase TechSupport and
> see if they can repro it too.
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE"
> "The Complete Sybase Replication Server Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
> "Adam Leszczyński" <adam_leszczynski.at.wp.pl@no.spam> wrote in message
> news:425cdb46$1@foru
ms-2-dub...
> these
> I
> null,
> null)
> null)
> null)
> null)
| |
| Adam Leszczyński 2005-04-25, 3:24 am |
| Maybe it was an error caused by old unpatched version ASE?
Actually, the system (the 12.5.1 ASE) will be working for a few days only
and I won't be able to patch it to a newer version of 12.5.1.
Especially, as it seems, that the error is corrected in latter versions of
the database.
Has anyone the same version of ASE to verify the problem?
Adam
Użytkownik "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
napisał w wiadomości news:4264e0e2@forums
-1-dub...
>I cannot reproduce your problem. When I run it, the results are exactly
> identical in 12.5.1 and 12.5.3 and they're also identical for both
> queries.
> This is what I would expect. It is really strange that you should see a
> different order.
> The only thing you could do is to submit a repro to Sybase TechSupport and
> see if they can repro it too.
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE"
> "The Complete Sybase Replication Server Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
> "Adam Leszczyński" <adam_leszczynski.at.wp.pl@no.spam> wrote in message
> news:425cdb46$1@foru
ms-2-dub...
> these
> I
> null,
> null)
> null)
> null)
> null)
>
>
|
|
|
|
|