operator 'NOT LIKE' doesn't work with a secondary index

hi Eivind,

the operator 'NOT LIKE' doesn't work correctly with a secondary index !!!!
:-(((

below a samples :
------------------------------------------- >> create a table with 2
indexes OK
create table "<tmp>"
(
id int primary key,
resid varchar(16)
);
create index idx on "<tmp>"(resid);
------------------------------------------- >> create samples records OK
insert into "<tmp>" values( 1, '123');
insert into "<tmp>" values( 2, '234');
insert into "<tmp>" values( 3, 'RT/100');
insert into "<tmp>" values( 4, 'RT/110');
insert into "<tmp>" values( 5, 'R100');

------------------------------------------- >> query 1 OK
select *
from "<tmp>"
where resid LIKE 'RT%'

==> return 2 records 4 & 5

------------------------------------------- >> query 2 NOT OK
select *
from "<tmp>"
where resid NOT LIKE 'RT%

==> returns NOTHING !!! WHY ??

------------------------------------------- >> query 3 works with a TRIM
function
select *
from "<tmp>"
where TRIM(resid) not like 'RT%'

==> returns 3 records (1, 2 and 5 ) ok, but i do not need preceded
this function for all my queries !


* anotherthing, why this syntax do not works :
SELECT *
FROM "<tmp>"
WHERE NOT (resid LIKE 'RT%' )

wrong syntax SQL ??

thank you for your help

_________________________________________
Mickael WONG Software Engineer
mkw@reshos.com ICQ : 66972000

Per?

--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


"Mickael WONG" <mkw@reshos.com> wrote in message news:3f8c80db@wic040d....
> hi Eivind,
>
> the operator 'NOT LIKE' doesn't work correctly with a secondary index !!!!
> :-(((
>
> below a samples :
> ------------------------------------------- >> create a table with 2
> indexes OK
> create table "<tmp>"
> (
> id int primary key,
> resid varchar(16)
> );
> create index idx on "<tmp>"(resid);
> ------------------------------------------- >> create samples records OK
> insert into "<tmp>" values( 1, '123');
> insert into "<tmp>" values( 2, '234');
> insert into "<tmp>" values( 3, 'RT/100');
> insert into "<tmp>" values( 4, 'RT/110');
> insert into "<tmp>" values( 5, 'R100');
>
> ------------------------------------------- >> query 1 OK
> select *
> from "<tmp>"
> where resid LIKE 'RT%'
>
> ==> return 2 records 4 & 5
>
> ------------------------------------------- >> query 2 NOT OK
> select *
> from "<tmp>"
> where resid NOT LIKE 'RT%
>
> ==> returns NOTHING !!! WHY ??
>
> ------------------------------------------- >> query 3 works with a
TRIM
> function
> select *
> from "<tmp>"
> where TRIM(resid) not like 'RT%'
>
> ==> returns 3 records (1, 2 and 5 ) ok, but i do not need preceded
> this function for all my queries !
>
>
> * anotherthing, why this syntax do not works :
> SELECT *
> FROM "<tmp>"
> WHERE NOT (resid LIKE 'RT%' )
>
> wrong syntax SQL ??
>
> thank you for your help
>
> _________________________________________
> Mickael WONG Software Engineer
> mkw@reshos.com ICQ : 66972000
>
>
>
>
>
>

> select *
> from "<tmp>"
> where resid NOT LIKE 'RT%
>
> ==> returns NOTHING !!! WHY ??

That's a bug. Issue #475. I'll fix this week.
Work-around for now is to prefix SELECT with #I- (disable the use of
indexes).

> * anotherthing, why this syntax do not works :
> SELECT *
> FROM "<tmp>"
> WHERE NOT (resid LIKE 'RT%' )
>
> wrong syntax SQL ??

No, that's another, unrelated bug. (Hey! Stop that! <g>). Issue #476. I'll
fix this one as well. Work-around for now is to prefix SELECT with #S-
(disable algebraic simplifications).

Thanks for the feedback, and sorry for the inconvenience.

- Per

"Mickael WONG" <mkw@reshos.com> wrote in message news:3f8c80db@wic040d....
> hi Eivind,
>
> the operator 'NOT LIKE' doesn't work correctly with a secondary index !!!!
> :-(((
>
> below a samples :
> ------------------------------------------- >> create a table with 2
> indexes OK
> create table "<tmp>"
> (
> id int primary key,
> resid varchar(16)
> );
> create index idx on "<tmp>"(resid);
> ------------------------------------------- >> create samples records OK
> insert into "<tmp>" values( 1, '123');
> insert into "<tmp>" values( 2, '234');
> insert into "<tmp>" values( 3, 'RT/100');
> insert into "<tmp>" values( 4, 'RT/110');
> insert into "<tmp>" values( 5, 'R100');
>
> ------------------------------------------- >> query 1 OK
> select *
> from "<tmp>"
> where resid LIKE 'RT%'
>
> ==> return 2 records 4 & 5
>
> ------------------------------------------- >> query 2 NOT OK
> select *
> from "<tmp>"
> where resid NOT LIKE 'RT%
>
> ==> returns NOTHING !!! WHY ??
>
> ------------------------------------------- >> query 3 works with a
TRIM
> function
> select *
> from "<tmp>"
> where TRIM(resid) not like 'RT%'
>
> ==> returns 3 records (1, 2 and 5 ) ok, but i do not need preceded
> this function for all my queries !
>
>
> * anotherthing, why this syntax do not works :
> SELECT *
> FROM "<tmp>"
> WHERE NOT (resid LIKE 'RT%' )
>
> wrong syntax SQL ??
>
> thank you for your help
>
> _________________________________________
> Mickael WONG Software Engineer
> mkw@reshos.com ICQ : 66972000
>
>
>
>
>
>

yes i know for the #i- but why do not set it as default for compatibility
with other SQL Engine ?

_________________________________________
Mickael WONG Software Engineer
mkw@reshos.com ICQ : 66972000


"Per Larsen" <per.larsenATndxDOTnexusdbDOTcom> a écrit dans le message de
news:3f8d0d43@wic040d....
> > select *
> > from "<tmp>"
> > where resid NOT LIKE 'RT%
> >
> > ==> returns NOTHING !!! WHY ??
>
> That's a bug. Issue #475. I'll fix this week.
> Work-around for now is to prefix SELECT with #I- (disable the use of
> indexes).
>
> > * anotherthing, why this syntax do not works :
> > SELECT *
> > FROM "<tmp>"
> > WHERE NOT (resid LIKE 'RT%' )
> >
> > wrong syntax SQL ??
>
> No, that's another, unrelated bug. (Hey! Stop that! <g>). Issue #476. I'll
> fix this one as well. Work-around for now is to prefix SELECT with #S-
> (disable algebraic simplifications).
>
> Thanks for the feedback, and sorry for the inconvenience.
>
> - Per
>
> "Mickael WONG" <mkw@reshos.com> wrote in message news:3f8c80db@wic040d....
> > hi Eivind,
> >
> > the operator 'NOT LIKE' doesn't work correctly with a secondary index
!!!!
> > :-(((
> >
> > below a samples :
> > ------------------------------------------- >> create a table with 2
> > indexes OK
> > create table "<tmp>"
> > (
> > id int primary key,
> > resid varchar(16)
> > );
> > create index idx on "<tmp>"(resid);
> > ------------------------------------------- >> create samples records
OK
> > insert into "<tmp>" values( 1, '123');
> > insert into "<tmp>" values( 2, '234');
> > insert into "<tmp>" values( 3, 'RT/100');
> > insert into "<tmp>" values( 4, 'RT/110');
> > insert into "<tmp>" values( 5, 'R100');
> >
> > ------------------------------------------- >> query 1 OK
> > select *
> > from "<tmp>"
> > where resid LIKE 'RT%'
> >
> > ==> return 2 records 4 & 5
> >
> > ------------------------------------------- >> query 2 NOT OK
> > select *
> > from "<tmp>"
> > where resid NOT LIKE 'RT%
> >
> > ==> returns NOTHING !!! WHY ??
> >
> > ------------------------------------------- >> query 3 works with a
> TRIM
> > function
> > select *
> > from "<tmp>"
> > where TRIM(resid) not like 'RT%'
> >
> > ==> returns 3 records (1, 2 and 5 ) ok, but i do not need preceded
> > this function for all my queries !
> >
> >
> > * anotherthing, why this syntax do not works :
> > SELECT *
> > FROM "<tmp>"
> > WHERE NOT (resid LIKE 'RT%' )
> >
> > wrong syntax SQL ??
> >
> > thank you for your help
> >
> > _________________________________________
> > Mickael WONG Software Engineer
> > mkw@reshos.com ICQ : 66972000
> >
> >
> >
> >
> >
> >
>
>

Per meant: "use this workaround while you are waiting for the fix". There is
no compatibility issue.

--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


"Mickael WONG" <mkw@reshos.com> wrote in message news:3f8d26c4@wic040d....
> yes i know for the #i- but why do not set it as default for
compatibility
> with other SQL Engine ?
>