db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2896) DatabaseMetaData.getTables() fails in TERRORITY_BASED collation database with SQLState 42818: Comparisions between CHAR and CHAR not allowed.
Date Thu, 12 Jul 2007 05:59:04 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2896?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511976
] 

Mamta A. Satoor commented on DERBY-2896:
----------------------------------------

The metadata query for getTables in trunk is as follows
	SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \
		SCHEMANAME AS TABLE_SCHEM,  \
		TABLENAME AS TABLE_NAME, \
		(CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12))) \
		AS TABLE_TYPE, CAST ('' AS VARCHAR(128)) AS REMARKS, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_SCHEM, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_NAME, \
                CAST (NULL AS VARCHAR(128)) AS SELF_REFERENCING_COL_NAME, \
                CAST (NULL AS VARCHAR(128)) AS REF_GENERATION \
	FROM \
		 SYS.SYSTABLES, \
	     SYS.SYSSCHEMAS, \
	     (VALUES ('T','TABLE'), ('S','SYSTEM TABLE'), \
			('V', 'VIEW'), ('A', 'SYNONYM')) T(TTABBREV,TABLE_TYPE) \
	WHERE (TTABBREV=TABLETYPE 	\
	  AND (SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \
	  AND ((1=1) OR ? IS NOT NULL) \
	  AND (SYS.SYSSCHEMAS.SCHEMANAME LIKE ?) \
	  AND (TABLENAME LIKE ?))


The problem occurs because of VALUES clause which uses character string constants. These character
string constants in Derby take their collation from the current compilation schema and this
happens in CharConstantNode.bindExpression. I put some printlns in that method and found that
the current compilation schema is whatever the current schema is when this metadata query
gets run. So, if the getTables is run in user schema, the current compilation schema ends
up being user schema and hence the collation type of character string constants in the query
end up being territory based. When such character string constants are later compared against
character string columns from SYS schema in the getTable query (TTABBREV=TABLETYPE), it results
in an exception because of collation mismatch. If getTables is run from SYS schema, the collations
of character string constants and character string columns match and hence no exception is
thrown.

SYSSTATEMENTS table has a column called COMPILATION SCHEMAID but that column is not used at
all in the logic above. On a brand new database, this column has schemaid for SYS for getTables.

I have to admit I don't understand why this metadata query may have worked earlier (DERBY-2656)
if it was run from a user schema.

I tried debugging in 10.2 codeline where we do not have collation support but I wanted to
check what is the current compilation schema for metadata queries that get recompiled and
found the current compilation schema behavior there to be same as the one in main codeline.

I am at lost at this point as to what paths I should take in fixing the problem. Couple I
can think of are as follows
1)Have Derby somehow use the COMPILATION SCHEMAID from SYSSTATEMENTS table while recompiling
the metadata queries. This might take some time to implement. At this point, I don't know
how long.
2)For the short term, fix the metadata queries to use CAST so that we are not dependent on
what schema the queries are run from. I have this change made in my codeline for all the metadata
queries that use character string constants for comparison. Specifically, for getTables, the
ugly query above can be made uglier by using CAST but it will fix the problem.
	SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \
		SCHEMANAME AS TABLE_SCHEM,  \
		TABLENAME AS TABLE_NAME, \
		(CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12))) \
		AS TABLE_TYPE, CAST ('' AS VARCHAR(128)) AS REMARKS, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_SCHEM, \
                CAST (NULL AS VARCHAR(128)) AS TYPE_NAME, \
                CAST (NULL AS VARCHAR(128)) AS SELF_REFERENCING_COL_NAME, \
                CAST (NULL AS VARCHAR(128)) AS REF_GENERATION \
	FROM \
		 SYS.SYSTABLES, \
	     SYS.SYSSCHEMAS, \
	     (VALUES ('T','TABLE'), ('S','SYSTEM TABLE'), \
			('V', 'VIEW'), ('A', 'SYNONYM')) T(TTABBREV,TABLE_TYPE) \
	WHERE (TTABBREV=CAST(TABLETYPE AS CHAR(1)) 	-- NOTICE THE CAST ON THIS LINE \
	  AND (SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \
	  AND ((1=1) OR ? IS NOT NULL) \
	  AND (SYS.SYSSCHEMAS.SCHEMANAME LIKE ?) \
	  AND (TABLENAME LIKE ?))

Comments?


> DatabaseMetaData.getTables() fails in TERRORITY_BASED collation database with SQLState
42818: Comparisions between CHAR and CHAR not allowed.
> ---------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2896
>                 URL: https://issues.apache.org/jira/browse/DERBY-2896
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.3.0.0, 10.3.1.0, 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Mamta A. Satoor
>            Priority: Critical
>         Attachments: Test2896.zip
>
>
> I tried adding DatabaseMetaDataTest.suite() to be run within CollationTest so that it
would test DatabaseMetaData within a collated database.
> I had to fix one item in JDBC.dropSchema() where a string constant was being compared
to a system column while not in a system schema,
> but with that fixed the next error hit was executing DatabaseMetaData.getTables().
> I will add the code to collation test with the use of DatabaseMetaDataTest commented
out with this bug number.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message