[jira] [Work started] (TRAFODION-2852) Update Messages Guide for some messages in the range 1200-1299

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2852 started by David Wayne Birdsall.
---
> Update Messages Guide for some messages in the range 1200-1299
> --
>
> Key: TRAFODION-2852
> URL: https://issues.apache.org/jira/browse/TRAFODION-2852
> Project: Apache Trafodion
>  Issue Type: Sub-task
>  Components: documentation
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2851) Update Messages Guide for some messages in the range 1200-1299

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2851?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2851.
---
Resolution: Fixed

Tried to make this a subtask of 2283 but got it wrong. Created another JIRA for 
this once I figured out how to create a subtask.

> Update Messages Guide for some messages in the range 1200-1299
> --
>
> Key: TRAFODION-2851
> URL: https://issues.apache.org/jira/browse/TRAFODION-2851
> Project: Apache Trafodion
>  Issue Type: Task
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2852) Update Messages Guide for some messages in the range 1200-1299

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2852:
---

 Summary: Update Messages Guide for some messages in the range 
1200-1299
 Key: TRAFODION-2852
 URL: https://issues.apache.org/jira/browse/TRAFODION-2852
 Project: Apache Trafodion
  Issue Type: Sub-task
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2851) Update Messages Guide for some messages in the range 1200-1299

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2851:
---

 Summary: Update Messages Guide for some messages in the range 
1200-1299
 Key: TRAFODION-2851
 URL: https://issues.apache.org/jira/browse/TRAFODION-2851
 Project: Apache Trafodion
  Issue Type: Task
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2843) Internal error (or core) on full outer join on an aggregate

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2843?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2843.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Internal error (or core) on full outer join on an aggregate
> ---
>
> Key: TRAFODION-2843
> URL: https://issues.apache.org/jira/browse/TRAFODION-2843
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> The following sqlci script demonstrates the problem. The "prepare x1" fails 
> with an error 7000 (generator internal error) on release builds, and cores on 
> debug builds.
> ?section setup
> drop schema if exists mytest cascade;
> create schema if not exists mytest;
> set schema mytest;
> Create table D03s
> (
> pk int not null not droppable primary key
> , val01 int
> , val02 int
> );
> Create table F01s
> (
>   pk int not null not droppable primary key
> , fk_d01 int not null
> , fk_d02 int not null
> , fk_d03 int not null
> , fk_d04 int not null
> , fk_d05 int not null
> , fk_d06 int not null
> , fk_d07 int not null
> , fk_d08 int not null
> , fk_d09 int not null
> , fk_d10 int not null
> , val01 int
> , val02 int
> , val01_d01 int
> , val02_d01 int
> , val01_d02 int
> , val02_d02 int
> , val01_d03 int
> , val02_d03 int
> ) salt using 8 partitions;
> insert into D03s
> select c1+c2*10+c3*100, c1, c1+c2*10
> from (values(1)) T
> transpose 0,1 as c1
> transpose 0,1 as c2
> transpose 0,1 as c3;
> insert with no rollback into F01s
> select c1+c2*10+c3*100+c4*1000+c5*1+c6*10
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,mod(c1+c2*100+c3*100,200)
>   ,mod(c1,3)
>   ,mod(c1,6)
>   ,mod(c1+c2*10,5)
>   ,c1
>   ,c1
>   ,c1+c2*10
> from (values(1)) T
> transpose 0,1 as c1
> transpose 0,1 as c2
> transpose 0,1 as c3
> transpose 0,1 as c4
> transpose 0 as c5
> transpose 0 as c6
> ;
> ?section testit
> set schema mytest;
> prepare x1 from
> select F01s.val01, TD03.val01
> From F01s 
> full outer join
>   (select D03s.val01,count(D03s.pk)
>   from D03s
>   group by D03s.val01) as TD03(val01,pk) 
> on (TD03.pk=F01s.fk_d03
>   AND TD03.pk>0 );



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2839) compGeneral/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2839 started by David Wayne Birdsall.
---
> compGeneral/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory
> ---
>
> Key: TRAFODION-2839
> URL: https://issues.apache.org/jira/browse/TRAFODION-2839
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The UPDATE STATISTICS incremental code leaves CBF files in the /home/xxx/cbfs 
> directory. These stick around even after tables are deleted. There should be 
> a way to clean these up.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2839) compGeneral/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2839:

Summary: compGeneral/TEST023 leaves a bunch of stuff in /home/xxx/cbfs 
directory  (was: executor/TEST023 leaves a bunch of stuff in /home/xxx/cbfs 
directory)

> compGeneral/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory
> ---
>
> Key: TRAFODION-2839
> URL: https://issues.apache.org/jira/browse/TRAFODION-2839
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The UPDATE STATISTICS incremental code leaves CBF files in the /home/xxx/cbfs 
> directory. These stick around even after tables are deleted. There should be 
> a way to clean these up.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2839) executor/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2839?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16289983#comment-16289983
 ] 

David Wayne Birdsall commented on TRAFODION-2839:
-

A possible solution is to change CQD USTAT_INCREMENTAL_UPDATE_STATISTICS from 
ON to SAMPLE. Doing that avoids computation and storage of CBFs (counting bloom 
filters) for incremental histogram processing. The performance of this 
possibility needs evaluation.

> executor/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory
> 
>
> Key: TRAFODION-2839
> URL: https://issues.apache.org/jira/browse/TRAFODION-2839
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The UPDATE STATISTICS incremental code leaves CBF files in the /home/xxx/cbfs 
> directory. These stick around even after tables are deleted. There should be 
> a way to clean these up.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2843) Internal error (or core) on full outer join on an aggregate

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2843?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16289716#comment-16289716
 ] 

David Wayne Birdsall commented on TRAFODION-2843:
-

The predicate TD03.pk>0 in the ON clause is essential; without it the failure 
does not occur. Full outer join is also essential; a left or right or inner 
join does not incur the failure.

The bug seems to be in logic in Join::preCodeGen that mistakenly thinks the 
TD03.pk>0 predicate can be evaluated before the join. But it cannot, because it 
is an aggregate expression. The problem seems to be that the COUNT(1) > 0 
predicate does not get placed in uncoveredPreds by GroupAttributes::coverTest.

> Internal error (or core) on full outer join on an aggregate
> ---
>
> Key: TRAFODION-2843
> URL: https://issues.apache.org/jira/browse/TRAFODION-2843
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The following sqlci script demonstrates the problem. The "prepare x1" fails 
> with an error 7000 (generator internal error) on release builds, and cores on 
> debug builds.
> ?section setup
> drop schema if exists mytest cascade;
> create schema if not exists mytest;
> set schema mytest;
> Create table D03s
> (
> pk int not null not droppable primary key
> , val01 int
> , val02 int
> );
> Create table F01s
> (
>   pk int not null not droppable primary key
> , fk_d01 int not null
> , fk_d02 int not null
> , fk_d03 int not null
> , fk_d04 int not null
> , fk_d05 int not null
> , fk_d06 int not null
> , fk_d07 int not null
> , fk_d08 int not null
> , fk_d09 int not null
> , fk_d10 int not null
> , val01 int
> , val02 int
> , val01_d01 int
> , val02_d01 int
> , val01_d02 int
> , val02_d02 int
> , val01_d03 int
> , val02_d03 int
> ) salt using 8 partitions;
> insert into D03s
> select c1+c2*10+c3*100, c1, c1+c2*10
> from (values(1)) T
> transpose 0,1 as c1
> transpose 0,1 as c2
> transpose 0,1 as c3;
> insert with no rollback into F01s
> select c1+c2*10+c3*100+c4*1000+c5*1+c6*10
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,c1+c2*10+c3*100
>   ,c1
>   ,c1+c2*10
>   ,mod(c1+c2*100+c3*100,200)
>   ,mod(c1,3)
>   ,mod(c1,6)
>   ,mod(c1+c2*10,5)
>   ,c1
>   ,c1
>   ,c1+c2*10
> from (values(1)) T
> transpose 0,1 as c1
> transpose 0,1 as c2
> transpose 0,1 as c3
> transpose 0,1 as c4
> transpose 0 as c5
> transpose 0 as c6
> ;
> ?section testit
> set schema mytest;
> prepare x1 from
> select F01s.val01, TD03.val01
> From F01s 
> full outer join
>   (select D03s.val01,count(D03s.pk)
>   from D03s
>   group by D03s.val01) as TD03(val01,pk) 
> on (TD03.pk=F01s.fk_d03
>   AND TD03.pk>0 );



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2843) Internal error (or core) on full outer join on an aggregate

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2843:
---

 Summary: Internal error (or core) on full outer join on an 
aggregate
 Key: TRAFODION-2843
 URL: https://issues.apache.org/jira/browse/TRAFODION-2843
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


The following sqlci script demonstrates the problem. The "prepare x1" fails 
with an error 7000 (generator internal error) on release builds, and cores on 
debug builds.

?section setup

drop schema if exists mytest cascade;
create schema if not exists mytest;
set schema mytest;

Create table D03s
(
pk int not null not droppable primary key
, val01 int
, val02 int
);

Create table F01s
(
  pk int not null not droppable primary key
, fk_d01 int not null
, fk_d02 int not null
, fk_d03 int not null
, fk_d04 int not null
, fk_d05 int not null
, fk_d06 int not null
, fk_d07 int not null
, fk_d08 int not null
, fk_d09 int not null
, fk_d10 int not null
, val01 int
, val02 int
, val01_d01 int
, val02_d01 int
, val01_d02 int
, val02_d02 int
, val01_d03 int
, val02_d03 int
) salt using 8 partitions;

insert into D03s
select c1+c2*10+c3*100, c1, c1+c2*10
from (values(1)) T
transpose 0,1 as c1
transpose 0,1 as c2
transpose 0,1 as c3;

insert with no rollback into F01s
select c1+c2*10+c3*100+c4*1000+c5*1+c6*10
  ,c1
  ,c1+c2*10
  ,c1+c2*10+c3*100
  ,c1
  ,c1+c2*10
  ,c1+c2*10+c3*100
  ,c1
  ,c1+c2*10
  ,c1+c2*10+c3*100
  ,c1
  ,c1+c2*10
  ,mod(c1+c2*100+c3*100,200)
  ,mod(c1,3)
  ,mod(c1,6)
  ,mod(c1+c2*10,5)
  ,c1
  ,c1
  ,c1+c2*10
from (values(1)) T
transpose 0,1 as c1
transpose 0,1 as c2
transpose 0,1 as c3
transpose 0,1 as c4
transpose 0 as c5
transpose 0 as c6
;

?section testit

set schema mytest;

prepare x1 from
select F01s.val01, TD03.val01
From F01s 
full outer join
  (select D03s.val01,count(D03s.pk)
  from D03s
  group by D03s.val01) as TD03(val01,pk) 
on (TD03.pk=F01s.fk_d03
  AND TD03.pk>0 );



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2838) Add missing error check in NATableDB::get

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2838?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2838.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Add missing error check in NATableDB::get
> -
>
> Key: TRAFODION-2838
> URL: https://issues.apache.org/jira/browse/TRAFODION-2838
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> In NATableDB::get, there is a call to the NATable constructor that lacks an 
> error check afterward. The NATable constructor calls several methods that 
> have error returns, leaving the NATable object having sometimes incomplete 
> state. In particular, if createNAFileSets fails, the clustering information 
> will be missing. This will cause NATable::updateExtTableAttrs to abend when 
> it then dereferences a null pointer.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2827) Turn CQD HBASE_UPDATE_COSTING 'ON' by default

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2827.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Turn CQD HBASE_UPDATE_COSTING 'ON' by default
> -
>
> Key: TRAFODION-2827
> URL: https://issues.apache.org/jira/browse/TRAFODION-2827
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> There are presently two sets of stubbed costing code for HBase updates in the 
> Optimizer. The original set simply gives a zero cost to any update node. This 
> is the original stub, and is the one executed by default. The second stub is 
> a copy of the delete costing code, and is executed if CQD 
> HBASE_UPDATE_COSTING is 'ON'. This is much better, because it at least 
> assigns non-zero costs to update nodes. One can get parallel plans for larger 
> update statements using this code, while all plans for the original stub are 
> serial. It isn't perfect, because the theory of operation for update is a bit 
> different than delete, but it should be much better for all but the most 
> trivial update statements.
> As we are beginning to see applications using Trafodion that have larger 
> update statements, it makes sense to change the default for CQD 
> HBASE_UPDATE_COSTING to 'ON' now.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2840) ORDER BY clause on a view circumvents [first n] updatability check

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2840:
---

 Summary: ORDER BY clause on a view circumvents [first n] 
updatability check
 Key: TRAFODION-2840
 URL: https://issues.apache.org/jira/browse/TRAFODION-2840
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


The following script fails:

>>create table t1 (a int not null, b int, primary key (a));

--- SQL operation complete.
>>
>>insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

--- 6 row(s) inserted.
>>
>>create view v1 as select [first 5] * from t1 order by a;

--- SQL operation complete.
>>
>>create view v2 as select [first 5] * from t1;

--- SQL operation complete.
>>
>>update v1 set b = 6;

--- 6 row(s) updated.
>> -- should fail; v1 should be non-updatable
>>
>>update v2 set b = 7;

*** ERROR[4028] Table or view TRAFODION.SEABASE.V2 is not updatable.

*** ERROR[8822] The statement was not prepared.

>>-- does fail; v2 is non-updatable (correctly)
>>

It seems the presence of the ORDER BY clause in the view definition circumvents 
the [first n] updatability check.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2839) executor/TEST023 leaves a bunch of stuff in /home/xxx/cbfs directory

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2839:
---

 Summary: executor/TEST023 leaves a bunch of stuff in 
/home/xxx/cbfs directory
 Key: TRAFODION-2839
 URL: https://issues.apache.org/jira/browse/TRAFODION-2839
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


The UPDATE STATISTICS incremental code leaves CBF files in the /home/xxx/cbfs 
directory. These stick around even after tables are deleted. There should be a 
way to clean these up.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2838) Add missing error check in NATableDB::get

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2838:
---

 Summary: Add missing error check in NATableDB::get
 Key: TRAFODION-2838
 URL: https://issues.apache.org/jira/browse/TRAFODION-2838
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


In NATableDB::get, there is a call to the NATable constructor that lacks an 
error check afterward. The NATable constructor calls several methods that have 
error returns, leaving the NATable object having sometimes incomplete state. In 
particular, if createNAFileSets fails, the clustering information will be 
missing. This will cause NATable::updateExtTableAttrs to abend when it then 
dereferences a null pointer.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2835) Script to help resolve merge conflicts particularly in regression test results

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2835.
-
   Resolution: Implemented
Fix Version/s: 2.3-incubating

> Script to help resolve merge conflicts particularly in regression test results
> --
>
> Key: TRAFODION-2835
> URL: https://issues.apache.org/jira/browse/TRAFODION-2835
> Project: Apache Trafodion
>  Issue Type: New Feature
>  Components: dev-environment
>Affects Versions: any
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
> Fix For: 2.3-incubating
>
>
> Frequently when merging changes between branches, one has to confront the 
> merging of regression test result files. These files often are loaded with 
> incidental information such as timestamps on DDL definitions or identifiers 
> associated with plans or object UIDs and so on, information that varies on 
> each test run. When a test has been modified on both branches, this 
> incidental information inevitably shows up as merge conflicts. In this case 
> it does not matter which we pick in the merged result though for 
> consistency's sake it is best to choose that from just one or the other 
> branch.
> A script that edits a merged file removing the HEAD part of the conflict 
> would speed this tedious work up greatly in some cases.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2835) Script to help resolve merge conflicts particularly in regression test results

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2835:

Description: 
Frequently when merging changes between branches, one has to confront the 
merging of regression test result files. These files often are loaded with 
incidental information such as timestamps on DDL definitions or identifiers 
associated with plans or object UIDs and so on, information that varies on each 
test run. When a test has been modified on both branches, this incidental 
information inevitably shows up as merge conflicts. In this case it does not 
matter which we pick in the merged result though for consistency's sake it is 
best to choose that from just one or the other branch.

A script that edits a merged file removing the HEAD part of the conflict would 
speed this tedious work up greatly in some cases.

> Script to help resolve merge conflicts particularly in regression test results
> --
>
> Key: TRAFODION-2835
> URL: https://issues.apache.org/jira/browse/TRAFODION-2835
> Project: Apache Trafodion
>  Issue Type: New Feature
>  Components: dev-environment
>Affects Versions: any
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> Frequently when merging changes between branches, one has to confront the 
> merging of regression test result files. These files often are loaded with 
> incidental information such as timestamps on DDL definitions or identifiers 
> associated with plans or object UIDs and so on, information that varies on 
> each test run. When a test has been modified on both branches, this 
> incidental information inevitably shows up as merge conflicts. In this case 
> it does not matter which we pick in the merged result though for 
> consistency's sake it is best to choose that from just one or the other 
> branch.
> A script that edits a merged file removing the HEAD part of the conflict 
> would speed this tedious work up greatly in some cases.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2835) Script to help resolve merge conflicts particularly in regression test results

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2835:
---

 Summary: Script to help resolve merge conflicts particularly in 
regression test results
 Key: TRAFODION-2835
 URL: https://issues.apache.org/jira/browse/TRAFODION-2835
 Project: Apache Trafodion
  Issue Type: New Feature
  Components: dev-environment
Affects Versions: any
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
Priority: Minor






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2771) Add new Trafodion LOB Guide to documentation build and web site

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2771:

Fix Version/s: 2.3-incubating

> Add new Trafodion LOB Guide to documentation build and web site
> ---
>
> Key: TRAFODION-2771
> URL: https://issues.apache.org/jira/browse/TRAFODION-2771
> Project: Apache Trafodion
>  Issue Type: New Feature
>  Components: documentation
>Affects Versions: 2.3-incubating
> Environment: N/A
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> An entry needs to be added to the incubator-trafodion/pom.xml file so that 
> the new Trafodion LOB Guide will be built in the documentation build.
> Once the guide does get built, we need a change to the Web page to link to 
> it. The file that needs to change is documentation.md in folder 
> incubator-trafodion/docs/src/site/markdown. 



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2822) MERGE on a view defined using [first n] or [any n] does not work

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2822?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2822.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> MERGE on a view defined using [first n] or [any n] does not work
> 
>
> Key: TRAFODION-2822
> URL: https://issues.apache.org/jira/browse/TRAFODION-2822
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> The following script produces incorrect results:
> drop schema if exists mytest cascade;
> create schema mytest;
> set schema mytest;
> create table t (c1 int not null primary key, c2 int);
> insert into t values (1,1),(2,2),(3,3);
> create view v1 as select [first 10] * from t;
> create view v2 as select [any 10] * from t;
> prepare x1 from merge into v1 on c1=-1 when not matched then insert values 
> (5,5);
> explain options 'f' x1;
> execute x1;
> prepare x2 from merge into v2 on c1=-1 when not matched then insert values 
> (6,6);
> explain options 'f' x2;
> execute x2;
> prepare x3 from merge into t on c1=-1 when not matched then insert values 
> (4,4);
> explain options 'f' x3;
> execute x3;
> select * from v1 order by 1;
> select * from v2 order by 1;
> select * from t order by 1;
> The SELECTs return rows (1,1), (2, 2), (3, 3), (4, 4), which suggests that 
> the INSERT action of statements x1 and x2 did not happen when it should.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2819) Drop table fails with error 4247 when certain CQDs are set

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2819:

Fix Version/s: 2.3-incubating

> Drop table fails with error 4247 when certain CQDs are set
> --
>
> Key: TRAFODION-2819
> URL: https://issues.apache.org/jira/browse/TRAFODION-2819
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> Drop table fails with error 4247 in the following scenario:
> >>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '200';
> --- SQL operation complete.
> >>cqd TRAF_MAX_CHARACTER_COL_LENGTH '200';
> --- SQL operation complete.
> >>
> >>create table my_traf_line (
> +>l_orderkey int not null not droppable,
> +>l_partkey int not null not droppable,
> +>l_suppkey int not null not droppable,
> +>l_linenumber int not null not droppable,
> +>l_quantity numeric(12,2) not null not droppable,
> +>l_extendedprice numeric(12,2) not null not droppable,
> +>l_discount numeric(12,2) not null not droppable,
> +>l_tax numeric(12,2) not null not droppable,
> +>l_returnflag char(1) not null not droppable,
> +>l_linestatus char(1) not null not droppable,
> +>l_shipdate date not null not droppable,
> +>l_commitdate date not null not droppable,
> +>l_receiptdate date not null not droppable,
> +>l_shipinstruct char(25) not null not droppable,
> +>l_shipmode char(10) not null not droppable,
> +>l_comment varchar(44) not null not droppable,
> +>primary key (l_shipdate asc, l_orderkey asc, l_linenumber asc) not 
> droppable)
> +>salt using 8 partitions;
> --- SQL operation complete.
> >>
> >>drop table my_traf_line cascade;
> *** ERROR[4247] Specified size in bytes (500) exceeds the maximum size 
> allowed (200) for column LOW_VALUE.
> *** ERROR[4082] Object TRAFODION.SCH.SB_HISTOGRAMS does not exist or is 
> inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2827) Turn CQD HBASE_UPDATE_COSTING 'ON' by default

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2827:
---

 Summary: Turn CQD HBASE_UPDATE_COSTING 'ON' by default
 Key: TRAFODION-2827
 URL: https://issues.apache.org/jira/browse/TRAFODION-2827
 Project: Apache Trafodion
  Issue Type: Improvement
  Components: sql-cmp
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


There are presently two sets of stubbed costing code for HBase updates in the 
Optimizer. The original set simply gives a zero cost to any update node. This 
is the original stub, and is the one executed by default. The second stub is a 
copy of the delete costing code, and is executed if CQD HBASE_UPDATE_COSTING is 
'ON'. This is much better, because it at least assigns non-zero costs to update 
nodes. One can get parallel plans for larger update statements using this code, 
while all plans for the original stub are serial. It isn't perfect, because the 
theory of operation for update is a bit different than delete, but it should be 
much better for all but the most trivial update statements.

As we are beginning to see applications using Trafodion that have larger update 
statements, it makes sense to change the default for CQD HBASE_UPDATE_COSTING 
to 'ON' now.




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2822) MERGE on a view defined using [first n] or [any n] does not work

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16269565#comment-16269565
 ] 

David Wayne Birdsall commented on TRAFODION-2822:
-

There are two issues that come to mind with this particular case.

1. Should [first n] / [any n] views be updatable at all? One can make a strong 
case that they should not: INSERT has clear semantics though the row inserted 
might not show up in a subsequent select on the view. UPDATE and DELETE are 
non-deterministic. Their action depends on what rows make the lucky [first n] / 
[any n] cut. An industry survey suggests that updatable [first n] / [any n] 
views are not common, and when allowed have non-trivial restrictions. So, 
perhaps we should change Trafodion to make such views non-updatable.

2. The reason that the MERGE fails is that view composition is changing a 
"merge" query node into a tree (tuple_flow (topn (scan)) (merge)). That is, the 
scan activity is separated out from the merge node. The MERGE run-time 
implementation requires that the scan be done in the merge node itself; the not 
matched semantics are triggered from there. So, if the tuple_flow 
transformation happens in the compiler, the merge node never gets activated at 
run time when the scan is empty. So, the insert logic never gets triggered. So, 
the issue here is, are there other circumstances in the compiler (other than 
[first n] / [any n] view composition) that cause this transformation to take 
place? If so, those should be corrected.

> MERGE on a view defined using [first n] or [any n] does not work
> 
>
> Key: TRAFODION-2822
> URL: https://issues.apache.org/jira/browse/TRAFODION-2822
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The following script produces incorrect results:
> drop schema if exists mytest cascade;
> create schema mytest;
> set schema mytest;
> create table t (c1 int not null primary key, c2 int);
> insert into t values (1,1),(2,2),(3,3);
> create view v1 as select [first 10] * from t;
> create view v2 as select [any 10] * from t;
> prepare x1 from merge into v1 on c1=-1 when not matched then insert values 
> (5,5);
> explain options 'f' x1;
> execute x1;
> prepare x2 from merge into v2 on c1=-1 when not matched then insert values 
> (6,6);
> explain options 'f' x2;
> execute x2;
> prepare x3 from merge into t on c1=-1 when not matched then insert values 
> (4,4);
> explain options 'f' x3;
> execute x3;
> select * from v1 order by 1;
> select * from v2 order by 1;
> select * from t order by 1;
> The SELECTs return rows (1,1), (2, 2), (3, 3), (4, 4), which suggests that 
> the INSERT action of statements x1 and x2 did not happen when it should.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2822) MERGE on a view defined using [first n] or [any n] does not work

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2822?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2822:

Description: 
The following script produces incorrect results:

drop schema if exists mytest cascade;
create schema mytest;
set schema mytest;

create table t (c1 int not null primary key, c2 int);
insert into t values (1,1),(2,2),(3,3);

create view v1 as select [first 10] * from t;
create view v2 as select [any 10] * from t;

prepare x1 from merge into v1 on c1=-1 when not matched then insert values 
(5,5);
explain options 'f' x1;
execute x1;

prepare x2 from merge into v2 on c1=-1 when not matched then insert values 
(6,6);
explain options 'f' x2;
execute x2;

prepare x3 from merge into t on c1=-1 when not matched then insert values (4,4);
explain options 'f' x3;
execute x3;

select * from v1 order by 1;
select * from v2 order by 1;
select * from t order by 1;

The SELECTs return rows (1,1), (2, 2), (3, 3), (4, 4), which suggests that the 
INSERT action of statements x1 and x2 did not happen when it should.


> MERGE on a view defined using [first n] or [any n] does not work
> 
>
> Key: TRAFODION-2822
> URL: https://issues.apache.org/jira/browse/TRAFODION-2822
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The following script produces incorrect results:
> drop schema if exists mytest cascade;
> create schema mytest;
> set schema mytest;
> create table t (c1 int not null primary key, c2 int);
> insert into t values (1,1),(2,2),(3,3);
> create view v1 as select [first 10] * from t;
> create view v2 as select [any 10] * from t;
> prepare x1 from merge into v1 on c1=-1 when not matched then insert values 
> (5,5);
> explain options 'f' x1;
> execute x1;
> prepare x2 from merge into v2 on c1=-1 when not matched then insert values 
> (6,6);
> explain options 'f' x2;
> execute x2;
> prepare x3 from merge into t on c1=-1 when not matched then insert values 
> (4,4);
> explain options 'f' x3;
> execute x3;
> select * from v1 order by 1;
> select * from v2 order by 1;
> select * from t order by 1;
> The SELECTs return rows (1,1), (2, 2), (3, 3), (4, 4), which suggests that 
> the INSERT action of statements x1 and x2 did not happen when it should.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2822) MERGE on a view defined using [first n] or [any n] does not work

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2822:
---

 Summary: MERGE on a view defined using [first n] or [any n] does 
not work
 Key: TRAFODION-2822
 URL: https://issues.apache.org/jira/browse/TRAFODION-2822
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2819) Drop table fails with error 4247 when certain CQDs are set

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2819.
-
Resolution: Fixed

> Drop table fails with error 4247 when certain CQDs are set
> --
>
> Key: TRAFODION-2819
> URL: https://issues.apache.org/jira/browse/TRAFODION-2819
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> Drop table fails with error 4247 in the following scenario:
> >>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '200';
> --- SQL operation complete.
> >>cqd TRAF_MAX_CHARACTER_COL_LENGTH '200';
> --- SQL operation complete.
> >>
> >>create table my_traf_line (
> +>l_orderkey int not null not droppable,
> +>l_partkey int not null not droppable,
> +>l_suppkey int not null not droppable,
> +>l_linenumber int not null not droppable,
> +>l_quantity numeric(12,2) not null not droppable,
> +>l_extendedprice numeric(12,2) not null not droppable,
> +>l_discount numeric(12,2) not null not droppable,
> +>l_tax numeric(12,2) not null not droppable,
> +>l_returnflag char(1) not null not droppable,
> +>l_linestatus char(1) not null not droppable,
> +>l_shipdate date not null not droppable,
> +>l_commitdate date not null not droppable,
> +>l_receiptdate date not null not droppable,
> +>l_shipinstruct char(25) not null not droppable,
> +>l_shipmode char(10) not null not droppable,
> +>l_comment varchar(44) not null not droppable,
> +>primary key (l_shipdate asc, l_orderkey asc, l_linenumber asc) not 
> droppable)
> +>salt using 8 partitions;
> --- SQL operation complete.
> >>
> >>drop table my_traf_line cascade;
> *** ERROR[4247] Specified size in bytes (500) exceeds the maximum size 
> allowed (200) for column LOW_VALUE.
> *** ERROR[4082] Object TRAFODION.SCH.SB_HISTOGRAMS does not exist or is 
> inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2819) Drop table fails with error 4247 when certain CQDs are set

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2819:

Description: 
Drop table fails with error 4247 in the following scenario:

>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '200';

--- SQL operation complete.
>>cqd TRAF_MAX_CHARACTER_COL_LENGTH '200';

--- SQL operation complete.
>>
>>create table my_traf_line (
+>l_orderkey int not null not droppable,
+>l_partkey int not null not droppable,
+>l_suppkey int not null not droppable,
+>l_linenumber int not null not droppable,
+>l_quantity numeric(12,2) not null not droppable,
+>l_extendedprice numeric(12,2) not null not droppable,
+>l_discount numeric(12,2) not null not droppable,
+>l_tax numeric(12,2) not null not droppable,
+>l_returnflag char(1) not null not droppable,
+>l_linestatus char(1) not null not droppable,
+>l_shipdate date not null not droppable,
+>l_commitdate date not null not droppable,
+>l_receiptdate date not null not droppable,
+>l_shipinstruct char(25) not null not droppable,
+>l_shipmode char(10) not null not droppable,
+>l_comment varchar(44) not null not droppable,
+>primary key (l_shipdate asc, l_orderkey asc, l_linenumber asc) not droppable)
+>salt using 8 partitions;

--- SQL operation complete.
>>
>>drop table my_traf_line cascade;

*** ERROR[4247] Specified size in bytes (500) exceeds the maximum size allowed 
(200) for column LOW_VALUE.

*** ERROR[4082] Object TRAFODION.SCH.SB_HISTOGRAMS does not exist or is 
inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.
>>


> Drop table fails with error 4247 when certain CQDs are set
> --
>
> Key: TRAFODION-2819
> URL: https://issues.apache.org/jira/browse/TRAFODION-2819
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> Drop table fails with error 4247 in the following scenario:
> >>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '200';
> --- SQL operation complete.
> >>cqd TRAF_MAX_CHARACTER_COL_LENGTH '200';
> --- SQL operation complete.
> >>
> >>create table my_traf_line (
> +>l_orderkey int not null not droppable,
> +>l_partkey int not null not droppable,
> +>l_suppkey int not null not droppable,
> +>l_linenumber int not null not droppable,
> +>l_quantity numeric(12,2) not null not droppable,
> +>l_extendedprice numeric(12,2) not null not droppable,
> +>l_discount numeric(12,2) not null not droppable,
> +>l_tax numeric(12,2) not null not droppable,
> +>l_returnflag char(1) not null not droppable,
> +>l_linestatus char(1) not null not droppable,
> +>l_shipdate date not null not droppable,
> +>l_commitdate date not null not droppable,
> +>l_receiptdate date not null not droppable,
> +>l_shipinstruct char(25) not null not droppable,
> +>l_shipmode char(10) not null not droppable,
> +>l_comment varchar(44) not null not droppable,
> +>primary key (l_shipdate asc, l_orderkey asc, l_linenumber asc) not 
> droppable)
> +>salt using 8 partitions;
> --- SQL operation complete.
> >>
> >>drop table my_traf_line cascade;
> *** ERROR[4247] Specified size in bytes (500) exceeds the maximum size 
> allowed (200) for column LOW_VALUE.
> *** ERROR[4082] Object TRAFODION.SCH.SB_HISTOGRAMS does not exist or is 
> inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2819) Drop table fails with error 4247 when certain CQDs are set

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2819:
---

 Summary: Drop table fails with error 4247 when certain CQDs are set
 Key: TRAFODION-2819
 URL: https://issues.apache.org/jira/browse/TRAFODION-2819
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2813) Salting + heuristics prevent MDAM plan on base table from being considered

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2813.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Salting + heuristics prevent MDAM plan on base table from being considered
> --
>
> Key: TRAFODION-2813
> URL: https://issues.apache.org/jira/browse/TRAFODION-2813
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> When a salted table also has salted indexes, it may happen that a query that 
> has a good MDAM plan on the base table gets a full scan on a salted index 
> instead.
> The problem is that there is a defective heuristic that rules out base table 
> access before we get to the costing code. So the costing code only sees one 
> access path, namely the index, and (correctly) picks a full scan on it.
> The script below reproduces the problem:
> ?section setup
> drop table if exists trafodion.seabase.t1 cascade;
> CREATE TABLE TRAFODION.SEABASE.T1
>   ( 
> key1   NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , key2  VARCHAR(64) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , key3   TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , nonkey   VARCHAR(1024) CHARACTER SET ISO88591
>   COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>ON (key2)
>   DIVISION BY (DATE_TRUNC('HOUR',key3)
>  NAMED AS ("_DIVISION_1_"))
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> upsert using load into TRAFODION.SEABASE.T1
> select 5 + c0,
>  'user ' || cast(c5 + 10*c0 + 100*c1 + 1000*c2 + 1*c3 as varchar(20)),
>  timestamp '2017-07-01 12:00:00.00' + cast(c1 + 10*c2 + 100*c3 + 1000*c4 
> + 1000*c5 as interval minute(6)),
>  'valid prod ' || cast(7*c3 + 4*c4 + 11*c2 as varchar(20))
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table TRAFODION.SEABASE.T1 on every column;
> CREATE INDEX T1_IDX1 ON TRAFODION.SEABASE.T1
>   ( 
> key2 ASC
>   , key3 ASC
>   )
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
>  SALT LIKE TABLE 
> ;
> ?section queries
> prepare s1 from
> SELECT key3, key2 FROM trafodion.seabase.t1 WHERE key1 = 50001 AND key3 
> >= to_date(cast (2017070508 as char(20)),'MMDDHH24MISS') AND key3 
> <= to_date(cast (20170705085959 as char(20)),'MMDDHH24MISS') ;
> explain s1;
> In this example, S1 gets full scans on the index, even though an MDAM plan on 
> the base table would be far more efficient.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2814) Error message 2243 needs updating

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2814.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Error message 2243 needs updating
> -
>
> Key: TRAFODION-2814
> URL: https://issues.apache.org/jira/browse/TRAFODION-2814
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-general
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
> Fix For: 2.3-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2814) Error message 2243 needs updating

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2814?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16261672#comment-16261672
 ] 

David Wayne Birdsall commented on TRAFODION-2814:
-

Error message 2243 makes mention of predecessor products. It should be updated 
to mention Trafodion equivalents.

> Error message 2243 needs updating
> -
>
> Key: TRAFODION-2814
> URL: https://issues.apache.org/jira/browse/TRAFODION-2814
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-general
>Affects Versions: 2.3-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2814) Error message 2243 needs updating

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2814:
---

 Summary: Error message 2243 needs updating
 Key: TRAFODION-2814
 URL: https://issues.apache.org/jira/browse/TRAFODION-2814
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-general
Affects Versions: 2.3-incubating
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
Priority: Minor






--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2813) Salting + heuristics prevent MDAM plan on base table from being considered

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16260009#comment-16260009
 ] 

David Wayne Birdsall commented on TRAFODION-2813:
-

To fix this issue, I added code to IndexProperty::compareIndexPromise to ignore 
the leading "_SALT_" column when comparing leading columns. The "_SALT_" column 
is ignored if both indexes have it.

> Salting + heuristics prevent MDAM plan on base table from being considered
> --
>
> Key: TRAFODION-2813
> URL: https://issues.apache.org/jira/browse/TRAFODION-2813
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When a salted table also has salted indexes, it may happen that a query that 
> has a good MDAM plan on the base table gets a full scan on a salted index 
> instead.
> The problem is that there is a defective heuristic that rules out base table 
> access before we get to the costing code. So the costing code only sees one 
> access path, namely the index, and (correctly) picks a full scan on it.
> The script below reproduces the problem:
> ?section setup
> drop table if exists trafodion.seabase.t1 cascade;
> CREATE TABLE TRAFODION.SEABASE.T1
>   ( 
> key1   NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , key2  VARCHAR(64) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , key3   TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , nonkey   VARCHAR(1024) CHARACTER SET ISO88591
>   COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>ON (key2)
>   DIVISION BY (DATE_TRUNC('HOUR',key3)
>  NAMED AS ("_DIVISION_1_"))
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> upsert using load into TRAFODION.SEABASE.T1
> select 5 + c0,
>  'user ' || cast(c5 + 10*c0 + 100*c1 + 1000*c2 + 1*c3 as varchar(20)),
>  timestamp '2017-07-01 12:00:00.00' + cast(c1 + 10*c2 + 100*c3 + 1000*c4 
> + 1000*c5 as interval minute(6)),
>  'valid prod ' || cast(7*c3 + 4*c4 + 11*c2 as varchar(20))
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table TRAFODION.SEABASE.T1 on every column;
> CREATE INDEX T1_IDX1 ON TRAFODION.SEABASE.T1
>   ( 
> key2 ASC
>   , key3 ASC
>   )
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
>  SALT LIKE TABLE 
> ;
> ?section queries
> prepare s1 from
> SELECT key3, key2 FROM trafodion.seabase.t1 WHERE key1 = 50001 AND key3 
> >= to_date(cast (2017070508 as char(20)),'MMDDHH24MISS') AND key3 
> <= to_date(cast (20170705085959 as char(20)),'MMDDHH24MISS') ;
> explain s1;
> In this example, S1 gets full scans on the index, even though an MDAM plan on 
> the base table would be far more efficient.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2813) Salting + heuristics prevent MDAM plan on base table from being considered

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16259908#comment-16259908
 ] 

David Wayne Birdsall commented on TRAFODION-2813:
-

I've debugged this. Here are my findings:

There is a layer of heuristics that tries to reduce the number of indexes that 
the Optimizer will consider during its costing phases. I will guess these 
heuristics were added in Neoview days when compilation time was a concern. The 
layer begins at Scan::addIndexInfo (optimizer/RelExpr.cpp). It analyzes the 
available indexes for such things as whether they cover all the required 
columns (so we can do index-only access), whether the index satisfies a 
required order, and so on. When INDEX_ELIMINATION_LEVEL is AGGRESSIVE, it also 
tries to prune out indexes that begin with the same column, keeping only the 
one with the smallest I/O. The latter logic is in 
IndexProperty::updatePossibleIndexes (optimizer/IndexDesc.cpp) and 
IndexProperty::compareIndexPromise (optimizer/IndexDesc.cpp). The latter 
routine has a very curious check:

// currently it is the same for indexOnlyScans ans alternateIndexScans.
// If index key starts from the same (single column) then the smaller index 
// (having smaller KbPerVolume attribute) is MORE promising, the bigger 
index
// is LESS promising, and the same index size has the SAME promise.


const IndexDesc * index = getIndexDesc();
const IndexDesc * otherIndex = ixProp->getIndexDesc();
if ( ((IndexColumn 
*)(index->getIndexKey()[0]).getItemExpr())->getDefinition() != 
 ((IndexColumn 
*)(otherIndex->getIndexKey()[0]).getItemExpr())->getDefinition() )

  return INCOMPATIBLE;

The goal of this code appears to be to keep around a representative of each 
class of indexes that begin on a particular column. So, for example, if the 
base table had primary key A, B, C, and we had an index B, C, A, this logic 
would keep both. Though if there were two indexes, B, A, C and B, C, A, this 
code would only keep one of them.

That code was written before we had salting and divisioning, however. In the 
case of Q3, both the base table and the index begin with "_SALT_", so this "if" 
was not taken. Logic after this attempts to compute how much I/O would be done 
through each index, and gets it wrong, and we keep the wrong index.

So, I think this code should be changed so we skip any matching leading salt or 
divisioning columns before doing the comparison. That will get us back to the 
original intent of this code. But this is a tentative conclusion; this 
particular code is new to me so there may be a better solution.

As a result of this analysis, I found a work-around: By setting CQD 
INDEX_ELIMINATION_LEVEL to 'MAXIMUM', the pruning heuristic is bypassed, the 
costing code considers both base table and index paths, and correctly picks an 
efficient MDAM plan on the base table. (The default for that CQD is 
'AGGRESSIVE'.)

> Salting + heuristics prevent MDAM plan on base table from being considered
> --
>
> Key: TRAFODION-2813
> URL: https://issues.apache.org/jira/browse/TRAFODION-2813
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When a salted table also has salted indexes, it may happen that a query that 
> has a good MDAM plan on the base table gets a full scan on a salted index 
> instead.
> The problem is that there is a defective heuristic that rules out base table 
> access before we get to the costing code. So the costing code only sees one 
> access path, namely the index, and (correctly) picks a full scan on it.
> The script below reproduces the problem:
> ?section setup
> drop table if exists trafodion.seabase.t1 cascade;
> CREATE TABLE TRAFODION.SEABASE.T1
>   ( 
> key1   NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , key2  VARCHAR(64) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , key3   TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , nonkey   VARCHAR(1024) CHARACTER SET ISO88591
>   COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>ON (key2)
>   DIVISION BY (DATE_TRUNC('HOUR',key3)
>  NAMED AS ("_DIVISION_1_"))
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> upsert using load into TRAFODION.SEABASE.T1
> select 5 + c0,
>  'user ' || 

[jira] [Created] (TRAFODION-2813) Salting + heuristics prevent MDAM plan on base table from being considered

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2813:
---

 Summary: Salting + heuristics prevent MDAM plan on base table from 
being considered
 Key: TRAFODION-2813
 URL: https://issues.apache.org/jira/browse/TRAFODION-2813
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


When a salted table also has salted indexes, it may happen that a query that 
has a good MDAM plan on the base table gets a full scan on a salted index 
instead.

The problem is that there is a defective heuristic that rules out base table 
access before we get to the costing code. So the costing code only sees one 
access path, namely the index, and (correctly) picks a full scan on it.

The script below reproduces the problem:

?section setup

drop table if exists trafodion.seabase.t1 cascade;

CREATE TABLE TRAFODION.SEABASE.T1
  ( 
key1   NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
  DROPPABLE NOT SERIALIZED
  , key2  VARCHAR(64) CHARACTER SET ISO88591 COLLATE
  DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , key3   TIMESTAMP(6) NO DEFAULT NOT NULL NOT
  DROPPABLE NOT SERIALIZED
  , nonkey   VARCHAR(1024) CHARACTER SET ISO88591
  COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
  )
  SALT USING 4 PARTITIONS
   ON (key2)
  DIVISION BY (DATE_TRUNC('HOUR',key3)
 NAMED AS ("_DIVISION_1_"))
 ATTRIBUTES ALIGNED FORMAT 
  HBASE_OPTIONS 
  ( 
DATA_BLOCK_ENCODING = 'FAST_DIFF',
MEMSTORE_FLUSH_SIZE = '1073741824' 
  ) 
;

upsert using load into TRAFODION.SEABASE.T1
select 5 + c0,
 'user ' || cast(c5 + 10*c0 + 100*c1 + 1000*c2 + 1*c3 as varchar(20)),
 timestamp '2017-07-01 12:00:00.00' + cast(c1 + 10*c2 + 100*c3 + 1000*c4 + 
1000*c5 as interval minute(6)),
 'valid prod ' || cast(7*c3 + 4*c4 + 11*c2 as varchar(20))
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c0
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5;

update statistics for table TRAFODION.SEABASE.T1 on every column;

CREATE INDEX T1_IDX1 ON TRAFODION.SEABASE.T1
  ( 
key2 ASC
  , key3 ASC
  )
 ATTRIBUTES ALIGNED FORMAT 
  HBASE_OPTIONS 
  ( 
DATA_BLOCK_ENCODING = 'FAST_DIFF',
MEMSTORE_FLUSH_SIZE = '1073741824' 
  ) 
 SALT LIKE TABLE 
;

?section queries

prepare s1 from
SELECT key3, key2 FROM trafodion.seabase.t1 WHERE key1 = 50001 AND key3 
>= to_date(cast (2017070508 as char(20)),'MMDDHH24MISS') AND key3 
<= to_date(cast (20170705085959 as char(20)),'MMDDHH24MISS') ;

explain s1;

In this example, S1 gets full scans on the index, even though an MDAM plan on 
the base table would be far more efficient.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2799) Transactions description on Trafodion Architecture page is out of date

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2799?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2799.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Transactions description on Trafodion Architecture page is out of date
> --
>
> Key: TRAFODION-2799
> URL: https://issues.apache.org/jira/browse/TRAFODION-2799
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: documentation
>Affects Versions: any
> Environment: N/A
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
> Fix For: 2.3-incubating
>
>
> The Trafodion architecture page on the web site 
> (http://trafodion.apache.org/architecture-overview.html) has some out-of-date 
> information in the section "Transactions". The bullet:
> Upgraded it to work on HBase version 0.98.1 (for CDH 5.1) or 0.98.0 (for HDP 
> 2.1).
> should be updated to read, 
> Upgraded to use HBase coprocessor mechanism
> Also the bullet:
> Added support for global transactions, that is, transactions that can 
> encompass resources (regions/HTables) across an HBase cluster.
> should be updated to replace the reference to HTables.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2791) 'Not casespecific' column comparison returns wrong results

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2791:
---

 Summary: 'Not casespecific' column comparison returns wrong results
 Key: TRAFODION-2791
 URL: https://issues.apache.org/jira/browse/TRAFODION-2791
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall


The current support of 'not casespecific' columns seems to be a bit erroneous. 
As shown below, c1 is a 'not casespecific' char column with a value of a upper 
case 'A', and c2 a 'not casespecific' char column with a value of a lower case 
'a'.

The following predicates seem to work:

t.c1='a' (not casespecific)
t.c1<='a' (not casespecific)
t.c1 in ('a' (not casespecific))

But the following predicates don't seem to work. They don't find the match 
while they should have in a case insensitive comparison.

t.c1<>'a' (not casespecific)
t.c1>='a' (not casespecific)
t.c1 between 'a' (not casespecific) and 'b' (not casespecific)
t.c1 like '%a%' (not casespecific)
t.c1=t.c2

For example:

>>drop table if exists t;

--- SQL operation complete.
>>
>>create table t (c1 char not casespecific, c2 char not casespecific);

--- SQL operation complete.
>>insert into t values ('A', 'a');

--- 1 row(s) inserted.
>>select * from t;

C1 C2
-- --

A a

--- 1 row(s) selected.
>>select * from t where t.c1='a' (not casespecific);

C1 C2
-- --

a a

--- 1 row(s) selected.
>>select * from t where t.c1<='a' (not casespecific);

C1 C2
-- --

A a

--- 1 row(s) selected.
>>select * from t where t.c1<>'a' (not casespecific);

C1 C2
-- --

A a

--- 1 row(s) selected.
>>select * from t where t.c1>='a' (not casespecific);

--- 0 row(s) selected.
>>select * from t where t.c1 between 'a' (not casespecific) and 'b' (not 
>>casespecific);

--- 0 row(s) selected.
>>select * from t where t.c1 in ('a' (not casespecific));

C1 C2
-- --

a a

--- 1 row(s) selected.
>>select * from t where t.c1 like '%a%' (not casespecific);

--- 0 row(s) selected.
>>select * from t where t.c1=t.c2;

--- 0 row(s) selected.
>>
>>drop table t;

--- SQL operation complete.

To reproduce, use the following script:

drop table if exists t;

create table t (c1 char not casespecific, c2 char not casespecific);
insert into t values ('A', 'a');
select * from t;
select * from t where t.c1='a' (not casespecific);
select * from t where t.c1<='a' (not casespecific);
select * from t where t.c1<>'a' (not casespecific);
select * from t where t.c1>='a' (not casespecific);
select * from t where t.c1 between 'a' (not casespecific) and 'b' (not 
casespecific);
select * from t where t.c1 in ('a' (not casespecific));
select * from t where t.c1 like '%a%' (not casespecific);
select * from t where t.c1=t.c2;

drop table t;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2781) UPDATE STATS unnecessarily creates LOB columns in sample tables

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2781?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2781.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> UPDATE STATS unnecessarily creates LOB columns in sample tables
> ---
>
> Key: TRAFODION-2781
> URL: https://issues.apache.org/jira/browse/TRAFODION-2781
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
> Fix For: 2.3-incubating
>
>
> When UPDATE STATISTICS creates a persistent sample table, it uses CREATE 
> TABLE LIKE, which means that the sample table will get LOB columns even 
> though we don't support statistics on LOBs. Moreover, we will populate these 
> LOB columns. This is an unnecessary overhead.
> We could improve the efficiency of UPDATE STATISTICS on tables with LOB 
> columns by omitting the LOB columns when creating the sample table, and 
> omitting the logic to populate them.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2781) UPDATE STATS unnecessarily creates LOB columns in sample tables

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2781:
---

 Summary: UPDATE STATS unnecessarily creates LOB columns in sample 
tables
 Key: TRAFODION-2781
 URL: https://issues.apache.org/jira/browse/TRAFODION-2781
 Project: Apache Trafodion
  Issue Type: Improvement
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
Priority: Minor


When UPDATE STATISTICS creates a persistent sample table, it uses CREATE TABLE 
LIKE, which means that the sample table will get LOB columns even though we 
don't support statistics on LOBs. Moreover, we will populate these LOB columns. 
This is an unnecessary overhead.

We could improve the efficiency of UPDATE STATISTICS on tables with LOB columns 
by omitting the LOB columns when creating the sample table, and omitting the 
logic to populate them.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2777) Fix latent bug unmasked by JIRA TRAFODION-2765 fix

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2777?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2777.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Fix latent bug unmasked by JIRA TRAFODION-2765 fix
> --
>
> Key: TRAFODION-2777
> URL: https://issues.apache.org/jira/browse/TRAFODION-2777
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> The fix to JIRA TRAFODION-2765 unmasked another bug that causes regression 
> test compGeneral/TEST006 to fail sometimes. The nature of the bug is that a 
> query plan on a table with no statistics sometimes flips over to an MDAM plan 
> where it wasn't before. One example of this that I saw happened in a release 
> build while in a debug build the test passed.
> The following script (sometimes!) reproduces the bug:
> ?section setup
> create table t006t9 (a int not null primary key, b int, c int) salt using 2 
> partitions ;
> create index t006t9ix1 on t006t9(b) ;
> insert into t006t9 values (1,2,3), (10,20,30);
> prepare expl from
> select operator, cardinality,
>cast(substring(description, position('scan_type: ' in description), 
> position('object_type: ' in description) - position('scan_type: ' in 
> description)) as char(100)) scan_type
> from table(explain(null, 'S'))
> where operator like '%SCAN%';
> ?section doit
> prepare s from
> select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
> execute expl;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2778) Query fails with error 8411 when it should succeed

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2778:
---

 Summary: Query fails with error 8411 when it should succeed
 Key: TRAFODION-2778
 URL: https://issues.apache.org/jira/browse/TRAFODION-2778
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Priority: Minor


This is a bug in LARGEINT UNSIGNED handling. A predicate of the form X = -1 is 
returning TRUE when X has the value 18446744073709551615 (which happens to be 
the largest valid value a LARGEINT UNSIGNED can hold). The value then gets 
passed to an expression X + 10, resulting in the 8411 overflow error.

The following script reproduces the error (it is similar to a query found in 
regress test seabase/TEST003):

create table t003t2(a largeint unsigned not null primary key, b largeint unsigne
d);
insert into t003t2 values (1, 2);
insert into t003t2 values (18446744073709551615, 18446744073709551615);

select b+10 from t003t2 where b = 2 or b = -1;

When run, the query fails as follows:

>>select b+10 from t003t2 where b = 2 or b = -1;

(EXPR) 


  12

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or 
data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source 
Value:0x09000100 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 1 row(s) selected.
>>

To see that the predicate is returning TRUE when it shouldn't (and hence that 
the overflow must be coming from the expression B+10), observe:

>>select b from t003t2 where b = 2 or b = -1;

B 


   2
18446744073709551615

--- 2 row(s) selected.
>>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2777) Fix latent bug unmasked by JIRA TRAFODION-2765 fix

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16210271#comment-16210271
 ] 

David Wayne Birdsall commented on TRAFODION-2777:
-

A pull request, https://github.com/apache/incubator-trafodion/pull/1269, has 
been submitted to fix this issue. (I forgot to include the JIRA name in the 
title, hence this manually-added comment.)

> Fix latent bug unmasked by JIRA TRAFODION-2765 fix
> --
>
> Key: TRAFODION-2777
> URL: https://issues.apache.org/jira/browse/TRAFODION-2777
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The fix to JIRA TRAFODION-2765 unmasked another bug that causes regression 
> test compGeneral/TEST006 to fail sometimes. The nature of the bug is that a 
> query plan on a table with no statistics sometimes flips over to an MDAM plan 
> where it wasn't before. One example of this that I saw happened in a release 
> build while in a debug build the test passed.
> The following script (sometimes!) reproduces the bug:
> ?section setup
> create table t006t9 (a int not null primary key, b int, c int) salt using 2 
> partitions ;
> create index t006t9ix1 on t006t9(b) ;
> insert into t006t9 values (1,2,3), (10,20,30);
> prepare expl from
> select operator, cardinality,
>cast(substring(description, position('scan_type: ' in description), 
> position('object_type: ' in description) - position('scan_type: ' in 
> description)) as char(100)) scan_type
> from table(explain(null, 'S'))
> where operator like '%SCAN%';
> ?section doit
> prepare s from
> select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
> execute expl;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2777) Fix latent bug unmasked by JIRA TRAFODION-2765 fix

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2777?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2777 started by David Wayne Birdsall.
---
> Fix latent bug unmasked by JIRA TRAFODION-2765 fix
> --
>
> Key: TRAFODION-2777
> URL: https://issues.apache.org/jira/browse/TRAFODION-2777
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The fix to JIRA TRAFODION-2765 unmasked another bug that causes regression 
> test compGeneral/TEST006 to fail sometimes. The nature of the bug is that a 
> query plan on a table with no statistics sometimes flips over to an MDAM plan 
> where it wasn't before. One example of this that I saw happened in a release 
> build while in a debug build the test passed.
> The following script (sometimes!) reproduces the bug:
> ?section setup
> create table t006t9 (a int not null primary key, b int, c int) salt using 2 
> partitions ;
> create index t006t9ix1 on t006t9(b) ;
> insert into t006t9 values (1,2,3), (10,20,30);
> prepare expl from
> select operator, cardinality,
>cast(substring(description, position('scan_type: ' in description), 
> position('object_type: ' in description) - position('scan_type: ' in 
> description)) as char(100)) scan_type
> from table(explain(null, 'S'))
> where operator like '%SCAN%';
> ?section doit
> prepare s from
> select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
> execute expl;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2765) MDAM is not considered when sometimes it should be

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2765?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16210211#comment-16210211
 ] 

David Wayne Birdsall commented on TRAFODION-2765:
-

Note: It turns out this fix unmasks another bug. That bug will be addressed by 
JIRA TRAFODION-2777.

> MDAM is not considered when sometimes it should be
> --
>
> Key: TRAFODION-2765
> URL: https://issues.apache.org/jira/browse/TRAFODION-2765
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> The Optimizer has logic in method ScanOptimizer::useSimpleFileScanOptimizer 
> that tries to determine scenarios where a single subset access will clearly 
> be optimal, and in those scenarios does not consider MDAM. An example would 
> be a query whose only key predicates are equality predicates on a leading 
> subset of the key columns.
> However, the logic in ScanOptimizer::useSimpleFileScanOptimizer sometimes 
> rules out considering MDAM when it would be beneficial to do so. An example 
> is a query with equality predicates on the first two columns of the key, no 
> predicates on the third, and equality on the fourth. When the UEC of the 
> third column is small, MDAM is likely to be the more efficient plan and 
> should be considered.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2777) Fix latent bug unmasked by JIRA TRAFODION-2765 fix

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2777:
---

 Summary: Fix latent bug unmasked by JIRA TRAFODION-2765 fix
 Key: TRAFODION-2777
 URL: https://issues.apache.org/jira/browse/TRAFODION-2777
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


The fix to JIRA TRAFODION-2765 unmasked another bug that causes regression test 
compGeneral/TEST006 to fail sometimes. The nature of the bug is that a query 
plan on a table with no statistics sometimes flips over to an MDAM plan where 
it wasn't before. One example of this that I saw happened in a release build 
while in a debug build the test passed.

The following script (sometimes!) reproduces the bug:

?section setup

create table t006t9 (a int not null primary key, b int, c int) salt using 2 
partitions ;
create index t006t9ix1 on t006t9(b) ;

insert into t006t9 values (1,2,3), (10,20,30);
prepare expl from
select operator, cardinality,
   cast(substring(description, position('scan_type: ' in description), 
position('object_type: ' in description) - position('scan_type: ' in 
description)) as char(100)) scan_type
from table(explain(null, 'S'))
where operator like '%SCAN%';

?section doit

prepare s from
select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
execute expl;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2771) Add new Trafodion LOB Guide to documentation build and web site

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2771.
-
Resolution: Fixed

> Add new Trafodion LOB Guide to documentation build and web site
> ---
>
> Key: TRAFODION-2771
> URL: https://issues.apache.org/jira/browse/TRAFODION-2771
> Project: Apache Trafodion
>  Issue Type: New Feature
>  Components: documentation
>Affects Versions: 2.3-incubating
> Environment: N/A
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> An entry needs to be added to the incubator-trafodion/pom.xml file so that 
> the new Trafodion LOB Guide will be built in the documentation build.
> Once the guide does get built, we need a change to the Web page to link to 
> it. The file that needs to change is documentation.md in folder 
> incubator-trafodion/docs/src/site/markdown. 



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2765) MDAM is not considered when sometimes it should be

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2765?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2765:

Fix Version/s: 2.3-incubating

> MDAM is not considered when sometimes it should be
> --
>
> Key: TRAFODION-2765
> URL: https://issues.apache.org/jira/browse/TRAFODION-2765
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> The Optimizer has logic in method ScanOptimizer::useSimpleFileScanOptimizer 
> that tries to determine scenarios where a single subset access will clearly 
> be optimal, and in those scenarios does not consider MDAM. An example would 
> be a query whose only key predicates are equality predicates on a leading 
> subset of the key columns.
> However, the logic in ScanOptimizer::useSimpleFileScanOptimizer sometimes 
> rules out considering MDAM when it would be beneficial to do so. An example 
> is a query with equality predicates on the first two columns of the key, no 
> predicates on the third, and equality on the fourth. When the UEC of the 
> third column is small, MDAM is likely to be the more efficient plan and 
> should be considered.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2765) MDAM is not considered when sometimes it should be

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2765?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2765.
-
Resolution: Fixed

> MDAM is not considered when sometimes it should be
> --
>
> Key: TRAFODION-2765
> URL: https://issues.apache.org/jira/browse/TRAFODION-2765
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The Optimizer has logic in method ScanOptimizer::useSimpleFileScanOptimizer 
> that tries to determine scenarios where a single subset access will clearly 
> be optimal, and in those scenarios does not consider MDAM. An example would 
> be a query whose only key predicates are equality predicates on a leading 
> subset of the key columns.
> However, the logic in ScanOptimizer::useSimpleFileScanOptimizer sometimes 
> rules out considering MDAM when it would be beneficial to do so. An example 
> is a query with equality predicates on the first two columns of the key, no 
> predicates on the third, and equality on the fourth. When the UEC of the 
> third column is small, MDAM is likely to be the more efficient plan and 
> should be considered.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2771) Add new Trafodion LOB Guide to documentation build and web site

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2771:
---

 Summary: Add new Trafodion LOB Guide to documentation build and 
web site
 Key: TRAFODION-2771
 URL: https://issues.apache.org/jira/browse/TRAFODION-2771
 Project: Apache Trafodion
  Issue Type: New Feature
  Components: documentation
Affects Versions: 2.3-incubating
 Environment: N/A
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


An entry needs to be added to the incubator-trafodion/pom.xml file so that the 
new Trafodion LOB Guide will be built in the documentation build.

Once the guide does get built, we need a change to the Web page to link to it. 
The file that needs to change is documentation.md in folder 
incubator-trafodion/docs/src/site/markdown. 



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-1715) UPDATE STATISTICS allocates memory even when a SAMPLE table is used

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-1715.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> UPDATE STATISTICS allocates memory even when a SAMPLE table is used
> ---
>
> Key: TRAFODION-1715
> URL: https://issues.apache.org/jira/browse/TRAFODION-1715
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> UPDATE STATISTICS attempts to use an internal sort when possible. It 
> estimates how much memory it has available to it, and estimates how much 
> memory is needed to store the sample data. It then attempts to allocate this 
> memory. If it fails to get everything it needs, it appears to retain what 
> memory it has allocated, but then runs a sample table load query. Thus, 
> memory has been allocated in the master executor process that might have been 
> useful to the load query. It would have been better perhaps to release this 
> memory, and attempted to reaquire it later.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-1715) UPDATE STATISTICS allocates memory even when a SAMPLE table is used

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-1715 started by David Wayne Birdsall.
---
> UPDATE STATISTICS allocates memory even when a SAMPLE table is used
> ---
>
> Key: TRAFODION-1715
> URL: https://issues.apache.org/jira/browse/TRAFODION-1715
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> UPDATE STATISTICS attempts to use an internal sort when possible. It 
> estimates how much memory it has available to it, and estimates how much 
> memory is needed to store the sample data. It then attempts to allocate this 
> memory. If it fails to get everything it needs, it appears to retain what 
> memory it has allocated, but then runs a sample table load query. Thus, 
> memory has been allocated in the master executor process that might have been 
> useful to the load query. It would have been better perhaps to release this 
> memory, and attempted to reaquire it later.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2630) Merge returns internal error when the table has a constraint

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2630.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Merge returns internal error when the table has a constraint
> 
>
> Key: TRAFODION-2630
> URL: https://issues.apache.org/jira/browse/TRAFODION-2630
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Reproduced this on a workstation; I suspect it will 
> happen in any environment.
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> Consider the following script:
> create table mytable1 (a int not null not droppable primary key, b int, c 
> int);
> insert into mytable1 values (1,1,1),(2,2,2),(3,3,3);
> merge into mytable1 on a=1 when matched then update set c=100;
> create table mytable2 (a int not null not droppable primary key, b int, c 
> int, check (b > 0));
> insert into mytable2 values (1,1,1),(2,2,2),(3,3,3);
> merge into mytable2 on a=1 when matched then update set c=100;
> When this script is executed, the first MERGE statement executes normally but 
> the second fails as follows:
> >>merge into mytable2 on a=1 when matched then update set c=100;
> *** ERROR[7000] An internal error occurred in the code generator in file 
> ../generator/GenRelUpdate.cpp at line 1721: Should not reach here. This 
> update should have been transformed to delete/insert.
> *** ERROR[2235] Compiler Internal Error: An unknown error, originated from 
> file ../generator/Generator.cpp at line 3065.
> *** ERROR[8822] The statement was not prepared.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-1610) Generate constraint expression for update/merge commands

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-1610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-1610.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Generate constraint expression for update/merge commands
> 
>
> Key: TRAFODION-1610
> URL: https://issues.apache.org/jira/browse/TRAFODION-1610
> Project: Apache Trafodion
>  Issue Type: Sub-task
>  Components: sql-cmp
>Reporter: Selvaganesan Govindarajan
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2630) Merge returns internal error when the table has a constraint

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2630 started by David Wayne Birdsall.
---
> Merge returns internal error when the table has a constraint
> 
>
> Key: TRAFODION-2630
> URL: https://issues.apache.org/jira/browse/TRAFODION-2630
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Reproduced this on a workstation; I suspect it will 
> happen in any environment.
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> Consider the following script:
> create table mytable1 (a int not null not droppable primary key, b int, c 
> int);
> insert into mytable1 values (1,1,1),(2,2,2),(3,3,3);
> merge into mytable1 on a=1 when matched then update set c=100;
> create table mytable2 (a int not null not droppable primary key, b int, c 
> int, check (b > 0));
> insert into mytable2 values (1,1,1),(2,2,2),(3,3,3);
> merge into mytable2 on a=1 when matched then update set c=100;
> When this script is executed, the first MERGE statement executes normally but 
> the second fails as follows:
> >>merge into mytable2 on a=1 when matched then update set c=100;
> *** ERROR[7000] An internal error occurred in the code generator in file 
> ../generator/GenRelUpdate.cpp at line 1721: Should not reach here. This 
> update should have been transformed to delete/insert.
> *** ERROR[2235] Compiler Internal Error: An unknown error, originated from 
> file ../generator/Generator.cpp at line 3065.
> *** ERROR[8822] The statement was not prepared.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2667) Incorrect UEC calculation

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2667?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16192252#comment-16192252
 ] 

David Wayne Birdsall commented on TRAFODION-2667:
-

I am addressing this issue as part of the work for JIRA TRAFODION-2645.

> Incorrect UEC calculation
> -
>
> Key: TRAFODION-2667
> URL: https://issues.apache.org/jira/browse/TRAFODION-2667
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When applying a predicate to histogram data for the same column, the 
> resulting UECs are sometimes incorrect.
> ColStatDesc::applySel (optimizer/ColStatDesc.cpp) calls 
> ColStatDesc::calculateCorrectResultUec to do this UEC calculation. The latter 
> assumes that the predicate is on a column that is independent of the 
> histogram column. In the case that the predicate is on the same column as the 
> histogram, it is clearly quite dependent! Instead, the UEC should be reduced 
> proportionately in the same manner as the row count.
> Though this is a simple change, the consequences may be far-reaching. Plan 
> changes are likely. Adjustments to Optimizer tuning parameters are likely to 
> be required. This change, then, should only be done in concert with a larger 
> tuning effort.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2645) MDAM costing overestimates I/O cost

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2645 started by David Wayne Birdsall.
---
> MDAM costing overestimates I/O cost
> ---
>
> Key: TRAFODION-2645
> URL: https://issues.apache.org/jira/browse/TRAFODION-2645
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The method MDAMCostWA::compute (optimizer/ScanOptimizer.cpp) has logic to 
> calculate the total cost of an MDAM plan and compare it to the cost of a 
> single subset plan. In the case of a Trafodion table, the cost logic for MDAM 
> cost has an error. It is adding the total number of probes (totalRqsts) to 
> the number of seeks (totalSeeks) in order to calculate I/O costs.
> TotalSeeks models direct access disk seeks. These happen at the block level 
> and are an I/O cost.
> TotalRqsts models the number of probes, which is a CPU cost. A probe is a 
> direct access within a block that has already been read into memory. This is 
> a vastly smaller unit of cost than an I/O operation.
> Note that TotalRqsts is already modeled as a message cost since it is added 
> to TotalRows.
> The effect of the bug is to vastly penalize MDAM plans having large numbers 
> of probes. This will show up the most on the largest tables.
> The fix is simply not to add totalRqsts to totalSeeks. However, in making 
> this fix, it is likely that other MDAM-related parameters will need tuning. 
> So this change needs to be done carefully.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2667) Incorrect UEC calculation

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2667?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2667 started by David Wayne Birdsall.
---
> Incorrect UEC calculation
> -
>
> Key: TRAFODION-2667
> URL: https://issues.apache.org/jira/browse/TRAFODION-2667
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When applying a predicate to histogram data for the same column, the 
> resulting UECs are sometimes incorrect.
> ColStatDesc::applySel (optimizer/ColStatDesc.cpp) calls 
> ColStatDesc::calculateCorrectResultUec to do this UEC calculation. The latter 
> assumes that the predicate is on a column that is independent of the 
> histogram column. In the case that the predicate is on the same column as the 
> histogram, it is clearly quite dependent! Instead, the UEC should be reduced 
> proportionately in the same manner as the row count.
> Though this is a simple change, the consequences may be far-reaching. Plan 
> changes are likely. Adjustments to Optimizer tuning parameters are likely to 
> be required. This change, then, should only be done in concert with a larger 
> tuning effort.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2765) MDAM is not considered when sometimes it should be

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2765?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16192246#comment-16192246
 ] 

David Wayne Birdsall commented on TRAFODION-2765:
-

Here is a script that reproduces the problem. 

?section create

drop table if exists tda;

CREATE TABLE  tda
  (
AINT NO DEFAULT NOT NULL NOT DROPPABLE
  SERIALIZED
  , BINT NO DEFAULT NOT NULL NOT DROPPABLE
  SERIALIZED
  , CINT NO DEFAULT NOT NULL NOT DROPPABLE
  SERIALIZED
  , DINT NO DEFAULT NOT NULL NOT DROPPABLE
  SERIALIZED
  , PRIMARY KEY (A ASC, B ASC, C ASC)
  )
  SALT USING 4 PARTITIONS on (A) 
;

?section populate

-- UEC of tda.a is 100, tda.b is 10, tda.c is 2000

upsert using load into tda
select x1+10*x2, 
   x3, 
   x4+10*x5+100*x6+1000*x7, x1+x2+10*x3+x4+10*x5+100*x6+1000*x7
-- the from clause below creates 2,000,000 rows, the cross product of
-- 6 copies of { 0, ... 9 } and one copy of { 0, 1 }
  from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x4
transpose 0,1,2,3,4,5,6,7,8,9 as x5
transpose 0,1,2,3,4,5,6,7,8,9 as x6
transpose 0,1 as x7;

update statistics for table tda on every column;

?section testit

prepare s1 from select * from tda where a = 5 and c = 20;

In this example, we have a query with an equality predicate on the second key 
column, A. There is an inferred equality predicate on the leading key column, 
"_SALT_", since it is a function of A.

In this example, the code in ScanOptimizer::useSimpleFileScanOptimizer does not 
consider MDAM.

> MDAM is not considered when sometimes it should be
> --
>
> Key: TRAFODION-2765
> URL: https://issues.apache.org/jira/browse/TRAFODION-2765
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> The Optimizer has logic in method ScanOptimizer::useSimpleFileScanOptimizer 
> that tries to determine scenarios where a single subset access will clearly 
> be optimal, and in those scenarios does not consider MDAM. An example would 
> be a query whose only key predicates are equality predicates on a leading 
> subset of the key columns.
> However, the logic in ScanOptimizer::useSimpleFileScanOptimizer sometimes 
> rules out considering MDAM when it would be beneficial to do so. An example 
> is a query with equality predicates on the first two columns of the key, no 
> predicates on the third, and equality on the fourth. When the UEC of the 
> third column is small, MDAM is likely to be the more efficient plan and 
> should be considered.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2762) UPDATE STATS fails when creating sample table with nullable key

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2762.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> UPDATE STATS fails when creating sample table with nullable key
> ---
>
> Key: TRAFODION-2762
> URL: https://issues.apache.org/jira/browse/TRAFODION-2762
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
> Fix For: 2.3-incubating
>
>
> Trafodion contains an experimental feature that optionally allows one to 
> create a table with a nullable primary key. That is, NULL values can be used 
> in the primary key. For purposes of uniqueness, NULL is treated like any 
> other value.
> One can turn on this feature via CQD ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT 
> 'ON'.
> However, if one creates a table with a nullable primary key, then later in 
> another session without this CQD attempts to do UPDATE STATISTICS where a 
> sample table is created, UPDATE STATISTICS will fail. For example:
> update statistics for table Traf2762 on every column sample;
> *** ERROR[9214] Object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 could 
> not be created.
> *** ERROR[1135] Clustering key column inputuid must be assigned a NOT NULL 
> NOT DROPPABLE constraint.
> *** ERROR[8839] Transaction was aborted.
> *** ERROR[9201] Unable to DROP object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_15057572 84_535476. 
> *** ERROR[1389] Object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 does not 
> exist in Trafodion.
> *** ERROR[8839] Transaction was aborted.
> Note: This example assumes that Traf2762 is a large enough table that a 
> sample table is required by UPDATE STATISTICS.
> A more reliable way to reproduce the error (which does not depend on table 
> size) is "update statistics for table Traf2762 create sample random 10 
> percent".



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2762) UPDATE STATS fails when creating sample table with nullable key

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2762:

Description: 
Trafodion contains an experimental feature that optionally allows one to create 
a table with a nullable primary key. That is, NULL values can be used in the 
primary key. For purposes of uniqueness, NULL is treated like any other value.

One can turn on this feature via CQD ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT 'ON'.

However, if one creates a table with a nullable primary key, then later in 
another session without this CQD attempts to do UPDATE STATISTICS where a 
sample table is created, UPDATE STATISTICS will fail. For example:

update statistics for table Traf2762 on every column sample;

*** ERROR[9214] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 could not 
be created.
*** ERROR[1135] Clustering key column inputuid must be assigned a NOT NULL NOT 
DROPPABLE constraint.
*** ERROR[8839] Transaction was aborted.
*** ERROR[9201] Unable to DROP object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_15057572 84_535476. 
*** ERROR[1389] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 does not 
exist in Trafodion.
*** ERROR[8839] Transaction was aborted.

Note: This example assumes that Traf2762 is a large enough table that a sample 
table is required by UPDATE STATISTICS.

A more reliable way to reproduce the error (which does not depend on table 
size) is "update statistics for table Traf2762 create sample random 10 percent".

  was:
Trafodion contains an experimental feature that optionally allows one to create 
a table with a nullable primary key. That is, NULL values can be used in the 
primary key. For purposes of uniqueness, NULL is treated like any other value.

One can turn on this feature via CQD ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT 'ON'.

However, if one creates a table with a nullable primary key, then later in 
another session without this CQD attempts to do UPDATE STATISTICS where a 
sample table is created, UPDATE STATISTICS will fail. For example:

update statistics for table "InputErrors_10_TestClientData_10" on every column 
sampl e;

*** ERROR[9214] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 could not 
be created.
*** ERROR[1135] Clustering key column inputuid must be assigned a NOT NULL NOT 
DROPPABLE constraint.
*** ERROR[8839] Transaction was aborted.
*** ERROR[9201] Unable to DROP object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_15057572 84_535476. 
*** ERROR[1389] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 does not 
exist in Trafodion.
*** ERROR[8839] Transaction was aborted.


> UPDATE STATS fails when creating sample table with nullable key
> ---
>
> Key: TRAFODION-2762
> URL: https://issues.apache.org/jira/browse/TRAFODION-2762
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> Trafodion contains an experimental feature that optionally allows one to 
> create a table with a nullable primary key. That is, NULL values can be used 
> in the primary key. For purposes of uniqueness, NULL is treated like any 
> other value.
> One can turn on this feature via CQD ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT 
> 'ON'.
> However, if one creates a table with a nullable primary key, then later in 
> another session without this CQD attempts to do UPDATE STATISTICS where a 
> sample table is created, UPDATE STATISTICS will fail. For example:
> update statistics for table Traf2762 on every column sample;
> *** ERROR[9214] Object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 could 
> not be created.
> *** ERROR[1135] Clustering key column inputuid must be assigned a NOT NULL 
> NOT DROPPABLE constraint.
> *** ERROR[8839] Transaction was aborted.
> *** ERROR[9201] Unable to DROP object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_15057572 84_535476. 
> *** ERROR[1389] Object 
> TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 does not 
> exist in Trafodion.
> *** ERROR[8839] Transaction was aborted.
> Note: This example assumes that Traf2762 is a large enough table that a 
> sample table is required by UPDATE STATISTICS.
> A more reliable way to reproduce the error (which does not depend on table 
> size) is "update statistics for table Traf2762 create sample random 10 
> percent".



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2762) UPDATE STATS fails when creating sample table with nullable key

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2762:
---

 Summary: UPDATE STATS fails when creating sample table with 
nullable key
 Key: TRAFODION-2762
 URL: https://issues.apache.org/jira/browse/TRAFODION-2762
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
Priority: Minor


Trafodion contains an experimental feature that optionally allows one to create 
a table with a nullable primary key. That is, NULL values can be used in the 
primary key. For purposes of uniqueness, NULL is treated like any other value.

One can turn on this feature via CQD ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT 'ON'.

However, if one creates a table with a nullable primary key, then later in 
another session without this CQD attempts to do UPDATE STATISTICS where a 
sample table is created, UPDATE STATISTICS will fail. For example:

update statistics for table "InputErrors_10_TestClientData_10" on every column 
sampl e;

*** ERROR[9214] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 could not 
be created.
*** ERROR[1135] Clustering key column inputuid must be assigned a NOT NULL NOT 
DROPPABLE constraint.
*** ERROR[8839] Transaction was aborted.
*** ERROR[9201] Unable to DROP object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_15057572 84_535476. 
*** ERROR[1389] Object 
TRAFODION.SEABASE.TRAF_SAMPLE_05267662795492559847_1505757284_535476 does not 
exist in Trafodion.
*** ERROR[8839] Transaction was aborted.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Assigned] (TRAFODION-1610) Generate constraint expression for update/merge commands

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-1610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall reassigned TRAFODION-1610:
---

Assignee: David Wayne Birdsall  (was: Hans Zeller)

> Generate constraint expression for update/merge commands
> 
>
> Key: TRAFODION-1610
> URL: https://issues.apache.org/jira/browse/TRAFODION-1610
> Project: Apache Trafodion
>  Issue Type: Sub-task
>  Components: sql-cmp
>Reporter: Selvaganesan Govindarajan
>Assignee: David Wayne Birdsall
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-1610) Generate constraint expression for update/merge commands

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-1610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-1610 started by David Wayne Birdsall.
---
> Generate constraint expression for update/merge commands
> 
>
> Key: TRAFODION-1610
> URL: https://issues.apache.org/jira/browse/TRAFODION-1610
> Project: Apache Trafodion
>  Issue Type: Sub-task
>  Components: sql-cmp
>Reporter: Selvaganesan Govindarajan
>Assignee: David Wayne Birdsall
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2749) UPDATE STATS mc histogram failure when column has reserved word name

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2749?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2749.
-
Resolution: Fixed

> UPDATE STATS mc histogram failure when column has reserved word name
> 
>
> Key: TRAFODION-2749
> URL: https://issues.apache.org/jira/browse/TRAFODION-2749
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When UPDATE STATISTICS tries to create a multi-column histogram, and one or 
> more of the columns has a name which happens to be a SQL reserved word, the 
> command fails with a syntax error on an internal statement.
> The following script reproduces the problem (here, the failure occurs when 
> UPDATE STATS tries to create a multi-column histogram on the two key columns):
> ?section setup
> create table ExampleTable 
> ( a smallint not null,
>   "YEAR" smallint not null,
>   primary key (a, "YEAR") );
> insert into ExampleTable values (1,1996),(2,1997),(3,1998);
> ?section doit
> update statistics for table ExampleTable on every column;
> Below is the execution output:
> >>obey repro.sql;
> >>?section setup
> >>
> >>create table ExampleTable 
> +>( a smallint not null,
> +>  "YEAR" smallint not null,
> +>  primary key (a, "YEAR") );
> --- SQL operation complete.
> >>
> >>insert into ExampleTable values (1,1996),(2,1997),(3,1998);
> --- 3 row(s) inserted.
> >>
> >>?section doit
> >>
> >>update statistics for table ExampleTable on every column;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.EXAMPLETABLE 
> encountered an error (15001) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[15001] A syntax error occurred at or before: 
> SELECT FMTVAL, SUMVAL FROM (SELECT "A","YEAR", _ucs2'unused', COUNT(*) FROM 
> TRA
> FODION.SCH.EXAMPLETABLE <<+ cardinality 3.00e+00 >>  GROUP BY A, YEAR FOR 
> R
> EAD UNCOMMITTED ACCESS) T(A, YEAR, FMTVAL, SUMVAL);
>^ (162 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>
> >>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2749) UPDATE STATS mc histogram failure when column has reserved word name

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2749?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2749 started by David Wayne Birdsall.
---
> UPDATE STATS mc histogram failure when column has reserved word name
> 
>
> Key: TRAFODION-2749
> URL: https://issues.apache.org/jira/browse/TRAFODION-2749
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When UPDATE STATISTICS tries to create a multi-column histogram, and one or 
> more of the columns has a name which happens to be a SQL reserved word, the 
> command fails with a syntax error on an internal statement.
> The following script reproduces the problem (here, the failure occurs when 
> UPDATE STATS tries to create a multi-column histogram on the two key columns):
> ?section setup
> create table ExampleTable 
> ( a smallint not null,
>   "YEAR" smallint not null,
>   primary key (a, "YEAR") );
> insert into ExampleTable values (1,1996),(2,1997),(3,1998);
> ?section doit
> update statistics for table ExampleTable on every column;
> Below is the execution output:
> >>obey repro.sql;
> >>?section setup
> >>
> >>create table ExampleTable 
> +>( a smallint not null,
> +>  "YEAR" smallint not null,
> +>  primary key (a, "YEAR") );
> --- SQL operation complete.
> >>
> >>insert into ExampleTable values (1,1996),(2,1997),(3,1998);
> --- 3 row(s) inserted.
> >>
> >>?section doit
> >>
> >>update statistics for table ExampleTable on every column;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.EXAMPLETABLE 
> encountered an error (15001) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[15001] A syntax error occurred at or before: 
> SELECT FMTVAL, SUMVAL FROM (SELECT "A","YEAR", _ucs2'unused', COUNT(*) FROM 
> TRA
> FODION.SCH.EXAMPLETABLE <<+ cardinality 3.00e+00 >>  GROUP BY A, YEAR FOR 
> R
> EAD UNCOMMITTED ACCESS) T(A, YEAR, FMTVAL, SUMVAL);
>^ (162 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>
> >>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2749) UPDATE STATS mc histogram failure when column has reserved word name

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2749:
---

 Summary: UPDATE STATS mc histogram failure when column has 
reserved word name
 Key: TRAFODION-2749
 URL: https://issues.apache.org/jira/browse/TRAFODION-2749
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.3-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


When UPDATE STATISTICS tries to create a multi-column histogram, and one or 
more of the columns has a name which happens to be a SQL reserved word, the 
command fails with a syntax error on an internal statement.

The following script reproduces the problem (here, the failure occurs when 
UPDATE STATS tries to create a multi-column histogram on the two key columns):

?section setup

create table ExampleTable 
( a smallint not null,
  "YEAR" smallint not null,
  primary key (a, "YEAR") );

insert into ExampleTable values (1,1996),(2,1997),(3,1998);

?section doit

update statistics for table ExampleTable on every column;

Below is the execution output:

>>obey repro.sql;
>>?section setup
>>
>>create table ExampleTable 
+>( a smallint not null,
+>  "YEAR" smallint not null,
+>  primary key (a, "YEAR") );

--- SQL operation complete.
>>
>>insert into ExampleTable values (1,1996),(2,1997),(3,1998);

--- 3 row(s) inserted.
>>
>>?section doit
>>
>>update statistics for table ExampleTable on every column;

*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.EXAMPLETABLE 
encountered an error (15001) from statement FETCH_BOUNDARY_ROWSET.

*** ERROR[15001] A syntax error occurred at or before: 
SELECT FMTVAL, SUMVAL FROM (SELECT "A","YEAR", _ucs2'unused', COUNT(*) FROM TRA
FODION.SCH.EXAMPLETABLE <<+ cardinality 3.00e+00 >>  GROUP BY A, YEAR FOR R
EAD UNCOMMITTED ACCESS) T(A, YEAR, FMTVAL, SUMVAL);
   ^ (162 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.
>>
>>





--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2732) UPDATE STATS fails sometimes when a column name is a SQL keyword

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2732.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> UPDATE STATS fails sometimes when a column name is a SQL keyword
> 
>
> Key: TRAFODION-2732
> URL: https://issues.apache.org/jira/browse/TRAFODION-2732
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: any
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> On tables having the following characteristics:
> 1. Large table having a column named using a reserved SQL keyword (e.g. 
> "YEAR")
> 2. The sample data is too large to fit in memory (or CQD USTAT_INTERNAL_SORT 
> is set to 'OFF')
> 3. The table also has a VARCHAR column of longer than 256 characters
> Then UPDATE STATISTICS will fail when it tries to populate a sample table, 
> reporting a syntax error on an internally-generated UPSERT statement.
> The following sqlci session demonstrates how to reproduce the problem:
> >>obey repro1j.sql;
> >>?section setup
> >>
> >>drop table UstatsBug;
> *** ERROR[1389] Object TRAFODION.SCH.USTATSBUG does not exist in Trafodion.
> --- SQL operation failed with errors.
> >>
> >>CREATE TABLE UstatsBug
> +>  (
> +>ID   VARCHAR(32 CHARS) CHARACTER SET UTF8
> +>  COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> +>  , "YEAR" NUMERIC(12, 0) DEFAULT NULL NOT 
> SERIALIZED
> +>  , JUNK VARCHAR(1000 CHARS) CHARACTER SET UTF8
> +>  , PRIMARY KEY (ID ASC)
> +>  )
> +> ATTRIBUTES ALIGNED FORMAT
> +>  HBASE_OPTIONS
> +>  (
> +>DATA_BLOCK_ENCODING = 'FAST_DIFF',
> +>MEMSTORE_FLUSH_SIZE = '1073741824'
> +>  )
> +>;
> --- SQL operation complete.
> >>
> >>insert into UstatsBug values 
> >>('a',1992,'junk'),('b',1993,'junk'),('c',1994,'junk'),('d',1995,'junk');
> --- 4 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'a', "YEAR", junk from UstatsBug;
> --- 4 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'b', "YEAR", junk from UstatsBug;
> --- 8 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'c', "YEAR", junk from UstatsBug;
> --- 16 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'd', "YEAR", junk from UstatsBug;
> --- 32 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'e', "YEAR", junk from UstatsBug;
> --- 64 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'f', "YEAR", junk from UstatsBug;
> --- 128 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'g', "YEAR", junk from UstatsBug;
> --- 256 row(s) inserted.
> >>
> >>?section doit
> >>
> >>cqd ustat_internal_sort 'OFF';
> --- SQL operation complete.
> >>
> >>UPDATE STATISTICS FOR TABLE UstatsBug create SAMPLE random 50 percent;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.USTATSBUG 
> encountered an error (15001) from statement Process_Query.
> *** ERROR[15001] A syntax error occurred at or before: 
> UPSERT USING LOAD INTO 
> TRAFODION.SCH.TRAF_SAMPLE_02393802717774510155_150488830
> 9_629934 SELECT ID, YEAR, SUBSTRING(JUNK FOR 64) AS JUNK FROM 
> TRAFODION.SCH.UST
> ^ (104 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>
> >>exit;
> End of MXCI Session



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Work started] (TRAFODION-2732) UPDATE STATS fails sometimes when a column name is a SQL keyword

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2732 started by David Wayne Birdsall.
---
> UPDATE STATS fails sometimes when a column name is a SQL keyword
> 
>
> Key: TRAFODION-2732
> URL: https://issues.apache.org/jira/browse/TRAFODION-2732
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: any
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> On tables having the following characteristics:
> 1. Large table having a column named using a reserved SQL keyword (e.g. 
> "YEAR")
> 2. The sample data is too large to fit in memory (or CQD USTAT_INTERNAL_SORT 
> is set to 'OFF')
> 3. The table also has a VARCHAR column of longer than 256 characters
> Then UPDATE STATISTICS will fail when it tries to populate a sample table, 
> reporting a syntax error on an internally-generated UPSERT statement.
> The following sqlci session demonstrates how to reproduce the problem:
> >>obey repro1j.sql;
> >>?section setup
> >>
> >>drop table UstatsBug;
> *** ERROR[1389] Object TRAFODION.SCH.USTATSBUG does not exist in Trafodion.
> --- SQL operation failed with errors.
> >>
> >>CREATE TABLE UstatsBug
> +>  (
> +>ID   VARCHAR(32 CHARS) CHARACTER SET UTF8
> +>  COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> +>  , "YEAR" NUMERIC(12, 0) DEFAULT NULL NOT 
> SERIALIZED
> +>  , JUNK VARCHAR(1000 CHARS) CHARACTER SET UTF8
> +>  , PRIMARY KEY (ID ASC)
> +>  )
> +> ATTRIBUTES ALIGNED FORMAT
> +>  HBASE_OPTIONS
> +>  (
> +>DATA_BLOCK_ENCODING = 'FAST_DIFF',
> +>MEMSTORE_FLUSH_SIZE = '1073741824'
> +>  )
> +>;
> --- SQL operation complete.
> >>
> >>insert into UstatsBug values 
> >>('a',1992,'junk'),('b',1993,'junk'),('c',1994,'junk'),('d',1995,'junk');
> --- 4 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'a', "YEAR", junk from UstatsBug;
> --- 4 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'b', "YEAR", junk from UstatsBug;
> --- 8 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'c', "YEAR", junk from UstatsBug;
> --- 16 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'd', "YEAR", junk from UstatsBug;
> --- 32 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'e', "YEAR", junk from UstatsBug;
> --- 64 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'f', "YEAR", junk from UstatsBug;
> --- 128 row(s) inserted.
> >>
> >>insert into UstatsBug select id || 'g', "YEAR", junk from UstatsBug;
> --- 256 row(s) inserted.
> >>
> >>?section doit
> >>
> >>cqd ustat_internal_sort 'OFF';
> --- SQL operation complete.
> >>
> >>UPDATE STATISTICS FOR TABLE UstatsBug create SAMPLE random 50 percent;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.USTATSBUG 
> encountered an error (15001) from statement Process_Query.
> *** ERROR[15001] A syntax error occurred at or before: 
> UPSERT USING LOAD INTO 
> TRAFODION.SCH.TRAF_SAMPLE_02393802717774510155_150488830
> 9_629934 SELECT ID, YEAR, SUBSTRING(JUNK FOR 64) AS JUNK FROM 
> TRAFODION.SCH.UST
> ^ (104 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>
> >>exit;
> End of MXCI Session



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2729) Parallel plan not chosen for native HBase table

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2729?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2729.
-
   Resolution: Fixed
Fix Version/s: 2.3-incubating

> Parallel plan not chosen for native HBase table
> ---
>
> Key: TRAFODION-2729
> URL: https://issues.apache.org/jira/browse/TRAFODION-2729
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: Probably all
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.3-incubating
>
>
> If I have a Trafodion table salted into 2 partitions, I can induce a parallel 
> plan on that table using a cardinality hint. For example:
> CREATE TABLE TRAFODION.SEABASE.ADEF
>   (
> ROW_ID   VARCHAR(100) CHARACTER SET ISO88591
>   COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , COLUMN_DETAILS   VARCHAR(1) CHARACTER SET ISO88591
>   COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , PRIMARY KEY (ROW_ID ASC)
>   )
>   SALT USING 2 PARTITIONS
>  ATTRIBUTES ALIGNED FORMAT
> ;
> >>prepare s1 from select column_details,count(*)  
> +> from adef <<+ cardinality 1e8 >>
> +> group by column_details;
> --- SQL command prepared.
> >>explain options 'f' s1;
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
> 4.5root  2.00E+000
> 3.4hash_partial_groupby  2.00E+000
> 2.3esp_exchange1:2(hash2)2.00E+000
> 1.2hash_partial_groupby  2.00E+000
> ..1trafodion_scan  ADEF  1.00E+008
> --- SQL operation complete.
> However, if I have a native HBase table split into two regions, the same 
> cardinality hint does not produce a parallel plan:
> >>prepare s2 from select column_details,count(*)
> +> from hbase."_ROW_"."def" <<+ cardinality 1e8 >>
> +> group by column_details;
> --- SQL command prepared.
> >>explain options 'f' s2;
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
> 2.3root  2.00E+000
> 1.2hash_groupby  2.00E+000
> ..1hbase_scan  def   1.00E+008
> --- SQL operation complete.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2728) SHOWSTATS sometimes gives strange message when stats don't exist

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2728:

Description: 
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.

Note: The things in italics are supposed to be surrounded by underscores; 
unfortunately underscore seems to be a metacharacter meaning "italics font" in 
JIRA and I don't know how to escape them.


  was:
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.

Note: The things in italics are supposed to be surrounded by underscores; 
unfortunately underscore seems to be a metacharacter meaning "italics font" in 
JIRA.



> SHOWSTATS sometimes gives strange message when stats don't exist
> 
>
> Key: TRAFODION-2728
> URL: https://issues.apache.org/jira/browse/TRAFODION-2728
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> When doing a SHOWSTATS on a native HBase table, the error message is strange 
> if the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
> instance where that schema has not been created yet:
> >>showstats for table hbase."_ROW_"."xyz" on existing columns;
> >>
> *** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
> error (4082) from statement .
> *** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist 
> or is inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> However, if the "_HBASESTATS_" schema and histogram tables exist, we get a 
> more friendly and normal looking error:
> >>showstats for table hbase."_CELL_"."xyz" on existing columns;
> Histogram data for Table HBASE."_CELL_"."xyz"
> Table ID: 0
>Hist ID # IntsRowcount UEC Colname(s)
> == == === === ===
> No Histograms exist for the requested columns or groups
> --- SQL operation complete.
> >>
> The objective of this JIRA is to give the latter error message even if the 
> "_HBASESTATS_" histograms tables do not exist.
> Note: The things in italics are supposed to be surrounded by underscores; 
> unfortunately underscore seems to be a metacharacter meaning "italics font" 
> in JIRA and I don't know how to escape them.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2728) SHOWSTATS sometimes gives strange message when stats don't exist

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2728:

Description: 
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.

Note: The things in italics are supposed to be surrounded by underscores; 
unfortunately underscore seems to be a metacharacter meaning "italics font" in 
JIRA.


  was:
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the {{"_HBASESTATS_"}} histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.



> SHOWSTATS sometimes gives strange message when stats don't exist
> 
>
> Key: TRAFODION-2728
> URL: https://issues.apache.org/jira/browse/TRAFODION-2728
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> When doing a SHOWSTATS on a native HBase table, the error message is strange 
> if the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
> instance where that schema has not been created yet:
> >>showstats for table hbase."_ROW_"."xyz" on existing columns;
> >>
> *** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
> error (4082) from statement .
> *** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist 
> or is inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> However, if the "_HBASESTATS_" schema and histogram tables exist, we get a 
> more friendly and normal looking error:
> >>showstats for table hbase."_CELL_"."xyz" on existing columns;
> Histogram data for Table HBASE."_CELL_"."xyz"
> Table ID: 0
>Hist ID # IntsRowcount UEC Colname(s)
> == == === === ===
> No Histograms exist for the requested columns or groups
> --- SQL operation complete.
> >>
> The objective of this JIRA is to give the latter error message even if the 
> "_HBASESTATS_" histograms tables do not exist.
> Note: The things in italics are supposed to be surrounded by underscores; 
> unfortunately underscore seems to be a metacharacter meaning "italics font" 
> in JIRA.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2728) SHOWSTATS sometimes gives strange message when stats don't exist

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2728:

Description: 
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the {{"_HBASESTATS_"}} histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.


  was:
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in 
a fresh instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.



> SHOWSTATS sometimes gives strange message when stats don't exist
> 
>
> Key: TRAFODION-2728
> URL: https://issues.apache.org/jira/browse/TRAFODION-2728
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> When doing a SHOWSTATS on a native HBase table, the error message is strange 
> if the {{"_HBASESTATS_"}} histogram tables do not exist. For example, in a 
> fresh instance where that schema has not been created yet:
> >>showstats for table hbase."_ROW_"."xyz" on existing columns;
> >>
> *** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
> error (4082) from statement .
> *** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist 
> or is inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> However, if the "_HBASESTATS_" schema and histogram tables exist, we get a 
> more friendly and normal looking error:
> >>showstats for table hbase."_CELL_"."xyz" on existing columns;
> Histogram data for Table HBASE."_CELL_"."xyz"
> Table ID: 0
>Hist ID # IntsRowcount UEC Colname(s)
> == == === === ===
> No Histograms exist for the requested columns or groups
> --- SQL operation complete.
> >>
> The objective of this JIRA is to give the latter error message even if the 
> "_HBASESTATS_" histograms tables do not exist.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (TRAFODION-2728) SHOWSTATS sometimes gives strange message when stats don't exist

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-2728:

Description: 
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in 
a fresh instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.


  was:
When doing a SHOWSTATS on a native HBase table, the error message is strange if 
the "_HBASESTATS_" histogram tables do not exist. For example, in a fresh 
instance where that schema has not been created yet:

>>showstats for table hbase."_ROW_"."xyz" on existing columns;  
>>  

*** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
error (4082) from statement .

*** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist or 
is inaccessible.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

However, if the "_HBASESTATS_" schema and histogram tables exist, we get a more 
friendly and normal looking error:

>>showstats for table hbase."_CELL_"."xyz" on existing columns;

Histogram data for Table HBASE."_CELL_"."xyz"
Table ID: 0

   Hist ID # IntsRowcount UEC Colname(s)
== == === === ===

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>

The objective of this JIRA is to give the latter error message even if the 
"_HBASESTATS_" histograms tables do not exist.



> SHOWSTATS sometimes gives strange message when stats don't exist
> 
>
> Key: TRAFODION-2728
> URL: https://issues.apache.org/jira/browse/TRAFODION-2728
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.3-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>Priority: Minor
>
> When doing a SHOWSTATS on a native HBase table, the error message is strange 
> if the "_HBASESTATS_" histogram tables do not exist. For 
> example, in a fresh instance where that schema has not been created yet:
> >>showstats for table hbase."_ROW_"."xyz" on existing columns;
> >>
> *** ERROR[9200] UPDATE STATISTICS for table HBASE._ROW_.xyz encountered an 
> error (4082) from statement .
> *** ERROR[4082] Object TRAFODION."_HBASESTATS_".SB_HISTOGRAMS does not exist 
> or is inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> However, if the "_HBASESTATS_" schema and histogram tables exist, we get a 
> more friendly and normal looking error:
> >>showstats for table hbase."_CELL_"."xyz" on existing columns;
> Histogram data for Table HBASE."_CELL_"."xyz"
> Table ID: 0
>Hist ID # IntsRowcount UEC Colname(s)
> == == === === ===
> No Histograms exist for the requested columns or groups
> --- SQL operation complete.
> >>
> The objective of this JIRA is to give the latter error message even if the 
> "_HBASESTATS_" histograms tables do not exist.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2700) Query that selects only a single salt value gets parallel plan

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16109182#comment-16109182
 ] 

David Wayne Birdsall commented on TRAFODION-2700:
-

So, in the case where the primary key is covered by equality predicates, does 
your proposed solution yield DoP of one? (For example, does the code correctly 
compute that the number of regions accessed is one?)

> Query that selects only a single salt value gets parallel plan
> --
>
> Key: TRAFODION-2700
> URL: https://issues.apache.org/jira/browse/TRAFODION-2700
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.1-incubating
> Environment: any
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> For some queries we saw parallel plans where the parallelism didn't really 
> help, because the WHERE predicate selected only a single salt values. The 
> overhead isn't huge, but it can add up when executing many such queries.
> Example:
> create table ts(a integer not null primary key, b char(2000)) salt using 4 
> partitions;
> explain  select count(*) from ts <<+ cardinality 1e7>> where a =1;
> The problem, I think, is in method 
> SimpleFileScanOptimizer::scmComputeCostVectorsForHbase(), file 
> core/sql/optimizer/ScmCostMethod.cpp. This computes separate degrees of 
> parallelism for the region server side and the client side and scales the 
> costs incurred on each side separately.
> However, if there are more ESPs (clients) than regions, some ESPs have 
> nothing to do, limiting the parallelism. On the other hand, if there are more 
> regions than ESPs, each ESP reads regions sequentially, so that limits the 
> DoP on the region server side.
> Therefore, my suggested fix is to use the minimum of those two DoPs to 
> compute the cost.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2700) Query that selects only a single salt value gets parallel plan

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16109154#comment-16109154
 ] 

David Wayne Birdsall commented on TRAFODION-2700:
-

The example cited is a unique access; I hope we get a serial plan for that?

I suppose a more interesting example might be when the primary key is two or 
more columns, but we salt on only one column? (More generally a proper subset 
of the key columns? with a WHERE clause of equality predicates on that proper 
subset?)

> Query that selects only a single salt value gets parallel plan
> --
>
> Key: TRAFODION-2700
> URL: https://issues.apache.org/jira/browse/TRAFODION-2700
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.1-incubating
> Environment: any
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> For some queries we saw parallel plans where the parallelism didn't really 
> help, because the WHERE predicate selected only a single salt values. The 
> overhead isn't huge, but it can add up when executing many such queries.
> Example:
> create table ts(a integer not null primary key, b char(2000)) salt using 4 
> partitions;
> explain  select count(*) from ts <<+ cardinality 1e7>> where a =1;
> The problem, I think, is in method 
> SimpleFileScanOptimizer::scmComputeCostVectorsForHbase(), file 
> core/sql/optimizer/ScmCostMethod.cpp. This computes separate degrees of 
> parallelism for the region server side and the client side and scales the 
> costs incurred on each side separately.
> However, if there are more ESPs (clients) than regions, some ESPs have 
> nothing to do, limiting the parallelism. On the other hand, if there are more 
> regions than ESPs, each ESP reads regions sequentially, so that limits the 
> DoP on the region server side.
> Therefore, my suggested fix is to use the minimum of those two DoPs to 
> compute the cost.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2680) CSV_FORMAT function

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16095548#comment-16095548
 ] 

David Wayne Birdsall commented on TRAFODION-2680:
-

Anoop + Rohit: I'll take a look at the trafci feature. I wasn't familiar with 
it.

> CSV_FORMAT function
> ---
>
> Key: TRAFODION-2680
> URL: https://issues.apache.org/jira/browse/TRAFODION-2680
> Project: Apache Trafodion
>  Issue Type: New Feature
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> To make it easier to get Trafodion data into spreadsheets, add a CSV_FORMAT 
> function to Trafodion SQL.
> Syntax: CSV_FORMAT(argument1, ... ,argumentn)
> Semantics: The result of CSV_FORMAT is a VARCHAR string consisting of a 
> comma-separated list of the argument values. If the argument itself contains 
> a comma, it is surrounded with double quotes.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2680) CSV_FORMAT function

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2680:
---

 Summary: CSV_FORMAT function
 Key: TRAFODION-2680
 URL: https://issues.apache.org/jira/browse/TRAFODION-2680
 Project: Apache Trafodion
  Issue Type: New Feature
  Components: sql-cmp
Affects Versions: 2.2-incubating
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


To make it easier to get Trafodion data into spreadsheets, add a CSV_FORMAT 
function to Trafodion SQL.

Syntax: CSV_FORMAT(argument1, ... ,argumentn)

Semantics: The result of CSV_FORMAT is a VARCHAR string consisting of a 
comma-separated list of the argument values. If the argument itself contains a 
comma, it is surrounded with double quotes.




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (TRAFODION-2661) MDAM not chosen for OR predicate of leading index column

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16072914#comment-16072914
 ] 

David Wayne Birdsall commented on TRAFODION-2661:
-

While doing further due diligence on this issue, I encountered another bug. In 
this bug, we have a table with several key columns. We have a query with key 
predicates on the first two columns (ignoring "_SALT_"), and also a key 
predicate on the last key column. An MDAM plan on the first two columns would 
be a good choice (and in fact the Optimizer chooses this), but the plan that 
gets generated traverses to the last key column (resulting in terrible 
performance). The bug is that on a partitioned table, at pre-code-gen time, we 
would bypass the code that passes "stop column" information to the generator. 
The script below reproduces the problem:

?section setup

drop table if exists mdamex;

create table mdamex
(a int not null,
 b int not null,
 c int not null,
 d int not null,
 e int not null,
 f int not null,
 g int,
 primary key (a, b, c, d, e, f))
 salt using 4 partitions;

upsert using load into mdamex
select c0, c1, c2, c3, c4, c5, c0 + 3*c1 + 7*c2 + 11*c3 + 17*c4 + 29*c5
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c0
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9,10,11 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5;

update statistics for table mdamex on every column;

?section query

prepare xx from
  select * from mdamex
  where a in (3,5) and b > 3 and f in (4,6,8);
explain xx;

showshape  
  select * from mdamex
  where a in (3,5) and b > 3 and f in (4,6,8);

prepare yy from
  select * from mdamex
  where a in (3,5) and b > 3;
explain yy;

showshape  
  select * from mdamex
  where a in (3,5) and b > 3;

In the Explain for query xx, notice the MDAM disjunct contains the predicate on 
F. This reflects the generated plan. The showshape output, however, happens to 
show what the Optimizer chose; that is, MDAM only on the first three columns 
("_SALT_", A and B). In the Explain for query yy, the explain output correctly 
shows an MDAM plan on just the first three columns.


> MDAM not chosen for OR predicate of leading index column
> 
>
> Key: TRAFODION-2661
> URL: https://issues.apache.org/jira/browse/TRAFODION-2661
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.0-incubating
>Reporter: Hans Zeller
>Assignee: David Wayne Birdsall
>
> We saw the following issue in a customer scenario:
> SELECT SUM(c)
> from tbl2
> where a in ('A','B')
> AND b >= '8';
> The table has a salted index on column a. That index has many more columns 
> (13 in this case, including the salt column).
> The plan we would expect is an MDAM plan with no predicate on _SALT_ and the 
> OR predicate on column a.
> However, we get a full index scan (we can use an index-only scan in this 
> case).
> When we force an MDAM plan, we see a vastly higher cost (250,000 in our 
> example) than for the single subset plan (cost of 9 in our example).
> Dave has already mentioned that this has to do with RangeSpecs. The following 
> workaround gives the MDAM plan for us:
> cqd RANGESPEC_TRANSFORMATION 'off';



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2673) Improve performance of incremental UPDATE STATISTICS

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2673?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2673.
---

Verified the change.

> Improve performance of incremental UPDATE STATISTICS
> 
>
> Key: TRAFODION-2673
> URL: https://issues.apache.org/jira/browse/TRAFODION-2673
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All, but most noticeable on large sample tables
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When running an incremental UPDATE STATISTICS utility, rows satisfying the 
> WHERE clause are first deleted from the sample table, then replaced with a 
> new sample taken from the base table.
> The statement used to delete rows from the sample table is a vanilla DELETE, 
> which performs poorly when the set of rows to be deleted is large. It would 
> be better (and much faster) to use DELETE NO ROLLBACK.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2673) Improve performance of incremental UPDATE STATISTICS

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2673?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2673.
-
Resolution: Fixed

> Improve performance of incremental UPDATE STATISTICS
> 
>
> Key: TRAFODION-2673
> URL: https://issues.apache.org/jira/browse/TRAFODION-2673
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All, but most noticeable on large sample tables
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> When running an incremental UPDATE STATISTICS utility, rows satisfying the 
> WHERE clause are first deleted from the sample table, then replaced with a 
> new sample taken from the base table.
> The statement used to delete rows from the sample table is a vanilla DELETE, 
> which performs poorly when the set of rows to be deleted is large. It would 
> be better (and much faster) to use DELETE NO ROLLBACK.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2673) Improve performance of incremental UPDATE STATISTICS

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2673:
---

 Summary: Improve performance of incremental UPDATE STATISTICS
 Key: TRAFODION-2673
 URL: https://issues.apache.org/jira/browse/TRAFODION-2673
 Project: Apache Trafodion
  Issue Type: Improvement
  Components: sql-cmp
Affects Versions: 2.2-incubating
 Environment: All, but most noticeable on large sample tables
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


When running an incremental UPDATE STATISTICS utility, rows satisfying the 
WHERE clause are first deleted from the sample table, then replaced with a new 
sample taken from the base table.

The statement used to delete rows from the sample table is a vanilla DELETE, 
which performs poorly when the set of rows to be deleted is large. It would be 
better (and much faster) to use DELETE NO ROLLBACK.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Resolved] (TRAFODION-2662) Incremental UPDATE STATS fails on very large sample tables

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2662.
-
   Resolution: Fixed
Fix Version/s: 2.2-incubating

> Incremental UPDATE STATS fails on very large sample tables
> --
>
> Key: TRAFODION-2662
> URL: https://issues.apache.org/jira/browse/TRAFODION-2662
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Large clusters
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> A user attempted to use the incremental UPDATE STATISTICS feature on a 90 
> billion row table. The persistent sample table was created using a sampling 
> rate of 5 per cent, and so it had about 4.5 billion rows. The incremental 
> UPDATE STATISTICS command failed with an error like the following:
> >>Update statistics for table traf1 on existing columns incremental where C2 
> >>>= '2017-06-10 00:00:00';
> *** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly 
> due to an invalid WHERE clause.
> *** ERROR[15001] A syntax error occurred at or before: 
> SELECT  FROM TRAFODION.SEABASE.TRAF_SAMPLE_32_1498066565_999632 WHERE  C2 >= 
> '2
>^ (12 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> The script below reproduces the problem on a workstation. The script creates 
> a table, puts a few rows in it, creates a sample table, then doctors the 
> sample table metadata to make UPDATE STATISTICS think it has 4.5 billion 
> rows. The failure then occurs.
> ?section setup
> drop table if exists traf1;
> CREATE TABLE traf1
>   ( 
> C1 CHAR(32) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C2 TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , C3 CHAR(32) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C4 VARCHAR(4096 BYTES) CHARACTER SET UTF8
>   COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C5 VARCHAR(1024 BYTES) CHARACTER SET UTF8
>   COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , C6 SMALLINT DEFAULT 1 NOT SERIALIZED
>   , PRIMARY KEY (C1 ASC, C2 DESC, C3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>ON (C1)
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> BLOCKSIZE = '262144',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> insert into traf1 values ('abcdef',current_timestamp - interval '20' day,
>   '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('abcdef01',current_timestamp - interval '20' day,
>   '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('012345',current_timestamp - interval '20' day,
>   'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('01234567',current_timestamp - interval '20' day,
>   'abc01234','www.blahblahblah.com','a bit of it is fun',31);
> insert into traf1 values ('def01234',current_timestamp - interval '20' day,
> '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('adbf01',current_timestamp - interval '20' day,
> '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('a0b0c0',current_timestamp - interval '20' day,
> 'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('021357',current_timestamp - interval '20' day,
> 'abc01234','www.blahblahblah.com','a bit of it is fun',30);
> insert into traf1 values ('abcdef',current_timestamp - interval '10' day,
>   '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('abcddf01',current_timestamp - interval '10' day,
>   '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('01234345',current_timestamp - interval '10' day,
>   'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('034567',current_timestamp - interval '10' day,
>   'abc01234','www.blahblahblah.com','a bit of it is fun',31);
> insert into traf1 values ('def8201234',current_timestamp - interval '10' day,
> '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',18);
> insert into traf1 values ('adbf9a01',current_timestamp - interval '10' day,
> '0123456789abcdef01','www.C4edin.com','a bit of try twice',2);
> insert into traf1 values ('a0bbc0c0',current_timestamp - interval '10' day,
> 'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values 

[jira] [Closed] (TRAFODION-2662) Incremental UPDATE STATS fails on very large sample tables

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2662.
---

Have verified the fix.

> Incremental UPDATE STATS fails on very large sample tables
> --
>
> Key: TRAFODION-2662
> URL: https://issues.apache.org/jira/browse/TRAFODION-2662
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Large clusters
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> A user attempted to use the incremental UPDATE STATISTICS feature on a 90 
> billion row table. The persistent sample table was created using a sampling 
> rate of 5 per cent, and so it had about 4.5 billion rows. The incremental 
> UPDATE STATISTICS command failed with an error like the following:
> >>Update statistics for table traf1 on existing columns incremental where C2 
> >>>= '2017-06-10 00:00:00';
> *** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly 
> due to an invalid WHERE clause.
> *** ERROR[15001] A syntax error occurred at or before: 
> SELECT  FROM TRAFODION.SEABASE.TRAF_SAMPLE_32_1498066565_999632 WHERE  C2 >= 
> '2
>^ (12 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> The script below reproduces the problem on a workstation. The script creates 
> a table, puts a few rows in it, creates a sample table, then doctors the 
> sample table metadata to make UPDATE STATISTICS think it has 4.5 billion 
> rows. The failure then occurs.
> ?section setup
> drop table if exists traf1;
> CREATE TABLE traf1
>   ( 
> C1 CHAR(32) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C2 TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>   DROPPABLE NOT SERIALIZED
>   , C3 CHAR(32) CHARACTER SET ISO88591 COLLATE
>   DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C4 VARCHAR(4096 BYTES) CHARACTER SET UTF8
>   COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , C5 VARCHAR(1024 BYTES) CHARACTER SET UTF8
>   COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , C6 SMALLINT DEFAULT 1 NOT SERIALIZED
>   , PRIMARY KEY (C1 ASC, C2 DESC, C3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>ON (C1)
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> BLOCKSIZE = '262144',
> MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> insert into traf1 values ('abcdef',current_timestamp - interval '20' day,
>   '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('abcdef01',current_timestamp - interval '20' day,
>   '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('012345',current_timestamp - interval '20' day,
>   'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('01234567',current_timestamp - interval '20' day,
>   'abc01234','www.blahblahblah.com','a bit of it is fun',31);
> insert into traf1 values ('def01234',current_timestamp - interval '20' day,
> '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('adbf01',current_timestamp - interval '20' day,
> '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('a0b0c0',current_timestamp - interval '20' day,
> 'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('021357',current_timestamp - interval '20' day,
> 'abc01234','www.blahblahblah.com','a bit of it is fun',30);
> insert into traf1 values ('abcdef',current_timestamp - interval '10' day,
>   '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);
> insert into traf1 values ('abcddf01',current_timestamp - interval '10' day,
>   '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);
> insert into traf1 values ('01234345',current_timestamp - interval '10' day,
>   'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('034567',current_timestamp - interval '10' day,
>   'abc01234','www.blahblahblah.com','a bit of it is fun',31);
> insert into traf1 values ('def8201234',current_timestamp - interval '10' day,
> '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',18);
> insert into traf1 values ('adbf9a01',current_timestamp - interval '10' day,
> '0123456789abcdef01','www.C4edin.com','a bit of try twice',2);
> insert into traf1 values ('a0bbc0c0',current_timestamp - interval '10' day,
> 'abc012','www.blahblah.com','a bit of it is fun',2);
> insert into traf1 values ('02111357',current_timestamp - interval '10' day,
> 

[jira] [Resolved] (TRAFODION-2655) MDAM plans on prefixes sometimes not chosen when they should be

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2655?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall resolved TRAFODION-2655.
-
   Resolution: Fixed
Fix Version/s: 2.2-incubating

> MDAM plans on prefixes sometimes not chosen when they should be
> ---
>
> Key: TRAFODION-2655
> URL: https://issues.apache.org/jira/browse/TRAFODION-2655
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Large clusters
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> Below are two scripts that reproduce the problems.
> In the first problem, a query using parameters does not get an MDAM plan, 
> even though it would be beneficial to do so. If the parameters on column C2 
> are replaced with literals that are close enough together, we do get the 
> desired MDAM plan.
> In the second problem, using a larger version of the same table, a query with 
> equality predicates on the first and third key columns ("first" and "third" 
> ignoring the SALT column) does not get an MDAM plan on the first key column 
> but chooses a full table scan instead.
> These problems were noticed on a cluster; it's not practical to create tables 
> of the necessary size on a workstation. The scripts below reproduce the 
> problem on a workstation by faking the Optimizer into thinking it is dealing 
> with much larger tables.
> First script:
> ?section setup
> drop table if exists MDAM_Q1_TEST;
> create table MDAM_Q1_TEST
> ( c1 integer not null,  -- will have uec 2
>   c2 integer not null,  -- will have uec 1,000,000
>   c3 integer not null,  -- will have uec 766
>   c4 integer not null,  -- will have uec 46
>   c5 integer not null,  -- will have uec don't care (but 1,000,000)
>   primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q1_TEST
> select mod(c0+c1+c2+c3+c4,2),
>c0 + 10*c1 + 100*c2 + 1000*c3 + 1*c4 + 10*c5,
>c3 + 10*c1 + 74*c5,
>c4 + 4*c5,
>c5 + 10*c4 + 100*c3 + 1000*c2 + 1*c1 + 10*c0   
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table MDAM_Q1_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 265 million rows in the table
> update sb_histograms set rowcount = 265 * rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q1_TEST');
> update sb_histogram_intervals set interval_rowcount = 265 * interval_rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q1_TEST');
> showstats for table MDAM_Q1_TEST on existing columns;
> ?section query1
> prepare xx from
> select * from MDAM_Q1_TEST
> where c1 = ? and c2 > ? and c2 <= ? and c3 = ? and c4 = ?;
> explain xx;  -- the plan gets a full scan instead of MDAM
> Second script
> ?section setup
> drop table if exists MDAM_Q2_TEST;
> create table MDAM_Q2_TEST
> ( c1 integer not null,  -- will have uec 100
>   c2 integer not null,  -- will have uec 2,000,000
>   c3 integer not null,  -- will have uec 9,100
>   c4 integer not null,  -- will have uec 11,800
>   c5 integer not null,  -- will have uec don't care 
>   primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q2_TEST
> select c3 + 10*c4,
>c0 + 10*c1 + 100*c2 + 1000*c3 + 1*c4 + 10*c5 + 100*c6,
>c3 + 10*c1 + 100*c5 + 900*c4,
>c4 + 10*c5 + 100*c2 + 600*c1 + 600*c3,
>c5 + 10*c4 + 100*c3 + 1000*c2 + 1*c1 + 10*c0 + 7*c6   
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5
> transpose 0,1 as c6;
> ?section setupstats
> update statistics for table MDAM_Q2_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 11 billion rows in the table
> update sb_histograms set rowcount = 5500 * rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q2_TEST');
> update sb_histogram_intervals set interval_rowcount = 5500 * interval_rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q2_TEST');
> -- finally fake out the UEC on C2 so optimizer will think
> -- the UEC is 5 billion instead of 2 million
> update sb_histograms set 

[jira] [Closed] (TRAFODION-2655) MDAM plans on prefixes sometimes not chosen when they should be

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2655?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2655.
---

Have verified the fix.

> MDAM plans on prefixes sometimes not chosen when they should be
> ---
>
> Key: TRAFODION-2655
> URL: https://issues.apache.org/jira/browse/TRAFODION-2655
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: Large clusters
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> Below are two scripts that reproduce the problems.
> In the first problem, a query using parameters does not get an MDAM plan, 
> even though it would be beneficial to do so. If the parameters on column C2 
> are replaced with literals that are close enough together, we do get the 
> desired MDAM plan.
> In the second problem, using a larger version of the same table, a query with 
> equality predicates on the first and third key columns ("first" and "third" 
> ignoring the SALT column) does not get an MDAM plan on the first key column 
> but chooses a full table scan instead.
> These problems were noticed on a cluster; it's not practical to create tables 
> of the necessary size on a workstation. The scripts below reproduce the 
> problem on a workstation by faking the Optimizer into thinking it is dealing 
> with much larger tables.
> First script:
> ?section setup
> drop table if exists MDAM_Q1_TEST;
> create table MDAM_Q1_TEST
> ( c1 integer not null,  -- will have uec 2
>   c2 integer not null,  -- will have uec 1,000,000
>   c3 integer not null,  -- will have uec 766
>   c4 integer not null,  -- will have uec 46
>   c5 integer not null,  -- will have uec don't care (but 1,000,000)
>   primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q1_TEST
> select mod(c0+c1+c2+c3+c4,2),
>c0 + 10*c1 + 100*c2 + 1000*c3 + 1*c4 + 10*c5,
>c3 + 10*c1 + 74*c5,
>c4 + 4*c5,
>c5 + 10*c4 + 100*c3 + 1000*c2 + 1*c1 + 10*c0   
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table MDAM_Q1_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 265 million rows in the table
> update sb_histograms set rowcount = 265 * rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q1_TEST');
> update sb_histogram_intervals set interval_rowcount = 265 * interval_rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q1_TEST');
> showstats for table MDAM_Q1_TEST on existing columns;
> ?section query1
> prepare xx from
> select * from MDAM_Q1_TEST
> where c1 = ? and c2 > ? and c2 <= ? and c3 = ? and c4 = ?;
> explain xx;  -- the plan gets a full scan instead of MDAM
> Second script
> ?section setup
> drop table if exists MDAM_Q2_TEST;
> create table MDAM_Q2_TEST
> ( c1 integer not null,  -- will have uec 100
>   c2 integer not null,  -- will have uec 2,000,000
>   c3 integer not null,  -- will have uec 9,100
>   c4 integer not null,  -- will have uec 11,800
>   c5 integer not null,  -- will have uec don't care 
>   primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q2_TEST
> select c3 + 10*c4,
>c0 + 10*c1 + 100*c2 + 1000*c3 + 1*c4 + 10*c5 + 100*c6,
>c3 + 10*c1 + 100*c5 + 900*c4,
>c4 + 10*c5 + 100*c2 + 600*c1 + 600*c3,
>c5 + 10*c4 + 100*c3 + 1000*c2 + 1*c1 + 10*c0 + 7*c6   
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5
> transpose 0,1 as c6;
> ?section setupstats
> update statistics for table MDAM_Q2_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 11 billion rows in the table
> update sb_histograms set rowcount = 5500 * rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q2_TEST');
> update sb_histogram_intervals set interval_rowcount = 5500 * interval_rowcount
>  where table_uid = (select object_uid from "_MD_".objects where object_name = 
> 'MDAM_Q2_TEST');
> -- finally fake out the UEC on C2 so optimizer will think
> -- the UEC is 5 billion instead of 2 million
> update sb_histograms set total_uec = 2500 * total_uec
>  where 

[jira] [Created] (TRAFODION-2667) Incorrect UEC calculation

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2667:
---

 Summary: Incorrect UEC calculation
 Key: TRAFODION-2667
 URL: https://issues.apache.org/jira/browse/TRAFODION-2667
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
 Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


When applying a predicate to histogram data for the same column, the resulting 
UECs are sometimes incorrect.

ColStatDesc::applySel (optimizer/ColStatDesc.cpp) calls 
ColStatDesc::calculateCorrectResultUec to do this UEC calculation. The latter 
assumes that the predicate is on a column that is independent of the histogram 
column. In the case that the predicate is on the same column as the histogram, 
it is clearly quite dependent! Instead, the UEC should be reduced 
proportionately in the same manner as the row count.

Though this is a simple change, the consequences may be far-reaching. Plan 
changes are likely. Adjustments to Optimizer tuning parameters are likely to be 
required. This change, then, should only be done in concert with a larger 
tuning effort.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (TRAFODION-2662) Incremental UPDATE STATS fails on very large sample tables

[David Wayne Birdsall (JIRA)]

David Wayne Birdsall created TRAFODION-2662:
---

 Summary: Incremental UPDATE STATS fails on very large sample tables
 Key: TRAFODION-2662
 URL: https://issues.apache.org/jira/browse/TRAFODION-2662
 Project: Apache Trafodion
  Issue Type: Bug
  Components: sql-cmp
Affects Versions: 2.2-incubating
 Environment: Large clusters
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall


A user attempted to use the incremental UPDATE STATISTICS feature on a 90 
billion row table. The persistent sample table was created using a sampling 
rate of 5 per cent, and so it had about 4.5 billion rows. The incremental 
UPDATE STATISTICS command failed with an error like the following:

>>Update statistics for table traf1 on existing columns incremental where C2 >= 
>>'2017-06-10 00:00:00';

*** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly 
due to an invalid WHERE clause.

*** ERROR[15001] A syntax error occurred at or before: 
SELECT  FROM TRAFODION.SEABASE.TRAF_SAMPLE_32_1498066565_999632 WHERE  C2 >= '2
   ^ (12 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.

The script below reproduces the problem on a workstation. The script creates a 
table, puts a few rows in it, creates a sample table, then doctors the sample 
table metadata to make UPDATE STATISTICS think it has 4.5 billion rows. The 
failure then occurs.

?section setup

drop table if exists traf1;

CREATE TABLE traf1
  ( 
C1 CHAR(32) CHARACTER SET ISO88591 COLLATE
  DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , C2 TIMESTAMP(6) NO DEFAULT NOT NULL NOT
  DROPPABLE NOT SERIALIZED
  , C3 CHAR(32) CHARACTER SET ISO88591 COLLATE
  DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , C4 VARCHAR(4096 BYTES) CHARACTER SET UTF8
  COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , C5 VARCHAR(1024 BYTES) CHARACTER SET UTF8
  COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C6 SMALLINT DEFAULT 1 NOT SERIALIZED
  , PRIMARY KEY (C1 ASC, C2 DESC, C3 ASC)
  )
  SALT USING 4 PARTITIONS
   ON (C1)
 ATTRIBUTES ALIGNED FORMAT 
  HBASE_OPTIONS 
  ( 
DATA_BLOCK_ENCODING = 'FAST_DIFF',
BLOCKSIZE = '262144',
MEMSTORE_FLUSH_SIZE = '1073741824' 
  ) 
;

insert into traf1 values ('abcdef',current_timestamp - interval '20' day,
  '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);

insert into traf1 values ('abcdef01',current_timestamp - interval '20' day,
  '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);

insert into traf1 values ('012345',current_timestamp - interval '20' day,
  'abc012','www.blahblah.com','a bit of it is fun',2);

insert into traf1 values ('01234567',current_timestamp - interval '20' day,
  'abc01234','www.blahblahblah.com','a bit of it is fun',31);

insert into traf1 values ('def01234',current_timestamp - interval '20' day,
'0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);

insert into traf1 values ('adbf01',current_timestamp - interval '20' day,
'0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);

insert into traf1 values ('a0b0c0',current_timestamp - interval '20' day,
'abc012','www.blahblah.com','a bit of it is fun',2);

insert into traf1 values ('021357',current_timestamp - interval '20' day,
'abc01234','www.blahblahblah.com','a bit of it is fun',30);

insert into traf1 values ('abcdef',current_timestamp - interval '10' day,
  '0123456789abcdef','www.xyz.stuff.com','a bit of stuff',14);

insert into traf1 values ('abcddf01',current_timestamp - interval '10' day,
  '0123456789abcdef01','www.xyz.morestuff.com','a bit of more stuff',12);

insert into traf1 values ('01234345',current_timestamp - interval '10' day,
  'abc012','www.blahblah.com','a bit of it is fun',2);

insert into traf1 values ('034567',current_timestamp - interval '10' day,
  'abc01234','www.blahblahblah.com','a bit of it is fun',31);

insert into traf1 values ('def8201234',current_timestamp - interval '10' day,
'0123456789abcdef','www.xyz.stuff.com','a bit of stuff',18);

insert into traf1 values ('adbf9a01',current_timestamp - interval '10' day,
'0123456789abcdef01','www.C4edin.com','a bit of try twice',2);

insert into traf1 values ('a0bbc0c0',current_timestamp - interval '10' day,
'abc012','www.blahblah.com','a bit of it is fun',2);

insert into traf1 values ('02111357',current_timestamp - interval '10' day,
'abc01234','www.blahblahblah.com','a bit of it is fun',30);

insert into traf1 values 
('def01234',current_timestamp,'01234789abcdef','www.xyz.stuff.com','a bit of 
stuff',14);

insert into traf1 values 
('adbf01',current_timestamp,'0123456789abef01','www.xyz.morestuff.com','a bit 
of more stuff',12);

insert into traf1 values 

[jira] [Commented] (TRAFODION-2661) MDAM not chosen for OR predicate of leading index column

[David Wayne Birdsall (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16057719#comment-16057719
 ] 

David Wayne Birdsall commented on TRAFODION-2661:
-

See also JIRA TRAFODION-1641.


> MDAM not chosen for OR predicate of leading index column
> 
>
> Key: TRAFODION-2661
> URL: https://issues.apache.org/jira/browse/TRAFODION-2661
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.0-incubating
>Reporter: Hans Zeller
>Assignee: David Wayne Birdsall
>
> We saw the following issue in a customer scenario:
> SELECT SUM(c)
> from tbl2
> where a in ('A','B')
> AND b >= '8';
> The table has a salted index on column a. That index has many more columns 
> (13 in this case, including the salt column).
> The plan we would expect is an MDAM plan with no predicate on _SALT_ and the 
> OR predicate on column a.
> However, we get a full index scan (we can use an index-only scan in this 
> case).
> When we force an MDAM plan, we see a vastly higher cost (250,000 in our 
> example) than for the single subset plan (cost of 9 in our example).
> Dave has already mentioned that this has to do with RangeSpecs. The following 
> workaround gives the MDAM plan for us:
> cqd RANGESPEC_TRANSFORMATION 'off';



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2529) Update stats on cell or row access to a Trafodion table raises error 9252

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2529?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2529.
---

I have verified this change.

> Update stats on cell or row access to a Trafodion table raises error 9252
> -
>
> Key: TRAFODION-2529
> URL: https://issues.apache.org/jira/browse/TRAFODION-2529
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2552) Skew buster plan not chosen when join predicate involves SUBSTRs

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2552?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2552.
---

I have verified this change.

> Skew buster plan not chosen when join predicate involves SUBSTRs
> 
>
> Key: TRAFODION-2552
> URL: https://issues.apache.org/jira/browse/TRAFODION-2552
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.1-incubating, 2.2-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> Skew-buster is a feature of Trafodion that modifies the partitioning function 
> on joins when skewed values are present. When we notice that there are a few 
> very frequent values among many low frequency values in a join column, we use 
> a different strategy for joining the frequent values than the non-frequent 
> ones.
> The following script reproduces the problem:
> ?section setup
> drop table if exists skewSubstr;
> create table skewSubstr (a int not null, b char(16), primary key (a))
>   salt using 4 partitions;
> upsert using load into skewSubstr
> select 
>   x1 + 10*x2 + 100*x3 + 1000*x4 + 1*x5 + 10*x6,
>   case when mod(x1 + 10*x2 + 100*x3 + 1000*x4 + 1*x5 + 10*x6,97) = 0 
> then 'askewvalue'
>else char(x5+97) || char(mod(x4 + 3*x2 + 7*x6,26)+97) || char(x4+97) 
> || char(x1+97) || 
> char(mod(2*x1 + 5*x5 + x6,26)+97) || char(mod(x1+x2+x3,26)+97) || 
> char(x6+97) ||
> char(x2+97) || char(x3+97) || char(mod(x1-x2+x4+2*x6,26)+97) 
>end
> -- the from clause below creates 1,000,000 rows, the cross product of
> -- 6 copies of { 0, ... 9 }
>   from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
> transpose 0,1,2,3,4,5,6,7,8,9 as x2
> transpose 0,1,2,3,4,5,6,7,8,9 as x3
> transpose 0,1,2,3,4,5,6,7,8,9 as x4
> transpose 0,1,2,3,4,5,6,7,8,9 as x5
> transpose 0,1,2,3,4,5,6,7,8,9 as x6;
> update statistics for table skewSubtr on every column;
> create table otherTable(c int not null, d char(16), primary key (c))
>   salt using 4 partitions;
> upsert using load into otherTable
>   select a, case when b = 'askewvalue' then substr(b,1,10) || 
> char(mod(a,26)+97) else b end
> from skewSubstr;
> update statistics for table otherTable on every column;
> ?section doit
> cqd SKEW_SENSITIVITY_THRESHOLD '0.0';
> cqd SKEW_ROWCOUNT_THRESHOLD '10';
> prepare goodQuery from select count(*) from skewSubstr join otherTable
>   on b = d;
> -- notice in the explain, that the descriptions on the esp_exchanges are
> -- (h2-ud) and (h2-br), which indicates that skew-buster is operative
> explain options 'f' goodQuery;
> prepare badQuery from select count(*) from skewSubstr join otherTable
>   on substr(b,1,9) = substr(d,1,9);
> -- notice in the explain, that the descriptions on the esp_exchanges are
> -- (hash2), which indicates that skew-buster is NOT operative
> explain options 'f' badQuery;
> To see the problem, look at the output of the EXPLAINs:
> >>
> >>cqd SKEW_SENSITIVITY_THRESHOLD '0.0';
> --- SQL operation complete.
> >>cqd SKEW_ROWCOUNT_THRESHOLD '10';
> --- SQL operation complete.
> >>
> >>prepare goodQuery from select count(*) from skewSubstr join otherTable
> +>  on b = d;
> --- SQL command prepared.
> >>
> >>-- notice in the explain, that the descriptions on the esp_exchanges are
> >>-- (h2-ud) and (h2-br), which indicates that skew-buster is operative
> >>explain options 'f' goodQuery;
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
> 8.9root  1.00E+000
> 7.8sort_partial_aggr_ro  1.00E+000
> 6.7esp_exchange1:4(h2-ud)1.00E+000
> 5.6sort_partial_aggr_le  1.00E+000
> 425hybrid_hash_join  1.01E+006
> 3.4esp_exchange4(h2-ud):4(hash2) 1.00E+006
> ..3trafodion_scan  SKEWSUBSTR1.00E+006
> 1.2esp_exchange4(h2-br):4(hash2) 1.00E+006
> ..1trafodion_scan  OTHERTABLE1.00E+006
> --- SQL operation complete.
> >>
> >>prepare badQuery from select count(*) from skewSubstr join otherTable
> +>  on substr(b,1,9) = substr(d,1,9);
> --- SQL command prepared.
> >>
> >>-- notice in the explain, that the descriptions on the esp_exchanges are
> >>-- (hash2), which indicates that skew-buster is NOT operative
> >>explain options 'f' badQuery;
> LC   RC   OP   OPERATOR  

[jira] [Closed] (TRAFODION-2576) Incremental UPDATE STATS fails on long varchar values

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2576?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2576.
---

I have verified this change.

> Incremental UPDATE STATS fails on long varchar values
> -
>
> Key: TRAFODION-2576
> URL: https://issues.apache.org/jira/browse/TRAFODION-2576
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> The following test script demonstrates the problem:
> -- This script creates a table with varchar values longer than 256 characters.
> -- It tnen demonstrates issues with incremental stats on such values.
> ?section setup
> drop table if exists testbigcol;
> create table testbigcol
>   ( a int not null,
> b varchar(500),
> primary key (a) )
> salt using 4 partitions;
> insert into testbigcol values (1,'axq'),(2,'bxw');
> insert into testbigcol select x.a + 2 * y.a, x.b || y.b from testbigcol x 
> cross join testbigcol y;
> insert into testbigcol select x.a + 6 * y.a, x.b || y.b from testbigcol x 
> cross join testbigcol y;
> insert into testbigcol select x.a + 42 * y.a, x.b || y.b from testbigcol x 
> cross join testbigcol y;
> --  0 1 2 3 4 
> 5
> --  
> 012345678901234567890123456789012345678901234567890123
> update testbigcol set b = b || ' and now for something completely different 
> 0123456789'
>   where mod(a,2) = 1;
> update testbigcol set b = b || ' that was fun, and now for something 
> completely different 0123456789'
>   where mod(a,3) = 1;
> update testbigcol set b = b || ' and then there was a great hue and cry and 
> now for something completely different 0123456789'
>   where mod(a,5) = 1;
> update testbigcol set b = b || ' and we can do all sorts of things to make 
> these strings longer, and now for something completely different 0123456789'
>   where mod(a,7) = 1;
> ?section testit
> update statistics for table testbigcol on every column sample random 100 
> percent persistent;
> showstats for table testbigcol on existing columns detail;
> update testbigcol set b = 'c' || b where a > 1700;
> update statistics for table testbigcol on existing columns incremental where 
> a > 1700;
> showstats for table testbigcol on existing columns detail;
> When run, the last UPDATE STATISTICS command fails as follows:
> >>update statistics for table testbigcol on existing columns incremental 
> >>where a > 1700;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SEABASE.TESTBIGCOL 
> encountered an error (8402) from statement IUS data set I creation.
> *** ERROR[8402] A string overflow occurred during the evaluation of a 
> character expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,290 
> BYTES,ISO88591) Source Value:caxqbxwbxwaxqaxqbxwbxwbxw and now for something 
> completely different 0123456789 and then there was a great hue and cry and 
> now for something completely different 0123456789 and we can do all sorts of 
> to Target Type:VARCHAR(REC_BYTE_V_ASCII,256 BYTES,ISO88591).
> --- SQL operation failed with errors.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2605) Rework fix to JIRA Trafodion 2294

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2605.
---

I have verified this change.

> Rework fix to JIRA Trafodion 2294
> -
>
> Key: TRAFODION-2605
> URL: https://issues.apache.org/jira/browse/TRAFODION-2605
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2575) UPDATE STATS sometimes fails on very long varchars

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2575?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2575.
---

I have verified this change.

> UPDATE STATS sometimes fails on very long varchars
> --
>
> Key: TRAFODION-2575
> URL: https://issues.apache.org/jira/browse/TRAFODION-2575
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> One symptom is observing 6003 or 6004 warnings after UPDATE STATISTICS is 
> done. That is, UPDATE STATISTICS seems to succeed, but when repeated it fails 
> with an error 9200, and 6003 or 6004 warnings. These warnings indicate that 
> the histogram interval values are not in order. In one example that I 
> debugged, the problem was that we used a SELECT statement to compute 
> aggregates on the column, and the plan for that SELECT statement did not sort 
> the results, even though an ORDER BY was specified. While debugging that, I 
> ran into several other issues with sorts on long varchars. Concluding that 
> the engine is simply not up to prime-time for such sorts (and seeing that 
> such sorts are unneeded anyway), I plan to change UPDATE STATISTICS to first 
> truncate the string to a shorter length and then generate the SELECT.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2530) UPDATE STATISTICS is sensitive to tdm_arkcmp autocommit setting

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2530.
---

I have verified this change.

> UPDATE STATISTICS is sensitive to tdm_arkcmp autocommit setting
> ---
>
> Key: TRAFODION-2530
> URL: https://issues.apache.org/jira/browse/TRAFODION-2530
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.1-incubating, 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2474) Add optimizer/opt_error.h to analyzeMessageGuide.py

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2474?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2474.
---

I have verified this change.

> Add optimizer/opt_error.h to analyzeMessageGuide.py
> ---
>
> Key: TRAFODION-2474
> URL: https://issues.apache.org/jira/browse/TRAFODION-2474
> Project: Apache Trafodion
>  Issue Type: Sub-task
>  Components: elopment-tools
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2583) Remove deprecated CQD HIVE_MAX_STRING_LENGTH

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2583.
---

I have verified that this CQD is no longer recognized.

> Remove deprecated CQD HIVE_MAX_STRING_LENGTH
> 
>
> Key: TRAFODION-2583
> URL: https://issues.apache.org/jira/browse/TRAFODION-2583
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> Last November in JIRA TRAFODION-2352, we added a CQD 
> HIVE_MAX_STRING_LENGTH_IN_BYTES which sets the length of a hive string type 
> for Trafodion's purposes. (Hive string types have unlimited length. Trafodion 
> treats these as VARCHARs, but they must have a maximum length.) This CQD was 
> intended to take the place of HIVE_MAX_STRING_LENGTH, which was not clear 
> about the storage unit and therefore error-prone. We left the old CQD in to 
> allow folks to make the change in their scripts at their leisure.
> The time has come to remove the old CQD.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2603) Remove obsolete utility commands, turn off obsolete privileges

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2603?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2603.
---

I have verified the changes.

> Remove obsolete utility commands, turn off obsolete privileges
> --
>
> Key: TRAFODION-2603
> URL: https://issues.apache.org/jira/browse/TRAFODION-2603
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> The PUBLISH and UNPUBLISH commands were a feature in the predecessor product 
> and do not work in Trafodion. The specific need for them does not exist in 
> Trafodion, so these can be removed.
> The INITIALIZE TRAFODION, CREATE SEQUENCE utility was used to create the 
> SEQ_GEN table in the metadata to support sequences. That table is now created 
> as part of INITIALIZE TRAFODION, so there is no longer a need for the CREATE 
> SEQUENCE option. It can be removed.
> The DML_* component privileges allow the Root ID to grant various DML 
> privileges to any user, giving them the ability to do a given DML operation 
> on any Trafodion object. They don't work at the present. And the wisdom of 
> having such component privileges is debatable. For now, we would like to add 
> code that simply raises an error and says these privileges are not supported 
> if an attempt is made to grant them.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2634) FLOOR and CEIL return a float data type instead of the argument's data type

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2634?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2634.
---

I have verified the change.

> FLOOR and CEIL return a float data type instead of the argument's data type
> ---
>
> Key: TRAFODION-2634
> URL: https://issues.apache.org/jira/browse/TRAFODION-2634
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp, sql-exe
>Affects Versions: 2.2-incubating
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Closed] (TRAFODION-2635) Core on select count(*) using hbase cell access on a salted Trafodion table

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-2635?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall closed TRAFODION-2635.
---

I have verified that the issue is fixed.

> Core on select count(*) using hbase cell access on a salted Trafodion table
> ---
>
> Key: TRAFODION-2635
> URL: https://issues.apache.org/jira/browse/TRAFODION-2635
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.2-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.2-incubating
>
>
> The following sqlci script demonstrates the problem:
> set schema sch;
> create table t1 
> ( a int not null, b int, primary key (a) )
> salt using 4 partitions;
> prepare s1 from select count(*) from hbase."_CELL_"."TRAFODION.SCH.T1";
> When doing the prepare, sqlci cores. The stack trace is as follows:
> (gdb) bt
> #0  0x7f2c6036a625 in raise () from /lib64/libc.so.6
> #1  0x7f2c6036bd8d in abort () from /lib64/libc.so.6
> #2  0x7f2c62145165 in ?? ()
>from /usr/lib/jvm/java-1.7.0-openjdk.x86_64/jre/lib/amd64/server/libjvm.so
> #3  0x7f2c622b985f in ?? ()
>from /usr/lib/jvm/java-1.7.0-openjdk.x86_64/jre/lib/amd64/server/libjvm.so
> #4  0x7f2c62149d02 in JVM_handle_linux_signal ()
>from /usr/lib/jvm/java-1.7.0-openjdk.x86_64/jre/lib/amd64/server/libjvm.so
> #5  
> #6  0x004028ba in folly::fbstring_core::category (this=0x10)
> at ../export/FBString.h:1001
> #7  0x00402890 in folly::fbstring_core::size (this=0x10)
> at ../export/FBString.h:803
> #8  0x0040285c in folly::basic_fbstring std::allocator, folly::fbstring_core >::size (this=0x10)
> at ../export/FBString.h:1388
> #9  0x7f2c5fd92318 in folly::basic_fbstring std::allocator, folly::fbstring_core >::find (this=0x10, 
> needle=0x7f2c5898d71e "_ISO88591", pos=0, nsize=9)
> at ../export/FBString.h:1960
> #10 0x7f2c5fd8f899 in NAString::index (this=0x0, 
> pattern=0x7f2c5898d71e "_ISO88591", plen=9, startIndex=0, 
> cmp=NAString::exact) at ../export/NAStringDef.cpp:316
> #11 0x7f2c629e89ac in NAString::index (this=0x0, 
> s=0x7f2c5898d71e "_ISO88591", i=0, cmp=NAString::exact)
> at ../export/NAStringDef.h:581
> #12 0x7f2c588174e8 in ValueIdList::computeEncodedKey 
> (this=0x7f2c3c5886c0, 
> tDesc=0x7f2c3e4dffd8, isMaxKey=0, encodedKeyBuffer=@0x7ffd8cf4cc80, 
> keyBufLen=@0x7ffd8cf4cc7c) at ../optimizer/ValueDesc.cpp:6802
> #13 0x7f2c5861df90 in 
> RangePartitioningFunction::computeNumOfActivePartitions (this=0x7f2c3e4e3300, 
> skey=0x7f2c3c5885b8, tDesc=0x7f2c3e4dffd8)
> at ../optimizer/PartFunc.cpp:5110
> #14 0x7f2c586a7d16 in FileScan::FileScan (this=0x7f2c3c587a10, 
> tableName=..., tableDescPtr=0x7f2c3e4dffd8, indexDescPtr=0x7f2c3e4e17e0, 
> isReverseScan=0, baseCardinality=@0x7ffd8cf4cf3c, accessOpts=..., 
> groupAttributesPtr=0x7f2c3e4bd508, selectionPredicates=..., 
> disjuncts=..., 
> generatedCCPreds=..., otype=REL_HBASE_ACCESS)
> at ../optimizer/RelExpr.cpp:9671
> #15 0x7f2c586a9fe2 in HbaseAccess::HbaseAccess (this=0x7f2c3c587a10, 
> corrName=..., tableDesc=0x7f2c3e4dffd8, idx=0x7f2c3e4e17e0, 
> isReverseScan=0, baseCardinality=@0x7ffd8cf4cf3c, accessOptions=..., 
> groupAttributesPtr=0x7f2c3e4bd508, selectionPredicates=..., 
> disjuncts=..., 
> generatedCCPreds=..., otype=REL_HBASE_ACCESS, oHeap=0x7f2c3e4d4b90)
> at ../optimizer/RelExpr.cpp:10139
> #16 0x7f2c5837b897 in createAndInsertHbaseScan (idesc=0x7f2c3e4e17e0, bef=
> 0x7f2c3e4bcd70, memory=@0x7ffd8cf4d460, disjunctsPtr=0x7f2c3c586ef8, 
> generatedCCPreds=..., oc=SAME_ORDER, ixMdamFlag=MDAM_OFF)
> at ../optimizer/ImplRule.cpp:1083
> #17 0x7f2c5837ba99 in createAndInsertScan (idesc=0x7f2c3e4e17e0, 
> bef=0x7f2c3e4bcd70, memory=@0x7ffd8cf4d460, disjunctsPtr=0x7f2c3c586ef8, 
> generatedCCPreds=..., oc=SAME_ORDER, ixMdamFlag=MDAM_OFF, isHbase=1)
> at ../optimizer/ImplRule.cpp:1122
> #18 0x7f2c5837c0d0 in generateScanSubstitutes (before=0x7f2c3e4bcd70, 
> context=0x7f2c3c5877e8, memory=@0x7ffd8cf4d460, isHbase=1)
> at ../optimizer/ImplRule.cpp:1257
> #19 0x7f2c5837d677 in HbaseScanRule::nextSubstitute (this=0x7f2c4d888d68, 
> before=0x7f2c3e4bcd70, context=0x7f2c3c5877e8, memory=@0x7ffd8cf4d460)
> at ../optimizer/ImplRule.cpp:1662
> #20 0x7f2c587c2251 in ApplyRuleTask::perform (this=0x7f2c3c586920, 
> taskId=11) at ../optimizer/tasks.cpp:1188
> #21 0x7f2c5857d06d in QueryOptimizerDriver::optimizeAPassHelper (
> this=0x7ffd8cf519b0, context=0x7f2c3c583c08) at ../optimizer/opt.cpp:7084
> 

[jira] [Updated] (TRAFODION-960) LP Bug: 1415248 - Update stats does not work for a volatile table

[David Wayne Birdsall (JIRA)]

 [ 
https://issues.apache.org/jira/browse/TRAFODION-960?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

David Wayne Birdsall updated TRAFODION-960:
---
Fix Version/s: (was: 2.1-incubating)

> LP Bug: 1415248 - Update stats does not work for a volatile table
> -
>
> Key: TRAFODION-960
> URL: https://issues.apache.org/jira/browse/TRAFODION-960
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Reporter: Weishiun Tsai
>Assignee: David Wayne Birdsall
>Priority: Critical
>
> Update statistics currently does not work for a volatile table, as shown in 
> the following example.  It has trouble handling SB_HISTOGRAMS in a volatile 
> schema and returns error 4082 right now.
> This is seen on the v1.0.0 rc2 build:
> --
> Here is the entire script to reproduce this problem:
> create schema mytest;
> set schema mytest;
> create volatile table myvtbl (c1 int, c2 int);
> insert into myvtbl values (1, 1);
> update statistics for table myvtbl on every column;
> drop schema mytest cascade;
> --
> Here is the execution output:
> >>set schema mytest;
> --- SQL operation complete.
> >>create volatile table myvtbl (c1 int, c2 int);
> --- SQL operation complete.
> >>insert into myvtbl values (1, 1);
> --- 1 row(s) inserted.
> >>update statistics for table myvtbl on every column;
> *** ERROR[9200] UPDATE STATISTICS for table 
> TRAFODION.VOLATILE_SCHEMA_MXID111009321228916065702146102.MYVTBL 
> encountered an error (4082) from statement .
> *** ERROR[4082] Object 
> TRAFODION.VOLATILE_SCHEMA_MXID111009321228916065702146102.SB_HISTOGRAMS
>  does not exist or is inaccessible.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>drop schema mytest cascade;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)