[TRAFODION-3131] test case added
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/cd095a0e Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/cd095a0e Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/cd095a0e Branch: refs/heads/master Commit: cd095a0e3beff02c813feb4898a2db1e270de88d Parents: a8860c1 Author: Guhaiyan <[email protected]> Authored: Wed Jul 11 06:30:29 2018 +0000 Committer: Guhaiyan <[email protected]> Committed: Wed Jul 11 06:30:29 2018 +0000 ---------------------------------------------------------------------- core/sql/regress/core/EXPECTED002.LINUX | 57 ++++++++++++++++++++++++++++ core/sql/regress/core/TEST002 | 25 ++++++++++++ 2 files changed, 82 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/cd095a0e/core/sql/regress/core/EXPECTED002.LINUX ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED002.LINUX b/core/sql/regress/core/EXPECTED002.LINUX index b7dee45..8d2562b 100644 --- a/core/sql/regress/core/EXPECTED002.LINUX +++ b/core/sql/regress/core/EXPECTED002.LINUX @@ -1279,4 +1279,61 @@ X 7 --- 1 row(s) selected. +>> +>>-- Tests of limit n in subqueries +>> +>>-- Should return 1 +>>select ++>(select y aa from t002sub b where b.x = a.b limit 1) as result_value ++>from t002main a; + +RESULT_VALUE +---------- + + 1 + +--- 1 row(s) selected. +>> +>>-- Should get a cardinality violation (error 8401) +>>select ++>(select y aa from t002sub b where b.y = a.b limit 2) as result_value ++>from t002main a; + +*** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. + +--- 0 row(s) selected. +>> +>>-- Should return 1 +>>select ++>(select y aa from t002sub b where b.y = a.b limit 1) as result_value ++>from t002main a; + +RESULT_VALUE +---------- + + 1 + +--- 1 row(s) selected. +>> +>>-- Should return 2 +>>select ++>(select count(*) from t002sub limit 20) as result_value ++>from t002main; + +RESULT_VALUE +---------- + + 2 + +--- 1 row(s) selected. +>> +>>-- Should return 7 +>>select x from (select x from t002sol order by x desc limit 1); + +X +---------- + + 2 + +--- 1 row(s) selected. >>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/cd095a0e/core/sql/regress/core/TEST002 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST002 b/core/sql/regress/core/TEST002 index a9db00c..19bdede 100755 --- a/core/sql/regress/core/TEST002 +++ b/core/sql/regress/core/TEST002 @@ -590,6 +590,31 @@ from t002main; -- Should return 7 select x from (select [first 1] x from t002sol order by x desc ); + +-- Tests of limit n in subqueries + +-- Should return 1 +select +(select y aa from t002sub b where b.x = a.b limit 1) as result_value +from t002main a; + +-- Should get a cardinality violation (error 8401) +select +(select y aa from t002sub b where b.y = a.b limit 2) as result_value +from t002main a; + +-- Should return 1 +select +(select y aa from t002sub b where b.y = a.b limit 1) as result_value +from t002main a; + +-- Should return 2 +select +(select count(*) from t002sub limit 20) as result_value +from t002main; + +-- Should return 7 +select x from (select x from t002sol order by x desc limit 1); log; obey TEST002(clnup); exit;
