MariaDB: calculating with calculated columns

Question

I am pretty new to SQL and try to calculate with the results of other calculations on Mariadb (10.3.15). I got one version running that uses a subquery. However, in regard to mysql, oracle or SqlServer, other and for me easier to understand solutions are discussed, but they all dont work on MariaDB.

I like to understand if the MariaDB SQL syntax is just a bit different or if that functionality is simply not available in MariaDB.

CREATE TABLE test.testcalculation (
  ID int(11) NOT NULL AUTO_INCREMENT,
  num1 int(11) DEFAULT NULL,
  num2 int(11) DEFAULT NULL,
  num3 int(11) DEFAULT NULL,
  num4 int(11) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;


USE test;
INSERT INTO testcalculation(num1, num2, num3, num4) VALUES(1, 2, 3, 4);
INSERT INTO testcalculation(num1, num2, num3, num4) VALUES(5, 10, 15, 20);

This one works well:

SELECT
  id,
  Number1,
  Number2,
  Number3,
  Number4,
  Sum1And2,
  Sum1And2Prod3,
  (Sum1And2 + Sum1And2Prod3) AS "SumOfCalculations"
FROM (SELECT
    id,
    num1 AS Number1,
    num2 AS Number2,
    num3 AS Number3,
    num4 AS Number4,
    (num1 + num2) AS Sum1And2,
    ((num1 + num2) * num3) AS Sum1And2Prod3
  FROM test.testcalculation) t;

(by the way, what does not "t" at the end mean?)

So i have a solution, but thats writing everything twice and might get quite complex if I need another level of calculation.

This logic was discussed in regard to Oracle and does not work on MariaDB

SELECT
  id,
  num1,
  num2,
  num3,
  num4,
  c.Sum1And2 AS Sum1And2,
  (c.Sum1And2 * num3) AS Sum1And2Prod3
  FROM test.testcalculation
  CROSS apply (SELECT (num1 + num2) AS Sum1And2) AS c;

Obviously MariaDB does not know "cross-apply"

But being new to SQL and MariaDB, I just like to know my other options, maybe more readable like this, what I found on the internet:

CREATE TABLE test.signups_by_month (
  id int(11) NOT NULL AUTO_INCREMENT,
  month date DEFAULT NULL,
  signups int(11) DEFAULT NULL,
  visitors int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;


INSERT INTO signups_by_month(month, signups, visitors) VALUES('2014-06-01', 133, 1910);
INSERT INTO signups_by_month(month, signups, visitors) VALUES('2014-01-07', 95, 3151);
INSERT INTO signups_by_month(month, signups, visitors) VALUES('2014-01-08', 118, 1844);

    select 
      month, 
      signups, 
      visitors, 
      p as avg, 
      low, 
      high
    from signups_by_month,
    lateral (select signups / visitors as p) probability,
    lateral (select sqrt(p * (1 - p) / visitors) as se) std_error,
    lateral (select p - 1.94 * se as low) lower_bound,
    lateral (select p + 1.94 * se as high) upper_bound;

The only error message I get is, that there is an error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select signups / visitors as p) probability lateral (select sqrt(p * (1' at line 17 SQL4.sql 1 5

Obviously, MariaDB needs a "join" after "lateral". I am not sure where standard SQL ends and vendor implementations start. I just read that "lateral" is SQL 99, hence thought it should work somehow. And calculating with calculations is not really fancy rocket science.

I just like to understand if thats (cross apply / lateral) simply not working on MariaDB or if so, how? I also tried digging into CTE`s but could not get that example running so far...

Maybe someone could explain other options or (considering the example above) the "best" solution for this to me. Or just state that there is nothing else but a subquery or CTE (and I can dig into that on my own), hence no reason for me to spend more time cross apply or lateral.

Answer 1

One way of doing this is to use database variables

create table testcalc (
   id int(11) not null auto_increment,
   num1 int(11) default null,
   num2 int(11) default null,
   num3 int(11) default null,
   num4 int(11) default null,
   primary key(id)
);

insert into testcalc values
(default, 1, 2, 3, 4),
(default, 5, 10, 15, 20);

Then you can get the same results as in your example by storing the calculation results in variable syntax like this

@youVar := (calc) as resultName01

Then it will be available to following calculations to use like this

(@youVar + newCalc) as resultName02

We can apply it to your example like this

select
   id,
   num1,
   num2,
   num3,
   num4,
   @1plus2 := (num1 + num2) as 1plus2,                   # create var01
   @1plus2mult3 := (@1plus2 * num3) as 1plus2mult3,      # create var02 using var01
   @sumOfCalc := (@1plus2 + @1plus2mult3) as sumOfCalc   # create var03 using var01 and var02
from testcalc;

Which gives

| id | num1 | num2 | num3 | num4 | 1plus2 | 1plus2mult3 | sumOfCalc |
| 1  | 1    | 2    | 3    | 4    | 3      | 9           | 12        |
| 2  | 5    | 10   | 15   | 20   | 15     | 225         | 240       |