Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1598

LOB: lobtofile functionality are not consistent with syntax/documentation in some cases

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.2-incubating
    • Fix Version/s: None
    • Component/s: sql-exe
    • Labels:
      None

      Description

      1. lobtofile() with the append option to a hdfs file truncates the existing file instead

      The append option is supposed to append to the file if the file already exists. This seems to work fine for a unix file. But s shown here, when doing this on an existing hdfs file, it truncates the file instead.
      (1) create a file 'lob.txt' with some string in it:
      $ echo 'old string' > lob.txt
      $ cat lob.txt

      (2) Copy the file into hdfs:

      $ $MY_SQROOT/sql/local_hadoop/hadoop/bin/hadoop fs -copyFromLocal lob.txt /lobs/lob.txt
      $ $MY_SQROOT/sql/local_hadoop/hadoop/bin/hadoop fs -cat /lobs/lob.txt

      (3) Run the following statements from sqlci:

      control query default TRAF_BLOB_AS_VARCHAR 'OFF';
      control query default TRAF_CLOB_AS_VARCHAR 'OFF';

      create schema mytest12;
      set schema mytest12;

      create table mytable (c clob);
      insert into mytable values (stringtolob('new string'));
      select * from mytable;

      (4) Get the lob handle from the last select statement and replace the following <lob handle> with it. Then run it in the same sqlci:

      extract lobtofile(LOB '<lob handle>', 'hdfs:///lobs/lob.txt', append);

      (5) Verify the content of the file lob.txt:

      $ $MY_SQROOT/sql/local_hadoop/hadoop/bin/hadoop fs -cat /lobs/lob.txt
      2. lobtofile() with default option truncates an existing file to empty after returning error 8442

      The default option is supposed to return an error if the file already exists. It does return error 8442 right now, but it should also leave the existing file intact after the error is returned. It currently truncates the existing file to an empty file.
      (1) create a file 'lob.txt' with some string in it:
      $ echo 'old string' > lob.txt
      $ cat lob.txt

      (2) Run the following statements from sqlci:

      control query default TRAF_BLOB_AS_VARCHAR 'OFF';
      control query default TRAF_CLOB_AS_VARCHAR 'OFF';

      create schema mytest11;
      set schema mytest11;

      create table mytable (c clob);
      insert into mytable values (stringtolob('new string'));
      select * from mytable;

      (3) Get the lob handle from the last select statement and replace the following <lob handle> with it. Then run it in the same sqlci:

      extract lobtofile(LOB '<lob handle>', './lob.txt');

      (4) Verify the content of the file lob.txt:
      $ cat lob.txt

      3. lobtofile() with the create, append option returns error 8442 if the file already exists
      The CREATE,APPEND option is supposed to append if the file exists. But as shown in the following example, it currently returns error 8442 if the file already exists.
      (1) create a file 'lob.txt' with some string in it:
      $ echo 'old string' > lob.txt

      (2) Run the following statements from sqlci:

      control query default TRAF_BLOB_AS_VARCHAR 'OFF';
      control query default TRAF_CLOB_AS_VARCHAR 'OFF';

      create schema mytest10;
      set schema mytest10;

      create table mytable (c clob);
      insert into mytable values (stringtolob('new string'));
      select * from mytable;

      (3) Get the lob handle from the last select statement and replace the following <lob handle> with it. Then run it in the same sqlci:

      extract lobtofile(LOB '<lob handle>', './lob.txt', create, append);

      4. lobtofile() with the truncate option does not return an error when the file does not exist
      The TRUNCATE option is supposed to return an error if the file does not exist. But as shown in the following example, it currently does not return any error and the file gets created.

      (1) Make sure that the file lob.txt does not exist in the current directory.
      (2) Run the following statements from sqlci:

      control query default TRAF_BLOB_AS_VARCHAR 'OFF';
      control query default TRAF_CLOB_AS_VARCHAR 'OFF';

      create schema mytest7;
      set schema mytest7;

      create table mytable (c clob);
      insert into mytable values (stringtolob('test string'));
      select * from mytable;

      (3) Get the lob handle from the last select statement and replace the following <lob handle> with it. Then run it in the same sqlci:

      extract lobtofile(LOB '<lob handle>', './lob.txt', truncate);

      (4) Check the directory again and lob.txt is created while it should not have been.

        Attachments

          Activity

            People

            • Assignee:
              sandhya Sandhya Sundaresan
              Reporter:
              sandhya Sandhya Sundaresan
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: