cockroachdb / cockroachdb/cockroach

sql: support multiplication of decimal and float

Open
#108,162 2 comments 0 reactions 0 assignees View on GitHub
A-tools-efcore C-enhancement O-community T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**
Current, multiplying decimal and float is not allowed as in PostgreSQL

Create a table as follows
```
CREATE TABLE "Order Details" (
"OrderID" integer NOT NULL ,
"ProductID" integer NOT NULL ,
"UnitPrice" numeric(12,2) NOT NULL CONSTRAINT "DF_Order_Details_UnitPrice" DEFAULT (0),
"Quantity" smallint NOT NULL CONSTRAINT "DF_Order_Details_Quantity" DEFAULT (1),
"Discount" real NOT NULL CONSTRAINT "DF_Order_Details_Discount" DEFAULT (0),
CONSTRAINT "CK_Discount" CHECK ("Discount" >= 0 and ("Discount" <= 1)),
CONSTRAINT "CK_Quantity" CHECK ("Quantity" > 0),
CONSTRAINT "CK_UnitPrice" CHECK ("UnitPrice" >= 0)
)
```

and populate it with data

```
INSERT INTO "Order Details" VALUES(10270,43,36.8,25,0);
INSERT INTO "Order Details" VALUES(10271,33,2,24,0);
INSERT INTO "Order Details" VALUES(10272,20,64.8,6,0);
INSERT INTO "Order Details" VALUES(10272,31,10,40,0);
```

Running the following query

```
SELECT (("Order Details"."UnitPrice"*"Quantity"*(1-"Discount")/100)*100) AS "ExtendedPrice" FROM "Order Details";
```

will return error

```
ERROR: unsupported binary operator: *
SQLSTATE: 22023
```

**Additional context**
This affects tests in efcore.pg library

@fqazi

Jira issue: CRDB-30338

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.