db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Wilson (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4966) Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions
Date Tue, 11 Jan 2011 15:10:47 GMT
Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions
----------------------------------------------------------------------------------------------------

                 Key: DERBY-4966
                 URL: https://issues.apache.org/jira/browse/DERBY-4966
             Project: Derby
          Issue Type: Bug
    Affects Versions: 10.7.1.1
            Reporter: Chris Wilson


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.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message