title: Oracle SQL weird issue with my below query
body: <p>I have a view name RULE_READOUT_VIEW which has varchar columns named 'READOUTTYPE' and 'CUSTOMERNUMBER'.</p>
  
  <p>When I run the following count query I get only one row as output. </p>
  
  <pre><code>SELECT count(1) FROM RULE_READOUT_VIEW
  WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';
  </code></pre>
  
  <p>However if run this other query I get a different result and this time count is 11 rows which is what is the expected result.</p>
  
  <pre><code>SELECT count(1) FROM RULE_READOUT_VIEW
  WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF';
  </code></pre>
  
  <p>I am not sure why the first query is not working where as only if I force a to_char on the column I get the correct answer. I searched for answers but cannot find any thing satisfactory. Can some one please explain why this behaviour?</p>
  
  <p>I ran the below query to check the length of each and every row too</p>
  
  <pre><code>SELECT READOUTTYPE, TO_CHAR(READOUTTYPE), LENGTH(READOUTTYPE), LENGTHB(READOUTTYPE) FROM teoss.RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' and CUSTOMERNUMBER = 'NSMPF';
  </code></pre>
  
  <p>I got 11 rows as output and all of the columns have length 1.</p>
  
  <pre><code>5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  5 5 1 1
  </code></pre>
  
  <p>So the columns do no have any addtional whitespaces as well.</p>
  
  <p>View DDL:</p>
  
  <pre><code>SELECT --ro.creation_date,
  ro.POINT_OF_TIME_DAY                      AS pointOfReadoutDay,
  ro.POINT_OF_TIME_HOUR                     AS pointOfReadoutHour,
  ro.POINT_OF_TIME_MINUTE                   AS pointOfReadoutMinute,
  rot.NAME                                  AS readoutType,
  cp.IDENTIFICATION                         AS customerNumber,
  mp.METER_POINT_NUMBER                     AS meterPointNumber,
  ro.ORDER_REFERENCE                        AS readoutOrderNumber,
  mtrefstat.NAME                            AS meterStatus,
  mt.ADDRESS                                AS address,
  mt.SERIAL_NUMBER                          AS serialNumber,
  mt.UPPER_SERIAL_NUMBER                    AS upperSerialNumber,
  servrefstat.NAME                          AS serverStatus,
  mus.MAC                                   AS hostname,
  mserv.IP_ADDRESS                          AS ipAddress ,
  mt.device_id                              AS deviceID,
  GET_ACCESSCONNECTION_TYPE(mus.device_id)  AS ACCESSCONNECTIONTYPE,
  FUNC_LIST_OBIS_NAME(ro.order_read_out_id) AS refObisList,
  mt.METER_NUMBER                           AS meterNumber
  FROM ORDER_READ_OUT ro,
  REF_METER_READ_OUT_TYPE rot,
  COMPANY cp,
  METER_POINT mp,
  METER mt,
  MAP_DEVICE_STATUS mtstat,
  REF_STATUS mtrefstat,
  DEVICE servdev,
  MUS mus,
  METERING_SERVER mserv,
  MAP_DEVICE_STATUS servstat,
  REF_STATUS servrefstat,
  MAP_ORD_RD_OUT_CNTRCT_MT_PT mocm,
  MAP_CONTRACT_METER_POINT mcm,
  CONTRACT co
  WHERE -- order readout relation has to be valid
  mocm .ORDER_READ_OUT_ID         = ro.ORDER_READ_OUT_ID
  AND ro.REF_METER_READ_OUT_TYPE_ID = rot.REF_METER_READ_OUT_TYPE_ID
  AND ro.ACTIVATION_DATE           &lt;= SYSTIMESTAMP
  AND (ro.DEACTIVATION_DATE        IS NULL
  OR ro.DEACTIVATION_DATE           &gt; SYSTIMESTAMP)
  -- contract relation has to be valid ...
  AND mocm.MAP_CONTRACT_METER_POINT_ID = mcm.MAP_CONTRACT_METER_POINT_ID
  AND mcm.CONTRACT_ID                  = co.CONTRACT_ID
  AND co.COMPANY_ID                    = cp.COMPANY_ID
  AND ((co.BEGIN                      IS NULL
  AND mcm.BEGIN                       &lt;= SYSTIMESTAMP)
  OR (co.BEGIN                        &lt;= SYSTIMESTAMP
  AND mcm.BEGIN                       &lt;= SYSTIMESTAMP
  AND mcm.begin                       &gt;= co.begin))
  AND (mcm.end                        IS NULL
  OR mcm.end                          &gt;= SYSTIMESTAMP)
  AND ((co.END                        IS NULL)
  OR (co.END                          &gt;= SYSTIMESTAMP
  AND ((mcm.end                       IS NULL)
  OR (mcm.end                         &lt;= co.end))))
  -- meter point relation has to be valid ...
  AND mcm.METER_POINT_ID   = mp.METER_POINT_ID
  AND mp.METER_POINT_ID    = mt.METER_POINT_ID
  AND mt.DEVICE_ID         = mtstat.DEVICE_ID
  AND mtstat.REF_STATUS_ID = mtrefstat.REF_STATUS_ID
  AND (mtrefstat.name      = 'active'
  OR mtrefstat.name        = 'toBeRemoved')
  AND mtstat.STATUS_DATE   =
  (SELECT MAX(mds.status_date)
  FROM Map_device_status mds
  WHERE mds.device_id = mtstat.DEVICE_ID
  )
  -- mus/metering-server relation has to be valid
  AND servdev.DEVICE_ID =
  (SELECT FUNC_FIND_SERVER_DEV_ID(mt.DEVICE_ID) FROM DUAL
  )
  AND servdev.DEVICE_ID      = mus.DEVICE_ID(+)
  AND servdev.DEVICE_ID      = mserv.DEVICE_ID(+)
  AND servdev.DEVICE_ID      = servstat.DEVICE_ID
  AND servstat.REF_STATUS_ID = servrefstat.REF_STATUS_ID
  AND (servrefstat.name      = 'active'
  OR servrefstat.name        = 'toBeRemoved')
  AND servstat.STATUS_DATE   =
  (SELECT MAX(mds.status_date)
  FROM Map_device_status mds
  WHERE mds.device_id = servstat.DEVICE_ID
  )
  AND ro.CREATION_DATE =
  (SELECT MAX(t9.CREATION_DATE)
  FROM ORDER_READ_OUT t9
  WHERE t9.point_of_time_day        = ro.point_of_time_day
  AND t9.point_of_time_hour         = ro.point_of_time_hour
  AND t9.point_of_time_minute       = ro.point_of_time_minute
  AND t9.ref_meter_read_out_type_id = ro.ref_meter_read_out_type_id
  AND t9.ORDER_READ_OUT_ID         IN
  (SELECT t5.ORDER_READ_OUT_ID
  FROM map_ord_rd_out_cntrct_mt_pt t5
  WHERE t5.MAP_CONTRACT_METER_POINT_ID = mcm.MAP_CONTRACT_METER_POINT_ID
  ));
  </code></pre>
  
  <p>Plans snippet:
  <code>explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';</code></p>
  
  <p>|  19 |                  TABLE ACCESS BY INDEX ROWID| REF_METER_READ_OUT_TYPE       |     1 |     7 |     1 |<br>
  |  20 |                   INDEX UNIQUE SCAN         | UQ_REF_METER_READ_OUT_TY_NAME |     1 |       |     0 </p>
  
  <pre><code>    explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF';
  </code></pre>
  
  <p>|  14 |               INDEX FAST FULL SCAN          | PK_METERREADTYPE              |     1 |     7 |     1 |<br>
  |  15 |               INDEX FAST FULL SCAN          | UQ_REF_METER_READ_OUT_TY_NAME |     1 |     7 |     1 |                                                                                                                                                                                      </p>