[R-sig-DB] possible bug in ROracle
Armstrong, Whit
wh|t@@rm@trong @end|ng |rom hcmny@com
Tue Sep 26 17:36:17 CEST 2006
Thanks for your suggestion, Don.
I tried dbGetQuery, but the problem still exists.
When I execute this query in other sql clients, it runs fine. The one
we are most is TOAD (http://www.quest.com/toad_for_oracle/)
In this case, I ran the query with the full list of IDENTS (126 distinct
tickers).
And the resulting data has 1480 unique IDENTS.
I'll try to debug this a bit further on my end, but if anyone who is
familiar with the codebase has any suggestions as to where to start I'd
love to hear them.
Thanks,
Whit
Here is a small sample of the output (sorry for the long post):
these are the ones I asked for:
Browse[1]> tickers
[1] "US0003M" "BP0006M" "JY0006M" "CDOR03" "EU0006M" "STIB3M"
"NIBOR6M" "BBSW6M" "NFIX3MID" "RPGT01M" "UKBRBASE" "JYMUON"
"CAONREPO" "ECBALTMR" "SWRP1M" "NOBRDEP"
[17] "RBACTR" "NZOCRS" "USSWAP2" "BPSW2" "JYSW2" "CDSW2"
"DMSW2" "USSWAP10" "BPSW10" "JYSW10" "CDSW10" "DMSW10"
"USGG2YR" "GUKG2" "GJGB2" "GCAN2YR"
[33] "GDBR2" "USGG10YR" "GUKG10" "GJGB10" "GCAN10YR" "GDBR10"
"USSS2" "USSS3" "USSS4" "USSS5" "USSS6" "USSS7"
"USSS8" "USSS9" "USSS10" "USSS15"
[49] "USSS20" "USSS30" "BPSS1" "BPSS2" "BPSS3" "BPSS4"
"BPSS5" "BPSS7" "BPSS8" "BPSS9" "BPSS10" "BPSS15"
"BPSS20" "BPSS30" "JYSS1" "JYSS2"
[65] "JYSS3" "JYSS4" "JYSS5" "JYSS6" "JYSS7" "JYSS8"
"JYSS9" "JYSS10" "JYSS15" "JYSS20" "CDSS2" "CDSS3"
"CDSS4" "CDSS5" "CDSS6" "CDSS7"
[81] "CDSS8" "CDSS9" "CDSS10" "CDSS15" "CDSS20" "CDSS30"
"DMSS1" "DMSS2" "DMSS3" "DMSS4" "DMSS5" "DMSS6"
"DMSS7" "DMSS8" "DMSS9" "DMSS10"
[97] "DMSS30" "SKSS1" "SKSS2" "SKSS3" "SKSS5" "SKSS10"
"NKSS1" "NKSS2" "NKSS3" "NKSS4" "NKSS5" "NKSS6"
"NKSS7" "NKSS8" "NKSS9" "NKSS10"
[113] "ADSF2Q" "ADSF3Q" "ADSF4" "ADSF5" "ADSF6" "ADSF7"
"ADSF10" "ADSF15" "NDSS022" "NDSS033" "NDSS044" "NDSS055"
"NDSS077" "NDSS1010"
Browse[1]>
Here is a small sample of the result:
[1345] "BPSS2030" "CDSS1530" "BPSS5330" "SKSS3330" "USSS6330" "JYSS2030"
"NKSS7330" "JYSS2330" "BPSS1030" "BPSS7330" "USSS1530" "BPSS3330"
"CDSW1030" "JYSS9550" "ADSF6550" "JYSS8550"
[1361] "CDSS9550" "JYSS1050" "USSS2050" "NKSS5550" "DMSS1550" "DMSS2550"
"DMSS6550" "DMSS1050" "GJGB2550" "DMSS4550" "DMSS3550" "STIB3M50"
"BBSW6M50" "DMSS5550" "GDBR2550" "DMSS3050"
[1377] "DMSS9550" "GUKG2550" "NZOCRS50" "DMSS7550" "DMSW2550" "DMSS8550"
"CDOR0350" "RBACTR50" "DMSW1050" "GDBR1050" "GJGB1050" "GUKG1050"
"GJGB1070" "GJGB1040" "GJGB1020" "GJGB10M0"
[1393] "GJGB10ID" "GJGB10PO" "GJGB10P0" "GJGB10R0" "GJGB10SE" "BPSS4070"
"BPSS1070" "CDSS5070" "JYSS7070" "JYSS6070" "JYSS4070" "SKSS2070"
"JYSW1070" "CDSS7070" "JYSS5070" "CDSS4070"
[1409] "BPSS1570" "BPSS3070" "JYSS1070" "CDSS2070" "NKSS1070" "USSS1070"
"USSS7070" "BPSW2070" "ADSF2Q70" "ADSF1070" "SKSS1070" "BPSS2070"
"CDSS1570" "BPSS5070" "SKSS3070" "USSS6070"
[1425] "JYSS2070" "NKSS7070" "BPSS7070" "USSS1570" "CDSW1070" "USSS4070"
"JYMUON70" "BPSW1070" "CDSS6070" "USSS3070" "ADSF4070" "ADSF1570"
"CDSS3070" "NKSS3070" "SKSS5070" "USSS8070"
[1441] "CDSS1070" "JYSS3070" "CDSS8070" "BPSS8070" "ADSF5070" "USSS9070"
"CDSW2070" "USSS2070" "ADSF3Q70" "USSS5070" "BPSS9070" "JYSS1570"
"JYSW2070" "ADSF7070" "JYSS9070" "ADSF6070"
[1457] "JYSS8070" "CDSS9070" "NKSS5070" "DMSS1070" "DMSS2070" "DMSS6070"
"GJGB2070" "DMSS4070" "DMSS3070" "STIB3M70" "BBSW6M70" "DMSS5070"
"GDBR2070" "DMSS9070" "GUKG2070" "NZOCRS70"
[1473] "DMSS7070" "DMSW2070" "DMSS8070" "CDOR0370" "RBACTR70" "DMSW1070"
"GDBR1070" "GUKG1070"
and finally the query I'm running:
Browse[1]> ticker.qry
[1] "select IDENT,FIELDDATE,to_number(FIELDVALUE) from
TS_DATALOOKUP_VIEW where FIELDNAME='PRICE' and IDENT in
('US0003M','BP0006M','JY0006M','CDOR03','EU0006M','STIB3M','NIBOR6M','BB
SW6M','NFIX3MID','RPGT01M','UKBRBASE','JYMUON','CAONREPO','ECBALTMR','SW
RP1M','NOBRDEP','RBACTR','NZOCRS','USSWAP2','BPSW2','JYSW2','CDSW2','DMS
W2','USSWAP10','BPSW10','JYSW10','CDSW10','DMSW10','USGG2YR','GUKG2','GJ
GB2','GCAN2YR','GDBR2','USGG10YR','GUKG10','GJGB10','GCAN10YR','GDBR10',
'USSS2','USSS3','USSS4','USSS5','USSS6','USSS7','USSS8','USSS9','USSS10'
,'USSS15','USSS20','USSS30','BPSS1','BPSS2','BPSS3','BPSS4','BPSS5','BPS
S7','BPSS8','BPSS9','BPSS10','BPSS15','BPSS20','BPSS30','JYSS1','JYSS2',
'JYSS3','JYSS4','JYSS5','JYSS6','JYSS7','JYSS8','JYSS9','JYSS10','JYSS15
','JYSS20','CDSS2','CDSS3','CDSS4','CDSS5','CDSS6','CDSS7','CDSS8','CDSS
9','CDSS10','CDSS15','CDSS20','CDSS30','DMSS1','DMSS2','DMSS3','DMSS4','
DMSS5','DMSS6','DMSS7','DMSS8','DMSS9','DMSS10','DMSS30','SKSS1','SKSS2'
,'SKSS3','SKSS5','SKSS10','NKSS1','NKSS2','NKSS3','NKSS4','NKSS5','NKSS6
','NKSS7','NKSS8','NKSS9','NKSS10','ADSF2Q','ADSF3Q','ADSF4','ADSF5','AD
SF6','ADSF7','ADSF10','ADSF15','NDSS022','NDSS033','NDSS044','NDSS055','
NDSS077','NDSS1010')"
Browse[1]>
Reading in the query result that was eported from TOAD:
> xx <- read.csv("big.qry.csv",header=F)
> nrow(xx)
[1] 342553
>
> unique(xx[,1])
[1] "US0003M" "BP0006M" "JY0006M" "GCAN10YR" "GDBR10" "GJGB10"
"GUKG10" "USGG10YR" "EU0006M" "CDOR03" "NIBOR6M" "BBSW6M"
"STIB3M" "RPGT01M" "NFIX3MID" "UKBRBASE"
[17] "CAONREPO" "JYMUON" "NOBRDEP" "SWRP1M" "GUKG2" "BPSS2"
"ADSF7" "ECBALTMR" "NZOCRS" "RBACTR" "USSWAP2" "JYSW2"
"BPSW2" "CDSW2" "DMSW2" "BPSW10"
[33] "USSWAP10" "JYSW10" "CDSW10" "DMSW10" "USGG2YR" "GJGB2"
"GCAN2YR" "GDBR2" "USSS2" "USSS5" "USSS4" "CDSS10"
"CDSS15" "CDSS20" "CDSS30" "DMSS1"
[49] "DMSS2" "DMSS3" "DMSS4" "DMSS5" "DMSS6" "DMSS7"
"DMSS8" "DMSS9" "DMSS10" "DMSS30" "SKSS2" "SKSS3"
"SKSS5" "SKSS10" "NKSS3" "NKSS5"
[65] "NKSS7" "NKSS10" "ADSF2Q" "ADSF3Q" "ADSF4" "ADSF5"
"ADSF6" "ADSF10" "ADSF15" "NDSS022" "NDSS033" "NDSS044"
"NDSS055" "NDSS077" "NDSS1010" "USSS3"
[81] "USSS6" "USSS7" "USSS8" "USSS9" "USSS10" "USSS15"
"USSS20" "USSS30" "BPSS1" "BPSS3" "BPSS4" "BPSS5"
"BPSS7" "BPSS8" "BPSS9" "BPSS10"
[97] "BPSS15" "BPSS20" "BPSS30" "JYSS1" "JYSS2" "JYSS3"
"JYSS4" "JYSS5" "JYSS6" "JYSS7" "JYSS8" "JYSS9"
"JYSS10" "JYSS15" "JYSS20" "CDSS2"
[113] "CDSS3" "CDSS4" "CDSS5" "CDSS6" "CDSS7" "CDSS8"
"CDSS9"
>
> -----Original Message-----
> From: Don MacQueen [mailto:macq using llnl.gov]
> Sent: Monday, September 25, 2006 7:45 PM
> To: Armstrong, Whit; r-sig-db using stat.math.ethz.ch
> Subject: Re: [R-sig-DB] possible bug in ROracle
>
> I don't have a solution, but here's what I would try.
>
> I have found it very useful, when debugging my queries, to
> take them outside of R and run them in some other sort of query tool.
> I have never found a case where a correct query outside of R
> returned incorrect results using ROracle.
>
> Is there some reason you're not using dbGetQuery()
> (instead of dbSendQuery, fetch, and dbClearResult)?
> Try dbGetQuery(), and see if it's better.
>
> -Don
>
> At 5:43 PM -0400 9/22/06, Armstrong, Whit wrote:
> >Content-class: urn:content-classes:message
> >Content-Type: text/plain;
> > charset="us-ascii"
> >Content-Disposition: inline
> >
> >the code which no one else will be able to execute is at the
> bottom of
> >the email The query I'm sending is this:
> >
> >"select IDENT,FIELDDATE,to_number(FIELDVALUE) from
> TS_DATALOOKUP_VIEW
> >where FIELDNAME='PRICE' and IDENT in
> >('USSWAP10','BPSW10','JYSW10','CDSW10','DMSW10','GJGB10','GCA
> N10YR','GD
> >B
> >R10')"
> >
> >which should just grap the IDENTS that I asked for.
> >
> >However, when I inspect the result (stored in qry.data). I see that
> >the colnmes to not mach the IDENTS that I asked for. Some of these
> >IDENTS do not even exist in the database.
> >
> >cnms <- unique(qry.data[,"IDENT"])
> >Browse[1]> cnms
> > [1] "GCAN10YR" "GDBR10YR" "GJGB10YR" "USSWAP10" "BPSW1010"
> "JYSW1010"
> >"CDSW1010" "DMSW1010" "GJGB1010" "GDBR1010"
> >
> >and the IDENTS I asked for:
> >Browse[1]> tickers
> >[1] "USSWAP10" "BPSW10" "JYSW10" "CDSW10" "DMSW10" "GJGB10"
> >"GCAN10YR" "GDBR10"
> >
> >the culprits:
> >Browse[1]> cnms[!cnms%in%tickers]
> >[1] "GDBR10YR" "GJGB10YR" "BPSW1010" "JYSW1010" "CDSW1010" "DMSW1010"
> >"GJGB1010" "GDBR1010"
> >
> >Only 2 of the resulting IDENTS match the ones passed into the query:
> >Browse[1]> cnms[cnms%in%tickers]
> >[1] "GCAN10YR" "USSWAP10"
> >
> >We are using Oracle 9.2.0.
> >
> >Is there something seriously wrong w/ my query or is there a bug
> >somewhere?
> >
> >Thanks,
> >Whit
> >
> >
> >
> >actual code:
> >grab.data <- function(tickers) {
> >
> > drv <- Oracle()
> > con <- dbConnect(drv,user="fi",password="fi",dbname="FINP1")
> >
> > tickers.string <-
> >paste(paste("\'",tickers,"\'",sep=""),collapse=",")
> > ticker.qry <- paste("select
> IDENT,FIELDDATE,to_number(FIELDVALUE)
> >from TS_DATALOOKUP_VIEW where FIELDNAME='PRICE' and IDENT in
> >(",tickers.string,")",sep="")
> > res <- dbSendQuery(con,ticker.qry )
> > qry.data <- fetch(res,n=-1)
> > dbClearResult(res)
> > dbDisconnect(con)
> > cnms <- unique(qry.data[,"IDENT"])
> >
> > if(length(cnms)!=length(tickers)) {
> > browser()
> > }
> >}
> >
> >
> >System details:
> >
> >Package: ROracle
> >Version: 0.5-7
> >Date: 2006-02-13
> >Title: Oracle database interface for R
> >Author: David A. James <dj using bell-labs.com> Jake Luciani
> ><jakeluciani using yahoo.com>
> >Maintainer: David A. James <dj using bell-labs.com>
> >Description: Oracle database interface (DBI) driver for R.
> This is a
> >DBI-compliant Oracle driver based on the ProC/C++ embedded SQL. It
> >implements the DBI version
> > 0.1-8 plus one extension.
> >SaveImage: yes
> >Depends: R (>= 2.0.0), methods, DBI (>= 0.1-8)
> >License: LGPL version 2 or newer
> >URL: http://stat.bell-labs.com/RS-DBI
> http://www.omegahat.org
> >Packaged: Mon Feb 13 16:16:30 2006; dj
> >Built: R 2.3.1; i686-pc-linux-gnu; 2006-08-14 15:29:35; unix
> >
> >
> >
> >Description:
> >
> >Package: DBI
> >Version: 0.1-10
> >Date: 2006-01-28
> >Title: R Database Interface
> >Author: R Special Interest Group on Databases (R-SIG-DB)
> >Maintainer: David A. James <dj using bell-labs.com>
> >Depends: R (>= 1.8.0), methods
> >Description: A database interface (DBI) definition for
> communication
> >between R and relational database management systems. All
> classes in
> >this package are virtual and
> > need to be extended by the various R/DBMS
> >implementations.
> >License: GPL (version 2 or later)
> >URL: http://stat.bell-labs.com/RS-DBI
> >http://developer.r-project.org/db
> >Packaged: Sat Jan 28 14:42:20 2006; dj
> >Built: R 2.3.1; ; 2006-08-14 15:29:07; unix
> >
> >
> >> R.Version()
> >$platform
> >[1] "i686-pc-linux-gnu"
> >
> >$arch
> >[1] "i686"
> >
> >$os
> >[1] "linux-gnu"
> >
> >$system
> >[1] "i686, linux-gnu"
> >
> >$status
> >[1] ""
> >
> >$major
> >[1] "2"
> >
> >$minor
> >[1] "3.1"
> >
> >$year
> >[1] "2006"
> >
> >$month
> >[1] "06"
> >
> >$day
> >[1] "01"
> >
> >$`svn rev`
> >[1] "38247"
> >
> >$language
> >[1] "R"
> >
> >$version.string
> >[1] "Version 2.3.1 (2006-06-01)"
> >
> >>
> >
> >
> >
> >
> >This e-mail message is intended only for the named
> recipient(s) above.
> >It may contain confidential information. If you are not the intended
> >recipient you are hereby notified that any dissemination,
> distribution
> >or copying of this e-mail and any attachment(s) is strictly
> prohibited.
> >If you have received this e-mail in error, please immediately notify
> >the sender by replying to this e-mail and delete the message and any
> >attachment(s) from your system. Thank you.
> >
> >
> >_______________________________________________
> >R-sig-DB mailing list -- R Special Interest Group
> >R-sig-DB using stat.math.ethz.ch
> >https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
>
> --
> --------------------------------------
> Don MacQueen
> Environmental Protection Department
> Lawrence Livermore National Laboratory
> Livermore, CA, USA
> --------------------------------------
>
This e-mail message is intended only for the named recipient(s) above. It may contain confidential information. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this e-mail and any attachment(s) is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and delete the message and any attachment(s) from your system. Thank you.
More information about the R-sig-DB
mailing list