Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JDBC driver: incorrect output result of SQL query: SELECT 1 ; #3471

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

JDBC driver: incorrect output result of SQL query: SELECT 1 ; #3471

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2014-04-11 17:57:56 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: clients devs <>
Version: 11.17.9 (Jan2014)
CC: ccummins, @njnes, @drstmane, @yzchang

Last updated: 2014-10-31 14:13:39 +0100

Comment 19743

Date: 2014-04-11 17:57:56 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:28.0) Gecko/20100101 Firefox/28.0
Build Identifier:

it outputs 49 instead of 1
Other numeric values produce outputs between 48 and 57
Negative numeric values produce output 45

Reproducible: Always

Steps to Reproduce:

  1. execute SQL query: SELECT 1 AS Expected_1;

Actual Results:

49

Expected Results:

1

Query: SELECT 1 AS wrong1, 2 AS wrong2, 3 AS wrong3, 4 AS wrong4, 9 AS wrong9, 10 AS wrong10, 99 AS wrong99, 0 AS wrong0, -1 AS wrong_1;
produces output:
wrong1 wrong2 wrong3 wrong4 wrong9 wrong10 wrong99 wrong0 wrong_1
49 50 51 52 57 49 57 48 45

Query: SELECT -1 AS wrong1, -2 AS wrong2, -3 AS wrong3, -4 AS wrong4, -9 AS wrong9, -10 AS wrong10, -99 AS wrong99, -0 AS wrong0;
produces output:
wrong1 wrong2 wrong3 wrong4 wrong9 wrong10 wrong99 wrong0
45 45 45 45 45 45 45 48

So the output numbers increase (to 57) and decrease (to 45).

However query:
SELECT 0 + 1 AS one;
produces correct output: 1

Comment 19744

Date: 2014-04-11 20:03:55 +0200
From: @njnes

I'm unable to repeat this bug. Anymore details on how to reproduce?

Comment 19746

Date: 2014-04-13 12:54:11 +0200
From: @yzchang

Just FYI, I can't reproduce it neither (MonetDB v11.17.14).

Comment 19747

Date: 2014-04-13 13:51:25 +0200
From: @drstmane

Same here, i.e., all works fine with Jan2014-SP1 on my 64-bit Fedora 20 laptop:

$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP1)
Database: MonetDB v11.17.13 (Jan2014-SP1), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>SELECT 1 AS wrong1, 2 AS wrong2, 3 AS wrong3, 4 AS wrong4, 9 AS wrong9, 10 AS wrong10, 99 AS wrong99, 0 AS wrong0, -1 AS wrong_1;
+--------+--------+--------+--------+--------+---------+---------+--------+---------+
| wrong1 | wrong2 | wrong3 | wrong4 | wrong9 | wrong10 | wrong99 | wrong0 | wrong_1 |
+========+========+========+========+========+=========+=========+========+=========+
| 1 | 2 | 3 | 4 | 9 | 10 | 99 | 0 | -1 |
+--------+--------+--------+--------+--------+---------+---------+--------+---------+
1 tuple (0.627ms)
sql>SELECT -1 AS wrong1, -2 AS wrong2, -3 AS wrong3, -4 AS wrong4, -9 AS wrong9, -10 AS wrong10, -99 AS wrong99, -0 AS wrong0;
+--------+--------+--------+--------+--------+---------+---------+--------+
| wrong1 | wrong2 | wrong3 | wrong4 | wrong9 | wrong10 | wrong99 | wrong0 |
+========+========+========+========+========+=========+=========+========+
| -1 | -2 | -3 | -4 | -9 | -10 | -99 | 0 |
+--------+--------+--------+--------+--------+---------+---------+--------+
1 tuple (1.472ms)
sql>SELECT 1 AS Expected_1;
+------------+
| expected_1 |
+============+
| 1 |
+------------+
1 tuple (0.469ms)
sql>SELECT 0 + 1 AS one;
+------+
| one |
+======+
| 1 |
+------+
1 tuple (39.836ms)
sql>

Comment 19748

Date: 2014-04-13 15:07:41 +0200
From: @yzchang

I saw Martin van Dinther's GUI client (SQuirreL?) displaying wrong results like he reported.
Corrupted database or bug in de client?

@martin van Dinther: would you please check if your monetdb server is giving wrong results by execute your queries using e.g. mclient?

Comment 19749

Date: 2014-04-13 15:30:26 +0200
From: @drstmane

Aha --- the original bug report did not mention Squirrel ... ;-)

well, then it seems like that for some reason Squirrel shows the ASCII codes of the single-character numbers (character '1' has ASCII code 49) than the numbers themselves ...

Comment 19760

Date: 2014-04-18 17:24:54 +0200
From: Martin van Dinther <<martin.van.dinther>>

  1. start mserver5
  2. start SQuirreL connecting to demo database using JDBC driver from monetdb-jdbc-2.10.jar
  3. execute SQL query: SELECT 1
    this returns value: 49
    with column name: single_value
    and column data type: tinyint
  4. the same result and data type when executing SQL: SELECT 1 / 1
  5. however when you execute SQL: SELECT 1 + 0
    this returns value: 1
    with column name: sql_add_single_value
    and column data type: smallint

So there is a difference is in the column data type meta data.
I tested this also via:
CREATE TABLE test_tinyint (val tinyint NOT NULL);
INSERT INTO test_tinyint values (1);
INSERT INTO test_tinyint values (2);
INSERT INTO test_tinyint values (9);
INSERT INTO test_tinyint values (10);
INSERT INTO test_tinyint values (0);
INSERT INTO test_tinyint values (-1);
SELECT * from test_tinyint
-- this returns follonwing output in SQuirreL
val
49
50
57
49
48
45

I checked java/src/nl/cwi/monetdb/jdbc/MonetResultSet.java for method:
public short getShort(int columnIndex) but couldn't see any issue.
There is no public short getTiny(int columnIndex) method in JDBC API, so I guess SQuirreL is using getShort() or getInt() methods for this column.

I will have to write a small Java program (testing getShort(), getInt() and getString()) to find out where it goes wrong in the fetching method. Will do that later.

Comment 19764

Date: 2014-04-18 19:01:42 +0200
From: Martin van Dinther <<martin.van.dinther>>

Have written a small Java program testing getShort(), getInt() and getString() with the test_tinyint table. No issues found for these methods.

However using the method getByte() produced the same results as SQuirreL.
I conclude that SQuirreL is probably using getByte() method when the data type of the column is tinyint as both represent a 1 byte value.

We have currently implemented in MonetResultSet.java
public byte getByte(int columnIndex) throws SQLException {
String bytes = getString(columnIndex);
if (bytes == null || bytes.length() == 0) {
return (byte)0;
} else {
return bytes.getBytes()[0];
}
}

As we transfer data as Strings, the getByte() takes the String value and if not empty converts it to bytes array and returns the first byte.
This goes wrong for all numeric data values.
Maybe we should improve this code by detecting what the data type of result column is (see types[]) and when it is an integer (tinyint, short, int, bigint) or decimal (incl. real, float, double) convert the String first to an integer (or double) and next to a byte value (if it matches int value between -128 and +127).

Comment 19807

Date: 2014-05-09 19:11:49 +0200
From: Martin van Dinther <<martin.van.dinther>>

I have created a fix for it and tested it succesfully.
Now all columns with resulttype tinyint return the expected numeric value in SQuirreL.

Tried to check in the fix into branch Jan2014, but got:
abort: push creates new remote head fe586d5e7439 on branch 'Jan2014'!

Comment 19808

Date: 2014-05-09 21:14:41 +0200
From: @drstmane

This it not the complete Mercurial error message.
It continues as follows:

"(pull and merge or see "hg help push" for details about pushing new heads)"

Please do NOT considere the second option, i.e., forcing push to push new heads!

The reason for the error message is that apparently there are new remote changes in the branch that you committed to locally and now want to push to.

To resolve the situation, you first need to pull the new remote changes.
To prevent the need to merge these changes after a pull, please use

hg pull --rebase

This command will rebase your local commits on top of the incoming changesets. This means that you don't need to create a separate changeset to merge your changes with the incoming changes.

To enable the rebase extensions, add the following to your configuration file:

[extensions]
rebase =

In case you did already pull the new remote changes without "--rebase", you will have to merge the two heads (the one you created locally, and the one created remotely) using

hg merge

For general and very useful information and guidelines about (using) Mercurial, please consult https://www.monetdb.org/Assets/MonetDB-Mercurial.html
among others section "Pull and Update".

In fact, the entire document is considered "mandatory literature" for any/all MonetDB developer(s); this includes studying the document and following advice given!
;-)

Comment 19813

Date: 2014-05-16 16:09:49 +0200
From: MonetDB Mercurial Repository <>

Changeset 2ccadb9bd115 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=2ccadb9bd115

Changeset description:

fix for bug #3471

Comment 19862

Date: 2014-06-30 14:20:08 +0200
From: @sjoerdmullender

*** Bug #3503 has been marked as a duplicate of this bug. ***

Comment 20332

Date: 2014-10-31 14:13:39 +0100
From: @sjoerdmullender

Oct2014 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant