Table salted with a float key can’t be invoked or dropped

[Weishiun Tsai]

When a table was created using salt with a float key, the table creation operation was completed successfully. But the table couldn’t be invoked, nor could it be dropped. The invoke statement returned error 1120 and the drop statement returned error 1389. The following output shows that td1 was created using salt with a float key and the errors from invoke and drop. Sd1 was created with the same ddl except for the salting. Invoke and drop work fine on sd1.

This is seen on the datalake v40174 build installed on a workstation.

>>set schema mytest;

--- SQL operation complete.
>>create table td1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c) salt using 2 partitions on (c, b);

--- SQL operation complete.
>>invoke td1;

*** ERROR[1120] Use of float datatype in a partitioning key is not allowed.

--- SQL operation failed with errors.
>>drop table td1;

*** ERROR[1389] Object TRAFODION.MYTEST.TD1 does not exist in Trafodion.

>>create table sd1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c);

--- SQL operation complete.
>>invoke sd1;

-- Definition of Trafodion table TRAFODION.MYTEST.SD1
-- Definition current Fri May 9 03:49:04 2014

  (
    SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , B SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C DOUBLE PRECISION NO DEFAULT NOT NULL NOT
      DROPPABLE
  , D DOUBLE PRECISION DEFAULT NULL
  )

--- SQL operation complete.
>>drop table sd1;

--- SQL operation complete.

[Hans Zeller (hans-zeller)]

We will disable the DDL to create a table that is salted on a floating point value because that could potentially cause data corruption. SeaQuest also doesn't allow float as a partitioning key. The DDL will return this error with the fix:

*** ERROR[1120] Use of float datatype in a partitioning key or salt clause is not allowed.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the datalake v40963 build. This problem has been fixed:

>>set schema mytest30;

--- SQL operation complete.
>>create table td1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c) salt using 2 partitions on (c, b);

--- SQL operation complete.
>>invoke td1;

-- Definition of Trafodion table TRAFODION.MYTEST30.TD1
-- Definition current Wed May 28 03:28:53 2014

  (
    "_SALT_" INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , B SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C DOUBLE PRECISION NO DEFAULT NOT NULL NOT
      DROPPABLE
  , D DOUBLE PRECISION DEFAULT NULL
  )

--- SQL operation complete.
>>drop table td1;

--- SQL operation complete.
>>create table sd1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c);

--- SQL operation complete.
>>invoke sd1;

-- Definition of Trafodion table TRAFODION.MYTEST30.SD1
-- Definition current Wed May 28 03:29:27 2014

  (
    SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , B SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C DOUBLE PRECISION NO DEFAULT NOT NULL NOT
      DROPPABLE
  , D DOUBLE PRECISION DEFAULT NULL
  )

--- SQL operation complete.
>>drop table sd1;

--- SQL operation complete.
>>

[Weishiun Tsai (wei-shiun-tsai)]

Reopen this case. It is decided that even though this does not cause trouble anymore, Trafodion will still disallow using float type as the salt key. The statement will be made to return an error.

[Julie Thai (julie-y-thai)]

We should also disallow salting on inexact numeric datatypes for volatile tables, too.

[Hans Zeller (hans-zeller)]

After an earlier fix by Anoop allowed salting on float columns, I now checked in a fix that returns an error on Jyly 17: https://review.trafodion.org/108

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the 0801_0830 daily build. Create table td1 with a float column salt key now returns an error 1120:

>>set schema mytest;

--- SQL operation complete.
>>
>>create table td1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c) salt using 2 partitions on (c, b);

*** ERROR[1120] Use of approximate numeric datatype (float, real, double precision) in a partitioning key or salt clause is not allowed.

--- SQL operation failed with errors.
>>
>>invoke td1;

*** ERROR[4082] Object TRAFODION.MYTEST.TD1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>drop table td1;

*** ERROR[1389] Object TRAFODION.MYTEST.TD1 does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>create table sd1 (a largeint not null, b smallint not null, c float(10) not null, d double precision) store by (a, b, c);

--- SQL operation complete.
>>invoke sd1;

-- Definition of Trafodion table TRAFODION.MYTEST.SD1
-- Definition current Fri Aug 1 21:04:44 2014

  (
    SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , B SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C DOUBLE PRECISION NO DEFAULT NOT NULL NOT
      DROPPABLE
  , D DOUBLE PRECISION DEFAULT NULL
  )

--- SQL operation complete.
>>
>>drop table sd1;

--- SQL operation complete.