From f54668d6179efdb4476b4f08d987f4ca7b10b303 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 4 Jan 2000 16:19:34 +0000 Subject: [PATCH] Match results with format from new psql. All of these tests have been completely inspected and give correct results. --- src/test/regress/expected/boolean.out | 379 ++++++++++++++++------------ src/test/regress/expected/char.out | 163 ++++++------ src/test/regress/expected/float4.out | 216 ++++++++-------- src/test/regress/expected/float8.out | 340 +++++++++++++------------ src/test/regress/expected/int2.out | 323 ++++++++++++------------ src/test/regress/expected/int4.out | 453 +++++++++++++++++----------------- src/test/regress/expected/int8.out | 188 +++++++------- src/test/regress/expected/name.out | 188 +++++++------- src/test/regress/expected/oid.out | 102 ++++---- src/test/regress/expected/text.out | 35 +-- src/test/regress/expected/varchar.out | 149 +++++------ 11 files changed, 1339 insertions(+), 1197 deletions(-) diff --git a/src/test/regress/expected/boolean.out b/src/test/regress/expected/boolean.out index b40f48234e..8cf3764319 100644 --- a/src/test/regress/expected/boolean.out +++ b/src/test/regress/expected/boolean.out @@ -1,220 +1,279 @@ -QUERY: SELECT 1 AS one; -one ---- - 1 +-- +-- BOOLEAN +-- +-- +-- sanity check - if this fails go insane! +-- +SELECT 1 AS one; + one +----- + 1 (1 row) -QUERY: SELECT 't'::bool AS true; -true ----- -t +-- ******************testing built-in type bool******************** +-- check bool type-casting as well as and, or, not in qualifications-- +SELECT bool 't' AS true; + true +------ + t (1 row) -QUERY: SELECT 'f'::bool AS false; -false ------ -f +SELECT bool 'f' AS false; + false +------- + f (1 row) -QUERY: SELECT 't'::bool or 'f'::bool AS true; -true ----- -t +SELECT bool 't' or bool 'f' AS true; + true +------ + t (1 row) -QUERY: SELECT 't'::bool and 'f'::bool AS false; -false ------ -f +SELECT bool 't' and bool 'f' AS false; + false +------- + f (1 row) -QUERY: SELECT not 'f'::bool AS true; -true ----- -t +SELECT not bool 'f' AS true; + true +------ + t (1 row) -QUERY: SELECT 't'::bool = 'f'::bool AS false; -false ------ -f +SELECT bool 't' = bool 'f' AS false; + false +------- + f (1 row) -QUERY: SELECT 't'::bool <> 'f'::bool AS true; -true ----- -t +SELECT bool 't' <> bool 'f' AS true; + true +------ + t (1 row) -QUERY: CREATE TABLE BOOLTBL1 (f1 bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('t'::bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('True'::bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('true'::bool); -QUERY: SELECT '' AS t_3, BOOLTBL1.*; -t_3|f1 ----+-- - |t - |t - |t +CREATE TABLE BOOLTBL1 (f1 bool); +INSERT INTO BOOLTBL1 (f1) VALUES (bool 't'); +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True'); +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true'); +-- BOOLTBL1 should be full of true's at this point +SELECT '' AS t_3, BOOLTBL1.*; + t_3 | f1 +-----+---- + | t + | t + | t (3 rows) -QUERY: SELECT '' AS t_3, BOOLTBL1.* +SELECT '' AS t_3, BOOLTBL1.* FROM BOOLTBL1 - WHERE f1 = 'true'::bool; -t_3|f1 ----+-- - |t - |t - |t + WHERE f1 = bool 'true'; + t_3 | f1 +-----+---- + | t + | t + | t (3 rows) -QUERY: SELECT '' AS t_3, BOOLTBL1.* +SELECT '' AS t_3, BOOLTBL1.* FROM BOOLTBL1 - WHERE f1 <> 'false'::bool; -t_3|f1 ----+-- - |t - |t - |t + WHERE f1 <> bool 'false'; + t_3 | f1 +-----+---- + | t + | t + | t (3 rows) -QUERY: SELECT '' AS zero, BOOLTBL1.* +SELECT '' AS zero, BOOLTBL1.* FROM BOOLTBL1 - WHERE booleq('false'::bool, f1); -zero|f1 -----+-- + WHERE booleq(bool 'false', f1); + zero | f1 +------+---- (0 rows) -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('f'::bool); -QUERY: SELECT '' AS f_1, BOOLTBL1.* +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); +SELECT '' AS f_1, BOOLTBL1.* FROM BOOLTBL1 - WHERE f1 = 'false'::bool; -f_1|f1 ----+-- - |f + WHERE f1 = bool 'false'; + f_1 | f1 +-----+---- + | f (1 row) -QUERY: CREATE TABLE BOOLTBL2 (f1 bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('f'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('false'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('False'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('FALSE'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) - VALUES ('XXX'::bool); +CREATE TABLE BOOLTBL2 (f1 bool); +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f'); +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false'); +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False'); +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE'); +-- This is now an invalid expression +-- For pre-v6.3 this evaluated to false - thomas 1997-10-23 +INSERT INTO BOOLTBL2 (f1) + VALUES (bool 'XXX'); ERROR: Bad boolean external representation 'XXX' -QUERY: SELECT '' AS f_4, BOOLTBL2.*; -f_4|f1 ----+-- - |f - |f - |f - |f +-- BOOLTBL2 should be full of false's at this point +SELECT '' AS f_4, BOOLTBL2.*; + f_4 | f1 +-----+---- + | f + | f + | f + | f (4 rows) -QUERY: SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* +SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; -tf_12|f1|f1 ------+--+-- - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f + tf_12 | f1 | f1 +-------+----+---- + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f (12 rows) -QUERY: SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* +SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); -tf_12|f1|f1 ------+--+-- - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f + tf_12 | f1 | f1 +-------+----+---- + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f (12 rows) -QUERY: SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = 'false'::bool; -ff_4|f1|f1 -----+--+-- - |f |f - |f |f - |f |f - |f |f +SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* + WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false'; + ff_4 | f1 | f1 +------+----+---- + | f | f + | f | f + | f | f + | f | f (4 rows) -QUERY: SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = 'true'::bool +SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* + WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true' ORDER BY BOOLTBL1.f1, BOOLTBL2.f1; -tf_12_ff_4|f1|f1 -----------+--+-- - |f |f - |f |f - |f |f - |f |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f - |t |f + tf_12_ff_4 | f1 | f1 +------------+----+---- + | f | f + | f | f + | f | f + | f | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f (16 rows) -QUERY: SELECT '' AS "True", BOOLTBL1.* +-- +-- SQL92 syntax +-- Try all combinations to ensure that we get nothing when we expect nothing +-- - thomas 2000-01-04 +-- +SELECT '' AS "True", f1 FROM BOOLTBL1 WHERE f1 IS TRUE; -True|f1 -----+-- - |t - |t - |t + True | f1 +------+---- + | t + | t + | t (3 rows) -QUERY: SELECT '' AS "Not False", BOOLTBL1.* +SELECT '' AS "Not False", f1 FROM BOOLTBL1 WHERE f1 IS NOT FALSE; -Not False|f1 ----------+-- - |t - |t - |t + Not False | f1 +-----------+---- + | t + | t + | t (3 rows) -QUERY: SELECT '' AS "False", BOOLTBL1.* +SELECT '' AS "False", f1 FROM BOOLTBL1 WHERE f1 IS FALSE; -False|f1 ------+-- - |f + False | f1 +-------+---- + | f (1 row) -QUERY: SELECT '' AS "Not True", BOOLTBL1.* +SELECT '' AS "Not True", f1 FROM BOOLTBL1 WHERE f1 IS NOT TRUE; -Not True|f1 ---------+-- - |f + Not True | f1 +----------+---- + | f (1 row) -QUERY: DROP TABLE BOOLTBL1; -QUERY: DROP TABLE BOOLTBL2; +SELECT '' AS "True", f1 + FROM BOOLTBL2 + WHERE f1 IS TRUE; + True | f1 +------+---- +(0 rows) + +SELECT '' AS "Not False", f1 + FROM BOOLTBL2 + WHERE f1 IS NOT FALSE; + Not False | f1 +-----------+---- +(0 rows) + +SELECT '' AS "False", f1 + FROM BOOLTBL2 + WHERE f1 IS FALSE; + False | f1 +-------+---- + | f + | f + | f + | f +(4 rows) + +SELECT '' AS "Not True", f1 + FROM BOOLTBL2 + WHERE f1 IS NOT TRUE; + Not True | f1 +----------+---- + | f + | f + | f + | f +(4 rows) + +-- +-- Clean up +-- Many tables are retained by the regression test, but these do not seem +-- particularly useful so just get rid of them for now. +-- - thomas 1997-11-30 +-- +DROP TABLE BOOLTBL1; +DROP TABLE BOOLTBL2; diff --git a/src/test/regress/expected/char.out b/src/test/regress/expected/char.out index 45d7eddea1..63606fba2d 100644 --- a/src/test/regress/expected/char.out +++ b/src/test/regress/expected/char.out @@ -1,104 +1,119 @@ -QUERY: SELECT 'c'::char = 'c'::char AS true; -true ----- -t +-- +-- CHAR +-- all inputs are SILENTLY truncated at 1 character +-- +-- fixed-length by value +-- internally passed by value if <= 4 bytes in storage +SELECT char 'c' = char 'c' AS true; + true +------ + t (1 row) -QUERY: CREATE TABLE CHAR_TBL(f1 char); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('a'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('A'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('1'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES (2); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('3'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES (''); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('cd'); -QUERY: SELECT '' AS seven, CHAR_TBL.*; -seven|f1 ------+-- - |a - |A - |1 - |2 - |3 - | - |c +-- +-- Build a table for testing +-- +CREATE TABLE CHAR_TBL(f1 char); +INSERT INTO CHAR_TBL (f1) VALUES ('a'); +INSERT INTO CHAR_TBL (f1) VALUES ('A'); +-- any of the following three input formats are acceptable +INSERT INTO CHAR_TBL (f1) VALUES ('1'); +INSERT INTO CHAR_TBL (f1) VALUES (2); +INSERT INTO CHAR_TBL (f1) VALUES ('3'); +-- zero-length char +INSERT INTO CHAR_TBL (f1) VALUES (''); +-- try char's of greater than 1 length +INSERT INTO CHAR_TBL (f1) VALUES ('cd'); +SELECT '' AS seven, CHAR_TBL.*; + seven | f1 +-------+---- + | a + | A + | 1 + | 2 + | 3 + | + | c (7 rows) -QUERY: SELECT '' AS six, c.* +SELECT '' AS six, c.* FROM CHAR_TBL c WHERE c.f1 <> 'a'; -six|f1 ----+-- - |A - |1 - |2 - |3 - | - |c + six | f1 +-----+---- + | A + | 1 + | 2 + | 3 + | + | c (6 rows) -QUERY: SELECT '' AS one, c.* +SELECT '' AS one, c.* FROM CHAR_TBL c WHERE c.f1 = 'a'; -one|f1 ----+-- - |a + one | f1 +-----+---- + | a (1 row) -QUERY: SELECT '' AS five, c.* +SELECT '' AS five, c.* FROM CHAR_TBL c WHERE c.f1 < 'a'; -five|f1 -----+-- - |A - |1 - |2 - |3 - | + five | f1 +------+---- + | A + | 1 + | 2 + | 3 + | (5 rows) -QUERY: SELECT '' AS six, c.* +SELECT '' AS six, c.* FROM CHAR_TBL c WHERE c.f1 <= 'a'; -six|f1 ----+-- - |a - |A - |1 - |2 - |3 - | + six | f1 +-----+---- + | a + | A + | 1 + | 2 + | 3 + | (6 rows) -QUERY: SELECT '' AS one, c.* +SELECT '' AS one, c.* FROM CHAR_TBL c WHERE c.f1 > 'a'; -one|f1 ----+-- - |c + one | f1 +-----+---- + | c (1 row) -QUERY: SELECT '' AS two, c.* +SELECT '' AS two, c.* FROM CHAR_TBL c WHERE c.f1 >= 'a'; -two|f1 ----+-- - |a - |c + two | f1 +-----+---- + | a + | c (2 rows) -QUERY: DROP TABLE CHAR_TBL; -QUERY: CREATE TABLE CHAR_TBL(f1 char(4)); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('a'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('ab'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('abcd'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('abcde'); -QUERY: SELECT '' AS four, CHAR_TBL.*; -four|f1 -----+---- - |a - |ab - |abcd - |abcd +DROP TABLE CHAR_TBL; +-- +-- Now test longer arrays of char +-- +CREATE TABLE CHAR_TBL(f1 char(4)); +INSERT INTO CHAR_TBL (f1) VALUES ('a'); +INSERT INTO CHAR_TBL (f1) VALUES ('ab'); +INSERT INTO CHAR_TBL (f1) VALUES ('abcd'); +INSERT INTO CHAR_TBL (f1) VALUES ('abcde'); +SELECT '' AS four, CHAR_TBL.*; + four | f1 +------+------ + | a + | ab + | abcd + | abcd (4 rows) diff --git a/src/test/regress/expected/float4.out b/src/test/regress/expected/float4.out index acea4c6c59..e03db639c5 100644 --- a/src/test/regress/expected/float4.out +++ b/src/test/regress/expected/float4.out @@ -1,144 +1,150 @@ -QUERY: CREATE TABLE FLOAT4_TBL (f1 float4); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40'); +-- +-- FLOAT4 +-- +CREATE TABLE FLOAT4_TBL (f1 float4); +INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); +-- test for over and under flow +INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40'); ERROR: Bad float4 input format -- overflow -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40'); ERROR: Bad float4 input format -- overflow -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40'); ERROR: Bad float4 input format -- underflow -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40'); +INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40'); ERROR: Bad float4 input format -- underflow -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+----------- - |0 - |1004.3 - |-34.84 - |1.23457e+20 - |1.23457e-20 +SELECT '' AS five, FLOAT4_TBL.*; + five | f1 +------+------------- + | 0 + | 1004.3 + | -34.84 + | 1.23457e+20 + | 1.23457e-20 (5 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; -four|f1 -----+----------- - |0 - |-34.84 - |1.23457e+20 - |1.23457e-20 +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; + four | f1 +------+------------- + | 0 + | -34.84 + | 1.23457e+20 + | 1.23457e-20 (4 rows) -QUERY: SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; -one| f1 ----+------ - |1004.3 +SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; + one | f1 +-----+-------- + | 1004.3 (1 row) -QUERY: SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; -three| f1 ------+----------- - | 0 - | -34.84 - |1.23457e-20 +SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; + three | f1 +-------+------------- + | 0 + | -34.84 + | 1.23457e-20 (3 rows) -QUERY: SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; -three| f1 ------+----------- - | 0 - | -34.84 - |1.23457e-20 +SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; + three | f1 +-------+------------- + | 0 + | -34.84 + | 1.23457e-20 (3 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; -four| f1 -----+----------- - | 0 - | 1004.3 - | -34.84 - |1.23457e-20 +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; + four | f1 +------+------------- + | 0 + | 1004.3 + | -34.84 + | 1.23457e-20 (4 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; -four| f1 -----+----------- - | 0 - | 1004.3 - | -34.84 - |1.23457e-20 +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; + four | f1 +------+------------- + | 0 + | 1004.3 + | -34.84 + | 1.23457e-20 (4 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f +SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+------------ - |1004.3 |-10043 - |1.23457e+20|-1.23457e+21 - |1.23457e-20|-1.23457e-19 + three | f1 | x +-------+-------------+-------------- + | 1004.3 | -10043 + | 1.23457e+20 | -1.23457e+21 + | 1.23457e-20 | -1.23457e-19 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f +SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+----------- - |1004.3 |994.3 - |1.23457e+20|1.23457e+20 - |1.23457e-20|-10 + three | f1 | x +-------+-------------+------------- + | 1004.3 | 994.3 + | 1.23457e+20 | 1.23457e+20 + | 1.23457e-20 | -10 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f +SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+------------ - |1004.3 |-100.43 - |1.23457e+20|-1.23457e+19 - |1.23457e-20|-1.23457e-21 + three | f1 | x +-------+-------------+-------------- + | 1004.3 | -100.43 + | 1.23457e+20 | -1.23457e+19 + | 1.23457e-20 | -1.23457e-21 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f +SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+----------- - |1004.3 |1014.3 - |1.23457e+20|1.23457e+20 - |1.23457e-20|10 + three | f1 | x +-------+-------------+------------- + | 1004.3 | 1014.3 + | 1.23457e+20 | 1.23457e+20 + | 1.23457e-20 | 10 (3 rows) -QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; +-- test divide by zero +SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; ERROR: float4div: divide by zero error -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+----------- - |0 - |1004.3 - |-34.84 - |1.23457e+20 - |1.23457e-20 +SELECT '' AS five, FLOAT4_TBL.*; + five | f1 +------+------------- + | 0 + | 1004.3 + | -34.84 + | 1.23457e+20 + | 1.23457e-20 (5 rows) -QUERY: SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; -five|f1 |abs_f1 -----+-----------+----------- - |0 |0 - |1004.3 |1004.3 - |-34.84 |34.84 - |1.23457e+20|1.23457e+20 - |1.23457e-20|1.23457e-20 +-- test the unary float4abs operator +SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; + five | f1 | abs_f1 +------+-------------+------------- + | 0 | 0 + | 1004.3 | 1004.3 + | -34.84 | 34.84 + | 1.23457e+20 | 1.23457e+20 + | 1.23457e-20 | 1.23457e-20 (5 rows) -QUERY: UPDATE FLOAT4_TBL +UPDATE FLOAT4_TBL SET f1 = FLOAT4_TBL.f1 * '-1' WHERE FLOAT4_TBL.f1 > '0.0'; -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+------------ - |0 - |-34.84 - |-1004.3 - |-1.23457e+20 - |-1.23457e-20 +SELECT '' AS five, FLOAT4_TBL.*; + five | f1 +------+-------------- + | 0 + | -34.84 + | -1004.3 + | -1.23457e+20 + | -1.23457e-20 (5 rows) diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out index 90f7bc37c8..fc7b3779d7 100644 --- a/src/test/regress/expected/float8.out +++ b/src/test/regress/expected/float8.out @@ -1,234 +1,246 @@ -QUERY: CREATE TABLE FLOAT8_TBL(f1 float8); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+-------------------- - |0 - |1004.3 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 +-- +-- FLOAT8 +-- +CREATE TABLE FLOAT8_TBL(f1 float8); +INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); +SELECT '' AS five, FLOAT8_TBL.*; + five | f1 +------+---------------------- + | 0 + | 1004.3 + | -34.84 + | 1.2345678901234e+200 + | 1.2345678901234e-200 (5 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; -four|f1 -----+-------------------- - |0 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 +SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; + four | f1 +------+---------------------- + | 0 + | -34.84 + | 1.2345678901234e+200 + | 1.2345678901234e-200 (4 rows) -QUERY: SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; -one| f1 ----+------ - |1004.3 +SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; + one | f1 +-----+-------- + | 1004.3 (1 row) -QUERY: SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; -three| f1 ------+-------------------- - | 0 - | -34.84 - |1.2345678901234e-200 +SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; + three | f1 +-------+---------------------- + | 0 + | -34.84 + | 1.2345678901234e-200 (3 rows) -QUERY: SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; -three| f1 ------+-------------------- - | 0 - | -34.84 - |1.2345678901234e-200 +SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; + three | f1 +-------+---------------------- + | 0 + | -34.84 + | 1.2345678901234e-200 (3 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; -four| f1 -----+-------------------- - | 0 - | 1004.3 - | -34.84 - |1.2345678901234e-200 +SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; + four | f1 +------+---------------------- + | 0 + | 1004.3 + | -34.84 + | 1.2345678901234e-200 (4 rows) -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; -four| f1 -----+-------------------- - | 0 - | 1004.3 - | -34.84 - |1.2345678901234e-200 +SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; + four | f1 +------+---------------------- + | 0 + | 1004.3 + | -34.84 + | 1.2345678901234e-200 (4 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 * '-10' AS x +SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+--------------------- - |1004.3 |-10043 - |1.2345678901234e+200|-1.2345678901234e+201 - |1.2345678901234e-200|-1.2345678901234e-199 + three | f1 | x +-------+----------------------+----------------------- + | 1004.3 | -10043 + | 1.2345678901234e+200 | -1.2345678901234e+201 + | 1.2345678901234e-200 | -1.2345678901234e-199 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 + '-10' AS x +SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+-------------------- - |1004.3 |994.3 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|-10 + three | f1 | x +-------+----------------------+---------------------- + | 1004.3 | 994.3 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | -10 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 / '-10' AS x +SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+--------------------- - |1004.3 |-100.43 - |1.2345678901234e+200|-1.2345678901234e+199 - |1.2345678901234e-200|-1.2345678901234e-201 + three | f1 | x +-------+----------------------+----------------------- + | 1004.3 | -100.43 + | 1.2345678901234e+200 | -1.2345678901234e+199 + | 1.2345678901234e-200 | -1.2345678901234e-201 (3 rows) -QUERY: SELECT '' AS three, f.f1, f.f1 - '-10' AS x +SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+-------------------- - |1004.3 |1014.3 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|10 + three | f1 | x +-------+----------------------+---------------------- + | 1004.3 | 1014.3 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 10 (3 rows) -QUERY: SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 +SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 FROM FLOAT8_TBL f where f.f1 = '1004.3'; -one| square_f1 ----+---------- - |1008618.49 + one | square_f1 +-----+------------ + | 1008618.49 (1 row) -QUERY: SELECT '' AS five, f.f1, @f.f1 AS abs_f1 +-- absolute value +SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT8_TBL f; -five|f1 |abs_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004.3 - |-34.84 |34.84 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|1.2345678901234e-200 + five | f1 | abs_f1 +------+----------------------+---------------------- + | 0 | 0 + | 1004.3 | 1004.3 + | -34.84 | 34.84 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 1.2345678901234e-200 (5 rows) -QUERY: SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 +-- truncate +SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 FROM FLOAT8_TBL f; -five|f1 |trunc_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004 - |-34.84 |-34 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|0 + five | f1 | trunc_f1 +------+----------------------+---------------------- + | 0 | 0 + | 1004.3 | 1004 + | -34.84 | -34 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 0 (5 rows) -QUERY: SELECT '' AS five, f.f1, f.f1 % AS round_f1 +-- round +SELECT '' AS five, f.f1, f.f1 % AS round_f1 FROM FLOAT8_TBL f; -five|f1 |round_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004 - |-34.84 |-35 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|0 + five | f1 | round_f1 +------+----------------------+---------------------- + | 0 | 0 + | 1004.3 | 1004 + | -34.84 | -35 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 0 (5 rows) -QUERY: SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 +-- square root +SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |sqrt_f1 ------+--------------------+--------------------- - |1004.3 |31.6906926399535 - |1.2345678901234e+200|1.11111110611109e+100 - |1.2345678901234e-200|1.11111110611109e-100 + three | f1 | sqrt_f1 +-------+----------------------+----------------------- + | 1004.3 | 31.6906926399535 + | 1.2345678901234e+200 | 1.11111110611109e+100 + | 1.2345678901234e-200 | 1.11111110611109e-100 (3 rows) -QUERY: SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 +-- take exp of ln(f.f1) +SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; -three|f1 |exp_ln_f1 ------+--------------------+--------------------- - |1004.3 |1004.3 - |1.2345678901234e+200|1.23456789012338e+200 - |1.2345678901234e-200|1.23456789012339e-200 + three | f1 | exp_ln_f1 +-------+----------------------+----------------------- + | 1004.3 | 1004.3 + | 1.2345678901234e+200 | 1.23456789012338e+200 + | 1.2345678901234e-200 | 1.23456789012339e-200 (3 rows) -QUERY: SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; -five|f1 |cbrt_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |10.014312837827 - |-34.84 |-3.26607421344208 - |1.2345678901234e+200|4.97933859234765e+66 - |1.2345678901234e-200|2.3112042409018e-67 +-- cube root +SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; + five | f1 | cbrt_f1 +------+----------------------+---------------------- + | 0 | 0 + | 1004.3 | 10.014312837827 + | -34.84 | -3.26607421344208 + | 1.2345678901234e+200 | 4.97933859234765e+66 + | 1.2345678901234e-200 | 2.3112042409018e-67 (5 rows) -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+-------------------- - |0 - |1004.3 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 +SELECT '' AS five, FLOAT8_TBL.*; + five | f1 +------+---------------------- + | 0 + | 1004.3 + | -34.84 + | 1.2345678901234e+200 + | 1.2345678901234e-200 (5 rows) -QUERY: UPDATE FLOAT8_TBL +UPDATE FLOAT8_TBL SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 > '0.0'; -QUERY: SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; +SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ERROR: Bad float8 input format -- overflow -QUERY: SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; +SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range -QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; +SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; ERROR: can't take log of zero -QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; +SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; ERROR: can't take log of a negative number -QUERY: SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; +SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; ERROR: exp() result is out of range -QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; +SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; ERROR: float8div: divide by zero error -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+--------------------- - |0 - |-34.84 - |-1004.3 - |-1.2345678901234e+200 - |-1.2345678901234e-200 +SELECT '' AS five, FLOAT8_TBL.*; + five | f1 +------+----------------------- + | 0 + | -34.84 + | -1004.3 + | -1.2345678901234e+200 + | -1.2345678901234e-200 (5 rows) -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); +-- test for over and under flow +INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); ERROR: Input '10e400' is out of range for float8 -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); ERROR: Input '-10e400' is out of range for float8 -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); ERROR: Input '10e-400' is out of range for float8 -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); ERROR: Input '-10e-400' is out of range for float8 -QUERY: DELETE FROM FLOAT8_TBL; -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+--------------------- - |0 - |-34.84 - |-1004.3 - |-1.2345678901234e+200 - |-1.2345678901234e-200 +-- maintain external table consistency across platforms +-- delete all values and reinsert well-behaved ones +DELETE FROM FLOAT8_TBL; +INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); +SELECT '' AS five, FLOAT8_TBL.*; + five | f1 +------+----------------------- + | 0 + | -34.84 + | -1004.3 + | -1.2345678901234e+200 + | -1.2345678901234e-200 (5 rows) diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index e95f234637..ec171f4821 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -1,206 +1,215 @@ -QUERY: CREATE TABLE INT2_TBL(f1 int2); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('0'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('1234'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('-1234'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('34.5'); +-- +-- INT2 +-- NOTE: int2 operators never check for over/underflow! +-- Some of these answers are consequently numerically incorrect. +-- +CREATE TABLE INT2_TBL(f1 int2); +INSERT INTO INT2_TBL(f1) VALUES ('0'); +INSERT INTO INT2_TBL(f1) VALUES ('1234'); +INSERT INTO INT2_TBL(f1) VALUES ('-1234'); +INSERT INTO INT2_TBL(f1) VALUES ('34.5'); ERROR: pg_atoi: error in "34.5": can't parse ".5" -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('32767'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('-32767'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('100000'); +-- largest and smallest values +INSERT INTO INT2_TBL(f1) VALUES ('32767'); +INSERT INTO INT2_TBL(f1) VALUES ('-32767'); +-- bad input values -- should give warnings +INSERT INTO INT2_TBL(f1) VALUES ('100000'); ERROR: pg_atoi: error reading "100000": Numerical result out of range -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('asdf'); +INSERT INTO INT2_TBL(f1) VALUES ('asdf'); ERROR: pg_atoi: error in "asdf": can't parse "asdf" -QUERY: SELECT '' AS five, INT2_TBL.*; -five| f1 -----+------ - | 0 - | 1234 - | -1234 - | 32767 - |-32767 +SELECT '' AS five, INT2_TBL.*; + five | f1 +------+-------- + | 0 + | 1234 + | -1234 + | 32767 + | -32767 (5 rows) -QUERY: SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int2; -four| f1 -----+------ - | 1234 - | -1234 - | 32767 - |-32767 +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0'; + four | f1 +------+-------- + | 1234 + | -1234 + | 32767 + | -32767 (4 rows) -QUERY: SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int4; -four| f1 -----+------ - | 1234 - | -1234 - | 32767 - |-32767 +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0'; + four | f1 +------+-------- + | 1234 + | -1234 + | 32767 + | -32767 (4 rows) -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int2; -one|f1 ----+-- - | 0 +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0'; + one | f1 +-----+---- + | 0 (1 row) -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int4; -one|f1 ----+-- - | 0 +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0'; + one | f1 +-----+---- + | 0 (1 row) -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int2; -two| f1 ----+------ - | -1234 - |-32767 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0'; + two | f1 +-----+-------- + | -1234 + | -32767 (2 rows) -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int4; -two| f1 ----+------ - | -1234 - |-32767 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0'; + two | f1 +-----+-------- + | -1234 + | -32767 (2 rows) -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int2; -three| f1 ------+------ - | 0 - | -1234 - |-32767 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0'; + three | f1 +-------+-------- + | 0 + | -1234 + | -32767 (3 rows) -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int4; -three| f1 ------+------ - | 0 - | -1234 - |-32767 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0'; + three | f1 +-------+-------- + | 0 + | -1234 + | -32767 (3 rows) -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int2; -two| f1 ----+----- - | 1234 - |32767 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0'; + two | f1 +-----+------- + | 1234 + | 32767 (2 rows) -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int4; -two| f1 ----+----- - | 1234 - |32767 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0'; + two | f1 +-----+------- + | 1234 + | 32767 (2 rows) -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int2; -three| f1 ------+----- - | 0 - | 1234 - |32767 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0'; + three | f1 +-------+------- + | 0 + | 1234 + | 32767 (3 rows) -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int4; -three| f1 ------+----- - | 0 - | 1234 - |32767 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0'; + three | f1 +-------+------- + | 0 + | 1234 + | 32767 (3 rows) -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; -one| f1 ----+----- - |32767 +-- positive odds +SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; + one | f1 +-----+------- + | 32767 (1 row) -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; -three| f1 ------+----- - | 0 - | 1234 - |-1234 +-- any evens +SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; + three | f1 +-------+------- + | 0 + | 1234 + | -1234 (3 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+----- - | 0| 0 - | 1234| 2468 - | -1234|-2468 - | 32767| -2 - |-32767| 2 +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+------- + | 0 | 0 + | 1234 | 2468 + | -1234 | -2468 + | 32767 | -2 + | -32767 | 2 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 2468 - | -1234| -2468 - | 32767| 65534 - |-32767|-65534 +SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | 0 + | 1234 | 2468 + | -1234 | -2468 + | 32767 | 65534 + | -32767 | -65534 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 2 - | 1234| 1236 - | -1234| -1232 - | 32767|-32767 - |-32767|-32765 +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | 2 + | 1234 | 1236 + | -1234 | -1232 + | 32767 | -32767 + | -32767 | -32765 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 2 - | 1234| 1236 - | -1234| -1232 - | 32767| 32769 - |-32767|-32765 +SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | 2 + | 1234 | 1236 + | -1234 | -1232 + | 32767 | 32769 + | -32767 | -32765 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+----- - | 0| -2 - | 1234| 1232 - | -1234|-1236 - | 32767|32765 - |-32767|32767 +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+------- + | 0 | -2 + | 1234 | 1232 + | -1234 | -1236 + | 32767 | 32765 + | -32767 | 32767 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| -2 - | 1234| 1232 - | -1234| -1236 - | 32767| 32765 - |-32767|-32769 +SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | -2 + | 1234 | 1232 + | -1234 | -1236 + | 32767 | 32765 + | -32767 | -32769 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 617 - | -1234| -617 - | 32767| 16383 - |-32767|-16383 +SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | 0 + | 1234 | 617 + | -1234 | -617 + | 32767 | 16383 + | -32767 | -16383 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 617 - | -1234| -617 - | 32767| 16383 - |-32767|-16383 +SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+-------- + | 0 | 0 + | 1234 | 617 + | -1234 | -617 + | 32767 | 16383 + | -32767 | -16383 (5 rows) diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index a9312ca582..9a0205b540 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -1,302 +1,315 @@ -QUERY: CREATE TABLE INT4_TBL(f1 int4); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('0'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('123456'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('-123456'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('34.5'); +-- +-- INT4 +-- WARNING: int4 operators never check for over/underflow! +-- Some of these answers are consequently numerically incorrect. +-- +CREATE TABLE INT4_TBL(f1 int4); +INSERT INTO INT4_TBL(f1) VALUES ('0'); +INSERT INTO INT4_TBL(f1) VALUES ('123456'); +INSERT INTO INT4_TBL(f1) VALUES ('-123456'); +INSERT INTO INT4_TBL(f1) VALUES ('34.5'); ERROR: pg_atoi: error in "34.5": can't parse ".5" -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); +-- largest and smallest values +INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); +INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); +-- bad input values -- should give warnings +INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); ERROR: pg_atoi: error reading "1000000000000": Numerical result out of range -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('asdf'); +INSERT INTO INT4_TBL(f1) VALUES ('asdf'); ERROR: pg_atoi: error in "asdf": can't parse "asdf" -QUERY: SELECT '' AS five, INT4_TBL.*; -five| f1 -----+----------- - | 0 - | 123456 - | -123456 - | 2147483647 - |-2147483647 +SELECT '' AS five, INT4_TBL.*; + five | f1 +------+------------- + | 0 + | 123456 + | -123456 + | 2147483647 + | -2147483647 (5 rows) -QUERY: SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int2; -four| f1 -----+----------- - | 123456 - | -123456 - | 2147483647 - |-2147483647 +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0'; + four | f1 +------+------------- + | 123456 + | -123456 + | 2147483647 + | -2147483647 (4 rows) -QUERY: SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int4; -four| f1 -----+----------- - | 123456 - | -123456 - | 2147483647 - |-2147483647 +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0'; + four | f1 +------+------------- + | 123456 + | -123456 + | 2147483647 + | -2147483647 (4 rows) -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int2; -one|f1 ----+-- - | 0 +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0'; + one | f1 +-----+---- + | 0 (1 row) -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int4; -one|f1 ----+-- - | 0 +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0'; + one | f1 +-----+---- + | 0 (1 row) -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int2; -two| f1 ----+----------- - | -123456 - |-2147483647 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0'; + two | f1 +-----+------------- + | -123456 + | -2147483647 (2 rows) -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int4; -two| f1 ----+----------- - | -123456 - |-2147483647 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0'; + two | f1 +-----+------------- + | -123456 + | -2147483647 (2 rows) -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int2; -three| f1 ------+----------- - | 0 - | -123456 - |-2147483647 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0'; + three | f1 +-------+------------- + | 0 + | -123456 + | -2147483647 (3 rows) -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int4; -three| f1 ------+----------- - | 0 - | -123456 - |-2147483647 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0'; + three | f1 +-------+------------- + | 0 + | -123456 + | -2147483647 (3 rows) -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int2; -two| f1 ----+---------- - | 123456 - |2147483647 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0'; + two | f1 +-----+------------ + | 123456 + | 2147483647 (2 rows) -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int4; -two| f1 ----+---------- - | 123456 - |2147483647 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0'; + two | f1 +-----+------------ + | 123456 + | 2147483647 (2 rows) -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int2; -three| f1 ------+---------- - | 0 - | 123456 - |2147483647 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0'; + three | f1 +-------+------------ + | 0 + | 123456 + | 2147483647 (3 rows) -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int4; -three| f1 ------+---------- - | 0 - | 123456 - |2147483647 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0'; + three | f1 +-------+------------ + | 0 + | 123456 + | 2147483647 (3 rows) -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; -one| f1 ----+---------- - |2147483647 +-- positive odds +SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; + one | f1 +-----+------------ + | 2147483647 (1 row) -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; -three| f1 ------+------- - | 0 - | 123456 - |-123456 +-- any evens +SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; + three | f1 +-------+--------- + | 0 + | 123456 + | -123456 (3 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+------- - | 0| 0 - | 123456| 246912 - | -123456|-246912 - | 2147483647| -2 - |-2147483647| 2 +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+--------- + | 0 | 0 + | 123456 | 246912 + | -123456 | -246912 + | 2147483647 | -2 + | -2147483647 | 2 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+------- - | 0| 0 - | 123456| 246912 - | -123456|-246912 - | 2147483647| -2 - |-2147483647| 2 +SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+--------- + | 0 | 0 + | 123456 | 246912 + | -123456 | -246912 + | 2147483647 | -2 + | -2147483647 | 2 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 2 - | 123456| 123458 - | -123456| -123454 - | 2147483647|-2147483647 - |-2147483647|-2147483645 +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------- + | 0 | 2 + | 123456 | 123458 + | -123456 | -123454 + | 2147483647 | -2147483647 + | -2147483647 | -2147483645 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 2 - | 123456| 123458 - | -123456| -123454 - | 2147483647|-2147483647 - |-2147483647|-2147483645 +SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------- + | 0 | 2 + | 123456 | 123458 + | -123456 | -123454 + | 2147483647 | -2147483647 + | -2147483647 | -2147483645 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+---------- - | 0| -2 - | 123456| 123454 - | -123456| -123458 - | 2147483647|2147483645 - |-2147483647|2147483647 +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------ + | 0 | -2 + | 123456 | 123454 + | -123456 | -123458 + | 2147483647 | 2147483645 + | -2147483647 | 2147483647 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+---------- - | 0| -2 - | 123456| 123454 - | -123456| -123458 - | 2147483647|2147483645 - |-2147483647|2147483647 +SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------ + | 0 | -2 + | 123456 | 123454 + | -123456 | -123458 + | 2147483647 | 2147483645 + | -2147483647 | 2147483647 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 0 - | 123456| 61728 - | -123456| -61728 - | 2147483647| 1073741823 - |-2147483647|-1073741823 +SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------- + | 0 | 0 + | 123456 | 61728 + | -123456 | -61728 + | 2147483647 | 1073741823 + | -2147483647 | -1073741823 (5 rows) -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 0 - | 123456| 61728 - | -123456| -61728 - | 2147483647| 1073741823 - |-2147483647|-1073741823 +SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------- + | 0 | 0 + | 123456 | 61728 + | -123456 | -61728 + | 2147483647 | 1073741823 + | -2147483647 | -1073741823 (5 rows) -QUERY: SELECT -2+3 AS one; -one ---- - 1 -(1 row) - -QUERY: SELECT 4-2 AS two; -two ---- - 2 +-- +-- more complex expressions +-- +-- variations on unary minus parsing +SELECT -2+3 AS one; + one +----- + 1 (1 row) -QUERY: SELECT 2- -1 AS three; -three +SELECT 4-2 AS two; + two ----- - 3 + 2 (1 row) -QUERY: SELECT 2 - -2 AS four; -four ----- - 4 +SELECT 2- -1 AS three; + three +------- + 3 (1 row) -QUERY: SELECT '2'::int2 * '2'::int2 = '16'::int2 / '4'::int2 AS true; -true ----- -t +SELECT 2 - -2 AS four; + four +------ + 4 (1 row) -QUERY: SELECT '2'::int4 * '2'::int2 = '16'::int2 / '4'::int4 AS true; -true ----- -t +SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true; + true +------ + t (1 row) -QUERY: SELECT '2'::int2 * '2'::int4 = '16'::int4 / '4'::int2 AS true; -true ----- -t +SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true; + true +------ + t (1 row) -QUERY: SELECT '1000'::int4 < '999'::int4 AS false; -false ------ -f +SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true; + true +------ + t (1 row) -QUERY: SELECT 4! AS twenty_four; -twenty_four ------------ - 24 +SELECT int4 '1000' < int4 '999' AS false; + false +------- + f (1 row) -QUERY: SELECT !!3 AS six; -six ---- - 6 +SELECT 4! AS twenty_four; + twenty_four +------------- + 24 (1 row) -QUERY: SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; -ten ---- - 10 +SELECT !!3 AS six; + six +----- + 6 (1 row) -QUERY: SELECT 2 + 2 / 2 AS three; -three +SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; + ten ----- - 3 + 10 (1 row) -QUERY: SELECT (2 + 2) / 2 AS two; -two ---- - 2 +SELECT 2 + 2 / 2 AS three; + three +------- + 3 (1 row) -QUERY: SELECT dsqrt('64'::float8) AS eight; -eight +SELECT (2 + 2) / 2 AS two; + two ----- - 8 + 2 (1 row) -QUERY: SELECT |/'64'::float8 AS eight; -eight ------ - 8 +SELECT dsqrt(float8 '64') AS eight; + eight +------- + 8 (1 row) -QUERY: SELECT ||/'27'::float8 AS three; -three ------ - 3 +SELECT |/float8 '64' AS eight; + eight +------- + 8 +(1 row) + +SELECT ||/float8 '27' AS three; + three +------- + 3 (1 row) diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index b9d4e4e094..24c58f01d7 100644 --- a/src/test/regress/expected/int8.out +++ b/src/test/regress/expected/int8.out @@ -1,115 +1,119 @@ -QUERY: CREATE TABLE INT8_TBL(q1 int8, q2 int8); -QUERY: INSERT INTO INT8_TBL VALUES('123','456'); -QUERY: INSERT INTO INT8_TBL VALUES('123','4567890123456789'); -QUERY: INSERT INTO INT8_TBL VALUES('4567890123456789','123'); -QUERY: INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789'); -QUERY: INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789'); -QUERY: SELECT * FROM INT8_TBL; - q1| q2 -----------------+----------------- - 123| 456 - 123| 4567890123456789 -4567890123456789| 123 -4567890123456789| 4567890123456789 -4567890123456789|-4567890123456789 +-- +-- INT8 +-- Test int8 64-bit integers. +-- +CREATE TABLE INT8_TBL(q1 int8, q2 int8); +INSERT INTO INT8_TBL VALUES('123','456'); +INSERT INTO INT8_TBL VALUES('123','4567890123456789'); +INSERT INTO INT8_TBL VALUES('4567890123456789','123'); +INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789'); +INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789'); +SELECT * FROM INT8_TBL; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 (5 rows) -QUERY: SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL; -five| plus| minus -----+----------------+----------------- - | 123| -123 - | 123| -123 - |4567890123456789|-4567890123456789 - |4567890123456789|-4567890123456789 - |4567890123456789|-4567890123456789 +SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL; + five | plus | minus +------+------------------+------------------- + | 123 | -123 + | 123 | -123 + | 4567890123456789 | -4567890123456789 + | 4567890123456789 | -4567890123456789 + | 4567890123456789 | -4567890123456789 (5 rows) -QUERY: SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL; -five| q1| q2| plus -----+----------------+-----------------+---------------- - | 123| 456| 579 - | 123| 4567890123456789|4567890123456912 - |4567890123456789| 123|4567890123456912 - |4567890123456789| 4567890123456789|9135780246913578 - |4567890123456789|-4567890123456789| 0 +SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL; + five | q1 | q2 | plus +------+------------------+-------------------+------------------ + | 123 | 456 | 579 + | 123 | 4567890123456789 | 4567890123456912 + | 4567890123456789 | 123 | 4567890123456912 + | 4567890123456789 | 4567890123456789 | 9135780246913578 + | 4567890123456789 | -4567890123456789 | 0 (5 rows) -QUERY: SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL; -five| q1| q2| minus -----+----------------+-----------------+----------------- - | 123| 456| -333 - | 123| 4567890123456789|-4567890123456666 - |4567890123456789| 123| 4567890123456666 - |4567890123456789| 4567890123456789| 0 - |4567890123456789|-4567890123456789| 9135780246913578 +SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL; + five | q1 | q2 | minus +------+------------------+-------------------+------------------- + | 123 | 456 | -333 + | 123 | 4567890123456789 | -4567890123456666 + | 4567890123456789 | 123 | 4567890123456666 + | 4567890123456789 | 4567890123456789 | 0 + | 4567890123456789 | -4567890123456789 | 9135780246913578 (5 rows) -QUERY: SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL +SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL WHERE q1 < 1000 or (q2 > 0 and q2 < 1000); -three| q1| q2| multiply ------+----------------+----------------+------------------ - | 123| 456| 56088 - | 123|4567890123456789|561850485185185047 - |4567890123456789| 123|561850485185185047 + three | q1 | q2 | multiply +-------+------------------+------------------+-------------------- + | 123 | 456 | 56088 + | 123 | 4567890123456789 | 561850485185185047 + | 4567890123456789 | 123 | 561850485185185047 (3 rows) -QUERY: SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL; -five| q1| q2| divide -----+----------------+-----------------+-------------- - | 123| 456| 0 - | 123| 4567890123456789| 0 - |4567890123456789| 123|37137318076884 - |4567890123456789| 4567890123456789| 1 - |4567890123456789|-4567890123456789| -1 +SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL; + five | q1 | q2 | divide +------+------------------+-------------------+---------------- + | 123 | 456 | 0 + | 123 | 4567890123456789 | 0 + | 4567890123456789 | 123 | 37137318076884 + | 4567890123456789 | 4567890123456789 | 1 + | 4567890123456789 | -4567890123456789 | -1 (5 rows) -QUERY: SELECT '' AS five, q1, float8(q1) FROM INT8_TBL; -five| q1|float8 -----+----------------+-------------------- - | 123|123 - | 123|123 - |4567890123456789|4.56789012345679e+15 - |4567890123456789|4.56789012345679e+15 - |4567890123456789|4.56789012345679e+15 +SELECT '' AS five, q1, float8(q1) FROM INT8_TBL; + five | q1 | float8 +------+------------------+---------------------- + | 123 | 123 + | 123 | 123 + | 4567890123456789 | 4.56789012345679e+15 + | 4567890123456789 | 4.56789012345679e+15 + | 4567890123456789 | 4.56789012345679e+15 (5 rows) -QUERY: SELECT '' AS five, q2, float8(q2) FROM INT8_TBL; -five| q2|float8 -----+-----------------+--------------------- - | 456|456 - | 4567890123456789|4.56789012345679e+15 - | 123|123 - | 4567890123456789|4.56789012345679e+15 - |-4567890123456789|-4.56789012345679e+15 +SELECT '' AS five, q2, float8(q2) FROM INT8_TBL; + five | q2 | float8 +------+-------------------+----------------------- + | 456 | 456 + | 4567890123456789 | 4.56789012345679e+15 + | 123 | 123 + | 4567890123456789 | 4.56789012345679e+15 + | -4567890123456789 | -4.56789012345679e+15 (5 rows) -QUERY: SELECT '' AS five, q1, int8(float8(q1)) AS "two coercions" FROM INT8_TBL; -five| q1| two coercions -----+----------------+---------------- - | 123| 123 - | 123| 123 - |4567890123456789|4567890123456789 - |4567890123456789|4567890123456789 - |4567890123456789|4567890123456789 +SELECT '' AS five, q1, int8(float8(q1)) AS "two coercions" FROM INT8_TBL; + five | q1 | two coercions +------+------------------+------------------ + | 123 | 123 + | 123 | 123 + | 4567890123456789 | 4567890123456789 + | 4567890123456789 | 4567890123456789 + | 4567890123456789 | 4567890123456789 (5 rows) -QUERY: SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL; -five| twice int4 -----+---------------- - | 246 - | 246 - |9135780246913578 - |9135780246913578 - |9135780246913578 +SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL; + five | twice int4 +------+------------------ + | 246 + | 246 + | 9135780246913578 + | 9135780246913578 + | 9135780246913578 (5 rows) -QUERY: SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL; -five| twice int4 -----+---------------- - | 246 - | 246 - |9135780246913578 - |9135780246913578 - |9135780246913578 +SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL; + five | twice int4 +------+------------------ + | 246 + | 246 + | 9135780246913578 + | 9135780246913578 + | 9135780246913578 (5 rows) diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out index 9d3cdd4658..ea9a6e6f64 100644 --- a/src/test/regress/expected/name.out +++ b/src/test/regress/expected/name.out @@ -1,116 +1,124 @@ -QUERY: SELECT 'name string'::name = 'name string'::name AS "True"; -True ----- -t +-- +-- NAME +-- all inputs are silently truncated at NAMEDATALEN (32) characters +-- +-- fixed-length by reference +SELECT name 'name string' = name 'name string' AS "True"; + True +------ + t (1 row) -QUERY: SELECT 'name string'::name = 'name string '::name AS "False"; -False ------ -f +SELECT name 'name string' = name 'name string ' AS "False"; + False +------- + f (1 row) -QUERY: CREATE TABLE NAME_TBL(f1 name); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('ABCDEFGHIJKLMNOP'); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('abcdefghijklmnop'); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;'); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('343f%2a'); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf'); -QUERY: INSERT INTO NAME_TBL(f1) VALUES (''); -QUERY: INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'); -QUERY: SELECT '' AS seven, NAME_TBL.*; -seven|f1 ------+------------------------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEFGHIJKLMNOPQRSTU +-- +-- +-- +CREATE TABLE NAME_TBL(f1 name); +INSERT INTO NAME_TBL(f1) VALUES ('ABCDEFGHIJKLMNOP'); +INSERT INTO NAME_TBL(f1) VALUES ('abcdefghijklmnop'); +INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;'); +INSERT INTO NAME_TBL(f1) VALUES ('343f%2a'); +INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf'); +INSERT INTO NAME_TBL(f1) VALUES (''); +INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'); +SELECT '' AS seven, NAME_TBL.*; + seven | f1 +-------+--------------------------------- + | ABCDEFGHIJKLMNOP + | abcdefghijklmnop + | asdfghjkl; + | 343f%2a + | d34aaasdf + | + | 1234567890ABCDEFGHIJKLMNOPQRSTU (7 rows) -QUERY: SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> 'ABCDEFGHIJKLMNOP'; -six|f1 ----+------------------------------- - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEFGHIJKLMNOPQRSTU +SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> 'ABCDEFGHIJKLMNOP'; + six | f1 +-----+--------------------------------- + | abcdefghijklmnop + | asdfghjkl; + | 343f%2a + | d34aaasdf + | + | 1234567890ABCDEFGHIJKLMNOPQRSTU (6 rows) -QUERY: SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = 'ABCDEFGHIJKLMNOP'; -one|f1 ----+---------------- - |ABCDEFGHIJKLMNOP +SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = 'ABCDEFGHIJKLMNOP'; + one | f1 +-----+------------------ + | ABCDEFGHIJKLMNOP (1 row) -QUERY: SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < 'ABCDEFGHIJKLMNOP'; -three|f1 ------+------------------------------- - |343f%2a - | - |1234567890ABCDEFGHIJKLMNOPQRSTU +SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < 'ABCDEFGHIJKLMNOP'; + three | f1 +-------+--------------------------------- + | 343f%2a + | + | 1234567890ABCDEFGHIJKLMNOPQRSTU (3 rows) -QUERY: SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= 'ABCDEFGHIJKLMNOP'; -four|f1 -----+------------------------------- - |ABCDEFGHIJKLMNOP - |343f%2a - | - |1234567890ABCDEFGHIJKLMNOPQRSTU +SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= 'ABCDEFGHIJKLMNOP'; + four | f1 +------+--------------------------------- + | ABCDEFGHIJKLMNOP + | 343f%2a + | + | 1234567890ABCDEFGHIJKLMNOPQRSTU (4 rows) -QUERY: SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > 'ABCDEFGHIJKLMNOP'; -three|f1 ------+---------------- - |abcdefghijklmnop - |asdfghjkl; - |d34aaasdf +SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > 'ABCDEFGHIJKLMNOP'; + three | f1 +-------+------------------ + | abcdefghijklmnop + | asdfghjkl; + | d34aaasdf (3 rows) -QUERY: SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= 'ABCDEFGHIJKLMNOP'; -four|f1 -----+---------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |d34aaasdf +SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= 'ABCDEFGHIJKLMNOP'; + four | f1 +------+------------------ + | ABCDEFGHIJKLMNOP + | abcdefghijklmnop + | asdfghjkl; + | d34aaasdf (4 rows) -QUERY: SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*'; -seven|f1 ------+------------------------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEFGHIJKLMNOPQRSTU +SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*'; + seven | f1 +-------+--------------------------------- + | ABCDEFGHIJKLMNOP + | abcdefghijklmnop + | asdfghjkl; + | 343f%2a + | d34aaasdf + | + | 1234567890ABCDEFGHIJKLMNOPQRSTU (7 rows) -QUERY: SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*'; -zero|f1 -----+-- +SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*'; + zero | f1 +------+---- (0 rows) -QUERY: SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]'; -three|f1 ------+------------------------------- - |343f%2a - |d34aaasdf - |1234567890ABCDEFGHIJKLMNOPQRSTU +SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]'; + three | f1 +-------+--------------------------------- + | 343f%2a + | d34aaasdf + | 1234567890ABCDEFGHIJKLMNOPQRSTU (3 rows) -QUERY: SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*'; -two|f1 ----+---------- - |asdfghjkl; - |d34aaasdf +SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*'; + two | f1 +-----+------------ + | asdfghjkl; + | d34aaasdf (2 rows) -QUERY: DROP TABLE NAME_TBL; +DROP TABLE NAME_TBL; diff --git a/src/test/regress/expected/oid.out b/src/test/regress/expected/oid.out index 0aa6675e21..11a53a5d24 100644 --- a/src/test/regress/expected/oid.out +++ b/src/test/regress/expected/oid.out @@ -1,64 +1,68 @@ -QUERY: CREATE TABLE OID_TBL(f1 oid); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('1234'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('1235'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('987'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('-1040'); -QUERY: INSERT INTO OID_TBL(f1) VALUES (''); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('asdfasd'); +-- +-- OID +-- +CREATE TABLE OID_TBL(f1 oid); +INSERT INTO OID_TBL(f1) VALUES ('1234'); +INSERT INTO OID_TBL(f1) VALUES ('1235'); +INSERT INTO OID_TBL(f1) VALUES ('987'); +INSERT INTO OID_TBL(f1) VALUES ('-1040'); +INSERT INTO OID_TBL(f1) VALUES (''); +-- bad inputs +INSERT INTO OID_TBL(f1) VALUES ('asdfasd'); ERROR: pg_atoi: error in "asdfasd": can't parse "asdfasd" -QUERY: SELECT '' AS five, OID_TBL.*; -five| f1 -----+----- - | 1234 - | 1235 - | 987 - |-1040 - | 0 +SELECT '' AS five, OID_TBL.*; + five | f1 +------+------- + | 1234 + | 1235 + | 987 + | -1040 + | 0 (5 rows) -QUERY: SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = '1234'::oid; -one| f1 ----+---- - |1234 +SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = oid '1234'; + one | f1 +-----+------ + | 1234 (1 row) -QUERY: SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <> '1234'; -four| f1 -----+----- - | 1235 - | 987 - |-1040 - | 0 +SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <> '1234'; + four | f1 +------+------- + | 1235 + | 987 + | -1040 + | 0 (4 rows) -QUERY: SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <= '1234'; -four| f1 -----+----- - | 1234 - | 987 - |-1040 - | 0 +SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <= '1234'; + four | f1 +------+------- + | 1234 + | 987 + | -1040 + | 0 (4 rows) -QUERY: SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 < '1234'; -three| f1 ------+----- - | 987 - |-1040 - | 0 +SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 < '1234'; + three | f1 +-------+------- + | 987 + | -1040 + | 0 (3 rows) -QUERY: SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 >= '1234'; -two| f1 ----+---- - |1234 - |1235 +SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 >= '1234'; + two | f1 +-----+------ + | 1234 + | 1235 (2 rows) -QUERY: SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 > '1234'; -one| f1 ----+---- - |1235 +SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 > '1234'; + one | f1 +-----+------ + | 1235 (1 row) -QUERY: DROP TABLE OID_TBL; +DROP TABLE OID_TBL; diff --git a/src/test/regress/expected/text.out b/src/test/regress/expected/text.out index ca99f8f7c6..2d732f6844 100644 --- a/src/test/regress/expected/text.out +++ b/src/test/regress/expected/text.out @@ -1,22 +1,25 @@ -QUERY: SELECT 'this is a text string'::text = 'this is a text string'::text AS true; -true ----- -t +-- +-- TEXT +-- +SELECT text 'this is a text string' = text 'this is a text string' AS true; + true +------ + t (1 row) -QUERY: SELECT 'this is a text string'::text = 'this is a text strin'::text AS false; -false ------ -f +SELECT text 'this is a text string' = text 'this is a text strin' AS false; + false +------- + f (1 row) -QUERY: CREATE TABLE TEXT_TBL (f1 text); -QUERY: INSERT INTO TEXT_TBL VALUES ('doh!'); -QUERY: INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor'); -QUERY: SELECT '' AS two, * FROM TEXT_TBL; -two|f1 ----+----------------- - |doh! - |hi de ho neighbor +CREATE TABLE TEXT_TBL (f1 text); +INSERT INTO TEXT_TBL VALUES ('doh!'); +INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor'); +SELECT '' AS two, * FROM TEXT_TBL; + two | f1 +-----+------------------- + | doh! + | hi de ho neighbor (2 rows) diff --git a/src/test/regress/expected/varchar.out b/src/test/regress/expected/varchar.out index 6bfa1a9317..5f4c0f1f56 100644 --- a/src/test/regress/expected/varchar.out +++ b/src/test/regress/expected/varchar.out @@ -1,98 +1,107 @@ -QUERY: CREATE TABLE VARCHAR_TBL(f1 varchar(1)); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('A'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('1'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES (2); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('3'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES (''); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('cd'); -QUERY: SELECT '' AS seven, VARCHAR_TBL.*; -seven|f1 ------+-- - |a - |A - |1 - |2 - |3 - | - |c +-- +-- VARCHAR +-- +CREATE TABLE VARCHAR_TBL(f1 varchar(1)); +INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); +INSERT INTO VARCHAR_TBL (f1) VALUES ('A'); +-- any of the following three input formats are acceptable +INSERT INTO VARCHAR_TBL (f1) VALUES ('1'); +INSERT INTO VARCHAR_TBL (f1) VALUES (2); +INSERT INTO VARCHAR_TBL (f1) VALUES ('3'); +-- zero-length char +INSERT INTO VARCHAR_TBL (f1) VALUES (''); +-- try varchar's of greater than 1 length +INSERT INTO VARCHAR_TBL (f1) VALUES ('cd'); +SELECT '' AS seven, VARCHAR_TBL.*; + seven | f1 +-------+---- + | a + | A + | 1 + | 2 + | 3 + | + | c (7 rows) -QUERY: SELECT '' AS six, c.* +SELECT '' AS six, c.* FROM VARCHAR_TBL c WHERE c.f1 <> 'a'; -six|f1 ----+-- - |A - |1 - |2 - |3 - | - |c + six | f1 +-----+---- + | A + | 1 + | 2 + | 3 + | + | c (6 rows) -QUERY: SELECT '' AS one, c.* +SELECT '' AS one, c.* FROM VARCHAR_TBL c WHERE c.f1 = 'a'; -one|f1 ----+-- - |a + one | f1 +-----+---- + | a (1 row) -QUERY: SELECT '' AS five, c.* +SELECT '' AS five, c.* FROM VARCHAR_TBL c WHERE c.f1 < 'a'; -five|f1 -----+-- - |A - |1 - |2 - |3 - | + five | f1 +------+---- + | A + | 1 + | 2 + | 3 + | (5 rows) -QUERY: SELECT '' AS six, c.* +SELECT '' AS six, c.* FROM VARCHAR_TBL c WHERE c.f1 <= 'a'; -six|f1 ----+-- - |a - |A - |1 - |2 - |3 - | + six | f1 +-----+---- + | a + | A + | 1 + | 2 + | 3 + | (6 rows) -QUERY: SELECT '' AS one, c.* +SELECT '' AS one, c.* FROM VARCHAR_TBL c WHERE c.f1 > 'a'; -one|f1 ----+-- - |c + one | f1 +-----+---- + | c (1 row) -QUERY: SELECT '' AS two, c.* +SELECT '' AS two, c.* FROM VARCHAR_TBL c WHERE c.f1 >= 'a'; -two|f1 ----+-- - |a - |c + two | f1 +-----+---- + | a + | c (2 rows) -QUERY: DROP TABLE VARCHAR_TBL; -QUERY: CREATE TABLE VARCHAR_TBL(f1 varchar(4)); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('ab'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd'); -QUERY: INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde'); -QUERY: SELECT '' AS four, VARCHAR_TBL.*; -four|f1 -----+---- - |a - |ab - |abcd - |abcd +DROP TABLE VARCHAR_TBL; +-- +-- Now test longer arrays of char +-- +CREATE TABLE VARCHAR_TBL(f1 varchar(4)); +INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); +INSERT INTO VARCHAR_TBL (f1) VALUES ('ab'); +INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd'); +INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde'); +SELECT '' AS four, VARCHAR_TBL.*; + four | f1 +------+------ + | a + | ab + | abcd + | abcd (4 rows) -- 2.11.0