Oracle SQL weird issue with my below query

Question

I have a view name RULE_READOUT_VIEW which has varchar columns named 'READOUTTYPE' and 'CUSTOMERNUMBER'.

When I run the following count query I get only one row as output.

SELECT count(1) FROM RULE_READOUT_VIEW
WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';

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.

SELECT count(1) FROM RULE_READOUT_VIEW
WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF';

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?

I ran the below query to check the length of each and every row too

SELECT READOUTTYPE, TO_CHAR(READOUTTYPE), LENGTH(READOUTTYPE), LENGTHB(READOUTTYPE) FROM teoss.RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' and CUSTOMERNUMBER = 'NSMPF';

I got 11 rows as output and all of the columns have length 1.

So the columns do no have any addtional whitespaces as well.

View DDL:

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           <= SYSTIMESTAMP
AND (ro.DEACTIVATION_DATE        IS NULL
OR ro.DEACTIVATION_DATE           > 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                       <= SYSTIMESTAMP)
OR (co.BEGIN                        <= SYSTIMESTAMP
AND mcm.BEGIN                       <= SYSTIMESTAMP
AND mcm.begin                       >= co.begin))
AND (mcm.end                        IS NULL
OR mcm.end                          >= SYSTIMESTAMP)
AND ((co.END                        IS NULL)
OR (co.END                          >= SYSTIMESTAMP
AND ((mcm.end                       IS NULL)
OR (mcm.end                         <= 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
));

Plans snippet: explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';

| 19 | TABLE ACCESS BY INDEX ROWID| REF_METER_READ_OUT_TYPE | 1 | 7 | 1 |
| 20 | INDEX UNIQUE SCAN | UQ_REF_METER_READ_OUT_TY_NAME | 1 | | 0

    explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF';

| 14 | INDEX FAST FULL SCAN | PK_METERREADTYPE | 1 | 7 | 1 |
| 15 | INDEX FAST FULL SCAN | UQ_REF_METER_READ_OUT_TY_NAME | 1 | 7 | 1 |

Make sure your readouttype column is clean, i.e. no entries with whitespace in them. — Mat
– Mat, Commented Dec 11, 2013 at 6:42
I checked the column values. i donot see any addtional white spaces before or after. Also if i run below query too i get 11 rows SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE like '5' AND CUSTOMERNUMBER = 'NSMPF'. The problem seems to be only if i use the equality operator alone. — thiyaga
– thiyaga, Commented Dec 11, 2013 at 6:45
How did you check? Both your "working" queries indicate that your readout column doesn't contain only a '5' but also has some other whitespace-like stuff. — Mat
– Mat, Commented Dec 11, 2013 at 6:49
In order to check there are no white spaces i ran the below query: SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE = 5 AND CUSTOMERNUMBER = 'NSMPF' where oracle should attempt implicit to_number conversion for column readoutype. This query returned 11 rows too. If there are whitespaces oracle should have thrown error during implicit type conversion. Am i correct? — thiyaga
– thiyaga, Commented Dec 11, 2013 at 6:55
No. Your to_char does the same thing, the implicit conversion will 'strip' whitespace. Replace the count with length(readouttype) or utl_raw.cast_to_raw(readouttype). — Mat
– Mat, Commented Dec 11, 2013 at 8:08

David Aldridge · Accepted Answer · 2013-12-11 09:24:10Z

1

I suspect that you might have a corrupt index on REF_METER_READ_OUT_TYPE.NAME, and the difference between the query results is explained by a difference in whether the index is used or not.

Try dropping and recreating the index (not rebuilding, as that might preserve any corruption).

answered Dec 11, 2013 at 9:24

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thiyaga Over a year ago

I dropped and recreated the index. Still the same issue.

David Aldridge Over a year ago

Can you post the execution plans for the two queries?

thiyaga Over a year ago

Since i cannot post complete plan as it exceeds allowed size i have updated my query with snippet of plan which uses the readoutype column.

WW. · Accepted Answer · 2013-12-11 21:59:37Z

1

Idea 1:

When you run this query, you should get the problematic rows back:-

SELECT READOUTTYPE, TO_CHAR(READOUTTYPE), LENGTH(READOUTTYPE) 
FROM RULE_READOUT_VIEW
WHERE to_char( READOUTTPYE ) = '5'
AND READOUTTYPE != '5'
AND CUSTOMERNUMBER = 'NSMPF';

Perhaps update your question with the result of this query to see if it makes it clear to anyone.

Idea 2:

Try this:

CREATE TABLE TEMP_RULE_READOUT AS 
SELECT * FROM RULE_READOUT_VIEW
WHERE CUSTOMERNUMBER = 'NSMPF';

SELECT count(1) FROM TEMP_RULE_READOUT
WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';

SELECT count(1) FROM RULE_READOUT_VIEW
WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF';

It would be interesting to see is this works correctly or not when the data is materialised into a table.

edited Dec 11, 2013 at 21:59

answered Dec 11, 2013 at 6:49

WW.

24.4k15 gold badges99 silver badges127 bronze badges

3 Comments

thiyaga Over a year ago

I ran the query you asked. There are no rows returned by the below query SQL> SELECT READOUTTYPE, TO_CHAR(READOUTTYPE), LENGTH(READOUTTYPE), LENGTH(TO_CHAR(READOUTTYPE)) FROM teoss.RULE_READOUT_VIEW WHERE TO_CHAR(READOUTTYPE ) = '5' AND READOUTTYPE != '5' AND CUSTOMERNUMBER = 'NSMPF'; no rows selected

thiyaga Over a year ago

Your Idea2 seems to produce correct results. If i run the query on the temporary table that is created then i get correct result. However i still do not get how it is working on the table that is created from view but does not work on the view itself?

WW. Over a year ago

@thiyaga I think that the data itself is ok, and you are running into an Oracle bug of some description, which is triggered/hidden by the difference in plan caused by the different queries.

Collectives™ on Stack Overflow

Oracle SQL weird issue with my below query

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related