hubble V310版本升级内容

create table t (a int primary key,b int,c int,d int);

CREATE  INDEX b_idx ON T (b) INCLUDE (c, d);

CREATE TABLE t1 (id INT PRIMARY KEY, name INT NOT NULL, INDEX(name));
CREATE TABLE t2 (LIKE t1 INCLUDING ALL EXCLUDING CONSTRAINTS);
insert into T2 select * from T1;

CREATE MATERIALIZED VIEW emppeo
as select empno, ename
  from emp where deptno = 10 ;

create index e_idx on emppeo(ename);

SET experimental_enable_temp_tables = 'on'

create temp view dname_c (dname,num)
as
select d.dname ,count(1) from 
dept d 
join emp e 
on  d.deptno=e.deptno 
group by d.dname;

create table t1(a int primary key,b text,c date);

create table t2(a int primary key,b int references t1(a),c text);

alter table t2 drop constraint t2_b_fkey;

alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;

with e as (select * from  emp where deptno=30)
  select e.empno,e.ename,d.loc from e left join dept as d  on e.deptno = d.deptno ;

   empno | ename  |   loc
--------+--------+----------
   7698 | BLAKE  | CHICAGO
   7499 | ALLEN  | CHICAGO
   7844 | TURNER | CHICAGO
   7521 | WARD   | CHICAGO
   7654 | MARTIN | CHICAGO
   7900 | JAMES  | CHICAGO

select extract(epoch from now());

   extract  
+--------------+
1665626601.62315

select regexp_split_to_array('hello world', E'\\s+') as res;

       res
-----------------
  {hello,world}

select current_date();

         current_date         
+-----------------------------+
          2022-10-11

select localtime();

    localtime        
+---------------+
     16:33:30

select localtimestamp();

        localtimestamp
------------------------------
  2022-11-04 10:21:11.173667

select age(TIMESTAMPTZ '2022-03-26 14:22:10+08:00',TIMESTAMPTZ '2020-02-26 10:10:10-05:00');

            age     
+--------------------------+
  2 years 28 days 15:12:00 

select experimental_strftime(b,'%Y-%m-%d %H:%M:%S') from a;

  experimental_strftime
-------------------------
  2018-12-21 10:21:24

SELECT experimental_strptime(a,'%Y-%m-%d %H:%M:%S') FROM b;

  experimental_strptime
--------------------------
  2018-12-21 18:21:24+08

SELECT (date_trunc('month',now()) + INTERVAL '1 MONTH' + INTERVAL '-1 second')::timestamp;

  date
--------------------------
  2022-10-31 23:59:59.000

select log(2.0, 64.0);

           log
-------------------------
  6.0000000000000000000

create table film (id int,pname  string,fname string);
insert into film values (1,'战狼二','wujing');
insert into film values (1,'断背山','lian');
insert into film values (3,'西游记','wuchengen');;
insert into film values (3,'水浒传','shinaian');
insert into film values (2,'亮剑','liyunlong');
insert into film values (2,'五号特工组','wanglikun');

SELECT
    id,
    ARRAY_AGG (pname || ' ' || fname) actors
FROM
    film 
group by id;

 id  |                actors
-----+----------------------------------------
   1 | {战狼二 wujing,断背山 lian}
   3 | {西游记 wuchengen,水浒传 shinaian}
   2 | {亮剑 liyunlong,五号特工组 wanglikun}

select  covar_pop(20,30);

  covar_pop
-------------
          0

DIFFERENCE(string, string)

SELECT SOUNDEX('poor') soundex_poor, SOUNDEX('pour') soundex_pour, 
DIFFERENCE('poor', 'pour') similarity;

  soundex_poor | soundex_pour | similarity
---------------+--------------+-------------
  P600         | P600         | 4

CREATE TYPE my_number AS ENUM (
    'one',
    'two',
    'three',
    'four',
    'five',
    'six',
    'seven',
    'eight',
    'nine',
    'ten'
);

select enum_first('one'::my_number);

  enum_first
--------------
  one
(1 row)

select enum_last('one'::my_number);

  enum_last
--------------
  ten
(1 row)

select bit_or(id) from (values(null),(1),(2)) as t(id);

  bit_or
----------
       3

SELECT num_nonnulls(1,null,2) AS total_nonnulls;

  total_nonnulls
------------------
               2

SELECT num_nulls('PostgreSQL','MySQL','','null',null) AS total_nulls;

total_nulls

-------------
  1

select  regexp_split_to_table('11,22,33',',');

  regexp_split_to_table
-------------------------
  11
  22
  33

select sind(45.0);

         sind
----------------------
  0.7071067811865475

select sinh(90.0);

          sinh
-------------------------
  6.102016471589204e+38

CREATE TABLE Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


INSERT INTO Player  
(PlayerName, RunScored,  WicketsTaken )
VALUES
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

SELECT  STDDEV_POP(WicketsTaken) 
as Pop_Std_Dev_Wickets   
FROM Player ;

  pop_standard_deviation
--------------------------
   16.876183086099639597

select uuid_generate_v4();

            uuid_generate_v4
----------------------------------------
  165f8c68-d670-4708-aa50-ae6fc1bb72b0
(1 row)

CREATE TABLE Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


INSERT INTO Player  
(PlayerName, RunScored,  WicketsTaken )
VALUES
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

SELECT  VAR_POP(RunScored ) as Run_POPVariance  
                          FROM Player ;

     run_popvariance
-------------------------
  284.80555555555555556

SELECT  VAR_SAMP(RunScored ) as Run_Variance 
FROM Player ;

      run_variance
-------------------------
  341.76666666666666667

select timeofday();

               timeofday
----------------------------------------
  Fri Nov 4 11:33:43.240953 2022 +0800

select asind(1);

  asind
---------
     90
(1 row)

select acosd(0);

  acosd
---------
     90
(1 row)

create table book (id int,sal int ,bname string);

INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');

SELECT EVERY(sal < 10) FROM book;

  every
---------
  true
(1 row)

create table expression_index (id int PRIMARY KEY ,name string,tel string,sex string ,remark jsonb);

CREATE INDEX remark_index ON expression_index (parse_timestamp(remark->>'birthdate'));
		 
insert into expression_index values(1,'杨洋','1111','男',jsonb '{"id":"1","name": "杨洋","tel": "1111","sex": "男","birthdate":"1991-9-9"}' ),(2,'刘亦菲','2222','女',jsonb '{"id":"2","name": "刘亦菲","tel": "2222","sex": "女","birthdate":"1987-8-25"}' ); 

explain select jsonb_pretty(remark) from expression_index where parse_timestamp(remark->>'birthdate') = '1987-8-25';

                                         info
--------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • render
  │ estimated row count: 1
  │
  └── • index join
      │ estimated row count: 1
      │ table: expression_index@expression_index_pkey
      │
      └── • scan
            estimated row count: 2 (99% of the table; stats collected 3 minutes ago)
            table: expression_index@remark_index
            spans: [/'1987-08-25 00:00:00' - /'1987-08-25 00:00:00']
(14 rows)

SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '60s';

SET CLUSTER SETTING sql.log.slow_query.internal_queries.enabled = 'true';

[root@poc-hubble04 logs]# cd /data/hubbledir310/logs/


[root@poc-hubble04 logs]# tail -n 4 hubble310-sql-slow.log
I220613 08:57:03.414866 1147934625 util/log/file_sync_buffer.go:238 ⋮ [config]   log format (utf8=✓): hubdb-v2
I220613 08:57:03.414873 1147934625 util/log/file_sync_buffer.go:238 ⋮ [config]   line format: [IWEF]yymmdd hh:mm:ss.uuuuuu goid [chan@]file:lineredactionmark \[tags\] [counter] msg
I220613 08:57:03.412822 1147934625 10@util/log/event_log.go:32 ⋮ [n3,client=‹192.168.1.11:50628›,hostssl,user=root] 1 ={"Timestamp":1655110514810544287,"EventType":"slow_query","Statement":"‹SELECT * FROM \"\".\"\".bptfhist_2y2 AS t WHERE main_ac LIKE '%10268427281' LIMIT 2›","Tag":"SELECT","User":"root","ApplicationName":"$ hubble sql","ExecMode":"exec","Age":108602.01,"FullTableScan":true,"TxnCounter":6}

jdbc:postgresql://xx:35432/defaultdb?ssl=true&sslmode=require&options=--statement_timeout=1000