github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_math_abs.test (about)

     1  #SELECT, 科学计数法
     2  select abs(-10);
     3  select abs(-2) * -2;
     4  select abs(1e10);
     5  
     6  #NULL
     7  select abs(NULL);
     8  
     9  #EXTREME VALUE
    10  select abs(9999999999999999999999);
    11  select abs(-9999999999999999999999);
    12  select abs(10/0);
    13  
    14  #嵌套
    15  select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2);
    16  
    17  #WHERE, 数据类型
    18  CREATE TABLE t(u TINYINT UNSIGNED NOT NULL);
    19  INSERT INTO t VALUES (0), (3), (255);
    20  SELECT * FROM t WHERE ABS(u=256)=0;
    21  DROP TABLE t;
    22  
    23  #DISTINCT
    24  create table t1(a int, b int, c int);
    25  insert into t1 values(100,1,2),(200,1,1),(300,2,1),(400,2,2);
    26  select distinct b from t1 order by abs(b);
    27  select distinct b as z from t1 order by abs(z);
    28  select distinct abs(b) as z from t1 order by z;
    29  select distinct abs(b) as z from t1 order by abs(b);
    30  select distinct abs(b) from t1 order by abs(b);
    31  drop table t1;
    32  
    33  #CASE WHEN
    34  CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
    35  INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1);
    36  SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
    37  DROP TABLE t1;
    38  CREATE TABLE t(i INT);
    39  INSERT INTO t VALUES (-1),(2),(1);
    40  SELECT ABS(i) AS a FROM t GROUP BY abs(i) ORDER BY a + 1;
    41  -- @bvt:issue#3265
    42  SELECT ABS(i) AS a FROM t GROUP BY a ORDER BY a + 1;
    43  -- @bvt:issue
    44  DROP TABLE t;
    45  
    46  
    47  #INSERT, HAVING, ON
    48  
    49  
    50  CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
    51  CREATE TABLE t2(c0 INTEGER, c1 INTEGER, c2 INTEGER);
    52  INSERT INTO t1 select abs(-341.741), abs(-234141113), abs(-141241);
    53  INSERT INTO t2 select abs(-341.5612), abs(-23413), abs(-14141);
    54  select * from t1;
    55  select * from t2;
    56  
    57  SELECT t1.c0, t2.c0 from t1 join t2 on abs(t1.c0) = abs(t2.c0) having abs(t1.c0)>100;
    58  
    59  drop table t1;
    60  drop table t2;
    61  
    62  
    63  
    64  
    65  #算式操作
    66  SELECT abs(10)-abs(-34);
    67  SELECT abs(1241)*abs(-0.4141);
    68  
    69  #比较操作
    70  SELECT abs(-100)>abs(-102);
    71  SELECT abs(-100)<>abs(100);