Limitations on varstrlst while using LIKE

[sergey]

I am having problems querying varstrlst field using LIKE
We are running Commercial Version 4.00.1009527445 of Dec 28, 2001 (sparc-sun-solaris2.6-64)

It looks like only first 45 bytes of varstrlst field are being looked at.

CREATE TABLE test_table (
id int NOT NULL,
priprod varstrlst,
secprod varstrlst,
info varchar(200)
);

insert into test_table values (1,'P10,P11,P12,P13,P14,P15,P16,P17,','','');
insert into test_table values (2,'P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,','','');
insert into test_table values (3,'P100,P110,P120,P130,P140,P15,P16,P17,P18,P19,P20,P21,P22,','','');
insert into test_table values (4,'P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P23,P24,P25,P26,P27,P16,P17,P18,P19,P20,P21,P22,','','');


SQL 1>select id,priprod from test_table where priprod like 'P21';
id priprod
------------+------------+
2 P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,

Note only one record was returned instead of three

Query
select id,priprod from test_table where priprod like 'P22';

returns no records even though three should be returned

Query
select id,priprod from test_table where priprod like 'P1'

returns all the rows even though only one should be returned.

If I'll build a metamorph index on this field
CREATE METAMORPH INDEX test_table_priprod_idx
ON test_table(priprod);
Everything works as it should, but any new records which were inserted after the index was created are not searchable and I get the following error

SQL 1>select * from test_table where priprod like 'P22';
100 Can't rank FTN type 20 in the function i3dbinsertp
100 Can't rank FTN type 20 in the function i3dbinsertp
100 Can't rank FTN type 20 in the function i3dbinsertp
100 Can't rank FTN type 20 in the function i3dbinsertp
100 Can't rank FTN type 20 in the function i3dbinsertp
id priprod secprod info
------------+------------+------------+------------+
2 P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,
4 P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P23,P24,P25,P26,P27,P16,P17,P18,P19,P20,P21,P22,
5 P99,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P23,P24,P25,P26,P27,P16,P17,P18,P19,P20,P21,P22,
3 P100,P110,P120,P130,P140,P15,P16,P17,P18,P19,P20,P21,P22,

I know that for my examples query can be modified to use
"=" instead of like, but this is not the queries I am running in the production environment.
I have to be able to list multiple items

select id,priprod from test_table where priprod like 'P22,P17,P21 @0';

and also 'In' syntax doesn't seem to work for varstrlst fields
select id,priprod from test_table where priprod in ('P22','P17','P21');

Please Help
Thanks
-Sergey

[John]

Why are you using strlst instead of varchar?

[sergey]

because in certain cases I would want to use
equal operator for the exact match:

priprod='P22'

[mark]

priprod like 'P22'
would be effectively the same thing if priprod were a varchar.

[sergey]

I've also done a couple more tests and found that varchar field is not behaving the way I would expect it to either

the matches are different for indexed rows vs non-indexed

when records are not indexed, query 'P1' matches 'P10'
when records are indexed query 'P1' does NOT match 'P10'

SQL 1>insert into test_table values (13,'','','P10,P11,P12,P13,P14,P15,P16,P17,');
id priprod secprod info
------------+------------+------------+------------+
SQL 1>select info from test_table where info like 'P1';
info
------------+
P10,P11,P12,P13,P14,P15,P16,P17,

As you see above 'P1' matched 'P10'
Creating the index

SQL 1>CREATE METAMORPH INDEX test_table_info_idx ON test_table(info);
SQL 1>select info from test_table where info like 'P1';
info
------------+

No records were returned (this is expected behavior)
inserting another record

SQL 1>insert into test_table values (43,'','','P10,P11,P12,P13,P14,P15,P16,P17,');
id priprod secprod info
------------+------------+------------+------------+
SQL 1>select info from test_table where info like 'P1';
info
------------+
P10,P11,P12,P13,P14,P15,P16,P17,

As you see 'P1' again matched 'P10'

Help!

[mark]

"set langc='[\alnum\' \-]'" before searching should make the unindexed rows behave like the indexed rows.

http://www.thunderstone.com/site/texisman/node150.html

[sergey]

Thank you that worked (though I had to set wordc as well)

I've just discovered that my test_table is corrupted

kdbfchk /cluster/prod/texis/data/bis/test_table.tbl
Checking file /cluster/prod/texis/data/bis/test_table.tbl:
Reading free tree and list
0% 50% 100%
000 ABEND: signal 10 (SIGBUS); exiting

I had another table corrupted this morning as well
the system has plenty of space and I didn't find anything in the logs, texis process has been up since Mar 1st.

I've created test_table today and it had under 20 rows.
Can somebody give me potential reasons, or let me know what else I can look at to make sure this will not happen again (I am sure that the box had plenty of diskspace)?

[John]

Does "select * from test_table" produce the same problems, or is it just kdbfchk?

[sergey]

Just kdbfchk