You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/04/26 18:38:42 UTC
[1/4] trafodion git commit: Add *RAND Function* in Trafodion SQL
Reference Manual
Repository: trafodion
Updated Branches:
refs/heads/master 06d38d5d3 -> 4985dbd84
Add *RAND Function* in Trafodion SQL Reference Manual
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/609cd5fc
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/609cd5fc
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/609cd5fc
Branch: refs/heads/master
Commit: 609cd5fc25de2a19903a733c0827d6f1a66c3393
Parents: c29ebc6
Author: liu.yu <qw...@hotmail.com>
Authored: Thu Apr 19 21:07:52 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Thu Apr 19 21:07:52 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 185 ++++++++++++++++++-
1 file changed, 183 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/609cd5fc/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index b86f4c8..83afe9d 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -1855,7 +1855,7 @@ COSH (1.25)
<<<
[[count_function]]
-=== COUNT Function
+== COUNT Function
The COUNT function counts the number of rows that result from a query or
the number of rows that contain a distinct value in a specific column.
@@ -4659,7 +4659,7 @@ See <<upper_function,UPPER Function>>.
<<<
[[lpad_function]]
-=== LPAD Function
+== LPAD Function
The LPAD function pads the left side of a string with the specified
string. Every character in the string, including multi-byte characters,
@@ -9032,6 +9032,187 @@ WHERE UPSHIFT(D1.deptname) = UPSHIFT(D2.deptname);
```
<<<
+[[rand_function]]
+== RAND Function
+
+The RAND Function generates pseudo-random numbers that are uniformly distributed
+in the range between 0 and 2^32^ (=4294967296),
+using an integer as an optional seed value.
+
+```
+RAND ([numeric-expression])
+```
+
+[[rand_function_syntax]]
+=== Syntax Descriptions of RAND Function
+
+* _numeric-expression_
+
++
+is an SQL numeric value expression that specifies the value for the argument of the RAND function. For more information, see <<numeric_value_expressions,Numeric Value Expressions>>.
+
+[[rand_function_considerations]]
+=== Considerations for RAND Function
+
+* When invoked with a seed value,
+
+** the seed value should be an integer greater than or equal to 0, and cannot be NULL.
+
++
+For example,
+
++
+```
+SQL>SELECT RAND(1-100) FROM DUAL;
+
+*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-99 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. [2018-04-18 04:44:37]
+
+
+SQL>SELECT RAND(NULL) FROM DUAL;
+
+*** ERROR[4097] A NULL operand is not allowed in function (RANDOMNUM(NULL)). [2018-04-18 04:44:48]
+```
+
+** the RAND function will use the value to seed the random number generator.
+
+** the RAND function will produce same series of numbers each time you seed the generator with an equal argument value
+for each run of the program.
+
+* When invoked with no seed value,
+
+** the RAND function is automatically seeded with a value of system clock.
+** the RAND function will produce different series of numbers even it is called consecutively within a query.
+
++
+For example,
+
++
+```
+SQL>SELECT RAND(), RAND(), RAND() FROM DUAL;
+
+(EXPR) (EXPR) (EXPR)
+---------- ---------- ----------
+1958129868 1958398780 1958482815
+
+--- 1 row(s) selected.
+```
+
+* To get desired numbers within a specified range, you can use a combination of built-in functions, operators and clauses.
+
++
+For example,
+
++
+```
+SQL>SELECT POWER(RAND(1)*10,3) FROM DUAL;
+
+(EXPR)
+------------------------
+ 4.747561509943E15
+
+--- 1 row(s) selected.
+```
+
+[[rand_function_example]]
+=== Examples of RAND Function
+
+* This example returns random values using 0, 1, 10, 100 and 1000 as seed values.
+
++
+```
+SQL>SELECT RAND(0), RAND(1), RAND(10), RAND(100), RAND(1000) FROM DUAL;
+
+(EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
+---------- ---------- ---------- ---------- ----------
+2147483647 16807 168070 1680700 16807000
+
+--- 1 row(s) selected.
+```
+
+* This example demostrates how to use the RAND function with built-in function (ABS function).
+
++
+```
+SQL>SELECT RAND(ABS(1-100)) FROM DUAL;
+
+(EXPR)
+----------
+ 1663893
+
+--- 1 row(s) selected.
+```
+
+* This example shows that the RAND function works with builts-in function (MOD function) to generate the number from 1 to 5 and gets
+the randomized results sorted by ORDER BY clause. The 100,000 rows of c1 are neary evenly distributed for each number.
+
++
+```
+SQL>SELECT COUNT(*) FROM t1;
+
+(EXPR)
+--------------------
+ 100000
+
+--- 1 row(s) selected.
+```
++
+```
+SQL>SHOWDDL t1;
+
+CREATE TABLE TRAFODION.SEABASE.T1
+ (
+ IDX INT DEFAULT NULL NOT SERIALIZED
+ )
+ATTRIBUTES ALIGNED FORMAT
+;
+
+--- SQL operation complete.
+```
++
+```
+SQL>CREATE TABLE t2(idx int,c1 int);
+
+--- SQL operation complete.
+
+```
++
+```
+SQL>UPSERT USING LOAD INTO t2 SELECT idx, MOD(RAND(),5)+1 FROM t1;
+
+--- 100000 row(s) inserted.
+```
++
+```
+SQL>SELECT c1, COUNT(*) FROM t2 GROUP BY c1 ORDER BY c1;
+
+C1 (EXPR)
+----------- --------------------
+ 1 19837
+ 2 19951
+ 3 20023
+ 4 19913
+ 5 20276
+
+--- 5 row(s) selected.
+```
+
++
+At this point, the RAND function can also be used in the ORDER BY clause in conjunction with the LIMIT clause
+to output rows.
++
+```
+SQL>SELECT c1, COUNT(*) FROM t2 GROUP BY c1 ORDER BY c1 LIMIT 3;
+
+C1 (EXPR)
+----------- --------------------
+ 1 19837
+ 2 19951
+ 3 20023
+
+--- 3 row(s) selected.
+```
+
+<<<
[[user_function]]
== USER Function
[2/4] trafodion git commit: Incorporate Comments 1
Posted by db...@apache.org.
Incorporate Comments 1
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/a2f740b9
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/a2f740b9
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/a2f740b9
Branch: refs/heads/master
Commit: a2f740b996828b12972741b19b2292cf9a476883
Parents: 609cd5f
Author: liu.yu <qw...@hotmail.com>
Authored: Fri Apr 20 14:08:04 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Fri Apr 20 14:08:04 2018 +0800
----------------------------------------------------------------------
.../_chapters/sql_functions_and_expressions.adoc | 17 +++--------------
1 file changed, 3 insertions(+), 14 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/a2f740b9/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 83afe9d..a12b983 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -9035,10 +9035,12 @@ WHERE UPSHIFT(D1.deptname) = UPSHIFT(D2.deptname);
[[rand_function]]
== RAND Function
-The RAND Function generates pseudo-random numbers that are uniformly distributed
+The RAND function generates pseudo-random numbers that are uniformly distributed
in the range between 0 and 2^32^ (=4294967296),
using an integer as an optional seed value.
+NOTE: the RAND function does not always behaves in predictive ways.
+
```
RAND ([numeric-expression])
```
@@ -9116,19 +9118,6 @@ SQL>SELECT POWER(RAND(1)*10,3) FROM DUAL;
[[rand_function_example]]
=== Examples of RAND Function
-* This example returns random values using 0, 1, 10, 100 and 1000 as seed values.
-
-+
-```
-SQL>SELECT RAND(0), RAND(1), RAND(10), RAND(100), RAND(1000) FROM DUAL;
-
-(EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
----------- ---------- ---------- ---------- ----------
-2147483647 16807 168070 1680700 16807000
-
---- 1 row(s) selected.
-```
-
* This example demostrates how to use the RAND function with built-in function (ABS function).
+
[4/4] trafodion git commit: Merge [TRAFODION-3030] PR 1527 Add RAND
function to SQL Reference Manual
Posted by db...@apache.org.
Merge [TRAFODION-3030] PR 1527 Add RAND function to SQL Reference Manual
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/4985dbd8
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/4985dbd8
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/4985dbd8
Branch: refs/heads/master
Commit: 4985dbd84e5c59ebeadca00effb8cffcdfe3f270
Parents: 06d38d5 fd70c7d
Author: Dave Birdsall <db...@apache.org>
Authored: Thu Apr 26 18:38:03 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Thu Apr 26 18:38:03 2018 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 176 ++++++++++++++++++-
1 file changed, 174 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
[3/4] trafodion git commit: Incorporate Comments 2
Posted by db...@apache.org.
Incorporate Comments 2
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/fd70c7d6
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/fd70c7d6
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/fd70c7d6
Branch: refs/heads/master
Commit: fd70c7d6ec2145b1fdb2c620e1ec35009f220616
Parents: a2f740b
Author: liu.yu <qw...@hotmail.com>
Authored: Wed Apr 25 19:40:42 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Wed Apr 25 19:40:42 2018 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_functions_and_expressions.adoc | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/fd70c7d6/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index a12b983..94b5126 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -9039,7 +9039,9 @@ The RAND function generates pseudo-random numbers that are uniformly distributed
in the range between 0 and 2^32^ (=4294967296),
using an integer as an optional seed value.
-NOTE: the RAND function does not always behaves in predictive ways.
+NOTE: The RAND function does not always behaves in predictive ways. It is non-deterministic
+and may cause unexpected side effect. It evaluates only once or per row depending on the optimizer.
+So it can only be used in very few places, please do not use it in a predicate.
```
RAND ([numeric-expression])