[
https://issues.apache.org/jira/browse/PHOENIX-6741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chaijunjie updated PHOENIX-6741:
--------------------------------
Description:
When two number set precision and scale, and {*}the second scale is bigger or
equal than the first{*},then the result will be scaled by 0.
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
A DECIMAL(28,8),
B DECIMAL(38,9)
);
UPSERT INTO TEST VALUES(1,120,200.358);
SELECT ID,A/B AS C FROM TEST;
+-----+----+
| ID | C |
+-----+----+
| 1 | 0 |
+-----+----+
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
A DECIMAL(18,10),
B DECIMAL(18,10)
);
UPSERT INTO TEST VALUES(1,100,200);
SELECT ID,A/B AS C FROM TEST;
0: jdbc:phoenix:> SELECT ID,A/B AS C FROM TEST;
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
|ID |C |
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
|1 |0 |
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
A DECIMAL(38,10),
B DECIMAL(18,10)
);
UPSERT INTO TEST VALUES(1,100,200);
SELECT ID,A/B AS C FROM TEST;
{+}---{-}{{-}}{-}{-}{+}--+
|ID |C |
{+}---{-}{{-}}{-}{-}{+}--+
|1 |0 |
{+}---{-}{{-}}{-}{-}{+}--+
But, we need a precise result, so we need add a 1.00*, then the result will be
right.
0: jdbc:phoenix:> SELECT ID,1.00*A/B AS C FROM TEST;
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
|ID | C |
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
|1 |0.5 |
{+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
was:
When two number set precision and scale, then the result will be scaled by 0.
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
A DECIMAL(18,10),
B DECIMAL(18,10)
);
UPSERT INTO TEST VALUES(1,100,200);
SELECT ID,A/B AS C FROM TEST;
0: jdbc:phoenix:> SELECT ID,A/B AS C FROM TEST;
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}-+
|ID |C |
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}-+
|1 |0 |
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}-+
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
A DECIMAL(38,10),
B DECIMAL(18,10)
);
UPSERT INTO TEST VALUES(1,100,200);
SELECT ID,A/B AS C FROM TEST;
{+}----{-}{-}{+}---+
|ID |C |
{+}----{-}{-}{+}---+
|1 |0 |
{+}----{-}{-}{+}---+
But, we need a precise result, so we need add a 1.00*, then the result will be
right.
0: jdbc:phoenix:> SELECT ID,1.00*A/B AS C FROM TEST;
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}---+
|ID | C |
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}---+
|1 |0.5 |
{+}--{-}{{-}}{{-}}{-}{-}{-}{+}---+
> The result of DivideExpression is wrong when two decimal number divide
> ----------------------------------------------------------------------
>
> Key: PHOENIX-6741
> URL: https://issues.apache.org/jira/browse/PHOENIX-6741
> Project: Phoenix
> Issue Type: Bug
> Components: core
> Affects Versions: 5.1.2
> Reporter: chaijunjie
> Assignee: chaijunjie
> Priority: Major
>
> When two number set precision and scale, and {*}the second scale is bigger
> or equal than the first{*},then the result will be scaled by 0.
> DROP TABLE IF EXISTS TEST;
> CREATE TABLE TEST (
> ID INTEGER NOT NULL PRIMARY KEY,
> A DECIMAL(28,8),
> B DECIMAL(38,9)
> );
> UPSERT INTO TEST VALUES(1,120,200.358);
> SELECT ID,A/B AS C FROM TEST;
> +-----+----+
> | ID | C |
> +-----+----+
> | 1 | 0 |
> +-----+----+
>
> DROP TABLE IF EXISTS TEST;
> CREATE TABLE TEST (
> ID INTEGER NOT NULL PRIMARY KEY,
> A DECIMAL(18,10),
> B DECIMAL(18,10)
> );
> UPSERT INTO TEST VALUES(1,100,200);
> SELECT ID,A/B AS C FROM TEST;
> 0: jdbc:phoenix:> SELECT ID,A/B AS C FROM TEST;
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
> |ID |C |
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
> |1 |0 |
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}+
>
> DROP TABLE IF EXISTS TEST;
> CREATE TABLE TEST (
> ID INTEGER NOT NULL PRIMARY KEY,
> A DECIMAL(38,10),
> B DECIMAL(18,10)
> );
> UPSERT INTO TEST VALUES(1,100,200);
> SELECT ID,A/B AS C FROM TEST;
> {+}---{-}{{-}}{-}{-}{+}--+
> |ID |C |
> {+}---{-}{{-}}{-}{-}{+}--+
> |1 |0 |
> {+}---{-}{{-}}{-}{-}{+}--+
>
> But, we need a precise result, so we need add a 1.00*, then the result will
> be right.
> 0: jdbc:phoenix:> SELECT ID,1.00*A/B AS C FROM TEST;
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
> |ID | C |
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
> |1 |0.5 |
> {+}-{-}{{-}}{{-}}{{-}}{-}{{-}}{-}{-}{+}--+
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
