Hello list,
I am trying to do QNX6 (aka QNX/Neutrino aka QNX RTP) port. It builds
(7.2b2) and mostly works already, but some regression tests fail.
There's some usual stuff like discrepancies in float formatting and
timezones which I can deal with, but some failures are rather deep.
In particular:
1. some numeric tests fail with:
ERROR: Can not create unique index. Table contains non-unique values.
2. one of the oidjoins tests gives unexpected row(s) (sometimes 1,
sometimes 2), when expected 0.
3. all create view tests fail with:
ERROR: Cannot insert a duplicate key into unique index
pg-rewrite_rulename_index
4. in some of privileges tests select produces 0 rows when it expects 1.
The released 7.1.3 has same problems, plus it exhibits signs of memory
corruptions (at least on QNX6), so 7.2b2 is really much better.
The compiler is GCC 2.95.2, C library is Dinkum (claims to be 100% ANSI
compliant). CPU is Pentium, linker is GNU LD (this is ELF platform) so
shared libs are supported and there are no issues related to that.
There's no modifications in code aside from addition of SYS5 sem/shm
emulation stuff, like for QNX4. Locking code is standard i386 assembly,
I also tried mutex-based locking and it works same way.
I need help from someone to understand reasons for those failures. I'm
pretty knowleageable about the platform.
Attached are results of above mentioned tests.
Thanks,
- Igor--
-- Test access privileges
--
CREATE USER regressuser1;
CREATE USER regressuser2;
CREATE USER regressuser3;
CREATE USER regressuser4;
CREATE USER regressuser4; -- duplicate
ERROR: CREATE USER: user name "regressuser4" already exists
CREATE GROUP regressgroup1;
CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
ALTER GROUP regressgroup1 ADD USER regressuser4;
ALTER GROUP regressgroup2 ADD USER regressuser2; -- duplicate
NOTICE: ALTER GROUP: user "regressuser2" is already in group "regressgroup2"
ALTER GROUP regressgroup2 DROP USER regressuser2;
ALTER GROUP regressgroup2 ADD USER regressuser4;
-- test owner privileges
SET SESSION AUTHORIZATION regressuser1;
SELECT session_user, current_user;
session_user | current_user
--------------+--------------
regressuser1 | regressuser1
(1 row)
CREATE TABLE atest1 ( a int, b text );
SELECT * FROM atest1;
a | b
---+---
(0 rows)
INSERT INTO atest1 VALUES (1, 'one');
DELETE FROM atest1;
UPDATE atest1 SET a = 1 WHERE b = 'blech';
LOCK atest1 IN ACCESS EXCLUSIVE MODE;
REVOKE ALL ON atest1 FROM PUBLIC;
SELECT * FROM atest1;
a | b
---+---
(0 rows)
GRANT ALL ON atest1 TO regressuser2;
GRANT SELECT ON atest1 TO regressuser3, regressuser4;
SELECT * FROM atest1;
a | b
---+---
(0 rows)
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regressuser2;
GRANT UPDATE ON atest2 TO regressuser3;
GRANT INSERT ON atest2 TO regressuser4;
SET SESSION AUTHORIZATION regressuser2;
SELECT session_user, current_user;
session_user | current_user
--------------+--------------
regressuser2 | regressuser2
(1 row)
-- try various combinations of queries on atest1 and atest2
SELECT * FROM atest1; -- ok
a | b
---+---
(0 rows)
SELECT * FROM atest2; -- ok
col1 | col2
------+------
(0 rows)
INSERT INTO atest1 VALUES (2, 'two'); -- ok
INSERT INTO atest2 VALUES ('foo', true); -- fail
ERROR: atest2: Permission denied.
INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fail
ERROR: atest2: Permission denied.
SELECT * FROM atest1 FOR UPDATE; -- ok
a | b
---+-----
1 | two
1 | two
(2 rows)
SELECT * FROM atest2 FOR UPDATE; -- fail
ERROR: atest2: Permission denied.
DELETE FROM atest2; -- fail
ERROR: atest2: Permission denied.
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
ERROR: LOCK TABLE: permission denied
COPY atest2 FROM stdin; -- fail
ERROR: atest2: Permission denied.
GRANT ALL ON atest1 TO PUBLIC; -- fail
ERROR: permission denied
-- checks in subquery, both ok
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
a | b
---+---
(0 rows)
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
col1 | col2
------+------
(0 rows)
SET SESSION AUTHORIZATION regressuser3;
SELECT session_user, current_user;
session_user | current_user
--------------+--------------
regressuser3 | regressuser3
(1 row)
SELECT * FROM atest1; -- ok
a | b
---+-----
1 | two
1 | two
(2 rows)
SELECT * FROM atest2; -- fail
ERROR: atest2: Permission denied.
INSERT INTO atest1 VALUES (2, 'two'); -- fail
ERROR: atest1: Permission denied.
INSERT INTO atest2 VALUES ('foo', true); -- fail
ERROR: atest2: Permission denied.
INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
ERROR: atest1: Permission denied.
UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
ERROR: atest1: Permission denied.
UPDATE atest2 SET col2 = NULL; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
ERROR: atest2: Permission denied.
UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
SELECT * FROM atest1 FOR UPDATE; -- fail
ERROR: atest1: Permission denied.
SELECT * FROM atest2 FOR UPDATE; -- fail
ERROR: atest2: Permission denied.
DELETE FROM atest2; -- fail
ERROR: atest2: Permission denied.
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
COPY atest2 FROM stdin; -- fail
ERROR: atest2: Permission denied.
-- checks in subquery, both fail
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
ERROR: atest2: Permission denied.
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
ERROR: atest2: Permission denied.
SET SESSION AUTHORIZATION regressuser4;
COPY atest2 FROM stdin; -- ok
SELECT * FROM atest1; -- ok
a | b
---+-----
1 | two
1 | two
(2 rows)
-- groups
SET SESSION AUTHORIZATION regressuser3;
CREATE TABLE atest3 (one int, two int, three int);
GRANT DELETE ON atest3 TO GROUP regressgroup2;
SET SESSION AUTHORIZATION regressuser1;
SELECT * FROM atest3; -- fail
ERROR: atest3: Permission denied.
DELETE FROM atest3; -- ok
-- views
SET SESSION AUTHORIZATION regressuser3;
CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
/* The next *should* fail, but it's not implemented that way yet. */
CREATE VIEW atestv2 AS SELECT * FROM atest2;
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
SELECT * FROM atestv1; -- ok
a | b
---+-----
1 | two
1 | two
(2 rows)
GRANT SELECT ON atestv1, atestv3 TO regressuser4;
SET SESSION AUTHORIZATION regressuser4;
SELECT * FROM atestv1; -- ok
a | b
---+-----
1 | two
1 | two
(2 rows)
SELECT * FROM atestv3; -- ok
one | two | three
-----+-----+-------
(0 rows)
-- has_table_privilege function
-- bad-input checks
select has_table_privilege(NULL,'pg_shadow','select');
has_table_privilege
---------------------
(1 row)
select has_table_privilege('pg_shad','select');
ERROR: has_table_privilege: relation "pg_shad" does not exist
select has_table_privilege('nosuchuser','pg_shadow','select');
ERROR: user "nosuchuser" does not exist
select has_table_privilege('pg_shadow','sel');
ERROR: has_table_privilege: invalid privilege type sel
select has_table_privilege(-999999,'pg_shadow','update');
ERROR: pg_aclcheck: invalid user id 4293967297
select has_table_privilege(1,'rule');
ERROR: has_table_privilege: invalid relation oid 1
-- superuser
\c regression
select has_table_privilege(current_user,'pg_shadow','select');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(current_user,'pg_shadow','insert');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,'pg_shadow','update')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,'pg_shadow','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege('pg_shadow','update');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege('pg_shadow','delete');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
has_table_privilege
---------------------
t
(1 row)
-- non-superuser
SET SESSION AUTHORIZATION regressuser3;
select has_table_privilege(current_user,'pg_class','select');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(current_user,'pg_class','insert');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,'pg_class','update')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,'pg_class','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_class') as t1;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_class') as t1;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_class') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege('pg_class','update');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege('pg_class','delete');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_class') as t1;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(current_user,'atest1','select');
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(current_user,'atest1','insert');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,'atest1','update')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,'atest1','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'atest1') as t1;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'atest1') as t1;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'atest1') as t1,
(select usesysid from pg_user where usename = current_user) as t2;
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege('atest1','update');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege('atest1','delete');
has_table_privilege
---------------------
f
(1 row)
select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1;
has_table_privilege
---------------------
t
(1 row)
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'atest1') as t1;
has_table_privilege
---------------------
f
(1 row)
-- clean up
\c regression
DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP VIEW atestv1;
DROP VIEW atestv2;
DROP VIEW atestv3;
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
DROP USER regressuser4;
--
-- NUMERIC
--
CREATE TABLE num_data (id int4, val numeric(210,10));
CREATE TABLE num_exp_add (id1 int4, id2 int4, expected numeric(210,10));
CREATE TABLE num_exp_sub (id1 int4, id2 int4, expected numeric(210,10));
CREATE TABLE num_exp_div (id1 int4, id2 int4, expected numeric(210,10));
CREATE TABLE num_exp_mul (id1 int4, id2 int4, expected numeric(210,10));
CREATE TABLE num_exp_sqrt (id int4, expected numeric(210,10));
CREATE TABLE num_exp_ln (id int4, expected numeric(210,10));
CREATE TABLE num_exp_log10 (id int4, expected numeric(210,10));
CREATE TABLE num_exp_power_10_ln (id int4, expected numeric(210,10));
CREATE TABLE num_result (id1 int4, id2 int4, result numeric(210,10));
-- ******************************
-- * The following EXPECTED results are computed by bc(1)
-- * with a scale of 200
-- ******************************
BEGIN TRANSACTION;
INSERT INTO num_exp_add VALUES (0,0,'0');
INSERT INTO num_exp_sub VALUES (0,0,'0');
INSERT INTO num_exp_mul VALUES (0,0,'0');
INSERT INTO num_exp_div VALUES (0,0,'NaN');
INSERT INTO num_exp_add VALUES (0,1,'0');
INSERT INTO num_exp_sub VALUES (0,1,'0');
INSERT INTO num_exp_mul VALUES (0,1,'0');
INSERT INTO num_exp_div VALUES (0,1,'NaN');
INSERT INTO num_exp_add VALUES (0,2,'-34338492.215397047');
INSERT INTO num_exp_sub VALUES (0,2,'34338492.215397047');
INSERT INTO num_exp_mul VALUES (0,2,'0');
INSERT INTO num_exp_div VALUES (0,2,'0');
INSERT INTO num_exp_add VALUES (0,3,'4.31');
INSERT INTO num_exp_sub VALUES (0,3,'-4.31');
INSERT INTO num_exp_mul VALUES (0,3,'0');
INSERT INTO num_exp_div VALUES (0,3,'0');
INSERT INTO num_exp_add VALUES (0,4,'7799461.4119');
INSERT INTO num_exp_sub VALUES (0,4,'-7799461.4119');
INSERT INTO num_exp_mul VALUES (0,4,'0');
INSERT INTO num_exp_div VALUES (0,4,'0');
INSERT INTO num_exp_add VALUES (0,5,'16397.038491');
INSERT INTO num_exp_sub VALUES (0,5,'-16397.038491');
INSERT INTO num_exp_mul VALUES (0,5,'0');
INSERT INTO num_exp_div VALUES (0,5,'0');
INSERT INTO num_exp_add VALUES (0,6,'93901.57763026');
INSERT INTO num_exp_sub VALUES (0,6,'-93901.57763026');
INSERT INTO num_exp_mul VALUES (0,6,'0');
INSERT INTO num_exp_div VALUES (0,6,'0');
INSERT INTO num_exp_add VALUES (0,7,'-83028485');
INSERT INTO num_exp_sub VALUES (0,7,'83028485');
INSERT INTO num_exp_mul VALUES (0,7,'0');
INSERT INTO num_exp_div VALUES (0,7,'0');
INSERT INTO num_exp_add VALUES (0,8,'74881');
INSERT INTO num_exp_sub VALUES (0,8,'-74881');
INSERT INTO num_exp_mul VALUES (0,8,'0');
INSERT INTO num_exp_div VALUES (0,8,'0');
INSERT INTO num_exp_add VALUES (0,9,'-24926804.045047420');
INSERT INTO num_exp_sub VALUES (0,9,'24926804.045047420');
INSERT INTO num_exp_mul VALUES (0,9,'0');
INSERT INTO num_exp_div VALUES (0,9,'0');
INSERT INTO num_exp_add VALUES (1,0,'0');
INSERT INTO num_exp_sub VALUES (1,0,'0');
INSERT INTO num_exp_mul VALUES (1,0,'0');
INSERT INTO num_exp_div VALUES (1,0,'NaN');
INSERT INTO num_exp_add VALUES (1,1,'0');
INSERT INTO num_exp_sub VALUES (1,1,'0');
INSERT INTO num_exp_mul VALUES (1,1,'0');
INSERT INTO num_exp_div VALUES (1,1,'NaN');
INSERT INTO num_exp_add VALUES (1,2,'-34338492.215397047');
INSERT INTO num_exp_sub VALUES (1,2,'34338492.215397047');
INSERT INTO num_exp_mul VALUES (1,2,'0');
INSERT INTO num_exp_div VALUES (1,2,'0');
INSERT INTO num_exp_add VALUES (1,3,'4.31');
INSERT INTO num_exp_sub VALUES (1,3,'-4.31');
INSERT INTO num_exp_mul VALUES (1,3,'0');
INSERT INTO num_exp_div VALUES (1,3,'0');
INSERT INTO num_exp_add VALUES (1,4,'7799461.4119');
INSERT INTO num_exp_sub VALUES (1,4,'-7799461.4119');
INSERT INTO num_exp_mul VALUES (1,4,'0');
INSERT INTO num_exp_div VALUES (1,4,'0');
INSERT INTO num_exp_add VALUES (1,5,'16397.038491');
INSERT INTO num_exp_sub VALUES (1,5,'-16397.038491');
INSERT INTO num_exp_mul VALUES (1,5,'0');
INSERT INTO num_exp_div VALUES (1,5,'0');
INSERT INTO num_exp_add VALUES (1,6,'93901.57763026');
INSERT INTO num_exp_sub VALUES (1,6,'-93901.57763026');
INSERT INTO num_exp_mul VALUES (1,6,'0');
INSERT INTO num_exp_div VALUES (1,6,'0');
INSERT INTO num_exp_add VALUES (1,7,'-83028485');
INSERT INTO num_exp_sub VALUES (1,7,'83028485');
INSERT INTO num_exp_mul VALUES (1,7,'0');
INSERT INTO num_exp_div VALUES (1,7,'0');
INSERT INTO num_exp_add VALUES (1,8,'74881');
INSERT INTO num_exp_sub VALUES (1,8,'-74881');
INSERT INTO num_exp_mul VALUES (1,8,'0');
INSERT INTO num_exp_div VALUES (1,8,'0');
INSERT INTO num_exp_add VALUES (1,9,'-24926804.045047420');
INSERT INTO num_exp_sub VALUES (1,9,'24926804.045047420');
INSERT INTO num_exp_mul VALUES (1,9,'0');
INSERT INTO num_exp_div VALUES (1,9,'0');
INSERT INTO num_exp_add VALUES (2,0,'-34338492.215397047');
INSERT INTO num_exp_sub VALUES (2,0,'-34338492.215397047');
INSERT INTO num_exp_mul VALUES (2,0,'0');
INSERT INTO num_exp_div VALUES (2,0,'NaN');
INSERT INTO num_exp_add VALUES (2,1,'-34338492.215397047');
INSERT INTO num_exp_sub VALUES (2,1,'-34338492.215397047');
INSERT INTO num_exp_mul VALUES (2,1,'0');
INSERT INTO num_exp_div VALUES (2,1,'NaN');
INSERT INTO num_exp_add VALUES (2,2,'-68676984.430794094');
INSERT INTO num_exp_sub VALUES (2,2,'0');
INSERT INTO num_exp_mul VALUES (2,2,'1179132047626883.596862135856320209');
INSERT INTO num_exp_div VALUES (2,2,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (2,3,'-34338487.905397047');
INSERT INTO num_exp_sub VALUES (2,3,'-34338496.525397047');
INSERT INTO num_exp_mul VALUES (2,3,'-147998901.44836127257');
INSERT INTO num_exp_div VALUES (2,3,'-7967167.56737750510440835266');
INSERT INTO num_exp_add VALUES (2,4,'-26539030.803497047');
INSERT INTO num_exp_sub VALUES (2,4,'-42137953.627297047');
INSERT INTO num_exp_mul VALUES (2,4,'-267821744976817.8111137106593');
INSERT INTO num_exp_div VALUES (2,4,'-4.40267480046830116685');
INSERT INTO num_exp_add VALUES (2,5,'-34322095.176906047');
INSERT INTO num_exp_sub VALUES (2,5,'-34354889.253888047');
INSERT INTO num_exp_mul VALUES (2,5,'-563049578578.769242506736077');
INSERT INTO num_exp_div VALUES (2,5,'-2094.18866914563535496429');
INSERT INTO num_exp_add VALUES (2,6,'-34244590.637766787');
INSERT INTO num_exp_sub VALUES (2,6,'-34432393.793027307');
INSERT INTO num_exp_mul VALUES (2,6,'-3224438592470.18449811926184222');
INSERT INTO num_exp_div VALUES (2,6,'-365.68599891479766440940');
INSERT INTO num_exp_add VALUES (2,7,'-117366977.215397047');
INSERT INTO num_exp_sub VALUES (2,7,'48689992.784602953');
INSERT INTO num_exp_mul VALUES (2,7,'2851072985828710.485883795');
INSERT INTO num_exp_div VALUES (2,7,'.41357483778485235518');
INSERT INTO num_exp_add VALUES (2,8,'-34263611.215397047');
INSERT INTO num_exp_sub VALUES (2,8,'-34413373.215397047');
INSERT INTO num_exp_mul VALUES (2,8,'-2571300635581.146276407');
INSERT INTO num_exp_div VALUES (2,8,'-458.57416721727870888476');
INSERT INTO num_exp_add VALUES (2,9,'-59265296.260444467');
INSERT INTO num_exp_sub VALUES (2,9,'-9411688.170349627');
INSERT INTO num_exp_mul VALUES (2,9,'855948866655588.453741509242968740');
INSERT INTO num_exp_div VALUES (2,9,'1.37757299946438931811');
INSERT INTO num_exp_add VALUES (3,0,'4.31');
INSERT INTO num_exp_sub VALUES (3,0,'4.31');
INSERT INTO num_exp_mul VALUES (3,0,'0');
INSERT INTO num_exp_div VALUES (3,0,'NaN');
INSERT INTO num_exp_add VALUES (3,1,'4.31');
INSERT INTO num_exp_sub VALUES (3,1,'4.31');
INSERT INTO num_exp_mul VALUES (3,1,'0');
INSERT INTO num_exp_div VALUES (3,1,'NaN');
INSERT INTO num_exp_add VALUES (3,2,'-34338487.905397047');
INSERT INTO num_exp_sub VALUES (3,2,'34338496.525397047');
INSERT INTO num_exp_mul VALUES (3,2,'-147998901.44836127257');
INSERT INTO num_exp_div VALUES (3,2,'-.00000012551512084352');
INSERT INTO num_exp_add VALUES (3,3,'8.62');
INSERT INTO num_exp_sub VALUES (3,3,'0');
INSERT INTO num_exp_mul VALUES (3,3,'18.5761');
INSERT INTO num_exp_div VALUES (3,3,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (3,4,'7799465.7219');
INSERT INTO num_exp_sub VALUES (3,4,'-7799457.1019');
INSERT INTO num_exp_mul VALUES (3,4,'33615678.685289');
INSERT INTO num_exp_div VALUES (3,4,'.00000055260225961552');
INSERT INTO num_exp_add VALUES (3,5,'16401.348491');
INSERT INTO num_exp_sub VALUES (3,5,'-16392.728491');
INSERT INTO num_exp_mul VALUES (3,5,'70671.23589621');
INSERT INTO num_exp_div VALUES (3,5,'.00026285234387695504');
INSERT INTO num_exp_add VALUES (3,6,'93905.88763026');
INSERT INTO num_exp_sub VALUES (3,6,'-93897.26763026');
INSERT INTO num_exp_mul VALUES (3,6,'404715.7995864206');
INSERT INTO num_exp_div VALUES (3,6,'.00004589912234457595');
INSERT INTO num_exp_add VALUES (3,7,'-83028480.69');
INSERT INTO num_exp_sub VALUES (3,7,'83028489.31');
INSERT INTO num_exp_mul VALUES (3,7,'-357852770.35');
INSERT INTO num_exp_div VALUES (3,7,'-.00000005190989574240');
INSERT INTO num_exp_add VALUES (3,8,'74885.31');
INSERT INTO num_exp_sub VALUES (3,8,'-74876.69');
INSERT INTO num_exp_mul VALUES (3,8,'322737.11');
INSERT INTO num_exp_div VALUES (3,8,'.00005755799201399553');
INSERT INTO num_exp_add VALUES (3,9,'-24926799.735047420');
INSERT INTO num_exp_sub VALUES (3,9,'24926808.355047420');
INSERT INTO num_exp_mul VALUES (3,9,'-107434525.43415438020');
INSERT INTO num_exp_div VALUES (3,9,'-.00000017290624149854');
INSERT INTO num_exp_add VALUES (4,0,'7799461.4119');
INSERT INTO num_exp_sub VALUES (4,0,'7799461.4119');
INSERT INTO num_exp_mul VALUES (4,0,'0');
INSERT INTO num_exp_div VALUES (4,0,'NaN');
INSERT INTO num_exp_add VALUES (4,1,'7799461.4119');
INSERT INTO num_exp_sub VALUES (4,1,'7799461.4119');
INSERT INTO num_exp_mul VALUES (4,1,'0');
INSERT INTO num_exp_div VALUES (4,1,'NaN');
INSERT INTO num_exp_add VALUES (4,2,'-26539030.803497047');
INSERT INTO num_exp_sub VALUES (4,2,'42137953.627297047');
INSERT INTO num_exp_mul VALUES (4,2,'-267821744976817.8111137106593');
INSERT INTO num_exp_div VALUES (4,2,'-.22713465002993920385');
INSERT INTO num_exp_add VALUES (4,3,'7799465.7219');
INSERT INTO num_exp_sub VALUES (4,3,'7799457.1019');
INSERT INTO num_exp_mul VALUES (4,3,'33615678.685289');
INSERT INTO num_exp_div VALUES (4,3,'1809619.81714617169373549883');
INSERT INTO num_exp_add VALUES (4,4,'15598922.8238');
INSERT INTO num_exp_sub VALUES (4,4,'0');
INSERT INTO num_exp_mul VALUES (4,4,'60831598315717.14146161');
INSERT INTO num_exp_div VALUES (4,4,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (4,5,'7815858.450391');
INSERT INTO num_exp_sub VALUES (4,5,'7783064.373409');
INSERT INTO num_exp_mul VALUES (4,5,'127888068979.9935054429');
INSERT INTO num_exp_div VALUES (4,5,'475.66281046305802686061');
INSERT INTO num_exp_add VALUES (4,6,'7893362.98953026');
INSERT INTO num_exp_sub VALUES (4,6,'7705559.83426974');
INSERT INTO num_exp_mul VALUES (4,6,'732381731243.745115764094');
INSERT INTO num_exp_div VALUES (4,6,'83.05996138436129499606');
INSERT INTO num_exp_add VALUES (4,7,'-75229023.5881');
INSERT INTO num_exp_sub VALUES (4,7,'90827946.4119');
INSERT INTO num_exp_mul VALUES (4,7,'-647577464846017.9715');
INSERT INTO num_exp_div VALUES (4,7,'-.09393717604145131637');
INSERT INTO num_exp_add VALUES (4,8,'7874342.4119');
INSERT INTO num_exp_sub VALUES (4,8,'7724580.4119');
INSERT INTO num_exp_mul VALUES (4,8,'584031469984.4839');
INSERT INTO num_exp_div VALUES (4,8,'104.15808298366741897143');
INSERT INTO num_exp_add VALUES (4,9,'-17127342.633147420');
INSERT INTO num_exp_sub VALUES (4,9,'32726265.456947420');
INSERT INTO num_exp_mul VALUES (4,9,'-194415646271340.1815956522980');
INSERT INTO num_exp_div VALUES (4,9,'-.31289456112403769409');
INSERT INTO num_exp_add VALUES (5,0,'16397.038491');
INSERT INTO num_exp_sub VALUES (5,0,'16397.038491');
INSERT INTO num_exp_mul VALUES (5,0,'0');
INSERT INTO num_exp_div VALUES (5,0,'NaN');
INSERT INTO num_exp_add VALUES (5,1,'16397.038491');
INSERT INTO num_exp_sub VALUES (5,1,'16397.038491');
INSERT INTO num_exp_mul VALUES (5,1,'0');
INSERT INTO num_exp_div VALUES (5,1,'NaN');
INSERT INTO num_exp_add VALUES (5,2,'-34322095.176906047');
INSERT INTO num_exp_sub VALUES (5,2,'34354889.253888047');
INSERT INTO num_exp_mul VALUES (5,2,'-563049578578.769242506736077');
INSERT INTO num_exp_div VALUES (5,2,'-.00047751189505192446');
INSERT INTO num_exp_add VALUES (5,3,'16401.348491');
INSERT INTO num_exp_sub VALUES (5,3,'16392.728491');
INSERT INTO num_exp_mul VALUES (5,3,'70671.23589621');
INSERT INTO num_exp_div VALUES (5,3,'3804.41728329466357308584');
INSERT INTO num_exp_add VALUES (5,4,'7815858.450391');
INSERT INTO num_exp_sub VALUES (5,4,'-7783064.373409');
INSERT INTO num_exp_mul VALUES (5,4,'127888068979.9935054429');
INSERT INTO num_exp_div VALUES (5,4,'.00210232958726897192');
INSERT INTO num_exp_add VALUES (5,5,'32794.076982');
INSERT INTO num_exp_sub VALUES (5,5,'0');
INSERT INTO num_exp_mul VALUES (5,5,'268862871.275335557081');
INSERT INTO num_exp_div VALUES (5,5,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (5,6,'110298.61612126');
INSERT INTO num_exp_sub VALUES (5,6,'-77504.53913926');
INSERT INTO num_exp_mul VALUES (5,6,'1539707782.76899778633766');
INSERT INTO num_exp_div VALUES (5,6,'.17461941433576102689');
INSERT INTO num_exp_add VALUES (5,7,'-83012087.961509');
INSERT INTO num_exp_sub VALUES (5,7,'83044882.038491');
INSERT INTO num_exp_mul VALUES (5,7,'-1361421264394.416135');
INSERT INTO num_exp_div VALUES (5,7,'-.00019748690453643710');
INSERT INTO num_exp_add VALUES (5,8,'91278.038491');
INSERT INTO num_exp_sub VALUES (5,8,'-58483.961509');
INSERT INTO num_exp_mul VALUES (5,8,'1227826639.244571');
INSERT INTO num_exp_div VALUES (5,8,'.21897461960978085228');
INSERT INTO num_exp_add VALUES (5,9,'-24910407.006556420');
INSERT INTO num_exp_sub VALUES (5,9,'24943201.083538420');
INSERT INTO num_exp_mul VALUES (5,9,'-408725765384.257043660243220');
INSERT INTO num_exp_div VALUES (5,9,'-.00065780749354660427');
INSERT INTO num_exp_add VALUES (6,0,'93901.57763026');
INSERT INTO num_exp_sub VALUES (6,0,'93901.57763026');
INSERT INTO num_exp_mul VALUES (6,0,'0');
INSERT INTO num_exp_div VALUES (6,0,'NaN');
INSERT INTO num_exp_add VALUES (6,1,'93901.57763026');
INSERT INTO num_exp_sub VALUES (6,1,'93901.57763026');
INSERT INTO num_exp_mul VALUES (6,1,'0');
INSERT INTO num_exp_div VALUES (6,1,'NaN');
INSERT INTO num_exp_add VALUES (6,2,'-34244590.637766787');
INSERT INTO num_exp_sub VALUES (6,2,'34432393.793027307');
INSERT INTO num_exp_mul VALUES (6,2,'-3224438592470.18449811926184222');
INSERT INTO num_exp_div VALUES (6,2,'-.00273458651128995823');
INSERT INTO num_exp_add VALUES (6,3,'93905.88763026');
INSERT INTO num_exp_sub VALUES (6,3,'93897.26763026');
INSERT INTO num_exp_mul VALUES (6,3,'404715.7995864206');
INSERT INTO num_exp_div VALUES (6,3,'21786.90896293735498839907');
INSERT INTO num_exp_add VALUES (6,4,'7893362.98953026');
INSERT INTO num_exp_sub VALUES (6,4,'-7705559.83426974');
INSERT INTO num_exp_mul VALUES (6,4,'732381731243.745115764094');
INSERT INTO num_exp_div VALUES (6,4,'.01203949512295682469');
INSERT INTO num_exp_add VALUES (6,5,'110298.61612126');
INSERT INTO num_exp_sub VALUES (6,5,'77504.53913926');
INSERT INTO num_exp_mul VALUES (6,5,'1539707782.76899778633766');
INSERT INTO num_exp_div VALUES (6,5,'5.72674008674192359679');
INSERT INTO num_exp_add VALUES (6,6,'187803.15526052');
INSERT INTO num_exp_sub VALUES (6,6,'0');
INSERT INTO num_exp_mul VALUES (6,6,'8817506281.4517452372676676');
INSERT INTO num_exp_div VALUES (6,6,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (6,7,'-82934583.42236974');
INSERT INTO num_exp_sub VALUES (6,7,'83122386.57763026');
INSERT INTO num_exp_mul VALUES (6,7,'-7796505729750.37795610');
INSERT INTO num_exp_div VALUES (6,7,'-.00113095617281538980');
INSERT INTO num_exp_add VALUES (6,8,'168782.57763026');
INSERT INTO num_exp_sub VALUES (6,8,'19020.57763026');
INSERT INTO num_exp_mul VALUES (6,8,'7031444034.53149906');
INSERT INTO num_exp_div VALUES (6,8,'1.25401073209839612184');
INSERT INTO num_exp_add VALUES (6,9,'-24832902.467417160');
INSERT INTO num_exp_sub VALUES (6,9,'25020705.622677680');
INSERT INTO num_exp_mul VALUES (6,9,'-2340666225110.29929521292692920');
INSERT INTO num_exp_div VALUES (6,9,'-.00376709254265256789');
INSERT INTO num_exp_add VALUES (7,0,'-83028485');
INSERT INTO num_exp_sub VALUES (7,0,'-83028485');
INSERT INTO num_exp_mul VALUES (7,0,'0');
INSERT INTO num_exp_div VALUES (7,0,'NaN');
INSERT INTO num_exp_add VALUES (7,1,'-83028485');
INSERT INTO num_exp_sub VALUES (7,1,'-83028485');
INSERT INTO num_exp_mul VALUES (7,1,'0');
INSERT INTO num_exp_div VALUES (7,1,'NaN');
INSERT INTO num_exp_add VALUES (7,2,'-117366977.215397047');
INSERT INTO num_exp_sub VALUES (7,2,'-48689992.784602953');
INSERT INTO num_exp_mul VALUES (7,2,'2851072985828710.485883795');
INSERT INTO num_exp_div VALUES (7,2,'2.41794207151503385700');
INSERT INTO num_exp_add VALUES (7,3,'-83028480.69');
INSERT INTO num_exp_sub VALUES (7,3,'-83028489.31');
INSERT INTO num_exp_mul VALUES (7,3,'-357852770.35');
INSERT INTO num_exp_div VALUES (7,3,'-19264149.65197215777262180974');
INSERT INTO num_exp_add VALUES (7,4,'-75229023.5881');
INSERT INTO num_exp_sub VALUES (7,4,'-90827946.4119');
INSERT INTO num_exp_mul VALUES (7,4,'-647577464846017.9715');
INSERT INTO num_exp_div VALUES (7,4,'-10.64541262725136247686');
INSERT INTO num_exp_add VALUES (7,5,'-83012087.961509');
INSERT INTO num_exp_sub VALUES (7,5,'-83044882.038491');
INSERT INTO num_exp_mul VALUES (7,5,'-1361421264394.416135');
INSERT INTO num_exp_div VALUES (7,5,'-5063.62688881730941836574');
INSERT INTO num_exp_add VALUES (7,6,'-82934583.42236974');
INSERT INTO num_exp_sub VALUES (7,6,'-83122386.57763026');
INSERT INTO num_exp_mul VALUES (7,6,'-7796505729750.37795610');
INSERT INTO num_exp_div VALUES (7,6,'-884.20756174009028770294');
INSERT INTO num_exp_add VALUES (7,7,'-166056970');
INSERT INTO num_exp_sub VALUES (7,7,'0');
INSERT INTO num_exp_mul VALUES (7,7,'6893729321395225');
INSERT INTO num_exp_div VALUES (7,7,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (7,8,'-82953604');
INSERT INTO num_exp_sub VALUES (7,8,'-83103366');
INSERT INTO num_exp_mul VALUES (7,8,'-6217255985285');
INSERT INTO num_exp_div VALUES (7,8,'-1108.80577182462841041118');
INSERT INTO num_exp_add VALUES (7,9,'-107955289.045047420');
INSERT INTO num_exp_sub VALUES (7,9,'-58101680.954952580');
INSERT INTO num_exp_mul VALUES (7,9,'2069634775752159.035758700');
INSERT INTO num_exp_div VALUES (7,9,'3.33089171198810413382');
INSERT INTO num_exp_add VALUES (8,0,'74881');
INSERT INTO num_exp_sub VALUES (8,0,'74881');
INSERT INTO num_exp_mul VALUES (8,0,'0');
INSERT INTO num_exp_div VALUES (8,0,'NaN');
INSERT INTO num_exp_add VALUES (8,1,'74881');
INSERT INTO num_exp_sub VALUES (8,1,'74881');
INSERT INTO num_exp_mul VALUES (8,1,'0');
INSERT INTO num_exp_div VALUES (8,1,'NaN');
INSERT INTO num_exp_add VALUES (8,2,'-34263611.215397047');
INSERT INTO num_exp_sub VALUES (8,2,'34413373.215397047');
INSERT INTO num_exp_mul VALUES (8,2,'-2571300635581.146276407');
INSERT INTO num_exp_div VALUES (8,2,'-.00218067233500788615');
INSERT INTO num_exp_add VALUES (8,3,'74885.31');
INSERT INTO num_exp_sub VALUES (8,3,'74876.69');
INSERT INTO num_exp_mul VALUES (8,3,'322737.11');
INSERT INTO num_exp_div VALUES (8,3,'17373.78190255220417633410');
INSERT INTO num_exp_add VALUES (8,4,'7874342.4119');
INSERT INTO num_exp_sub VALUES (8,4,'-7724580.4119');
INSERT INTO num_exp_mul VALUES (8,4,'584031469984.4839');
INSERT INTO num_exp_div VALUES (8,4,'.00960079113741758956');
INSERT INTO num_exp_add VALUES (8,5,'91278.038491');
INSERT INTO num_exp_sub VALUES (8,5,'58483.961509');
INSERT INTO num_exp_mul VALUES (8,5,'1227826639.244571');
INSERT INTO num_exp_div VALUES (8,5,'4.56673929509287019456');
INSERT INTO num_exp_add VALUES (8,6,'168782.57763026');
INSERT INTO num_exp_sub VALUES (8,6,'-19020.57763026');
INSERT INTO num_exp_mul VALUES (8,6,'7031444034.53149906');
INSERT INTO num_exp_div VALUES (8,6,'.79744134113322314424');
INSERT INTO num_exp_add VALUES (8,7,'-82953604');
INSERT INTO num_exp_sub VALUES (8,7,'83103366');
INSERT INTO num_exp_mul VALUES (8,7,'-6217255985285');
INSERT INTO num_exp_div VALUES (8,7,'-.00090187120721280172');
INSERT INTO num_exp_add VALUES (8,8,'149762');
INSERT INTO num_exp_sub VALUES (8,8,'0');
INSERT INTO num_exp_mul VALUES (8,8,'5607164161');
INSERT INTO num_exp_div VALUES (8,8,'1.00000000000000000000');
INSERT INTO num_exp_add VALUES (8,9,'-24851923.045047420');
INSERT INTO num_exp_sub VALUES (8,9,'25001685.045047420');
INSERT INTO num_exp_mul VALUES (8,9,'-1866544013697.195857020');
INSERT INTO num_exp_div VALUES (8,9,'-.00300403532938582735');
INSERT INTO num_exp_add VALUES (9,0,'-24926804.045047420');
INSERT INTO num_exp_sub VALUES (9,0,'-24926804.045047420');
INSERT INTO num_exp_mul VALUES (9,0,'0');
INSERT INTO num_exp_div VALUES (9,0,'NaN');
INSERT INTO num_exp_add VALUES (9,1,'-24926804.045047420');
INSERT INTO num_exp_sub VALUES (9,1,'-24926804.045047420');
INSERT INTO num_exp_mul VALUES (9,1,'0');
INSERT INTO num_exp_div VALUES (9,1,'NaN');
INSERT INTO num_exp_add VALUES (9,2,'-59265296.260444467');
INSERT INTO num_exp_sub VALUES (9,2,'9411688.170349627');
INSERT INTO num_exp_mul VALUES (9,2,'855948866655588.453741509242968740');
INSERT INTO num_exp_div VALUES (9,2,'.72591434384152961526');
INSERT INTO num_exp_add VALUES (9,3,'-24926799.735047420');
INSERT INTO num_exp_sub VALUES (9,3,'-24926808.355047420');
INSERT INTO num_exp_mul VALUES (9,3,'-107434525.43415438020');
INSERT INTO num_exp_div VALUES (9,3,'-5783481.21694835730858468677');
INSERT INTO num_exp_add VALUES (9,4,'-17127342.633147420');
INSERT INTO num_exp_sub VALUES (9,4,'-32726265.456947420');
INSERT INTO num_exp_mul VALUES (9,4,'-194415646271340.1815956522980');
INSERT INTO num_exp_div VALUES (9,4,'-3.19596478892958416484');
INSERT INTO num_exp_add VALUES (9,5,'-24910407.006556420');
INSERT INTO num_exp_sub VALUES (9,5,'-24943201.083538420');
INSERT INTO num_exp_mul VALUES (9,5,'-408725765384.257043660243220');
INSERT INTO num_exp_div VALUES (9,5,'-1520.20159364322004505807');
INSERT INTO num_exp_add VALUES (9,6,'-24832902.467417160');
INSERT INTO num_exp_sub VALUES (9,6,'-25020705.622677680');
INSERT INTO num_exp_mul VALUES (9,6,'-2340666225110.29929521292692920');
INSERT INTO num_exp_div VALUES (9,6,'-265.45671195426965751280');
INSERT INTO num_exp_add VALUES (9,7,'-107955289.045047420');
INSERT INTO num_exp_sub VALUES (9,7,'58101680.954952580');
INSERT INTO num_exp_mul VALUES (9,7,'2069634775752159.035758700');
INSERT INTO num_exp_div VALUES (9,7,'.30021990699995814689');
INSERT INTO num_exp_add VALUES (9,8,'-24851923.045047420');
INSERT INTO num_exp_sub VALUES (9,8,'-25001685.045047420');
INSERT INTO num_exp_mul VALUES (9,8,'-1866544013697.195857020');
INSERT INTO num_exp_div VALUES (9,8,'-332.88556569820675471748');
INSERT INTO num_exp_add VALUES (9,9,'-49853608.090094840');
INSERT INTO num_exp_sub VALUES (9,9,'0');
INSERT INTO num_exp_mul VALUES (9,9,'621345559900192.420120630048656400');
INSERT INTO num_exp_div VALUES (9,9,'1.00000000000000000000');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO num_exp_sqrt VALUES (0,'0');
INSERT INTO num_exp_sqrt VALUES (1,'0');
INSERT INTO num_exp_sqrt VALUES (2,'5859.90547836712524903505');
INSERT INTO num_exp_sqrt VALUES (3,'2.07605394920266944396');
INSERT INTO num_exp_sqrt VALUES (4,'2792.75158435189147418923');
INSERT INTO num_exp_sqrt VALUES (5,'128.05092147657509145473');
INSERT INTO num_exp_sqrt VALUES (6,'306.43364311096782703406');
INSERT INTO num_exp_sqrt VALUES (7,'9111.99676251039939975230');
INSERT INTO num_exp_sqrt VALUES (8,'273.64392922189960397542');
INSERT INTO num_exp_sqrt VALUES (9,'4992.67503899937593364766');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO num_exp_ln VALUES (0,'NaN');
INSERT INTO num_exp_ln VALUES (1,'NaN');
INSERT INTO num_exp_ln VALUES (2,'17.35177750493897715514');
INSERT INTO num_exp_ln VALUES (3,'1.46093790411565641971');
INSERT INTO num_exp_ln VALUES (4,'15.86956523951936572464');
INSERT INTO num_exp_ln VALUES (5,'9.70485601768871834038');
INSERT INTO num_exp_ln VALUES (6,'11.45000246622944403127');
INSERT INTO num_exp_ln VALUES (7,'18.23469429965478772991');
INSERT INTO num_exp_ln VALUES (8,'11.22365546576315513668');
INSERT INTO num_exp_ln VALUES (9,'17.03145425013166006962');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO num_exp_log10 VALUES (0,'NaN');
INSERT INTO num_exp_log10 VALUES (1,'NaN');
INSERT INTO num_exp_log10 VALUES (2,'7.53578122160797276459');
INSERT INTO num_exp_log10 VALUES (3,'.63447727016073160075');
INSERT INTO num_exp_log10 VALUES (4,'6.89206461372691743345');
INSERT INTO num_exp_log10 VALUES (5,'4.21476541614777768626');
INSERT INTO num_exp_log10 VALUES (6,'4.97267288886207207671');
INSERT INTO num_exp_log10 VALUES (7,'7.91922711353275546914');
INSERT INTO num_exp_log10 VALUES (8,'4.87437163556421004138');
INSERT INTO num_exp_log10 VALUES (9,'7.39666659961986567059');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO num_exp_power_10_ln VALUES (0,'NaN');
INSERT INTO num_exp_power_10_ln VALUES (1,'NaN');
INSERT INTO num_exp_power_10_ln VALUES (2,'224790267919917955.13261618583642653184');
INSERT INTO num_exp_power_10_ln VALUES (3,'28.90266599445155957393');
INSERT INTO num_exp_power_10_ln VALUES (4,'7405685069594999.07733999469386277636');
INSERT INTO num_exp_power_10_ln VALUES (5,'5068226527.32127265408584640098');
INSERT INTO num_exp_power_10_ln VALUES (6,'281839893606.99372343357047819067');
INSERT INTO num_exp_power_10_ln VALUES (7,'1716699575118597095.42330819910640247627');
INSERT INTO num_exp_power_10_ln VALUES (8,'167361463828.07491320069016125952');
INSERT INTO num_exp_power_10_ln VALUES (9,'107511333880052007.04141124673540337457');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO num_data VALUES (0, '0');
INSERT INTO num_data VALUES (1, '0');
INSERT INTO num_data VALUES (2, '-34338492.215397047');
INSERT INTO num_data VALUES (3, '4.31');
INSERT INTO num_data VALUES (4, '7799461.4119');
INSERT INTO num_data VALUES (5, '16397.038491');
INSERT INTO num_data VALUES (6, '93901.57763026');
INSERT INTO num_data VALUES (7, '-83028485');
INSERT INTO num_data VALUES (8, '74881');
INSERT INTO num_data VALUES (9, '-24926804.045047420');
COMMIT TRANSACTION;
-- ******************************
-- * Create indices for faster checks
-- ******************************
CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
CREATE UNIQUE INDEX num_exp_sub_idx ON num_exp_sub (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
CREATE UNIQUE INDEX num_exp_div_idx ON num_exp_div (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
CREATE UNIQUE INDEX num_exp_mul_idx ON num_exp_mul (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
CREATE UNIQUE INDEX num_exp_sqrt_idx ON num_exp_sqrt (id);
CREATE UNIQUE INDEX num_exp_ln_idx ON num_exp_ln (id);
CREATE UNIQUE INDEX num_exp_log10_idx ON num_exp_log10 (id);
CREATE UNIQUE INDEX num_exp_power_10_ln_idx ON num_exp_power_10_ln (id);
VACUUM ANALYZE num_exp_add;
VACUUM ANALYZE num_exp_sub;
VACUUM ANALYZE num_exp_div;
VACUUM ANALYZE num_exp_mul;
VACUUM ANALYZE num_exp_sqrt;
VACUUM ANALYZE num_exp_ln;
VACUUM ANALYZE num_exp_log10;
VACUUM ANALYZE num_exp_power_10_ln;
-- ******************************
-- * Now check the behaviour of the NUMERIC type
-- ******************************
-- ******************************
-- * Addition check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, t1.val + t2.val
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, t2.expected
FROM num_result t1, num_exp_add t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != t2.expected;
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val + t2.val, 10)
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 10) as expected
FROM num_result t1, num_exp_add t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != round(t2.expected, 10);
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
-- ******************************
-- * Subtraction check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, t1.val - t2.val
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, t2.expected
FROM num_result t1, num_exp_sub t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != t2.expected;
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val - t2.val, 40)
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 40)
FROM num_result t1, num_exp_sub t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != round(t2.expected, 40);
id1 | id2 | result | round
-----+-----+--------+-------
(0 rows)
-- ******************************
-- * Multiply check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, t1.val * t2.val
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, t2.expected
FROM num_result t1, num_exp_mul t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != t2.expected;
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val * t2.val, 30)
FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 30) as expected
FROM num_result t1, num_exp_mul t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != round(t2.expected, 30);
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
-- ******************************
-- * Division check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, t1.val / t2.val
FROM num_data t1, num_data t2
WHERE t2.val != '0.0';
SELECT t1.id1, t1.id2, t1.result, t2.expected
FROM num_result t1, num_exp_div t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != t2.expected;
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val / t2.val, 80)
FROM num_data t1, num_data t2
WHERE t2.val != '0.0';
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 80) as expected
FROM num_result t1, num_exp_div t2
WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
AND t1.result != round(t2.expected, 80);
id1 | id2 | result | expected
-----+-----+--------+----------
(0 rows)
-- ******************************
-- * Square root check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
FROM num_data;
SELECT t1.id1, t1.result, t2.expected
FROM num_result t1, num_exp_sqrt t2
WHERE t1.id1 = t2.id
AND t1.result != t2.expected;
id1 | result | expected
-----+--------+----------
(0 rows)
-- ******************************
-- * Natural logarithm check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, LN(ABS(val))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
FROM num_result t1, num_exp_ln t2
WHERE t1.id1 = t2.id
AND t1.result != t2.expected;
id1 | result | expected
-----+--------+----------
(0 rows)
-- ******************************
-- * Logarithm base 10 check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
FROM num_result t1, num_exp_log10 t2
WHERE t1.id1 = t2.id
AND t1.result != t2.expected;
id1 | result | expected
-----+--------+----------
(0 rows)
-- ******************************
-- * POW(10, LN(value)) check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, POW(numeric '10', LN(ABS(round(val,200))))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
FROM num_result t1, num_exp_power_10_ln t2
WHERE t1.id1 = t2.id
AND t1.result != t2.expected;
id1 | result | expected
-----+--------+----------
(0 rows)
-- ******************************
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
-- numeric AVG used to fail on some platforms
SELECT AVG(val) FROM num_data;
avg
----------------------
-13430913.5922423207
(1 row)
-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
INSERT INTO fract_only VALUES (1, '0.0');
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
ERROR: overflow on numeric ABS(value) >= 10^0 for field with precision 4 scale 4
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
ERROR: overflow on numeric ABS(value) >= 10^0 for field with precision 4 scale 4
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
id | val
----+---------
1 | 0.0000
2 | 0.1000
4 | -0.9999
5 | 0.9999
7 | 0.0000
8 | 0.0002
(6 rows)
DROP TABLE fract_only;
-- TO_CHAR()
--
SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999')
FROM num_data;
to_char_1 | to_char
-----------+------------------------
| 0
| 0
| -34,338,492
| 4
| 7,799,461
| 16,397
| 93,902
| -83,028,485
| 74,881
| -24,926,804
(10 rows)
SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
FROM num_data;
to_char_2 | to_char
-----------+--------------------------------------------
| .000,000,000,000,000
| .000,000,000,000,000
| -34,338,492.215,397,047,000,000
| 4.310,000,000,000,000
| 7,799,461.411,900,000,000,000
| 16,397.038,491,000,000,000
| 93,901.577,630,260,000,000
| -83,028,485.000,000,000,000,000
| 74,881.000,000,000,000,000
| -24,926,804.045,047,420,000,000
(10 rows)
SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR')
FROM num_data;
to_char_3 | to_char
-----------+------------------------------------
| .000000000000000
| .000000000000000
| <34338492.215397047000000>
| 4.310000000000000
| 7799461.411900000000000
| 16397.038491000000000
| 93901.577630260000000
| <83028485.000000000000000>
| 74881.000000000000000
| <24926804.045047420000000>
(10 rows)
SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S')
FROM num_data;
to_char_4 | to_char
-----------+-----------------------------------
| .000000000000000+
| .000000000000000+
| 34338492.215397047000000-
| 4.310000000000000+
| 7799461.411900000000000+
| 16397.038491000000000+
| 93901.577630260000000+
| 83028485.000000000000000-
| 74881.000000000000000+
| 24926804.045047420000000-
(10 rows)
SELECT '' AS to_char_5, to_char(val, 'MI9999999999999999.999999999999999') FROM num_data;
to_char_5 | to_char
-----------+------------------------------------
| .000000000000000
| .000000000000000
| - 34338492.215397047000000
| 4.310000000000000
| 7799461.411900000000000
| 16397.038491000000000
| 93901.577630260000000
| - 83028485.000000000000000
| 74881.000000000000000
| - 24926804.045047420000000
(10 rows)
SELECT '' AS to_char_6, to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data;
to_char_6 | to_char
-----------+---------------------
| +0.
| +0.
| -34338492.215397047
| +4.31
| +7799461.4119
| +16397.038491
| +93901.57763026
| -83028485
| +74881
| -24926804.04504742
(10 rows)
SELECT '' AS to_char_7, to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
to_char_7 | to_char
-----------+----------------------
| 0.
| 0.
| <34338492.215397047>
| 4.31
| 7799461.4119
| 16397.038491
| 93901.57763026
| <83028485>
| 74881
| <24926804.04504742>
(10 rows)
SELECT '' AS to_char_8, to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data;
to_char_8 | to_char
-----------+-----------------------------------
| + .000000000000000
| + .000000000000000
| - 34338492.215397047000000
| + 4.310000000000000
| + 7799461.411900000000000
| + 16397.038491000000000
| + 93901.577630260000000
| - 83028485.000000000000000
| + 74881.000000000000000
| - 24926804.045047420000000
(10 rows)
SELECT '' AS to_char_9, to_char(val, '0999999999999999.999999999999999') FROM num_data;
to_char_9 | to_char
-----------+-----------------------------------
| 0000000000000000.000000000000000
| 0000000000000000.000000000000000
| -0000000034338492.215397047000000
| 0000000000000004.310000000000000
| 0000000007799461.411900000000000
| 0000000000016397.038491000000000
| 0000000000093901.577630260000000
| -0000000083028485.000000000000000
| 0000000000074881.000000000000000
| -0000000024926804.045047420000000
(10 rows)
SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999') FROM num_data;
to_char_10 | to_char
------------+-----------------------------------
| +0000000000000000.000000000000000
| +0000000000000000.000000000000000
| -0000000034338492.215397047000000
| +0000000000000004.310000000000000
| +0000000007799461.411900000000000
| +0000000000016397.038491000000000
| +0000000000093901.577630260000000
| -0000000083028485.000000000000000
| +0000000000074881.000000000000000
| -0000000024926804.045047420000000
(10 rows)
SELECT '' AS to_char_11, to_char(val, 'FM0999999999999999.999999999999999') FROM num_data;
to_char_11 | to_char
------------+-----------------------------
| 0000000000000000.
| 0000000000000000.
| -0000000034338492.215397047
| 0000000000000004.31
| 0000000007799461.4119
| 0000000000016397.038491
| 0000000000093901.57763026
| -0000000083028485
| 0000000000074881
| -0000000024926804.04504742
(10 rows)
SELECT '' AS to_char_12, to_char(val, 'FM9999999999999999.099999999999999') FROM num_data;
to_char_12 | to_char
------------+---------------------
| .0
| .0
| -34338492.215397047
| 4.31
| 7799461.4119
| 16397.038491
| 93901.57763026
| -83028485.0
| 74881.0
| -24926804.04504742
(10 rows)
SELECT '' AS to_char_13, to_char(val, 'FM9999999999990999.990999999999999') FROM num_data;
to_char_13 | to_char
------------+---------------------
| 0000.000
| 0000.000
| -34338492.215397047
| 0004.310
| 7799461.4119
| 16397.038491
| 93901.57763026
| -83028485.000
| 74881.000
| -24926804.04504742
(10 rows)
SELECT '' AS to_char_14, to_char(val, 'FM0999999999999999.999909999999999') FROM num_data;
to_char_14 | to_char
------------+-----------------------------
| 0000000000000000.00000
| 0000000000000000.00000
| -0000000034338492.215397047
| 0000000000000004.31000
| 0000000007799461.41190
| 0000000000016397.038491
| 0000000000093901.57763026
| -0000000083028485.00000
| 0000000000074881.00000
| -0000000024926804.04504742
(10 rows)
SELECT '' AS to_char_15, to_char(val, 'FM9999999990999999.099999999999999') FROM num_data;
to_char_15 | to_char
------------+---------------------
| 0000000.0
| 0000000.0
| -34338492.215397047
| 0000004.31
| 7799461.4119
| 0016397.038491
| 0093901.57763026
| -83028485.0
| 0074881.0
| -24926804.04504742
(10 rows)
SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data;
to_char_16 | to_char
------------+------------------------------------
| .000000000000000
| .000000000000000
| -34338492.215397047000000
| 4.310000000000000
| 7799461.411900000000000
| 16397.038491000000000
| 93901.577630260000000
| -83028485.000000000000000
| 74881.000000000000000
| -24926804.045047420000000
(10 rows)
SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data;
to_char_17 | to_char
------------+---------------------
| 0.
| 0.
| -34338492.215397047
| 4.31
| 7799461.4119
| 16397.038491
| 93901.57763026
| -83028485
| 74881
| -24926804.04504742
(10 rows)
SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM
num_data;
to_char_18 | to_char
------------+-----------------------------------------------------------------------
| . +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
| . +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
| -3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7 0 0 0 0 0 0 0 0
| +4 . 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
| +7 7 9 9 4 6 1 . 4 1 1 9 0 0 0 0 0 0 0 0 0 0 0 0 0
| +1 6 3 9 7 . 0 3 8 4 9 1 0 0 0 0 0 0 0 0 0 0 0
| +9 3 9 0 1 . 5 7 7 6 3 0 2 6 0 0 0 0 0 0 0 0 0
| -8 3 0 2 8 4 8 5 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
| +7 4 8 8 1 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
| -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 0 0 0 0 0 0 0 0 0
(10 rows)
SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM
num_data;
to_char_19 | to_char
------------+-------------------------------------------------------
| + 0 .
| + 0 .
| - 3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7
| + 4 . 3 1
| + 7 7 9 9 4 6 1 . 4 1 1 9
| + 1 6 3 9 7 . 0 3 8 4 9 1
| + 9 3 9 0 1 . 5 7 7 6 3 0 2 6
| - 8 3 0 2 8 4 8 5
| + 7 4 8 8 1
| - 2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
(10 rows)
SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
num_data;
to_char_20 | to_char
------------+-----------------------------------------------------------
| text 9999 "text between quote marks" 0
| text 9999 "text between quote marks" 0
| text -3 9999 433 "text between quote marks" 8492
| text 9999 "text between quote marks" 4
| text 9999 779 "text between quote marks" 9461
| text 9999 1 "text between quote marks" 6397
| text 9999 9 "text between quote marks" 3902
| text -8 9999 302 "text between quote marks" 8485
| text 9999 7 "text between quote marks" 4881
| text -2 9999 492 "text between quote marks" 6804
(10 rows)
SELECT '' AS to_char_21, to_char(val, '999999SG9999999999') FROM num_data;
to_char_21 | to_char
------------+-------------------
| + 0
| + 0
| - 34338492
| + 4
| + 7799461
| + 16397
| + 93902
| - 83028485
| + 74881
| - 24926804
(10 rows)
SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
to_char_22 | to_char
------------+---------------------
| 0.
| 0.
| -34338492.215397047
| 4.31
| 7799461.4119
| 16397.038491
| 93901.57763026
| -83028485
| 74881
| -24926804.04504742
(10 rows)
-- TO_NUMBER()
--
SELECT '' AS to_number_1, to_number('-34,338,492', '99G999G999');
to_number_1 | to_number
-------------+-----------
| -34338492
(1 row)
SELECT '' AS to_number_2, to_number('-34,338,492.654,878', '99G999G999D999G999');
to_number_2 | to_number
-------------+------------------
| -34338492.654878
(1 row)
SELECT '' AS to_number_3, to_number('<564646.654564>', '999999.999999PR');
to_number_3 | to_number
-------------+----------------
| -564646.654564
(1 row)
SELECT '' AS to_number_4, to_number('0.00001-', '9.999999S');
to_number_4 | to_number
-------------+-----------
| -0.00001
(1 row)
SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999S');
to_number_5 | to_number
-------------+-----------
| -5.01
(1 row)
SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999MI');
to_number_5 | to_number
-------------+-----------
| -5.01
(1 row)
SELECT '' AS to_number_7, to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
to_number_7 | to_number
-------------+-----------
| 544448.78
(1 row)
SELECT '' AS to_number_8, to_number('.01', 'FM9.99');
to_number_8 | to_number
-------------+-----------
| 0.01
(1 row)
SELECT '' AS to_number_9, to_number('.0', '99999999.99999999');
to_number_9 | to_number
-------------+-----------
| 0.0
(1 row)
SELECT '' AS to_number_10, to_number('0', '99.99');
to_number_10 | to_number
--------------+-----------
| 0
(1 row)
SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
to_number_11 | to_number
--------------+-----------
| -0.01
(1 row)
SELECT '' AS to_number_12, to_number('.01-', '99.99S');
to_number_12 | to_number
--------------+-----------
| -0.01
(1 row)
SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
to_number_13 | to_number
--------------+-----------
| -0.01
(1 row)
--
-- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check
--
SELECT ctid, pg_aggregate.aggtransfn
FROM pg_aggregate
WHERE pg_aggregate.aggtransfn != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn);
ctid | aggtransfn
------+------------
(0 rows)
SELECT ctid, pg_aggregate.aggfinalfn
FROM pg_aggregate
WHERE pg_aggregate.aggfinalfn != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn);
ctid | aggfinalfn
------+------------
(0 rows)
SELECT ctid, pg_aggregate.aggbasetype
FROM pg_aggregate
WHERE pg_aggregate.aggbasetype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggbasetype);
ctid | aggbasetype
------+-------------
(0 rows)
SELECT ctid, pg_aggregate.aggtranstype
FROM pg_aggregate
WHERE pg_aggregate.aggtranstype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype);
ctid | aggtranstype
------+--------------
(0 rows)
SELECT ctid, pg_aggregate.aggfinaltype
FROM pg_aggregate
WHERE pg_aggregate.aggfinaltype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggfinaltype);
ctid | aggfinaltype
------+--------------
(0 rows)
SELECT ctid, pg_am.amgettuple
FROM pg_am
WHERE pg_am.amgettuple != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple);
ctid | amgettuple
------+------------
(0 rows)
SELECT ctid, pg_am.aminsert
FROM pg_am
WHERE pg_am.aminsert != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert);
ctid | aminsert
------+----------
(0 rows)
SELECT ctid, pg_am.ambeginscan
FROM pg_am
WHERE pg_am.ambeginscan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan);
ctid | ambeginscan
------+-------------
(0 rows)
SELECT ctid, pg_am.amrescan
FROM pg_am
WHERE pg_am.amrescan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan);
ctid | amrescan
------+----------
(0 rows)
SELECT ctid, pg_am.amendscan
FROM pg_am
WHERE pg_am.amendscan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan);
ctid | amendscan
------+-----------
(0 rows)
SELECT ctid, pg_am.ammarkpos
FROM pg_am
WHERE pg_am.ammarkpos != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos);
ctid | ammarkpos
------+-----------
(0 rows)
SELECT ctid, pg_am.amrestrpos
FROM pg_am
WHERE pg_am.amrestrpos != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos);
ctid | amrestrpos
------+------------
(0 rows)
SELECT ctid, pg_am.ambuild
FROM pg_am
WHERE pg_am.ambuild != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild);
ctid | ambuild
------+---------
(0 rows)
SELECT ctid, pg_am.ambulkdelete
FROM pg_am
WHERE pg_am.ambulkdelete != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete);
ctid | ambulkdelete
------+--------------
(0 rows)
SELECT ctid, pg_am.amcostestimate
FROM pg_am
WHERE pg_am.amcostestimate != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate);
ctid | amcostestimate
------+----------------
(0 rows)
SELECT ctid, pg_amop.amopclaid
FROM pg_amop
WHERE pg_amop.amopclaid != 0 AND
NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid);
ctid | amopclaid
------+-----------
(0 rows)
SELECT ctid, pg_amop.amopopr
FROM pg_amop
WHERE pg_amop.amopopr != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr);
ctid | amopopr
------+---------
(0 rows)
SELECT ctid, pg_amproc.amopclaid
FROM pg_amproc
WHERE pg_amproc.amopclaid != 0 AND
NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid);
ctid | amopclaid
------+-----------
(0 rows)
SELECT ctid, pg_amproc.amproc
FROM pg_amproc
WHERE pg_amproc.amproc != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc);
ctid | amproc
------+--------
(0 rows)
SELECT ctid, pg_attribute.attrelid
FROM pg_attribute
WHERE pg_attribute.attrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid);
ctid | attrelid
------+----------
(0 rows)
SELECT ctid, pg_attribute.atttypid
FROM pg_attribute
WHERE pg_attribute.atttypid != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid);
ctid | atttypid
------+----------
(0 rows)
SELECT ctid, pg_class.reltype
FROM pg_class
WHERE pg_class.reltype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype);
ctid | reltype
------+---------
(0 rows)
SELECT ctid, pg_class.relam
FROM pg_class
WHERE pg_class.relam != 0 AND
NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam);
ctid | relam
------+-------
(0 rows)
SELECT ctid, pg_class.reltoastrelid
FROM pg_class
WHERE pg_class.reltoastrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid);
ctid | reltoastrelid
------+---------------
(0 rows)
SELECT ctid, pg_class.reltoastidxid
FROM pg_class
WHERE pg_class.reltoastidxid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid);
ctid | reltoastidxid
------+---------------
(0 rows)
SELECT ctid, pg_description.classoid
FROM pg_description
WHERE pg_description.classoid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid);
ctid | classoid
------+----------
(0 rows)
SELECT ctid, pg_index.indexrelid
FROM pg_index
WHERE pg_index.indexrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid);
ctid | indexrelid
------+------------
(0 rows)
SELECT ctid, pg_index.indrelid
FROM pg_index
WHERE pg_index.indrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid);
ctid | indrelid
------+----------
(0 rows)
SELECT ctid, pg_opclass.opcamid
FROM pg_opclass
WHERE pg_opclass.opcamid != 0 AND
NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid);
ctid | opcamid
------+---------
(0 rows)
SELECT ctid, pg_opclass.opcintype
FROM pg_opclass
WHERE pg_opclass.opcintype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype);
ctid | opcintype
------+-----------
(0 rows)
SELECT ctid, pg_operator.oprleft
FROM pg_operator
WHERE pg_operator.oprleft != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft);
ctid | oprleft
------+---------
(0 rows)
SELECT ctid, pg_operator.oprright
FROM pg_operator
WHERE pg_operator.oprright != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright);
ctid | oprright
------+----------
(0 rows)
SELECT ctid, pg_operator.oprresult
FROM pg_operator
WHERE pg_operator.oprresult != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult);
ctid | oprresult
------+-----------
(0 rows)
SELECT ctid, pg_operator.oprcom
FROM pg_operator
WHERE pg_operator.oprcom != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom);
ctid | oprcom
------+--------
(0 rows)
SELECT ctid, pg_operator.oprnegate
FROM pg_operator
WHERE pg_operator.oprnegate != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate);
ctid | oprnegate
------+-----------
(0 rows)
SELECT ctid, pg_operator.oprlsortop
FROM pg_operator
WHERE pg_operator.oprlsortop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop);
ctid | oprlsortop
------+------------
(0 rows)
SELECT ctid, pg_operator.oprrsortop
FROM pg_operator
WHERE pg_operator.oprrsortop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop);
ctid | oprrsortop
------+------------
(0 rows)
SELECT ctid, pg_operator.oprcode
FROM pg_operator
WHERE pg_operator.oprcode != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode);
ctid | oprcode
------+---------
(0 rows)
SELECT ctid, pg_operator.oprrest
FROM pg_operator
WHERE pg_operator.oprrest != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest);
ctid | oprrest
------+---------
(0 rows)
SELECT ctid, pg_operator.oprjoin
FROM pg_operator
WHERE pg_operator.oprjoin != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin);
ctid | oprjoin
------+---------
(0 rows)
SELECT ctid, pg_proc.prolang
FROM pg_proc
WHERE pg_proc.prolang != 0 AND
NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang);
ctid | prolang
------+---------
(0 rows)
SELECT ctid, pg_proc.prorettype
FROM pg_proc
WHERE pg_proc.prorettype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype);
ctid | prorettype
------+------------
(0 rows)
SELECT ctid, pg_rewrite.ev_class
FROM pg_rewrite
WHERE pg_rewrite.ev_class != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class);
ctid | ev_class
-------+------------
(0,1) | 49
(0,3) | 1634082865
(2 rows)
SELECT ctid, pg_statistic.starelid
FROM pg_statistic
WHERE pg_statistic.starelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_statistic.starelid);
ctid | starelid
------+----------
(0 rows)
SELECT ctid, pg_statistic.staop1
FROM pg_statistic
WHERE pg_statistic.staop1 != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop1);
ctid | staop1
------+--------
(0 rows)
SELECT ctid, pg_statistic.staop2
FROM pg_statistic
WHERE pg_statistic.staop2 != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop2);
ctid | staop2
------+--------
(0 rows)
SELECT ctid, pg_statistic.staop3
FROM pg_statistic
WHERE pg_statistic.staop3 != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop3);
ctid | staop3
------+--------
(0 rows)
SELECT ctid, pg_trigger.tgrelid
FROM pg_trigger
WHERE pg_trigger.tgrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid);
ctid | tgrelid
------+---------
(0 rows)
SELECT ctid, pg_trigger.tgfoid
FROM pg_trigger
WHERE pg_trigger.tgfoid != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid);
ctid | tgfoid
------+--------
(0 rows)
SELECT ctid, pg_type.typrelid
FROM pg_type
WHERE pg_type.typrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid);
ctid | typrelid
------+----------
(0 rows)
SELECT ctid, pg_type.typelem
FROM pg_type
WHERE pg_type.typelem != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem);
ctid | typelem
------+---------
(0 rows)
SELECT ctid, pg_type.typinput
FROM pg_type
WHERE pg_type.typinput != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput);
ctid | typinput
------+----------
(0 rows)
SELECT ctid, pg_type.typoutput
FROM pg_type
WHERE pg_type.typoutput != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput);
ctid | typoutput
------+-----------
(0 rows)
SELECT ctid, pg_type.typreceive
FROM pg_type
WHERE pg_type.typreceive != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typreceive);
ctid | typreceive
------+------------
(0 rows)
SELECT ctid, pg_type.typsend
FROM pg_type
WHERE pg_type.typsend != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typsend);
ctid | typsend
------+---------
(0 rows)
--
-- CREATE_VIEW
-- Virtual class definitions
-- (this also tests the query rewrite system)
--
CREATE VIEW street AS
SELECT r.name, r.thepath, c.cname AS cname
FROM ONLY road r, real_city c
WHERE c.outline ## r.thepath;
ERROR: Cannot insert a duplicate key into unique index pg_rewrite_rulename_index
CREATE VIEW iexit AS
SELECT ih.name, ih.thepath,
interpt_pp(ih.thepath, r.thepath) AS exit
FROM ihighway ih, ramp r
WHERE ih.thepath ## r.thepath;
ERROR: Cannot insert a duplicate key into unique index pg_rewrite_rulename_index
CREATE VIEW toyemp AS
SELECT name, age, location, 12*salary AS annualsal
FROM emp;
ERROR: Cannot insert a duplicate key into unique index pg_rewrite_rulename_index