Skip navigation links

MySQL Forums :: Connector/JDBC and Java :: Data mismatch xxx!=xxx


Advanced Search

Data mismatch xxx!=xxx
Posted by: ed ziffel ()
Date: August 31, 2011 08:43AM

Have two tables t1,t2 from different sources with common data in respective columns t1.c1, t2.c1. The data is mixed alpha numeric.

Running
SELECT t1.c1, t2.c1 from t1.c1 JOIN t2.c1 ON t1.c1 = t2.c1; returns all null for t2.c1.
A query where you select either column on a fixed value (x) will return the correct value. Both columns are set up the same in their respective tables
t1.c1 `Style` varchar(100) COLLATE utf8_bin DEFAULT NULL
t2.c1 `STYLE` varchar(100) COLLATE utf8_bin DEFAULT NULL

So ran a CHAR_LENTH(t(x).c(x)) on both and discovered that the length for a given value is off. One specific result was when both were set to 2307 t1.c1 returned 4 while t2.c1 returned 5. Tried TRIM, CAST, UPPER, LOWER alone and in combinations with no success.

The problem appears to be the STYLE (t2.c1) field Note: c1 not really the right position in actual table;
mysql> SELECT 6_HTS09X.id_6 FROM 6_HTS09X LIMIT 20;
+------+
| id_6 |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
|   16 |
|   17 |
|   18 |
|   19 |
|   20 |
+------+
20 rows in set (0.00 sec)
SELECT 6_HTS09X.id_6,6_HTS09X.STYLE AS 'c.1' FROM 6_HTS09X LIMIT 20;
+------+-----------+
| id_6 | c.1       |
+------+-----------+
     | | 1201
     | | 1303
     | | 1401
     | | 2001
   | 5 | 2001AW
   | 6 | 2001BP
  |  7 | 2001GEO
   | 8 | 2001HA
   | 9 | 2001LA
   |10 | 2001LG
  | 11 | 2001ORG
    |2 | 2001P
    |3 | 2001P
   |14 | 2001SA
 |  15 | 2001STAW
  | 16 | 2001SUN
 |  17 | 2001VIVA
     | | 2007
  | 19 | 2007ORG
     | | 2065
+------+-----------+
20 rows in set (0.00 sec)
[\CODE]
mysql> SELECT 6_HTS09X.STYLE AS 'c.1' FROM 6_HTS09X LIMIT 20;
+-----------+
| c.1       |
+-----------+
     |
     |
     |
     |
   |01AW
   |01BP
  |001GEO
   |01HA
   |01LA
   |01LG
  |001ORG
    |1P
    |1P
   |01SA
 |2001STAW
  |001SUN
 |2001VIVA
     |
  |007ORG
     |
+-----------+
20 rows in set (0.00 sec)
Using all other fields in any combination are fine. Only when the c1 field is used is there a problem.
I do have an xlxs file for it, the original source document, which I've tried to clean up but maybe missed something.

How should I proceed?
Besides rubbing a lamp, what should I do?

Options: ReplyQuote


Subject Written By Posted
Data mismatch xxx!=xxx ed ziffel 08/31/2011 08:43AM
RESOLVED ed ziffel 08/31/2011 09:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.