Update stats on tables in schemas with delimited names fail with syntax error

[Aruna Sadashiva]

Select queries on repository tables returned warnings about stats not updated, so I tried running update stats and it fails with the errors below.

[trafodion@n007 ~]$ sqid
MY_SQROOT=/opt/home/trafodion/trafodion-20150604
who@host=trafodion@amber-hn1
JAVA_HOME=/usr/java/jdk1.7.0_75
linux=2.6.32-279.el6.x86_64
redhat=6.3
Release 1.2.0 (Build release [1.1.0rc1-142-ge1edc95_Bld478], branch e1edc95-master, date 20150604_083001)

[trafodion@n007 ~]$ sqlci
Trafodion Conversational Interface 1.2.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>set schema "_REPOS_";
--- SQL operation complete.

>>update statistics for table metric_query_table on every column;
*** ERROR[9200] UPDATE STATISTICS for table TRAFODION._REPOS_.METRIC_QUERY_TABLE encountered an error (8605) from statement Process_Query.
*** ERROR[8605] Committing a transaction which has not started.
*** ERROR[9200] UPDATE STATISTICS for table TRAFODION._REPOS_.METRIC_QUERY_TABLE encountered an error (15001) from statement .
*** ERROR[15001] A syntax error occurred at or before:
TABLE ;
      ^ (7 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
--- SQL operation failed with errors.

>>update statistics for table metric_query_aggr_table on every column;
*** ERROR[9200] UPDATE STATISTICS for table TRAFODION._REPOS_.METRIC_QUERY_AGGR_TABLE encountered an error (8605) from statement Process_Query.
*** ERROR[8605] Committing a transaction which has not started.
*** ERROR[9200] UPDATE STATISTICS for table TRAFODION._REPOS_.METRIC_QUERY_AGGR_TABLE encountered an error (15001) from statement .
*** ERROR[15001] A syntax error occurred at or before:
TABLE ;
      ^ (7 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
--- SQL operation failed with errors.
>>

[Anoop Sharma (anoop-sharma)]

this is not an issue related to repository tables.
this is a pbm related to delimited schema names
which are invalid identifiers if unquoted.
Looks like some internal stmt issued by upd stats
in not double quoting schema name. And that
gets an error from parser.

>>create schema "1ab";

--- SQL operation complete.

>>set schema "1ab";

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>update statistics for table t on every column;

*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.1ab.T encountered an error (15001) from statement .

*** ERROR[15001] A syntax error occurred at or before:
TABLE ;
      ^ (7 characters from start of SQL statement)

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

--- SQL operation failed with errors.
>>

[Aruna Sadashiva (aruna-sadashiva)]

Changed the title. It works ok with tables with delimited names.

[Dave Birdsall (dave-birdsall)]

It appears there is something wrong with the metadata for the associated histograms table when the schema is a delimited identifier. For example:

>>showddl trafodion."2ab".sb_histograms;

*** ERROR[15001] A syntax error occurred at or before:
TABLE ;
      ^ (7 characters from start of SQL statement)

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

>>

In the UPDATE STATISTICS example, the error occurs when UPDATE STATISTICS is executing the query, "SELECT HISTOGRAM_ID, COL_POSITION, COLUMN_NUMBER, COLCOUNT, cast(READ_TIME as char(19)), REASON FROM TRAFODION.\"2ab\".SB_HISTOGRAMS WHERE TABLE_UID = 21460790725208305 ORDER BY TABLE_UID, HISTOGRAM_ID,"...". During binding of this statement, we go through this code path:

(gdb) bt
#0 arkcmperror (errtext=0x7ffff5489ad6 "syntax error") <--- this is yyerror in the parser; it is actually arkcmperror
    at ../parser/SqlParserAux.cpp:138
#1 0x00007ffff53a5fad in arkcmpparse ()
    at parser/linux/64bit/debug/sqlparser.cpp:77503
#2 0x00007ffff7a740eb in Parser::parseSQL (this=0x7fffffff1990,
    node=0x7fffffff1938, internalExpr=0, paramItemList=0x0)
    at ../sqlcomp/parser.cpp:761
#3 0x00007ffff7a74d9f in Parser::parseDML (this=0x7fffffff1990,
    instr=0x7fffffff1aa8 "TABLE ;", inlen=7, charset=CharInfo::UTF8,
    node=0x7fffffff1938, internalExpr=0, paramItemList=0x0)
    at ../sqlcomp/parser.cpp:965
#4 0x00007ffff7a75849 in Parser::parseDML (this=0x7fffffff1990, <--- and here is where the funny "TABLE ;" thing comes from
    str=0x7fffffff1aa8 "TABLE ;", len=7, charset=CharInfo::UTF8)
    at ../sqlcomp/parser.cpp:1117
#5 0x00007ffff47b0ef0 in QualifiedName::QualifiedName (this=0x7fffffff1d20, <--- gets an empty string for ansiString parameter; that's bad
    ansiString=..., minNameParts=3, h=0x0, bindWA=0x7fffffff4f60)
    at ../optimizer/ObjectNames.cpp:538
#6 0x00007ffff473cd30 in createConstraintInfo (table_desc=0x7fffde9e3518,
    tableQualName=..., columnArray=..., checkConstraints=...,
    uniqueConstraints=..., refConstraints=..., heap=0x7fffde9cb828,
    bindWA=0x7fffffff4f60) at ../optimizer/NATable.cpp:4583
#7 0x00007ffff473e65e in NATable::NATable (this=0x7fffde9e4b70, <---- corrName looks good here; has TRAFODION.2ab for schema, SB_HISTOGRAMS for object name
    bindWA=0x7fffffff4f60, corrName=..., heap=0x7fffde9cb828,
---Type <return> to continue, or q <return> to quit---
    inTableDesc=0x7fffde9e3518) at ../optimizer/NATable.cpp:5011
#8 0x00007ffff474928e in NATableDB::get (this=0x7fffe7dac558, corrName=...,
    bindWA=0x7fffffff4f60, inTableDescStruct=0x0)
    at ../optimizer/NATable.cpp:7766
#9 0x00007ffff4493ed4 in BindWA::getNATable (this=0x7fffffff4f60, <--- things look OK in this frame so the problem is likely higher up
    corrName=..., catmanCollectTableUsages=1, inTableDescStruct=0x0)
    at ../optimizer/BindRelExpr.cpp:1508
#10 0x00007ffff44ac0bb in Scan::bindNode (this=0x7fffde9c7308,
    bindWA=0x7fffffff4f60) at ../optimizer/BindRelExpr.cpp:7286
#11 0x00007ffff449716d in RelExpr::bindChildren (this=0x7fffde9da810,
    bindWA=0x7fffffff4f60) at ../optimizer/BindRelExpr.cpp:2216
#12 0x00007ffff44a3c76 in RelRoot::bindNode (this=0x7fffde9da810,...

[Dave Birdsall (dave-birdsall)]

Found the root cause. In method CmpSeabaseDDL::getSeabaseHistTableDesc, schName passed in is in internal format. It is then passed as an argument to a ComObjectName constructor. However, the latter requires the schema name to be in external format. The result is an invalid name in the ComObjectName object, which is silently marked as an empty string. This results in an empty string being used in the constraint descriptor for the SB_HISTOGRAMS table, resulting in the funny syntax error described in this case.

The fix is to change the logic in CmpSeabaseDDL::getSeabaseHistTableDesc to pass an external format schema name to the ComObjectName constructor instead.

[Trafodion-Gerrit (neo-devtools)]

Fix proposed to branch: master
Review: https://review.trafodion.org/1777

[Trafodion-Gerrit (neo-devtools)]

Reviewed: https://review.trafodion.org/1777
Committed: https://github.com/trafodion/core/commit/b8439fd05edd5d4e70b7a17be13791835ff3c2bb
Submitter: Trafodion Jenkins
Branch: master

commit b8439fd05edd5d4e70b7a17be13791835ff3c2bb
Author: David Wayne Birdsall <email address hidden>
Date: Tue Jun 16 17:34:03 2015 +0000

    fixes bug 1464364

    Update Stats on a table in a schema with a delimited identifier name
    failed with a funny syntax error. Turns out in a new sqlci session,
    Showddl on the same table fails the same way.

    The root cause was the schema name was passed to a ComObjectName
    constructor in internal format, when external format was required.
    This has been fixed.

    Change-Id: Ic81ba3d69e006264340fbdcbff6fb455593e30cf