Bug 1120073 - Teiid Connection import fails to parse DDL when table contains VARCHAR column with DEFAULT value
Summary: Teiid Connection import fails to parse DDL when table contains VARCHAR column...
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Tooling
Version: 6.0.0
Hardware: Unspecified
OS: Unspecified
unspecified
high
Target Milestone: ---
: ---
Assignee: Barry LaFond
QA Contact: Andrej Smigala
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-07-16 08:33 UTC by Andrej Smigala
Modified: 2015-11-17 18:07 UTC (History)
5 users (show)

Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2015-11-17 18:07:13 UTC
Type: Bug


Attachments (Terms of Use)
Screenshot (33.91 KB, image/png)
2014-07-16 08:33 UTC, Andrej Smigala
no flags Details


Links
System ID Priority Status Summary Last Updated
JBoss Issue Tracker TEIIDDES-2248 Major Closed Teiid Connection import fails to parse DDL when table contains VARCHAR column with DEFAULT value 2016-03-30 04:23:00 UTC

Description Andrej Smigala 2014-07-16 08:33:10 UTC
Created attachment 918351 [details]
Screenshot

Description of problem:

Create a table with

CREATE TABLE t1 ( c1 VARCHAR(10) DEFAULT 'c1' )


When trying to import it through Teiid Connection Importer, the importer DDL is shown as 

CREATE FOREIGN TABLE "dballo00.dbo.t1" (
	c1 string(10) DEFAULT '(''c1'')' OPTIONS (NAMEINSOURCE '"c1"', NATIVE_TYPE 'varchar')
) OPTIONS (NAMEINSOURCE '"dballo00"."dbo"."t1"', UPDATABLE TRUE, CARDINALITY 0);

but on the next page, the following error is shown (see screenshot): 
The DDL failed to parse with message: "Unparsable table body"


Version-Release number of selected component (if applicable):
Teiid Designer 8.3.3
Teeid 8.4.1-redhat-7

Comment 2 Barry LaFond 2014-07-22 16:14:05 UTC
What is the JDBC database in this use-case?

After discussing with Teiid, apparently the JDBC driver ended up NOT returning the original 'c1' DEFAULT value, but converted to ('c1').

Can you verify in your DB (via squirrel, etc..) what the default ended up being in the DB?

Comment 3 Andrej Smigala 2014-07-23 08:42:53 UTC
This was with MS SQL 2012 from DB allocator.
Squirrel reports the COLUMN_DEF as ('c1')


I did a quick test with a couple other DBs in allocator and these are the results:

PostgreSQL 9.2:
DDL is
CREATE FOREIGN TABLE "public.t1" (
    c1 string(10) DEFAULT '''c1''::character varying' OPTIONS (NAMEINSOURCE '"c1"', NATIVE_TYPE 'varchar')
) OPTIONS (NAMEINSOURCE '"public"."t1"', UPDATABLE TRUE);

The import fails
COLUMN_DEF according to squirrel is 'c1'::character varying


Oracle 12c
DDL is
CREATE FOREIGN TABLE "DBALLO01.T1" (
	C1 string(10) DEFAULT '''c1''' OPTIONS (NAMEINSOURCE '"C1"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"DBALLO01"."T1"', UPDATABLE TRUE, CARDINALITY 0);

The import fails
COLUMN_DEF according to squirrel is 'c1'


MySQL 5.5
DDL is
CREATE FOREIGN TABLE "dballo00.t1" (
	c1 string(10) DEFAULT 'c1' OPTIONS (ANNOTATION '', NAMEINSOURCE '`c1`', NATIVE_TYPE 'VARCHAR')
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`dballo00`.`t1`', UPDATABLE TRUE);

The import actually works
COLUMN_DEF according to squirrel is c1

Comment 5 Barry LaFond 2015-03-25 11:24:01 UTC
This appears to be a MS Teiid Parsing issue.  The work-around is to save the DDL to your filesystem/workspace, remove the offending quotes and re-import using the DDL Importer selecting the Teiid Dialect.

Recommend DV 6.3 target

Comment 6 JBoss JIRA Server 2015-07-27 20:48:24 UTC
Barry LaFond <blafond@jboss.org> updated the status of jira TEIIDDES-2248 to Resolved

Comment 7 JBoss JIRA Server 2015-08-03 12:47:08 UTC
Andrej Šmigala <asmigala@redhat.com> updated the status of jira TEIIDDES-2248 to Closed


Note You need to log in before you can comment on or make changes to this bug.