| >> |
| >>create table T015TA |
| +> ( |
| +> A VARCHAR(4) NOT NULL |
| +> ,B VARCHAR(3) NOT NULL |
| +> ,C INT |
| +> ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table T015TX |
| +> ( |
| +> AAA VARCHAR(4) NOT NULL |
| +> ,BBB VARCHAR(3) NOT NULL |
| +> ,CCC INT |
| +> ); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>?section dml |
| >>-- 4 rows |
| >>insert into T015TA values('a', 'b', 3); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TA values('m', 'z', 1); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TA values('r', 'q', 15); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TA values('z', 'y', 25); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- 4 rows plus 4 more |
| >>insert into T015TX values('a', 'b', 3); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('a', 'B', 33); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('m', 'z', 1); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('M', 'Z', 1); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('r', 'q', 15); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('R', 'Q', 15); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('z', 'y', 25); |
| |
| --- 1 row(s) inserted. |
| >>insert into T015TX values('z', 'Y', 225); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>?section test |
| >> |
| >>-- 16 rows |
| >>select x.a from t015ta x join t015ta y on 1=1 order by 1; |
| |
| A |
| ---- |
| |
| a |
| a |
| a |
| a |
| m |
| m |
| m |
| m |
| r |
| r |
| r |
| r |
| z |
| z |
| z |
| z |
| |
| --- 16 row(s) selected. |
| >> |
| >>-- Ambiguous col ref A |
| >>select a from t015ta x join t015ta y on 1=1; |
| |
| *** ERROR[4004] Column name A is ambiguous. Tables in scope: X, Y. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- 16 rows |
| >>select * from t015ta x join t015ta y on 1=1 |
| +>order by 1,2,3,4,5,6; |
| |
| A B C A B C |
| ---- --- ----------- ---- --- ----------- |
| |
| a b 3 a b 3 |
| a b 3 m z 1 |
| a b 3 r q 15 |
| a b 3 z y 25 |
| m z 1 a b 3 |
| m z 1 m z 1 |
| m z 1 r q 15 |
| m z 1 z y 25 |
| r q 15 a b 3 |
| r q 15 m z 1 |
| r q 15 r q 15 |
| r q 15 z y 25 |
| z y 25 a b 3 |
| z y 25 m z 1 |
| z y 25 r q 15 |
| z y 25 z y 25 |
| |
| --- 16 row(s) selected. |
| >> |
| >>-- 12 rows |
| >>select count(*) from ( select * from t015ta join t015tx on c=ccc union all |
| +> select * from t015ta join t015tx on c=ccc) x; |
| |
| (EXPR) |
| -------------------- |
| |
| 12 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- 4 rows |
| >>select * from |
| +> t015ta aa natural join t015ta bb |
| +>order by 1,2,3; |
| |
| A B C |
| ---- --- ----------- |
| |
| a b 3 |
| m z 1 |
| r q 15 |
| z y 25 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- 4 rows |
| >>select a from |
| +> t015ta aa natural join t015ta bb |
| +>order by 1; |
| |
| A |
| ---- |
| |
| a |
| m |
| r |
| z |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- 16 rows |
| >>select * from |
| +> (t015ta aa natural join t015ta bb) |
| +> join |
| +> (t015ta cc natural join t015ta dd) |
| +> on 1=1 |
| +>order by 1,2,3,4,5,6; |
| |
| A B C A B C |
| ---- --- ----------- ---- --- ----------- |
| |
| a b 3 a b 3 |
| a b 3 m z 1 |
| a b 3 r q 15 |
| a b 3 z y 25 |
| m z 1 a b 3 |
| m z 1 m z 1 |
| m z 1 r q 15 |
| m z 1 z y 25 |
| r q 15 a b 3 |
| r q 15 m z 1 |
| r q 15 r q 15 |
| r q 15 z y 25 |
| z y 25 a b 3 |
| z y 25 m z 1 |
| z y 25 r q 15 |
| z y 25 z y 25 |
| |
| --- 16 row(s) selected. |
| >> |
| >>-- ambig col ref A |
| >>select a from |
| +> (t015ta aa natural join t015ta bb) |
| +> join |
| +> (t015ta cc natural join t015ta dd) |
| +> on 1=1; |
| |
| *** ERROR[4004] Column name A is ambiguous. Tables in scope: AA, BB, CC, DD. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- 4 rows |
| >>select * from |
| +> (t015ta aa natural join t015ta bb) |
| +> natural join |
| +> (t015ta cc natural join t015ta dd) |
| +>order by 1,2,3 ; |
| |
| A B C |
| ---- --- ----------- |
| |
| a b 3 |
| m z 1 |
| r q 15 |
| z y 25 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- 4 rows |
| >>select a from |
| +> (t015ta aa natural join t015ta bb) |
| +> natural join |
| +> (t015ta cc natural join t015ta dd) |
| +>order by 1 ; |
| |
| A |
| ---- |
| |
| a |
| m |
| r |
| z |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- 4 rows |
| >>select * from |
| +> (select * from t015ta a natural join t015ta b) as x |
| +> join |
| +> (select * from t015ta a natural join t015ta b) as y |
| +> on x.a=y.a |
| +>order by 1,2,3,4,5,6 ; |
| |
| A B C A B C |
| ---- --- ----------- ---- --- ----------- |
| |
| a b 3 a b 3 |
| m z 1 m z 1 |
| r q 15 r q 15 |
| z y 25 z y 25 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- parse error expected |
| >>select * from ( |
| +> (select * from t015ta a natural join t015ta b) as x |
| +> join |
| +> (select * from t015ta a natural join t015ta b) as y |
| +> on x.a=y.a) q |
| +> ; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| select * from ( (select * from t015ta a natural join t015ta b) as x join |
| (select * from t015ta a natural join t015ta b) as y on x.a=y.a) q ; |
| ^ (147 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- these next 3 queries should all return the same 4 rows |
| >>select * from t015ta where exists(select 1 from (select a,a from t015ta) x) |
| +>order by 1,2,3; |
| |
| A B C |
| ---- --- ----------- |
| |
| a b 3 |
| m z 1 |
| r q 15 |
| z y 25 |
| |
| --- 4 row(s) selected. |
| >>select * from t015ta where exists(select * from (select a,a from t015ta) x) |
| +>order by 1,2,3; |
| |
| A B C |
| ---- --- ----------- |
| |
| a b 3 |
| m z 1 |
| r q 15 |
| z y 25 |
| |
| --- 4 row(s) selected. |
| >>select * from t015ta where exists(select * from (select a,a from t015ta) x |
| +>group by b) |
| +>order by 1,2,3; |
| |
| A B C |
| ---- --- ----------- |
| |
| a b 3 |
| m z 1 |
| r q 15 |
| z y 25 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- ambig col ref A |
| >>select * from t015ta where exists(select * from (select a,a from t015ta) x group by a); |
| |
| *** ERROR[4004] Column name A is ambiguous. Tables in scope: X. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- col X not found |
| >>select * from t015ta where exists(select * from (select a,a from t015ta) x group by x); |
| |
| *** ERROR[4001] Column X is not found. Tables in scope: TRAFODION.SCH.T015TA, X. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- col B not found |
| >>select * from (select a,a from t015ta) x group by b; |
| |
| *** ERROR[4001] Column B is not found. Tables in scope: X. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- ambig col ref A |
| >>select * from (select a,a from t015ta) x group by a; |
| |
| *** ERROR[4004] Column name A is ambiguous. Tables in scope: X. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- ambig star colref |
| >>select * from (select a,a from t015ta) x; |
| |
| *** ERROR[4011] Reference made to column X.A via star (*) is ambiguous. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- ambig col ref A |
| >>select * from t015ta where exists(select a from (select a,a from t015ta) x group by b); |
| |
| *** ERROR[4004] Column name A is ambiguous. Tables in scope: X. Default schema: TRAFODION.SCH. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- ambig star colref |
| >>select x.* from (select a,a from t015ta) x; |
| |
| *** ERROR[4011] Reference made to column X.A via star (*) is ambiguous. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- select list contains a non-grouping column |
| >>select * from (select a,b from t015ta) x group by a; |
| |
| *** ERROR[4012] Column reference X.B must be a grouping column or be specified within an aggregate. On this grouped table a star reference is not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>?section test_9737 |
| >>prepare xx from |
| +> update t015ta set c = |
| +> (select seq |
| +> from (select runningsum(1) as seq, a |
| +> from t015ta i |
| +> sequence by i.b |
| +> ) u |
| +> where u.a = t015ta.a |
| +> ); |
| |
| --- SQL command prepared. |
| >>-- should compile cleanly (it used to get "optimizer pass two assertion failure |
| >>-- ((partReqForChild != NULL) AND (reqdOrderForChild != NULL)) in file ... |
| >>-- OptPhysRelExpr.cpp at line 4805. Attempting to recover and produce a plan.") |
| >> |
| >>#ifNSK |
| >>?section verify_10_080701_4240 |
| >>--verify that single replicate broadcast ESPs float |
| >>set schema $$TEST_CATALOG$$.myhcube; |
| >> |
| >>-- seed random number generator |
| >>control query default float_esp_random_num_seed '7'; |
| >> |
| >>-- first test has a 6.25% probability of raising a false alarm |
| >>prepare xx from select * from t10 a, t9, t8, t10 b |
| +>where a.c = t9.c and a.b = t8.b and b.c = t8.c; |
| >>execute countESPdistinctCPUs; |
| >> -- remember result of 1st test |
| >> |
| >>-- repeat test 3 times |
| >>control query default query_template_cache 'OFF'; |
| >>prepare xx from select * from t10 a, t9, t8, t10 b |
| +>where a.c = t9.c and a.b = t8.b and b.c = t8.c; |
| >>execute countESPdistinctCPUs; |
| >> -- remember result of 2nd test |
| >> |
| >>prepare xx from select * from t10 a, t9, t8, t10 b |
| +>where a.c = t9.c and a.b = t8.b and b.c = t8.c; |
| >>execute countESPdistinctCPUs; |
| >> -- remember result of 3rd test |
| >> |
| >>prepare xx from select * from t10 a, t9, t8, t10 b |
| +>where a.c = t9.c and a.b = t8.b and b.c = t8.c; |
| >>execute countESPdistinctCPUs; |
| >> -- remember result of 4th test |
| >>control query default query_template_cache reset; |
| >> |
| >>-- probablity of all 4 tests raising false alarm is 0.0015% |
| >>execute checkPASSorFAIL; |
| >>#ifNSK |
| >> |
| >>?section maxCardEst_setup |
| >>prepare showMaxCardEst from |
| +>select distinct operator, tab_name, cardinality, max_cardinality |
| +> from (select |
| +> substring(cast(SEQ_NUM+100 as char(3)),2,2), |
| +> substring(operator,1,16) operator, |
| +> substring |
| +> (substring(tname from (1+locate('.',tname))), |
| +> (1+locate('.',substring(tname from (1+locate('.',tname))))), |
| +> 10), |
| +> cast(cardinality as char(11)), |
| +> cast(substring(description from |
| +> position('max_card_est' in description)+14 |
| +> for position('fragment_id' in description) |
| +> -position('max_card_est' in description)) as char(12)) |
| +> from table (explain(NULL,'XX')) |
| +> ) as t(s, operator, tab_name, cardinality, max_cardinality) |
| +> where operator not in ('ESP_EXCHANGE','PARTITION_ACCESS','SPLIT_TOP') |
| +> order by 1,2,3,4 desc; |
| |
| --- SQL command prepared. |
| >> |
| >>?section maxCard_tests1 |
| >>-- test max cardinality for single table scans |
| >>control query default dynamic_histogram_compression 'off'; |
| |
| --- SQL operation complete. |
| >>set schema $$TEST_CATALOG$$.myhcube; |
| |
| --- SQL operation complete. |
| >>prepare xx from |
| +>SELECT * FROM t0; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+001 10 fragment_ |
| TRAFODION_SCAN T0 1.000E+001 10 fragment_ |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t6; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+002 100 fragment |
| TRAFODION_SCAN T6 1.000E+002 100 fragment |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t8; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+003 1000 fragmen |
| TRAFODION_SCAN T8 1.000E+003 1000 fragmen |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t9; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+004 10000 fragme |
| TRAFODION_SCAN T9 1.000E+004 10000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.250E+004 100000 fragm |
| TRAFODION_SCAN T10 1.250E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >>select count(*) from (SELECT * FROM t10 WHERE d like 'one%') as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 10000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d not like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 8.750E+004 100000 fragm |
| TRAFODION_SCAN T10 8.750E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >>select count(*) from (SELECT * FROM t10 WHERE d not like 'one%') as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 90000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- keyColumn = constant |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >>select count(*) from (SELECT * FROM t10 WHERE a = 1) as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- keyColumn = ? |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- nonKeyColumn = constant |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d = 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+004 10000 fragme |
| TRAFODION_SCAN T10 1.000E+004 10000 fragme |
| |
| --- 2 row(s) selected. |
| >>select count(*) from (SELECT * FROM t10 WHERE d = 'one') as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 10000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- nonKeyColumn = ? |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d = ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+004 10000 fragme |
| TRAFODION_SCAN T10 1.000E+004 10000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- keyColumn non-equality predicates |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a > 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 9.999E+004 99999 fragme |
| TRAFODION_SCAN T10 9.999E+004 99999 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a >= 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 9.999E+004 99999 fragme |
| TRAFODION_SCAN T10 9.999E+004 99999 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a < 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <= 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 2.000E+000 2 fragment_i |
| TRAFODION_SCAN T10 2.000E+000 2 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <> 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- nonKeyColumn non-equality predicates |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d > 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 5.000E+004 50000 fragme |
| TRAFODION_SCAN T10 5.000E+004 50000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d >= 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 6.000E+004 60000 fragme |
| TRAFODION_SCAN T10 6.000E+004 60000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d < 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 4.000E+004 40000 fragme |
| TRAFODION_SCAN T10 4.000E+004 40000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d <= 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 5.000E+004 50000 fragme |
| TRAFODION_SCAN T10 5.000E+004 50000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d <> 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 9.000E+004 90000 fragme |
| TRAFODION_SCAN T10 9.000E+004 90000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a > ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a >= ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a < ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <= ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <> ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 9.999E+004 100000 fragm |
| TRAFODION_SCAN T10 9.999E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d > ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d >= ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d < ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d <= ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d <> ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 9.000E+004 100000 fragm |
| TRAFODION_SCAN T10 9.000E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a is null; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a is not null; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d is null; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d is not null; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 5.000E+004 100000 fragm |
| TRAFODION_SCAN T10 5.000E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a > b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a >= b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a < b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <= b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.333E+004 100000 fragm |
| TRAFODION_SCAN T10 3.333E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a <> b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 5.000E+004 100000 fragm |
| TRAFODION_SCAN T10 5.000E+004 100000 fragm |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a BETWEEN 1 and 2; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 3.000E+000 3 fragment_i |
| TRAFODION_SCAN T10 3.000E+000 3 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE d BETWEEN 'one' and 'two'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 5.000E+004 50000 fragme |
| TRAFODION_SCAN T10 5.000E+004 50000 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = 1 and d = 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = ? and d = ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = 1 or d = 'one'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+004 10001 fragme |
| TRAFODION_SCAN T10 1.000E+004 10001 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t10 WHERE a = ? or d = ?; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+004 10001 fragme |
| TRAFODION_SCAN T10 1.000E+004 10001 fragme |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE a = ? and d like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> -- min(1e5, 1) should = 1 |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE d = ? and d like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.250E+004 12500 fragme |
| TRAFODION_SCAN T10 1.250E+004 12500 fragme |
| |
| --- 2 row(s) selected. |
| >> -- min(1e5, 1e4) should = 1e4 |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE a = 1 and d like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 1 fragment_i |
| |
| --- 2 row(s) selected. |
| >> -- min(1e5, 1) should = 1 |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE d = 'one' and d not like 'one%'; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 10000 fragme |
| TRAFODION_SCAN T10 1.000E+000 10000 fragme |
| |
| --- 2 row(s) selected. |
| >> -- min(1e4, 1e5) should = 1e4 |
| >> |
| >>-- we want to test an in list but sqlparseraux.cpp's processINlist |
| >>-- transforms "col IN (list)" into: |
| >>-- 1) an OR predicate if |list| < 100 elements, or |
| >>-- 2) a VALUES subquery if |list| > 100 elements |
| >>prepare xx from SELECT * FROM t10 WHERE b in (1,2); |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 2.000E+002 200 fragment |
| TRAFODION_SCAN T10 2.000E+002 200 fragment |
| |
| --- 2 row(s) selected. |
| >>-- maxSel("b in (1,2)") is min(maxSel("b=1")+maxSel("b=2"),1.) |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE b in (1,2) and a between 1 and 2; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 2.000E+000 3 fragment_i |
| TRAFODION_SCAN T10 2.000E+000 3 fragment_i |
| |
| --- 2 row(s) selected. |
| >> -- maxSel(p1 and p2) is min(maxSel(p1), maxSel(p2)) |
| >> |
| >>prepare xx from SELECT * FROM t10 WHERE b in (1,2) or a between 1 and 2; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 2.030E+002 203 fragment |
| TRAFODION_SCAN T10 2.030E+002 203 fragment |
| |
| --- 2 row(s) selected. |
| >> -- maxSel(p1 or p2) is min(maxSel(p1)+maxSel(p2),1) |
| >> |
| >>?section maxCard_tests2 |
| >>-- test max cardinality for equi-joins |
| >>prepare xx from |
| +>SELECT * FROM t1, t10 WHERE t1.a = t10.a; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| NESTED_JOIN 1.000E+001 10 fragment_ |
| ROOT 1.000E+001 10 fragment_ |
| TRAFODION_SCAN T1 1.000E+001 10 fragment_ |
| TRAFODION_VSBB_S T10 1.000E+000 10 fragment_ |
| |
| --- 4 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t1, t10 WHERE t1.a = t10.b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HYBRID_HASH_JOIN 1.000E+003 1000 fragmen |
| ROOT 1.000E+003 1000 fragmen |
| TRAFODION_SCAN T1 1.000E+001 10 fragment_ |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 4 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t1, t10 WHERE t10.a = t1.b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| NESTED_JOIN 1.000E+001 10 fragment_ |
| PROBE_CACHE 1.000E+000 10 fragment_ |
| ROOT 1.000E+001 10 fragment_ |
| TRAFODION_SCAN T1 1.000E+001 10 fragment_ |
| TRAFODION_VSBB_S T10 1.000E+000 10 fragment_ |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- test max cardinality for equi-joins where one side is complex |
| >>prepare xx from |
| +>SELECT * FROM t8, t10 WHERE t8.a+1 = t10.a; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| NESTED_JOIN 1.000E+003 1000 fragmen |
| PROBE_CACHE 1.000E+000 1000 fragmen |
| ROOT 1.000E+003 1000 fragmen |
| TRAFODION_SCAN T8 1.000E+003 1000 fragmen |
| TRAFODION_VSBB_S T10 1.000E+000 1000 fragmen |
| |
| --- 5 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t9, t10 WHERE t9.a = t10.b-1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HYBRID_HASH_JOIN 1.000E+005 100000 fragm |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| TRAFODION_SCAN T9 1.000E+004 10000 fragme |
| |
| --- 4 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT * FROM t9, t10 WHERE t10.a+t10.b = t9.b; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HYBRID_HASH_JOIN 1.000E+006 1e+06 fragme |
| ROOT 1.000E+006 1e+06 fragme |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| TRAFODION_SCAN T9 1.000E+004 10000 fragme |
| |
| --- 4 row(s) selected. |
| >> |
| >>?section maxCard_tests3 |
| >>-- test max cardinality for group by |
| >>update statistics for table t10 on (a,b,c,d),(b,c,d),(c,d),(b,c); |
| |
| --- SQL operation complete. |
| >>prepare xx from |
| +>SELECT a,b,c,d FROM t10 GROUP BY a,b,c,d; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 2 row(s) selected. |
| >>select count(*) from (SELECT a,b,c,d FROM t10 GROUP BY a,b,c,d) as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 100000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT b,c,d FROM t10 GROUP BY b,c,d; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HASH_GROUPBY 1.000E+005 100000 fragm |
| ROOT 1.000E+005 100000 fragm |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 3 row(s) selected. |
| >>select count(*) from (SELECT b,c,d FROM t10 GROUP BY b,c,d) as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 100000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT c,d FROM t10 GROUP BY c,d; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HASH_GROUPBY 1.000E+002 100 fragment |
| ROOT 1.000E+002 100 fragment |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 3 row(s) selected. |
| >>select count(*) from (SELECT c,d FROM t10 GROUP BY c,d) as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from |
| +>SELECT d FROM t10 GROUP BY d; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HASH_GROUPBY 1.000E+001 10 fragment_ |
| ROOT 1.000E+001 10 fragment_ |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| |
| --- 3 row(s) selected. |
| >>select count(*) from (SELECT d FROM t10 GROUP BY d) as t; |
| |
| (EXPR) |
| -------------------- |
| |
| 10 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- verify fix to genesis cases 10-080530-0291, 10-080530-0305 |
| >>-- previously maxcardinality(a=1 and b=1) was incorrectly computed as |
| >>-- maxcardinality(a=b and b=1) |
| >>prepare xx from SELECT count(*) FROM t10 where b = 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+002 100 fragment |
| |
| --- 3 row(s) selected. |
| >>prepare xx from SELECT count(*) FROM t10 where c = 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+003 1000 fragmen |
| |
| --- 3 row(s) selected. |
| >>prepare xx from SELECT count(*) FROM t10 where b = 1 and c = 1; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+000 100 fragment |
| |
| --- 3 row(s) selected. |
| >> |
| >>?section fixes |
| >>-- fix for soln 10-080908-5708 |
| >>prepare xx from select count(*) from t10 where c > 999; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+003 1000 fragmen |
| |
| --- 3 row(s) selected. |
| >>execute xx; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from select count(*) from t10 where c < 0; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+003 1000 fragmen |
| |
| --- 3 row(s) selected. |
| >>execute xx; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- Fix for bugzilla case 3529 |
| >>CREATE TABLE NPJOB028D |
| +> ( |
| +> C1 INT NO DEFAULT -- NOT NULL NOT DROPPABLE |
| +> , C2 INT DEFAULT NULL |
| +> , C3 VARCHAR(12) CHARACTER SET ISO88591 COLLATE |
| +> DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE |
| +> , C4 INT DEFAULT NULL |
| +> , C5 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| +> DEFAULT DEFAULT NULL |
| +> , C6 INT DEFAULT NULL |
| +> , C7 CHAR(12) CHARACTER SET ISO88591 COLLATE |
| +> DEFAULT DEFAULT NULL |
| +> , CONSTRAINT NPJOB028D_117387252_7433 PRIMARY KEY (C1 ASC, |
| +> C3 ASC) NOT DROPPABLE |
| +> , CONSTRAINT NPJOB028D_896387252_7433 CHECK |
| +> (NPJOB028D.C1 IS NOT NULL AND |
| +> NPJOB028D.C3 IS NOT NULL) NOT DROPPABLE |
| +> ) |
| +> HASH PARTITION |
| +> STORE BY (C1 ASC, C3 ASC) |
| +> ; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare st1 from UPDATE NPJOB028D SET ("C2","C3","C4","C5","C6","C7") = (?[10],?[10],?[10],?[10],?[10],?[10]) |
| +>WHERE "C1" = ?[10]; |
| |
| --- SQL command prepared. |
| >> |
| >>drop table NPJOB028D; |
| |
| --- SQL operation complete. |
| >> |
| >>?section joinMaxCard |
| >>-- verify fix for soln 10-090129-8763 |
| >>prepare xx from select count(*) from t10, t9 where t10.a = t9.a; |
| |
| --- SQL command prepared. |
| >>execute showMaxCardESt; |
| |
| OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY |
| ---------------- ---------- ----------- --------------- |
| |
| HYBRID_HASH_JOIN 1.000E+004 10000 fragme |
| ROOT 1.000E+000 1 fragment_i |
| SORT_SCALAR_AGGR 1.000E+000 1 fragment_i |
| TRAFODION_SCAN T10 1.000E+005 100000 fragm |
| TRAFODION_SCAN T9 1.000E+004 10000 fragme |
| |
| --- 5 row(s) selected. |
| >>execute xx; |
| |
| (EXPR) |
| -------------------- |
| |
| 10000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>?section monkeydoodletest |
| >>-- if any of the tests in this & the following section fail, please check |
| >>-- the code in optimizer/OptItemExpr.cpp ItemExpr::isAnEquiJoinPredicate |
| >>-- to make sure it still returns true for equi-join predicates else false. |
| >>-- make sure the cmpassert that was reported in genesis case 10-090107-9003 |
| >>-- solution 10-090107-8259 does not recur |
| >>control query default query_template_cache 'OFF'; |
| |
| --- SQL operation complete. |
| >>control query default COMP_BOOL_113 'ON'; |
| |
| --- SQL operation complete. |
| >>PREPARE xx FROM |
| +> SELECT T0.i3 , t0.i3 FROM T15D2 T0 CROSS JOIN T15D1 t1 |
| +> WHERE EXISTS ( |
| +> SELECT T0.i2 FROM t15d1 AS t2 |
| +> WHERE ( 'cC' IN ( T2.I2 ) ) |
| +> OR ( T2.I2 < SOME ( |
| +> SELECT t3.i2 FROM t15d2 T3, t15d1 AS T4 |
| +> WHERE t1.i1 IN ( T4.I1 ) ) |
| +> ) |
| +> ) ; |
| |
| --- SQL command prepared. |
| >> |
| >>control query default COMP_BOOL_113 'OFF'; |
| |
| --- SQL operation complete. |
| >>PREPARE xx FROM |
| +> SELECT T0.i3 , t0.i3 FROM T15D2 T0 CROSS JOIN T15D1 t1 |
| +> WHERE EXISTS ( |
| +> SELECT T0.i2 FROM t15d1 AS t2 |
| +> WHERE ( 'cC' IN ( T2.I2 ) ) |
| +> OR ( T2.I2 < SOME ( |
| +> SELECT t3.i2 FROM t15d2 T3, t15d1 AS T4 |
| +> WHERE t1.i1 IN ( T4.I1 ) ) |
| +> ) |
| +> ) ; |
| |
| --- SQL command prepared. |
| >>control query default COMP_BOOL_113 reset; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>?section 3651_mod_equi_join_pred |
| >>-- make sure the query reported in genesis solution 10-080604-3651 |
| >>-- continue to get an inner-join (and not a cross producct) |
| >>control query default COMP_BOOL_113 'ON'; |
| |
| --- SQL operation complete. |
| >>prepare xx from select * from t15t1, t15t2 where ((t15t1.a-mod(t15t1.a,1000))/1000)=t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from select count(*) from t15t1, t15t2 where ((t15t1.a-mod(t15t1.a,1000))/1000)=t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from select * from t15t1, t15t2 where t15t1.a + ? = t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query default COMP_BOOL_113 'OFF'; |
| |
| --- SQL operation complete. |
| >>prepare xx from select * from t15t1, t15t2 where ((t15t1.a-mod(t15t1.a,1000))/1000)=t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from select count(*) from t15t1, t15t2 where ((t15t1.a-mod(t15t1.a,1000))/1000)=t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from select * from t15t1, t15t2 where t15t1.a + ? = t15t2.a; |
| |
| --- SQL command prepared. |
| >>select substring(description from position('join_type:' in description) for 17) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| ----------------- |
| |
| join_type: inner |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query default COMP_BOOL_113 reset; |
| |
| --- SQL operation complete. |
| >>control query default query_template_cache reset; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>?section moreEquiJoinsThatShouldNotBeCrossProducts |
| >>-- test genesis solution 10-090305-9790 |
| >>control query default COMP_BOOL_113 'ON'; |
| |
| --- SQL operation complete. |
| >>prepare xx from select * from $$TEST_CATALOG$$.myhcube.t10 t10 |
| +>left join $$TEST_CATALOG$$.myhcube.t9 t9 |
| +>on t9.d = lpad(trim(cast(t10.d as char(5))),10,'0'); |
| |
| --- SQL command prepared. |
| >>-- used to get a cross product |
| >>select substring(description from position('join_method:' in description) for 33) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| --------------------------------- |
| |
| join_method: hash (cross product) |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query default COMP_BOOL_113 'OFF'; |
| |
| --- SQL operation complete. |
| >>prepare xx from select * from $$TEST_CATALOG$$.myhcube.t10 t10 |
| +>left join $$TEST_CATALOG$$.myhcube.t9 t9 |
| +>on t9.d = lpad(trim(cast(t10.d as char(5))),10,'0'); |
| |
| --- SQL command prepared. |
| >>-- used to get a cross product |
| >>select substring(description from position('join_method:' in description) for 33) |
| +>from table(explain(null, 'XX')) where operator like '%JOIN%'; |
| |
| (EXPR) |
| --------------------------------- |
| |
| join_method: hash hash_join_predi |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query default COMP_BOOL_113 reset; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>?section RQPcqdtests |
| >>control query default showcontrol_show_all 'ON'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 1 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 0.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 1 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 1.15 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'minimum'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| MINIMUM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 0.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'system'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'maximum'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| MAXIMUM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 5.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization reset; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 5.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'MINIMUM'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| MINIMUM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 0.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'HIGH'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| HIGH |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 2.5 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 1.5 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 1 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 3.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 1.5 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'MAXIMUM'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| MAXIMUM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 5.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 2 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 2.0 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization 'SYSTEM'; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>control query default robust_query_optimization reset; |
| |
| --- SQL operation complete. |
| >>obey test015(showRQPcqds); |
| >>showcontrol default robust_query_optimization, match full, no header; |
| SYSTEM |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_nj, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_serial, match full, no header; |
| 1.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default partitioning_scheme_sharing, match full, no header; |
| 1 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_hj_to_nj_fudge_factor, match full, no header; |
| 0.0 |
| |
| --- SQL operation complete. |
| >>showcontrol default robust_sortgroupby, match full, no header; |
| 1 |
| |
| --- SQL operation complete. |
| >>showcontrol default risk_premium_mj, match full, no header; |
| 1.15 |
| |
| --- SQL operation complete. |
| >> |
| >> -- show RQP cqd settings |
| >> |
| >>?section keylessNJtest_setup |
| >>prepare showSimplePlan from |
| +> select [first 2] operator |
| +> from (select |
| +> substring(cast(SEQ_NUM+100 as char(3)),2,2), |
| +> substring(operator,1,16) operator |
| +> from table (explain(NULL,'XX')) |
| +> where trim(operator) <> 'ESP_EXCHANGE' |
| +> ) as t(s, operator) |
| +> order by s desc; |
| |
| --- SQL command prepared. |
| >> |
| >>?section keylessNJtests |
| >>set schema $$TEST_CATALOG$$.myhcube; |
| |
| --- SQL operation complete. |
| >> |
| >>control query default merge_joins 'OFF'; |
| |
| --- SQL operation complete. |
| >>control query default query_cache '0'; |
| |
| --- SQL operation complete. |
| >>control query default risk_premium_serial '1.0'; |
| |
| --- SQL operation complete. |
| >> |
| >>-- force keyless NJ |
| >>control query shape implicit exchange nested_join(cut,cut); |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from select * from t10 f, t0 d where f.b=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t9abc f, t0 d where f.b=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- keyless NJ must fail |
| >>control query default comp_int_71 '1'; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from select * from t10 f, t0 d where f.b=d.b; |
| |
| *** ERROR[2105] This query could not be compiled because of incompatible Control Query Shape (CQS) specifications. Inspect the CQS in effect. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>prepare xx from select * from t9abc f, t0 d where f.b=d.b; |
| |
| *** ERROR[2105] This query could not be compiled because of incompatible Control Query Shape (CQS) specifications. Inspect the CQS in effect. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- must not get a NJ |
| >>control query default comp_int_71 '0'; |
| |
| --- SQL operation complete. |
| >>control query shape cut; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from select * from t10 f, t0 d where f.b=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| HYBRID_HASH_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t9abc f, t0 d where f.b=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| HYBRID_HASH_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- keyed NJ must be allowed |
| >>prepare xx from select * from t10 f, t0 d where f.a=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t9abc f, t0 d where f.a=d.b; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- shutdown HJ iff outer has 1 row |
| >>prepare xx from select * from t10 f where f.b=(select max(b) from t0); |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t10 f where f.b=some(select b from t0); |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| HYBRID_HASH_SEMI |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- constant key predicate does not favor NJ over HJ |
| >>prepare xx from select * from t9abc f, t6 d where f.a=d.b and d.b=1; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| HYBRID_HASH_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t9abc f, t6 d where f.a=d.b and d.b=1 |
| +>and f.b=d.c and d.c=2; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| HYBRID_HASH_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- must allow keyed NJ that have constant prefix key predicates |
| >>control query default comp_int_71 '1'; |
| |
| --- SQL operation complete. |
| >>control query shape implicit exchange nested_join(cut,cut); |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from select * from t9abc f, t0abc d where f.a=d.b and f.a=9 |
| +>and f.b=d.c; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from select * from t9abc f, t0abc d where f.a=d.b and f.a=9 |
| +>and f.b=d.c and f.b=8 and f.c=d.a; |
| |
| --- SQL command prepared. |
| >>execute showSimplePlan; |
| |
| OPERATOR |
| ---------------- |
| |
| ROOT |
| NESTED_JOIN |
| |
| --- 2 row(s) selected. |
| >> |
| >>?section insSel_test |
| >>control query shape cut ; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare showSort from |
| +>select substring(operator,1,16) operator |
| +>from table (explain(NULL,'XX')) t |
| +>where operator = 'SORT' ; |
| |
| --- SQL command prepared. |
| >> |
| >>-- fix for bugzilla 2439, left child of tuple_flow should be sorted. |
| >>prepare XX from |
| +>insert with no rollback into t0abcg |
| +>select t0.a, t1.a, t2.a, t0.a, t1.a, |
| +> cast(t0.a*t1.a*t2.a as char(10)) |
| +>from t0, t1, t2; |
| |
| --- SQL command prepared. |
| >> |
| >>execute showSort; |
| |
| OPERATOR |
| ---------------- |
| |
| SORT |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- fix for HPIT case 10-090826-1194. |
| >>-- left child of tuple_flow should be sorted. |
| >>prepare XX from |
| +>insert with no rollback |
| +>into t1abc(d, a, e, c, f, b) |
| +>select t2.a, t0.a, t1.b, t1.a, t9abc.a, t2.a |
| +>from t0, t1, t2, t9abc |
| +>where t2.a = t1.b; |
| |
| --- SQL command prepared. |
| >> |
| >>execute showSort; |
| |
| OPERATOR |
| ---------------- |
| |
| SORT |
| |
| --- 1 row(s) selected. |
| >> |
| >>?section INList_test |
| >>control query shape cut ; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare explainIt from |
| +>select substring(operator,1,16) operator |
| +>from table (explain(NULL,'XX')) t |
| +>where operator LIKE '%TUPLE%' ; |
| |
| --- SQL command prepared. |
| >> |
| >>set schema $$TEST_CATALOG$$.myhcube; |
| |
| --- SQL operation complete. |
| >> |
| >>-- semijoin trans in Normalizer will kick in for in list longer than 1 entry |
| >>control query default or_pred_to_semijoin '1' ; |
| |
| --- SQL operation complete. |
| >>control query default or_pred_to_jumptable '0'; |
| |
| --- SQL operation complete. |
| >> |
| >>-- expect join plan with TupleList |
| >>-- rangespec interferes with this |
| >>cqd rangespec_transformation 'off'; |
| |
| --- SQL operation complete. |
| >>prepare XX from select b from t1 where b in (1,2,3,11) ; |
| |
| --- SQL command prepared. |
| >>cqd rangespec_transformation reset; |
| |
| --- SQL operation complete. |
| >>execute explainIt ; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| B |
| ----------- |
| |
| 1 |
| 2 |
| 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- OR is handled the same as IN List |
| >>-- rangespec interferes with this |
| >>cqd rangespec_transformation 'off'; |
| |
| --- SQL operation complete. |
| >>prepare XX from select b from t1 where b = 1 or b =2 or b = 3 or b = 11; |
| |
| --- SQL command prepared. |
| >>cqd rangespec_transformation reset; |
| |
| --- SQL operation complete. |
| >>execute explainIt ; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| B |
| ----------- |
| |
| 1 |
| 2 |
| 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >> |
| >>-- prameters are handled the same as literals |
| >>prepare XX from select b from t1 where b in (?p1, ?p2, ?p3, ?p4) ; |
| |
| --- SQL command prepared. |
| >>execute explainIt ; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>set param ?p1 1 ; |
| >>set param ?p2 2 ; |
| >>set param ?p3 3 ; |
| >>set param ?p4 11; |
| >>execute XX; |
| |
| B |
| ----------- |
| |
| 1 |
| 2 |
| 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- should not be transformed, two columns in OR |
| >>prepare XX from select * from t1 where a in (1,2,3) or b = 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt ; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- should be transformed |
| >>prepare XX from select a from t1 where a in (1,2,3,11) and b = 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| A |
| ----------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- two in lists on different columns related by and |
| >>-- rangespec interferes with this |
| >>cqd rangespec_transformation 'off'; |
| |
| --- SQL operation complete. |
| >>prepare XX from select a,b from t1 where a in (1,2,3,11) and b in (1,2,3,11); |
| |
| --- SQL command prepared. |
| >>cqd rangespec_transformation reset; |
| |
| --- SQL operation complete. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| TUPLELIST |
| |
| --- 2 row(s) selected. |
| >>execute XX; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| 3 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- two in lists on different columns related by OR |
| >>prepare XX from select a,b from t1 where a in (1,2,3) or b in (1,2,3); |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- expression outside the IN list |
| >>prepare XX from select a from t1 where a+3 in (1,2,4, 11); |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| A |
| ----------- |
| |
| 1 |
| 8 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- expression involving two columns outside the IN List |
| >>prepare XX from select a,b from t1 where a+b in (1,2,4); |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- The R2.4 AMB merge changed some defaults to fix a customer |
| >>-- issue. In order to keep the behavior of the following tests |
| >>-- consistent, the following CQD is being set to its old default for now. |
| >>-- This can be removed when the default changes back to its old value in |
| >>-- R2.5 and the subsequent merge into Seaquest. |
| >>--control query default or_pred_to_semijoin reset ; |
| >>control query default or_pred_to_semijoin '25' ; |
| |
| --- SQL operation complete. |
| >> |
| >>control query default or_pred_to_jumptable reset ; |
| |
| --- SQL operation complete. |
| >> |
| >>-- missing pred on part col b, should not get transformed. |
| >>prepare XX from select * from t11 where a in (1,2) ; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- should get transformed |
| >>prepare XX from select * from t11 where a in (1,2) and b = 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- range pred on part col b, should not get transformed. |
| >>prepare XX from select * from t11 where a in (1,2) and b > 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >> |
| >>control query default or_pred_to_semijoin_table_min_size '100' ; |
| |
| --- SQL operation complete. |
| >> |
| >>-- should transform |
| >>prepare XX from select * from t11 |
| +>where a in (1,2,3,4,5) and b = 1 order by a; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- should transform |
| >>prepare XX from select * from t11 |
| +>where a in (1,2,3,4,5) order by a; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >>execute XX; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 0 |
| 2 2 0 |
| 3 3 0 |
| 4 4 0 |
| 5 5 0 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- should transform |
| >>prepare XX from select * from t11 |
| +>where b in (1,2,3,4,5) and a = 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TUPLELIST |
| |
| --- 1 row(s) selected. |
| >>execute XX; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- no transform |
| >>prepare XX from select * from t11 |
| +>where b in (1,2,3,4,5) and a > 1; |
| |
| --- SQL command prepared. |
| >>execute explainIt; |
| |
| --- 0 row(s) selected. |
| >> |
| >>?section mdam_under_nj_test |
| >> |
| >>prepare mdam_reporter from |
| +>select cast(tname as char(30)) tname, |
| +>substring(description from position('mdam_disjunct' in description) + 15 for 30) mdam_disjunct |
| +>from table(explain(NULL, 'XX')) |
| +>where operator in ('FILE_SCAN') and position('mdam_disjunct' in description) > 0 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare plan_reporter from |
| +>select operator from table(explain(NULL, 'XX')) |
| +>where position('JOIN' in operator) > 0; |
| |
| --- SQL command prepared. |
| >> |
| >> |
| >>cqd query_cache '0'; |
| |
| --- SQL operation complete. |
| >> |
| >>cqd MDAM_SCAN_METHOD 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST015(mdam_under_nj_real_tests); |
| >> |
| >>-- positve tests |
| >> |
| >>-- count(*) is one value = one probe |
| >>prepare xx from select c2.f from (select count(*) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(a) is one value = one probe |
| >>prepare xx from select c2.f from (select max(a) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(b) is one value = one probe |
| >>prepare xx from select c2.f from (select sum(b) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- a is a PK column. should send one probe to cube2. |
| >>prepare xx from select c2.f from (select a from t0 where a = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- negative |
| >> |
| >>-- t0 could contain more than one row. Should not get mdam plan |
| >>prepare xx from select c2.f from (select a from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>--t0.b is not a PK column. should not get mdam plan |
| >>prepare xx from select c2.f from (select b from t0 where b = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >> |
| >> |
| >>-- set the cqd to 0 should turn off the feature |
| >>cqd MDAM_UNDER_NJ_PROBES_THRESHOLD '0'; |
| |
| --- SQL operation complete. |
| >>obey TEST015(mdam_under_nj_real_tests); |
| >> |
| >>-- positve tests |
| >> |
| >>-- count(*) is one value = one probe |
| >>prepare xx from select c2.f from (select count(*) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(a) is one value = one probe |
| >>prepare xx from select c2.f from (select max(a) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(b) is one value = one probe |
| >>prepare xx from select c2.f from (select sum(b) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- a is a PK column. should send one probe to cube2. |
| >>prepare xx from select c2.f from (select a from t0 where a = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- negative |
| >> |
| >>-- t0 could contain more than one row. Should not get mdam plan |
| >>prepare xx from select c2.f from (select a from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>--t0.b is not a PK column. should not get mdam plan |
| >>prepare xx from select c2.f from (select b from t0 where b = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >> |
| >> |
| >>cqd MDAM_SCAN_METHOD reset; |
| |
| --- SQL operation complete. |
| >>cqd MDAM_UNDER_NJ_PROBES_THRESHOLD reset; |
| |
| --- SQL operation complete. |
| >> |
| >>?section mdam_under_nj_real_tests |
| >> |
| >>-- positve tests |
| >> |
| >>-- count(*) is one value = one probe |
| >>prepare xx from select c2.f from (select count(*) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(a) is one value = one probe |
| >>prepare xx from select c2.f from (select max(a) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- max(b) is one value = one probe |
| >>prepare xx from select c2.f from (select sum(b) from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- a is a PK column. should send one probe to cube2. |
| >>prepare xx from select c2.f from (select a from t0 where a = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- negative |
| >> |
| >>-- t0 could contain more than one row. Should not get mdam plan |
| >>prepare xx from select c2.f from (select a from t0) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >>--t0.b is not a PK column. should not get mdam plan |
| >>prepare xx from select c2.f from (select b from t0 where b = 2) as x(a), cube2 c2 |
| +>where x.a = c2.b and (c2.a = 1 or c2.a = 3); |
| |
| --- SQL command prepared. |
| >> |
| >>execute mdam_reporter; |
| |
| --- 0 row(s) selected. |
| >>execute plan_reporter; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >> |
| >>?section done |
| >>log; |
