Conference orarep::nomahs::dbintegrator_public_public

1148.0. "NSDS 3.0E - predicates no longer work" by BROKE::BITHER () Wed Apr 09 1997 11:35

Hi Everyone,

NSDS 3.0E
NSDS 3.0E-1

Certain predicate values no longer work as of NSDS 3.0E.  I've found three 
so far.  Tested both NSDS X3.0-1 and NSDS v3.0D and all the queries work fine.

PROBLEM:

An RMS indexed file with 3 keys defined.  Each field is of datatype text.
The following predicate values return incorrect data:
  1) select * from table where key0='value' - returns no data when
      there is in fact a match.
  2) select * from table where key1='value' - returns no data when
      there is in fact a match.
  3) select * from table where field>'value' - may return incorrect data
      depending on the value of the field.

NSDS 3.0D, NSDS X3.0C-1:
------------------------
The following illustrates what works in older versions of NSDS.  TEST.SQL has
five SQL statements that all work as expected.

SQL> att 'f /type=nsds/path=cdd$compatibility:vessel';
SQL> @test
SQL> select * from vessel;
 C_DATE   HEAT     C_TURN
 970407   L00002   1
 970406   L00004   2
2 rows selected
SQL> select * from vessel where heat='L00002';
 C_DATE   HEAT     C_TURN
 970407   L00002   1
1 row selected
SQL> select * from vessel where c_date='970407';
 C_DATE   HEAT     C_TURN
 970407   L00002   1
1 row selected
SQL> select * from vessel where heat>'L00002';
 C_DATE   HEAT     C_TURN
 970406   L00004   2
1 row selected
SQL> select * from vessel where c_date>'970406';
 C_DATE   HEAT     C_TURN
 970407   L00002   1
1 row selected

NSDS 3.0E:
---------
Using the exact same cdo definitions, rms data file and sql script three
out of the five SQL statments return incorrect results.  The first and last
sql statements are the only ones that work as expected.

(NOTE: For some reason, when I execute an SQL script file using nsds 3.0E,
the sql statements do not show in the output.  So in this case I actually
typed in each select statement by hand so you can see them.  But they do
all exist in test.sql.)

 SQL> select * from vessel;
 C_DATE   HEAT     C_TURN
 970407   L00002   1
 970406   L00004   2
2 rows selected
SQL> select * from vessel where heat='L00002';
0 rows selected
SQL> select * from vessel where c_date='970407';
0 rows selected
SQL> select * from vessel where heat>'L00002';
 C_DATE   HEAT     C_TURN
 970407   L00002   1
 970406   L00004   2
2 rows selected
SQL> select * from vessel where c_date>'970406';
 C_DATE   HEAT     C_TURN
 970407   L00002   1
1 row selected


To recreate the problem:
-----------------------
  Log on to DBS400 as username SQLUSER61.  This system is running 3.0E-1.
  Set default [.jay]
  mcr SQL$
  SQL> attach 'f /type=nsds/path=cdd$compatibility:vessel';
  SQL> @test

To try NSDS v3.0D:
  $ sys$update:vmsinstal nsds_v_md030 (from dka100:[savesets])
  Repeat steps to recreate problem.
  Please restore NSDS v3.0E from dka100:[savesets] when done.

I will need to bug this in a couple of days if there is no resolution.

Thanks a lot, Diane

More info:

1.  Customer is using CDO reader, I'm using CDD so both dictionaries fail.

2.  I tried two new examples:

    Example 1
     Same example only with 3 distinct key names, i.e.
        c_date
        heat
        tom
      instead of:
        c_date
        heat
        c_turn

      This was done because Tom discovered that the key values that should
      be of length 6 are truncated to length 1 (length of c_turn) and that
      is why data not being found.  Maybe names "C_" somehow overwriting/
      interfering with each other.

      Making each column name completely unique did not help.  Same results.

   Example 2
    New example much simpler.  Only one key field.  This all works.

define field name datatype is text size is 20.
define field age datatype is text size is 2.
define field address datatype is text size is 25.

define record kids_record.
 name.
 age.
 address.
end kids_record record.

define rms_database kids_rms.
 record kids_record.
  file_definition
  organization indexed
  format fixed.
  keys.
  key 0
   segment name in kids_record.
  end keys.
 end.

Select = > < on both key and nonkey fields appear to work correctly.

To use these on dbs400, login as sqluser61 and attach to:
  /type=nsds/path=cdd$compatibility:vessel1
  /type=nsds/path=cdd$compatibility:kids
respectively.

Thanks, Diane