MariaDB create view changes the SELECT to a different (incorrect) query

user_3096657 0
Viewed 242

I have a query I would like as a view in MariaDB 10.3(win), however when I attempt to create such view it is being changed to a different (and incorrect) one, removing parentheses:

create or replace view v_ReceiptSumByVAT
as
select VAT, SUM(RetailPrice) / (1 + VAT) as Sum from ReceiptItem
  group by VAT

when I run the SELECT VIEW_DEFINITION later, the returned query is (note missing parentheses near VAT)

select VAT, SUM(RetailPrice) / 1 + VAT as Sum from ReceiptItem
  group by VAT

which gives different results than the original SELECT query - A / 1 + B is not equal to A / (1 + B) !
I have found a similar question why mysql change my code view? that however deals with MySql and the query being changed to equivalent, not a different one. How can I ensure the view gets created correctly?

mariadb sql-view sum sql
3 Answers

Hmm, seems like a bug, consider reporting that to the project... As a workaround you can try to use a derived table only doing the aggregation and an outer query that then does the arithmetic operations on the sum.

CREATE VIEW v_receiptsumbyvat
AS
SELECT vat,
       sum / (vat + 1) sum
       FROM (SELECT vat,
                    sum(retailprice) sum
                    FROM receiptitem
             GROUP BY vat) x;

At least on db<>fiddle that seems to work.

user_96614240

It is a bug in MariaDB server.

For status please check MDEV-23656 in MariaDB's bug ticket system

user_69305010

change your query like this and see your result

  create or replace view v_ReceiptSumByVAT
  as
  select ROUND(VAT, 1 / (1 + VAT) * SUM(RetailPrice)) as Sum from ReceiptItem
  group by VAT

in MySQL/MariaDB the view removes the parenthesis if the expression is specified after the function so specify exp before the function. it works

user_141299140
Related