Ridiculous Sql bugs in Sybase 
Author Message
 Ridiculous Sql bugs in Sybase

I have the following happen in both 4.2 and 4.9.1 Sybase versions.

table1
col1
------
Bob
John
Ricky

A query : select * from table1 where col1 not like '____'

will return:

Ricky

should return:

Bob
Ricky

The wild character match "_" for a length of 4 should be returning
all rows with < 4 AND > 4 length.  Has anybody seen this bug before?
Is this in their list of bugs? I called into Sybase tech-support
and waiting...

Over 8months ago, I reported another bug to Sybase which they confirmed
on version 4.2 and said it would be fixed in 4.8.
It is like this:

table1    table2
col1      col1
-----     -----
1         1
6         6

select * from table1 where table1.col1 >= any (select col1 from table2)

returns:

1
6
6

It should return:

1
6

        This bug is still there on 4.9.1 !  How could this kind of
ridiculous bugs exist for so long?  Any kind of decent testing on the
part of Sybase should have revealed this BEFORE they release the product.

I think that it is too long a time for such simple bugs to exist and they
should not take any time at all if they want to fix. What do you folks
think?  It is just frustrating for users to see this bugs live on forever.

Sreedhar.



Sat, 12 Aug 1995 02:57:35 GMT
 Ridiculous Sql bugs in Sybase


|>Path: pnl-oracle!ogicse!emory!swrinde!cs.utexas.edu!uunet!infonode!sbarakam

|>Newsgroups: comp.databases.sybase
|>Subject: Ridiculous Sql bugs in Sybase

|>Date: Mon, 22 Feb 93 10:57:35 PST
|>Article-I.D.: infonode.1993Feb22.185735.15435
|>Organization: Intergraph Corporation, Huntsville, AL.
|>Lines: 57
|>
|>I have the following happen in both 4.2 and 4.9.1 Sybase versions.
|>
|>table1
|>col1
|>------
|>Bob
|>John
|>Ricky
|>
|>A query : select * from table1 where col1 not like '____'
|>
|>will return:
|>
|>Ricky
|>
|>should return:
|>
|>Bob
|>Ricky
|>
|>The wild character match "_" for a length of 4 should be returning
|>all rows with < 4 AND > 4 length.  Has anybody seen this bug before?
|>Is this in their list of bugs? I called into Sybase tech-support
|>and waiting...

        Is col1 varchar?

select * from table1 where datalength(col1) != 4

|>
|>Over 8months ago, I reported another bug to Sybase which they confirmed
|>on version 4.2 and said it would be fixed in 4.8.
|>It is like this:
|>
|>table1    table2
|>col1      col1
|>-----     -----
|>1         1
|>6         6
|>
|>select * from table1 where table1.col1 >= any (select col1 from table2)
|>
|>returns:
|>
|>1
|>6
|>6
|>
|>It should return:
|>
|>1
|>6
|>    
|>   This bug is still there on 4.9.1 !  How could this kind of
|>ridiculous bugs exist for so long?  Any kind of decent testing on the
|>part of Sybase should have revealed this BEFORE they release the product.
|>
|>I think that it is too long a time for such simple bugs to exist and they
|>should not take any time at all if they want to fix. What do you folks
|>think?  It is just frustrating for users to see this bugs live on forever.
|>
|>Sreedhar.
|>

table1    table2
col1      col1
-----     -----
1         1
6         6

1 = 1
1 NOT > 6
6 > 1
6 = 6

1
6
6

IS correct.

select distinct * from table1 where table1.col1 >= any (select col1 from table2)

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        To Thyne Own Self be true...
                                     myne opinions are.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Sat, 12 Aug 1995 09:41:29 GMT
 Ridiculous Sql bugs in Sybase

Quote:
(Randy Jordan) writes:


>|>
>|>table1
>|>col1
>|>------
>|>Bob
>|>John
>|>Ricky
>|>
>|>A query : select * from table1 where col1 not like '____'
>|>
>|>will return:
>|>
>|>Ricky
>|>
>|>should return:
>|>
>|>Bob
>|>Ricky
>|>
>|>The wild character match "_" for a length of 4 should be returning
>|>all rows with < 4 AND > 4 length.  Has anybody seen this bug before?
>|>Is this in their list of bugs? I called into Sybase tech-support
>|>and waiting...

>    Is col1 varchar?

>select * from table1 where datalength(col1) != 4

This is a workaround, but it's still a bug.

- Show quoted text -

Quote:

>|>
>|>Over 8 months ago, I reported another bug to Sybase which they confirmed
>|>on version 4.2 and said it would be fixed in 4.8.
>|>It is like this:
>|>
>|>table1    table2
>|>col1      col1
>|>-----     -----
>|>1         1
>|>6         6
>|>
>|>select * from table1 where table1.col1 >= any (select col1 from table2)
>|>
>|>returns:
>|>
>|>1
>|>6
>|>6
>|>
>|>It should return:
>|>
>|>1
>|>6
>|>    

>table1    table2
>col1      col1
>-----     -----
>1         1
>6         6

>1 = 1
>1 NOT > 6
>6 > 1
>6 = 6

>1
>6
>6

>IS correct.

I disagree with this analysis.  This is not a join, it is a subquery.
VAX Rdb says:

 select t1.col1 from table1 t1,table2 t2 where t1.col1 >= t2.col1;
     T1.COL1
           1
           6
           6

[a join], but

 select * from table1  where col1 >= any(select col1 from table2);
        COL1
           1
           6

[a subquery], which I believe is correct.  To quote the Sybase manual,
  ". . . For each title, the inner query finds a list of . . . amounts. . .
  The outer query looks at the largest value in the list and determines
  whether the title currently being considered has . . . an even
  bigger [amount].

Quote:
>select distinct * from table1 where table1.col1 >= any (select col1 from
>   table2)

Again, workarounds are helpful, but bugs are bugs.

Tom Warfield

Claritas/NPDC: vnunet!twarfield      (Mail)



Sat, 12 Aug 1995 21:41:33 GMT
 Ridiculous Sql bugs in Sybase

Quote:

>select * from table1 where table1.col1 >= any (select col1 from table2)

>returns:

>1
>6
>6

>It should return:

>1
>6

I tried this on our 4.9.1 Sybase engine, and got the same response as
Sreedhar (1 6 6).  Then I tried it on our InformixOnLine engine, and got the
(correct?) answere (1 6).
So, like any flailing programmer, I looked in the Sybase book (ver 4.9).
It says:
   "any - >any is true (and the subquery returns results) if the value
    in the outer query is greater than any value in the list."

The key word here is "results", plural, more than ONE.  Sybase appearently
defines the any to return a list of results from the subquery, and filters
them with the comparison operator.  Therefor, you would get a 1 and 6
returned for each iteration of the outer query.  The comparison eliminated
the 1<=6, and what is left is 1 1, 6 1, 6 6.  Hence the results: 1 6 6.

These are my opinions and are subject to change without notice.
!------------------------------------------------------------------------!
! "In these uncertain times, one must think of others' viewpoints, and   !
! always remember that a crowded elevator smells different to a midget." !


!------------------------------------------------------------------------!



Sun, 13 Aug 1995 02:17:02 GMT
 Ridiculous Sql bugs in Sybase

Quote:
>I have the following happen in both 4.2 and 4.9.1 Sybase versions.

>table1
>col1
>------
>Bob
>John
>Ricky

>A query : select * from table1 where col1 not like '____'

>will return:

>Ricky

>should return:

>Bob
>Ricky

>The wild character match "_" for a length of 4 should be returning
>all rows with < 4 AND > 4 length.  Has anybody seen this bug before?
>Is this in their list of bugs? I called into Sybase tech-support
>and waiting...

I forgot to add here that the above results show up when the column's data
type is char as well as varchar.

Sreedhar.



Sun, 13 Aug 1995 05:03:55 GMT
 Ridiculous Sql bugs in Sybase
Sybase treats fields of char datatypes that allow NULLS as varchar fields,
could this have some effect on your tests.  What happens if the field is
of type char without NULLS?

--
--------------------------------------------------------------------------

Soldier, Cola Wars
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^



Sun, 13 Aug 1995 09:17:18 GMT
 Ridiculous Sql bugs in Sybase
|> table1    table2
|> col1      col1
|> -----     -----
|> 1         1
|> 6         6
|>
|>select * from table1 where table1.col1 >= any (select col1 from table2)
|>
|> 1 = 1
|> 1 NOT > 6
|> 6 > 1
|> 6 = 6
|>
|> 1
|> 6
|> 6
|>
|> IS correct.
|>
|> select distinct * from table1
        where table1.col1 >= any (select col1 from table2)

Not necessarily correct.  In the above case, the original query could
equivalently be interpreted as

select * from table1 where col1 >= 1 or col1 >= 6 [or col1 >= x or ...
ad infinitum for as many values that are returned by the subquery];

The results you came up with imply some join or cartesian product is
actually occurring.

Roderick Manalac
Oracle Corporation
[DISCLAIMER:  Opinions expressed here are strictly my own]



Wed, 16 Aug 1995 15:07:47 GMT
 Ridiculous Sql bugs in Sybase

Quote:

>>select * from table1 where table1.col1 >= any (select col1 from table2)

>>returns:

>>1
>>6
>>6

>>It should return:

>>1
>>6

>I tried this on our 4.9.1 Sybase engine, and got the same response as
>Sreedhar (1 6 6).  Then I tried it on our InformixOnLine engine, and got the
>(correct?) answere (1 6).
>So, like any flailing programmer, I looked in the Sybase book (ver 4.9).
>It says:
>   "any - >any is true (and the subquery returns results) if the value
>    in the outer query is greater than any value in the list."
>The key word here is "results", plural, more than ONE.  Sybase appearently
>defines the any to return a list of results from the subquery, and filters
>them with the comparison operator.  Therefor, you would get a 1 and 6
>returned for each iteration of the outer query.  The comparison eliminated
>the 1<=6, and what is left is 1 1, 6 1, 6 6.  Hence the results: 1 6 6.

But why does the subquery iterate?  It is not correlated, so it doesn't
need to, and therefore (IMHO) should not.

graeme
--
Disclaimer:     The author's opinions are his own, and not necessarily
                those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???

---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.



Fri, 18 Aug 1995 19:16:31 GMT
 Ridiculous Sql bugs in Sybase

Quote:



>>>select * from table1 where table1.col1 >= any (select col1 from table2)

>>>returns:

>>>1
>>>6
>>>6

>>>It should return:

>>>1
>>>6

This acknowledged bug will be fixed in Sybase's system 10 release.  This
is the feedback I got from Sybase's support.

I am still waiting for a reply about the following suspected bug though.

select * from t1 where c1 not like '____'

returning c1(varchar) values of length > 4 but not < 4

Sreedhar.



Mon, 21 Aug 1995 00:40:17 GMT
 Ridiculous Sql bugs in Sybase

Quote:

>I have the following happen in both 4.2 and 4.9.1 Sybase versions.
>table1
>col1
>------
>Bob
>John
>Ricky
>A query : select * from table1 where col1 not like '____'
>will return:
>Ricky
>should return:
>Bob
>Ricky

I did some experimenting on our version 4.2 of Sybase. I was only able
to make this happen if I declared col1 to be CHAR and NOT NULL.
If col1 was VARCHAR, the query worked correctly. If col1 was CHAR and
NULL, the query also worked correctly. I'm sorry to hear that this
bug still is not fixed in version 4.9.1 of Sybase.
--

  Academic Computing Services
  Simon Fraser University                 Telephone: (604) 291-4449
  Burnaby, B.C. Canada V5A 1S6            Fax:       (604) 291-4242


Tue, 22 Aug 1995 01:56:23 GMT
 Ridiculous Sql bugs in Sybase

Quote:



>>>select * from table1 where table1.col1 >= any (select col1 from table2)

>>>returns:

>>>1
>>>6
>>>6

>>>It should return:

>>>1
>>>6

>>I tried this on our 4.9.1 Sybase engine, and got the same response as
>>Sreedhar (1 6 6).  Then I tried it on our InformixOnLine engine, and got the
>>(correct?) answere (1 6).
>>So, like any flailing programmer, I looked in the Sybase book (ver 4.9).
>>It says:
>>   "any - >any is true (and the subquery returns results) if the value
>>    in the outer query is greater than any value in the list."

>>The key word here is "results", plural, more than ONE.  Sybase appearently
>>defines the any to return a list of results from the subquery, and filters
>>them with the comparison operator.  Therefor, you would get a 1 and 6
>>returned for each iteration of the outer query.  The comparison eliminated
>>the 1<=6, and what is left is 1 1, 6 1, 6 6.  Hence the results: 1 6 6.

>But why does the subquery iterate?  It is not correlated, so it doesn't
>need to, and therefore (IMHO) should not.

>graeme
>--

 This seems to be Sybase bug # 13495 - Since as far back as anyone can
 remember Sybase has been very fond (overly fond in fact) of converting
 subqueries into joins when evaluating them, hence the duplicate row in
 this example.
 This bug is apparently fixed in System 10.

Regards
David S.



Tue, 22 Aug 1995 08:59:29 GMT
 Ridiculous Sql bugs in Sybase

Quote:
Richter) writes:

>>A query : select * from table1 where col1 not like '____'
>>will return:
>> Ricky
>>should return:
>> Bob
>> Ricky

> [...] I'm sorry to hear that this bug still is not fixed in version
>4.9.1 of Sybase.

It isn't fixed because it isn't a bug.  The query is written incorrectly.

I was largely responsible for pushing this change through when it happened
a couple of years ago.  The way we USED to do business (returning "Bob") WAS
a bug, and was reported as such by one of our customers in a case involving
rules processing.  Here's why:

 1. ["Bob" = "Bob "] is TRUE.  This is a given, since ANSI sez that in
    comparing two strings, the shorter string will be conceptually padded
    with blanks to equal the length of the longer string before comparing.

 2. If 1 is TRUE, then ["Bob" LIKE "Bob "] is also TRUE.  Otherwise, a LIKE
    comparison would differ fundamentally from an EQUAL comparison.

 3. ["Bob" LIKE "___"] and [" " LIKE "_"] are both TRUE, by Sybase's
    definitions of the wildcards.

 4. By 2 and 3, ["Bob" LIKE "Bob_"] is TRUE.

Therefore, ["Bob" LIKE "____"] is TRUE, and ["Bob" NOT LIKE "____"] is
FALSE.  The query should NOT return "Bob", because the string has been
extended with blanks to pad it out to the length of the "longer" (pattern)
string.

To select all names of NOT EXACTLY 4 characters, use

        NOT LIKE "[^ ][^ ][^ ][^ ]"

This pattern string will match ONLY non-blank characters, so the query will
fail to match all strings with blanks in them ("Bob ") as well as all
strings longer than 4 characters ("Ricky").

                        -- Elton Wildermuth
                           Sybase SQL Server Development



Sat, 02 Sep 1995 04:47:16 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. Ridiculous SQL question

2. BUGS idapi BUGS idapi BUGS idapi BUGS

3. JDBC BUG with SQL server and sybase SQLAnywhere

4. Sybase JDBC bug with java.sql.ResultSet.getString()

5. Ridiculous problem

6. Ridiculous trouble with view perfomance =((

7. SQLXML and Windows Server 2003... this is ridiculous

8. Ridiculous error message from ExecSQL.

9. Ridiculous

10. Ridiculous Crashes : 7.31xD[24] on HP 11.0

11. Ridiculous Crashes : 7.31xD[24] on HP 11.0


 
Powered by phpBB® Forum Software