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] [Updated] (DERBY-4966) Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions" - Give identifying detail on failing comparison
Date Fri, 28 Sep 2012 16:51:09 GMT

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

Mamta A. Satoor updated DERBY-4966:
-----------------------------------

    Urgency: Normal
     Labels: derby_triage10_10  (was: )
    
> Unhelpful error message: "Comparisons between (types) are not supported" without showing
expressions" - Give identifying detail on failing comparison
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4966
>                 URL: https://issues.apache.org/jira/browse/DERBY-4966
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.7.1.1
>            Reporter: Chris Wilson
>              Labels: derby_triage10_10
>
> java.sql.SQLSyntaxErrorException: Comparisons between 'INTEGER' and 'VARCHAR (UCS_BASIC)'
are not supported. Types must be comparable. String types must also have matching collation.
If collation does not match, a possible solution is to cast operands to force them to the
default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128))
= 'T1')
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
> 	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> 	at sun.reflect.GeneratedMethodAccessor647.invoke(Unknown Source)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:616)
> 	at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
> 	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
> Caused by: java.sql.SQLException: Comparisons between 'INTEGER' and 'VARCHAR (UCS_BASIC)'
are not supported. Types must be comparable. String types must also have matching collation.
If collation does not match, a possible solution is to cast operands to force them to the
default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128))
= 'T1')
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
> 	... 18 more
> Caused by: ERROR 42818: Comparisons between 'INTEGER' and 'VARCHAR (UCS_BASIC)' are not
supported. Types must be comparable. String types must also have matching collation. If collation
does not match, a possible solution is to cast operands to force them to the default collation
(e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindComparisonOperator(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.OrNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> 	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
> 	... 12 more
> This is generated by the following query:
> SELECT
>   project.name AS project_name,
>   project.web_address AS project_web_address,
>   project.email_address AS project_email_address,
>   producing_site.site_name AS producing_site_name,
>   TRIM(project_site.site_prefix)
>   	|| '-' 
>   	|| SUBSTR('0000', 1, 4 - LENGTH(TRIM(CAST(request.seqno AS CHAR(10))))) 
>   	|| TRIM(CAST(request.seqno AS CHAR(10))) 
>   	|| suffix
>   	AS cmr_number,
>   sender_org.name AS sender_org_name,
>   request.owners_ref AS request_owners_ref,
>   request.track_idnf AS request_track_idnf,
>   origin_site.site_name AS province,
>   origin.id AS origin_id,
>   origin.name AS origin_name,
>   origin.address1 AS origin_address1,
>   destination.id AS destination_id,
>   destination.name AS destination_name,
>   destination.address1 AS destination_address1,
>   line.id AS line_id,
>   line.seqno AS line_seqno,
>   product.description AS product_description,
>   line.ownr_item_desc AS line_ownr_item_desc,
>   line.ltu_qty AS line_qty,
>   line.total_weight / 1000 AS line_weight,
>   line.total_volume AS line_volume,
>   DATE(journey.est_dispatch_time) AS dispatch_date,
>   dispatch.movement_ledger_code AS movement_ledger,
>   SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END * dispatch.ltu_qty)
as dispatch_qty,
>   SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END * dispatch.total_weight)
/ 1000 as dispatch_weight,
>   SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END * dispatch.total_volume)
as dispatch_volume,
>   CASE
>     WHEN dispatch.movement_ledger_code = 'S' AND dispatch.ltu_qty > 0 THEN 'Received'
>     WHEN dispatch.movement_ledger_code = 'S' AND dispatch.ltu_qty < 0 THEN 'Dispatched'
>     WHEN dispatch.movement_ledger_code = 'P' THEN 'Planned for Dispatch'
>     WHEN dispatch.movement_ledger_code = 'X' AND dispatch.ltu_qty > 0 THEN 'Planned
to Collect & Deliver'
>     WHEN dispatch.movement_ledger_code = 'X' AND dispatch.ltu_qty < 0 THEN 'Collected
& Delivered'
>     ELSE 'Unknown'
>   END AS movement_description,
>   ltu.description AS ltu_name
> FROM
>   movement dispatch
>   INNER JOIN request_line line
>     ON  dispatch.request_site_id = line.request_site_id
>     AND dispatch.request_line_id = line.id
>   INNER JOIN product_ltu AS ltu
>     ON  ltu.id = line.ltu_id
>   INNER JOIN request request
>     ON  line.request_site_id = request.request_site_id
>     AND request.id = line.request_id
>   INNER JOIN product product
>     ON  line.product_id = product.id
>   INNER JOIN project_site project_site
>     ON  request.project_id = project_site.project_id
>     AND project_site.site_id = request.request_site_id
>   INNER JOIN project project
>     ON  request.project_id = project.id
>   INNER JOIN shipment shipment
>     ON  dispatch.shipment_site_id = shipment.shipment_site_id
>     AND shipment.id = dispatch.shipment_id
>   INNER JOIN location origin
>     ON  origin.location_site_id = dispatch.affected_location_site_id
>     AND origin.id = dispatch.affected_location_id
>   INNER JOIN site origin_site
>     ON  origin.parent_site_id = origin_site.id
>   INNER JOIN location destination
>     ON  destination.location_site_id = shipment.destination_location_site_id
>     AND destination.id = shipment.destination_location_id
>   INNER JOIN site destination_site
>     ON  destination.parent_site_id = destination_site.id
>   INNER JOIN journey journey
>     ON  shipment.shipment_site_id = journey.journey_site_id
>     AND journey.id = shipment.journey_id
>   -- LEFT JOIN vehicle_category ON vehicle_category.id = journey.vehicle_category_id
>   -- AND journey.id = shipment.journey_id
>   INNER JOIN contact sender ON sender.owner_site_id = request.sender_site_id
>     AND sender.id = request.sender_contact_id
>   INNER JOIN org sender_org ON sender_org.id = sender.org_id
>   INNER JOIN site AS producing_site ON producing_site.id = $P{Site_ID}
> WHERE movement_ledger_code IN ('C','S','P','X')
>   AND project.id IN ($P{Project_ID})
>   AND journey.is_deleted = 0
>   AND shipment.is_deleted = 0
>   AND dispatch.is_deleted = 0
>   AND ($P{Restrict_To_Active} = 0 OR request.status_code IN ('AC','IP'))
>   AND ($P{Specific_Request} IS NULL 
>     OR $P{Specific_Request} = ''
>     OR
>       (
>         project_site.site_prefix = SUBSTR($P{Specific_Request}, 1, 3) AND
>         request.seqno = SUBSTR($P{Specific_Request}, 5, 4) AND
>         request.suffix = SUBSTR($P{Specific_Request}, 9)
>       )
>     )
> GROUP BY
>   project.name,
>   project.web_address,
>   project.email_address,
>   producing_site.site_name,
>   project_site.site_prefix,
>   request.seqno,
>   request.suffix,
>   sender_org.name,
>   request.owners_ref,
>   request.track_idnf,
>   origin_site.site_name,
>   origin.name,
>   origin.address1,
>   destination.name,
>   destination.address1,
>   line.seqno,
>   product.description,
>   line.ownr_item_desc,
>   line.ltu_qty,
>   ltu.description,
>   journey.est_dispatch_time,
>   dispatch.movement_ledger_code,
>   CASE WHEN movement_ledger_code IN ('S','X') AND dispatch.ltu_qty > 0 THEN 1 ELSE
0 END
> As you can imagine, it takes quite some work to figure out which expressions it's complaining
about.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message