dbGetQuery() returns wrong value

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

dbGetQuery() returns wrong value

Bogaso
Hi,

I used following SQL query to fetch information from DB

> dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18' and ID =
'72075186224672770' limit 10")
         ID
1 72075186224672768

As you see, it is returning a different result from what actual query
string contains.

However when I used the same query in some other SQL client, I get the
expected result as:

72075186224672770

Any idea on what went wrong in R supplied query would be highly appreciated.

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: dbGetQuery() returns wrong value

Bogaso
Session Information for above error:

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 16299)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C
                 LC_TIME=English_United States.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] csvread_1.2   bit64_0.9-7   bit_1.1-14    RJDBC_0.2-7.1 rJava_0.9-10
DBI_1.0.0

loaded via a namespace (and not attached):
[1] compiler_3.5.0 tools_3.5.0

On Mon, Jul 30, 2018 at 2:27 PM Christofer Bogaso <
[hidden email]> wrote:

> Hi,
>
> I used following SQL query to fetch information from DB
>
> > dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18' and ID =
> '72075186224672770' limit 10")
>          ID
> 1 72075186224672768
>
> As you see, it is returning a different result from what actual query
> string contains.
>
> However when I used the same query in some other SQL client, I get the
> expected result as:
>
> 72075186224672770
>
> Any idea on what went wrong in R supplied query would be highly
> appreciated.
>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: dbGetQuery() returns wrong value

Eric Berger
The ID matches in the first 16 characters.
How is your table XXXX declared?


On Mon, Jul 30, 2018 at 2:00 PM, Christofer Bogaso <
[hidden email]> wrote:

> Session Information for above error:
>
> > sessionInfo()
> R version 3.5.0 (2018-04-23)
> Platform: x86_64-w64-mingw32/x64 (64-bit)
> Running under: Windows 10 x64 (build 16299)
>
> Matrix products: default
>
> locale:
> [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
> States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C
>                  LC_TIME=English_United States.1252
>
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
>
> other attached packages:
> [1] csvread_1.2   bit64_0.9-7   bit_1.1-14    RJDBC_0.2-7.1 rJava_0.9-10
> DBI_1.0.0
>
> loaded via a namespace (and not attached):
> [1] compiler_3.5.0 tools_3.5.0
>
> On Mon, Jul 30, 2018 at 2:27 PM Christofer Bogaso <
> [hidden email]> wrote:
>
> > Hi,
> >
> > I used following SQL query to fetch information from DB
> >
> > > dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18' and ID
> =
> > '72075186224672770' limit 10")
> >          ID
> > 1 72075186224672768
> >
> > As you see, it is returning a different result from what actual query
> > string contains.
> >
> > However when I used the same query in some other SQL client, I get the
> > expected result as:
> >
> > 72075186224672770
> >
> > Any idea on what went wrong in R supplied query would be highly
> > appreciated.
> >
> >
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: dbGetQuery() returns wrong value

Bogaso
The data type is defined as bigint

On Mon, Jul 30, 2018 at 4:45 PM Eric Berger <[hidden email]> wrote:

> The ID matches in the first 16 characters.
> How is your table XXXX declared?
>
>
> On Mon, Jul 30, 2018 at 2:00 PM, Christofer Bogaso <
> [hidden email]> wrote:
>
>> Session Information for above error:
>>
>> > sessionInfo()
>> R version 3.5.0 (2018-04-23)
>> Platform: x86_64-w64-mingw32/x64 (64-bit)
>> Running under: Windows 10 x64 (build 16299)
>>
>> Matrix products: default
>>
>> locale:
>> [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
>> States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C
>>                  LC_TIME=English_United States.1252
>>
>> attached base packages:
>> [1] stats     graphics  grDevices utils     datasets  methods   base
>>
>> other attached packages:
>> [1] csvread_1.2   bit64_0.9-7   bit_1.1-14    RJDBC_0.2-7.1 rJava_0.9-10
>> DBI_1.0.0
>>
>> loaded via a namespace (and not attached):
>> [1] compiler_3.5.0 tools_3.5.0
>>
>> On Mon, Jul 30, 2018 at 2:27 PM Christofer Bogaso <
>> [hidden email]> wrote:
>>
>> > Hi,
>> >
>> > I used following SQL query to fetch information from DB
>> >
>> > > dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18' and
>> ID =
>> > '72075186224672770' limit 10")
>> >          ID
>> > 1 72075186224672768
>> >
>> > As you see, it is returning a different result from what actual query
>> > string contains.
>> >
>> > However when I used the same query in some other SQL client, I get the
>> > expected result as:
>> >
>> > 72075186224672770
>> >
>> > Any idea on what went wrong in R supplied query would be highly
>> > appreciated.
>> >
>> >
>>
>>         [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: dbGetQuery() returns wrong value

Jeff Newmiller
If you have not read [1] already, you should. As to how JDBC handles this issue I don't know, but such a package-specific conversation belongs on r-sig-db.

[1] http://www.win-vector.com/blog/2015/06/r-in-a-64-bit-world/

On July 30, 2018 6:06:04 AM PDT, Christofer Bogaso <[hidden email]> wrote:

>The data type is defined as bigint
>
>On Mon, Jul 30, 2018 at 4:45 PM Eric Berger <[hidden email]>
>wrote:
>
>> The ID matches in the first 16 characters.
>> How is your table XXXX declared?
>>
>>
>> On Mon, Jul 30, 2018 at 2:00 PM, Christofer Bogaso <
>> [hidden email]> wrote:
>>
>>> Session Information for above error:
>>>
>>> > sessionInfo()
>>> R version 3.5.0 (2018-04-23)
>>> Platform: x86_64-w64-mingw32/x64 (64-bit)
>>> Running under: Windows 10 x64 (build 16299)
>>>
>>> Matrix products: default
>>>
>>> locale:
>>> [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
>>> States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C
>>>                  LC_TIME=English_United States.1252
>>>
>>> attached base packages:
>>> [1] stats     graphics  grDevices utils     datasets  methods   base
>>>
>>> other attached packages:
>>> [1] csvread_1.2   bit64_0.9-7   bit_1.1-14    RJDBC_0.2-7.1
>rJava_0.9-10
>>> DBI_1.0.0
>>>
>>> loaded via a namespace (and not attached):
>>> [1] compiler_3.5.0 tools_3.5.0
>>>
>>> On Mon, Jul 30, 2018 at 2:27 PM Christofer Bogaso <
>>> [hidden email]> wrote:
>>>
>>> > Hi,
>>> >
>>> > I used following SQL query to fetch information from DB
>>> >
>>> > > dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18'
>and
>>> ID =
>>> > '72075186224672770' limit 10")
>>> >          ID
>>> > 1 72075186224672768
>>> >
>>> > As you see, it is returning a different result from what actual
>query
>>> > string contains.
>>> >
>>> > However when I used the same query in some other SQL client, I get
>the
>>> > expected result as:
>>> >
>>> > 72075186224672770
>>> >
>>> > Any idea on what went wrong in R supplied query would be highly
>>> > appreciated.
>>> >
>>> >
>>>
>>>         [[alternative HTML version deleted]]
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide
>>> http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>>
>>
>>
>
> [[alternative HTML version deleted]]
>
>______________________________________________
>[hidden email] mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: dbGetQuery() returns wrong value

Enrico Schumann-2
In reply to this post by Bogaso

Quoting Christofer Bogaso <[hidden email]>:

> The data type is defined as bigint

Your query does not specify a number, but a string
(you single-quote the digits). Databases may do type conversion;
for instance, see the MySQL manual:  
https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

Since you send the query as a string anyway, there may be no need
for a "'string' within a string".

Please note that I have Cc'ed <[hidden email]>. Any follow-up
should probably go to that mailing list.


>
> On Mon, Jul 30, 2018 at 4:45 PM Eric Berger <[hidden email]> wrote:
>
>> The ID matches in the first 16 characters.
>> How is your table XXXX declared?
>>
>>
>> On Mon, Jul 30, 2018 at 2:00 PM, Christofer Bogaso <
>> [hidden email]> wrote:
>>
>>> Session Information for above error:
>>>
>>> > sessionInfo()
>>> R version 3.5.0 (2018-04-23)
>>> Platform: x86_64-w64-mingw32/x64 (64-bit)
>>> Running under: Windows 10 x64 (build 16299)
>>>
>>> Matrix products: default
>>>
>>> locale:
>>> [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
>>> States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C
>>>                  LC_TIME=English_United States.1252
>>>
>>> attached base packages:
>>> [1] stats     graphics  grDevices utils     datasets  methods   base
>>>
>>> other attached packages:
>>> [1] csvread_1.2   bit64_0.9-7   bit_1.1-14    RJDBC_0.2-7.1 rJava_0.9-10
>>> DBI_1.0.0
>>>
>>> loaded via a namespace (and not attached):
>>> [1] compiler_3.5.0 tools_3.5.0
>>>
>>> On Mon, Jul 30, 2018 at 2:27 PM Christofer Bogaso <
>>> [hidden email]> wrote:
>>>
>>> > Hi,
>>> >
>>> > I used following SQL query to fetch information from DB
>>> >
>>> > > dbGetQuery(Conn, "select ID from XXXX where date = '2018-07-18' and
>>> ID =
>>> > '72075186224672770' limit 10")
>>> >          ID
>>> > 1 72075186224672768
>>> >
>>> > As you see, it is returning a different result from what actual query
>>> > string contains.
>>> >
>>> > However when I used the same query in some other SQL client, I get the
>>> > expected result as:
>>> >
>>> > 72075186224672770
>>> >
>>> > Any idea on what went wrong in R supplied query would be highly
>>> > appreciated.
>>> >
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



--
Enrico Schumann
Lucerne, Switzerland
http://enricoschumann.net

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.