diff options
Diffstat (limited to 'src/test/regress/expected/numerology.out')
| -rw-r--r-- | src/test/regress/expected/numerology.out | 179 |
1 files changed, 179 insertions, 0 deletions
diff --git a/src/test/regress/expected/numerology.out b/src/test/regress/expected/numerology.out new file mode 100644 index 0000000..77d4843 --- /dev/null +++ b/src/test/regress/expected/numerology.out @@ -0,0 +1,179 @@ +-- +-- NUMEROLOGY +-- Test various combinations of numeric types and functions. +-- +-- +-- Trailing junk in numeric literals +-- +SELECT 123abc; +ERROR: trailing junk after numeric literal at or near "123a" +LINE 1: SELECT 123abc; + ^ +SELECT 0x0o; +ERROR: trailing junk after numeric literal at or near "0x" +LINE 1: SELECT 0x0o; + ^ +SELECT 1_2_3; +ERROR: trailing junk after numeric literal at or near "1_" +LINE 1: SELECT 1_2_3; + ^ +SELECT 0.a; +ERROR: trailing junk after numeric literal at or near "0.a" +LINE 1: SELECT 0.a; + ^ +SELECT 0.0a; +ERROR: trailing junk after numeric literal at or near "0.0a" +LINE 1: SELECT 0.0a; + ^ +SELECT .0a; +ERROR: trailing junk after numeric literal at or near ".0a" +LINE 1: SELECT .0a; + ^ +SELECT 0.0e1a; +ERROR: trailing junk after numeric literal at or near "0.0e1a" +LINE 1: SELECT 0.0e1a; + ^ +SELECT 0.0e; +ERROR: trailing junk after numeric literal at or near "0.0e" +LINE 1: SELECT 0.0e; + ^ +SELECT 0.0e+a; +ERROR: trailing junk after numeric literal at or near "0.0e+" +LINE 1: SELECT 0.0e+a; + ^ +PREPARE p1 AS SELECT $1a; +ERROR: trailing junk after parameter at or near "$1a" +LINE 1: PREPARE p1 AS SELECT $1a; + ^ +-- +-- Test implicit type conversions +-- This fails for Postgres v6.1 (and earlier?) +-- so let's try explicit conversions for now - tgl 97/05/07 +-- +CREATE TABLE TEMP_FLOAT (f1 FLOAT8); +INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT4_TBL; +INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT2_TBL; +SELECT f1 FROM TEMP_FLOAT + ORDER BY f1; + f1 +------------- + -2147483647 + -123456 + -32767 + -1234 + 0 + 0 + 1234 + 32767 + 123456 + 2147483647 +(10 rows) + +-- int4 +CREATE TABLE TEMP_INT4 (f1 INT4); +INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM FLOAT8_TBL + WHERE (f1 > -2147483647) AND (f1 < 2147483647); +INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM INT2_TBL; +SELECT f1 FROM TEMP_INT4 + ORDER BY f1; + f1 +-------- + -32767 + -1234 + -1004 + -35 + 0 + 0 + 0 + 1234 + 32767 +(9 rows) + +-- int2 +CREATE TABLE TEMP_INT2 (f1 INT2); +INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM FLOAT8_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); +INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM INT4_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); +SELECT f1 FROM TEMP_INT2 + ORDER BY f1; + f1 +------- + -1004 + -35 + 0 + 0 + 0 +(5 rows) + +-- +-- Group-by combinations +-- +CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8); +INSERT INTO TEMP_GROUP + SELECT 1, (- i.f1), (- f.f1) + FROM INT4_TBL i, FLOAT8_TBL f; +INSERT INTO TEMP_GROUP + SELECT 2, i.f1, f.f1 + FROM INT4_TBL i, FLOAT8_TBL f; +SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1; + two +----- + 1 + 2 +(2 rows) + +SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float + FROM TEMP_GROUP + GROUP BY f1 + ORDER BY two, max_float, min_float; + two | max_float | min_float +-----+----------------------+----------------------- + 1 | 1.2345678901234e+200 | -0 + 2 | 0 | -1.2345678901234e+200 +(2 rows) + +-- GROUP BY a result column name is not legal per SQL92, but we accept it +-- anyway (if the name is not the name of any column exposed by FROM). +SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, max_float, min_float; + two | max_float | min_float +-----+----------------------+----------------------- + 1 | 1.2345678901234e+200 | -0 + 2 | 0 | -1.2345678901234e+200 +(2 rows) + +SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 + FROM TEMP_GROUP + GROUP BY f1 + ORDER BY two, min_minus_1; + two | max_plus_1 | min_minus_1 +-----+----------------------+----------------------- + 1 | 1.2345678901234e+200 | -1 + 2 | 1 | -1.2345678901234e+200 +(2 rows) + +SELECT f1 AS two, + max(f2) + min(f2) AS max_plus_min, + min(f3) - 1 AS min_minus_1 + FROM TEMP_GROUP + GROUP BY f1 + ORDER BY two, min_minus_1; + two | max_plus_min | min_minus_1 +-----+--------------+----------------------- + 1 | 0 | -1 + 2 | 0 | -1.2345678901234e+200 +(2 rows) + +DROP TABLE TEMP_INT2; +DROP TABLE TEMP_INT4; +DROP TABLE TEMP_FLOAT; +DROP TABLE TEMP_GROUP; |