YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 510 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/boolean.err b/yql/essentials/tests/postgresql/cases/boolean.err
new file mode 100644
index 0000000000..60a444e2d1
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/boolean.err
@@ -0,0 +1,510 @@
+<sql-statement>
+--
+-- BOOLEAN
+--
+--
+-- sanity check - if this fails go insane!
+--
+SELECT 1 AS one;
+</sql-statement>
+<sql-statement>
+-- ******************testing built-in type bool********************
+-- check bool input syntax
+SELECT true AS true;
+</sql-statement>
+<sql-statement>
+SELECT false AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool '   f           ' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'true' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'test' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'test' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "test"
+
+        	SELECT bool 'test' AS error;
+	^
+<sql-statement>
+SELECT bool 'false' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'foo' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'foo' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "foo"
+
+        	SELECT bool 'foo' AS error;
+	^
+<sql-statement>
+SELECT bool 'y' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'yes' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'yeah' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'yeah' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "yeah"
+
+        	SELECT bool 'yeah' AS error;
+	^
+<sql-statement>
+SELECT bool 'n' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'no' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'nay' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'nay' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "nay"
+
+        	SELECT bool 'nay' AS error;
+	^
+<sql-statement>
+SELECT bool 'on' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'off' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'of' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 'o' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'o' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "o"
+
+        	SELECT bool 'o' AS error;
+	^
+<sql-statement>
+SELECT bool 'on_' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'on_' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "on_"
+
+        	SELECT bool 'on_' AS error;
+	^
+<sql-statement>
+SELECT bool 'off_' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool 'off_' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "off_"
+
+        	SELECT bool 'off_' AS error;
+	^
+<sql-statement>
+SELECT bool '1' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool '11' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool '11' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "11"
+
+        	SELECT bool '11' AS error;
+	^
+<sql-statement>
+SELECT bool '0' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool '000' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool '000' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "000"
+
+        	SELECT bool '000' AS error;
+	^
+<sql-statement>
+SELECT bool '' AS error;
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT bool '' AS error;
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: ""
+
+        	SELECT bool '' AS error;
+	^
+<sql-statement>
+-- and, or, not in qualifications
+SELECT bool 't' or bool 'f' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' and bool 'f' AS false;
+</sql-statement>
+<sql-statement>
+SELECT not bool 'f' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' = bool 'f' AS false;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' <> bool 'f' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' > bool 'f' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 't' >= bool 'f' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'f' < bool 't' AS true;
+</sql-statement>
+<sql-statement>
+SELECT bool 'f' <= bool 't' AS true;
+</sql-statement>
+<sql-statement>
+-- explicit casts to/from text
+SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false;
+</sql-statement>
+<sql-statement>
+SELECT '    true   '::text::boolean AS true,
+       '     FALSE'::text::boolean AS false;
+</sql-statement>
+<sql-statement>
+SELECT true::boolean::text AS true, false::boolean::text AS false;
+</sql-statement>
+<sql-statement>
+SELECT '  tru e '::text::boolean AS invalid;    -- error
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT '  tru e '::text::boolean AS invalid;    -- error
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "  tru e "
+
+        	SELECT '  tru e '::text::boolean AS invalid;    -- error
+	^
+<sql-statement>
+SELECT ''::text::boolean AS invalid;            -- error
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	SELECT ''::text::boolean AS invalid;            -- error
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: ""
+
+        	SELECT ''::text::boolean AS invalid;            -- error
+	^
+<sql-statement>
+CREATE TABLE BOOLTBL1 (f1 bool);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true');
+</sql-statement>
+<sql-statement>
+-- BOOLTBL1 should be full of true's at this point
+SELECT BOOLTBL1.* FROM BOOLTBL1;
+</sql-statement>
+<sql-statement>
+SELECT BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE f1 = bool 'true';
+</sql-statement>
+<sql-statement>
+SELECT BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE f1 <> bool 'false';
+</sql-statement>
+<sql-statement>
+SELECT BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE booleq(bool 'false', f1);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f');
+</sql-statement>
+<sql-statement>
+SELECT BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE f1 = bool 'false';
+</sql-statement>
+<sql-statement>
+CREATE TABLE BOOLTBL2 (f1 bool);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f');
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false');
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False');
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE');
+</sql-statement>
+<sql-statement>
+-- 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');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: YtFill!
+    	-- This is now an invalid expression
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  invalid input syntax for type boolean: "XXX"
+
+        	-- This is now an invalid expression
+	^
+<sql-statement>
+-- BOOLTBL2 should be full of false's at this point
+SELECT BOOLTBL2.* FROM BOOLTBL2;
+</sql-statement>
+<sql-statement>
+SELECT BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 <> BOOLTBL1.f1;
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
+    	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+        -stdin-:<main>:1:1: Error: Duplicated member: f1
+        	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+<sql-statement>
+SELECT BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1);
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
+    	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+        -stdin-:<main>:1:1: Error: Duplicated member: f1
+        	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+<sql-statement>
+SELECT BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false';
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
+    	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+        -stdin-:<main>:1:1: Error: Duplicated member: f1
+        	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+<sql-statement>
+SELECT BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true'
+   ORDER BY BOOLTBL1.f1, BOOLTBL2.f1;
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
+    	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+        -stdin-:<main>:1:1: Error: Duplicated member: f1
+        	SELECT BOOLTBL1.*, BOOLTBL2.*
+	^
+<sql-statement>
+--
+-- SQL syntax
+-- Try all combinations to ensure that we get nothing when we expect nothing
+-- - thomas 2000-01-04
+--
+SELECT f1
+   FROM BOOLTBL1
+   WHERE f1 IS TRUE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL1
+   WHERE f1 IS NOT FALSE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL1
+   WHERE f1 IS FALSE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL1
+   WHERE f1 IS NOT TRUE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL2
+   WHERE f1 IS TRUE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT FALSE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL2
+   WHERE f1 IS FALSE;
+</sql-statement>
+<sql-statement>
+SELECT f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT TRUE;
+</sql-statement>
+<sql-statement>
+--
+-- Tests for BooleanTest
+--
+CREATE TABLE BOOLTBL3 (d text, b bool, o int);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL3 (d, b, o) VALUES ('true', true, 1);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL3 (d, b, o) VALUES ('false', false, 2);
+</sql-statement>
+<sql-statement>
+INSERT INTO BOOLTBL3 (d, b, o) VALUES ('null', null, 3);
+</sql-statement>
+<sql-statement>
+SELECT
+    d,
+    b IS TRUE AS istrue,
+    b IS NOT TRUE AS isnottrue,
+    b IS FALSE AS isfalse,
+    b IS NOT FALSE AS isnotfalse,
+    b IS UNKNOWN AS isunknown,
+    b IS NOT UNKNOWN AS isnotunknown
+FROM booltbl3 ORDER BY o;
+</sql-statement>
+-stdin-:<main>: Fatal: Table metadata loading
+
+    -stdin-:<main>: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.booltbl3
+    
+<sql-statement>
+-- Test to make sure short-circuiting and NULL handling is
+-- correct. Use a table as source to prevent constant simplification
+-- to interfer.
+CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool);
+</sql-statement>
+<sql-statement>
+INSERT INTO booltbl4 VALUES (false, true, null);
+</sql-statement>
+<sql-statement>
+\pset null '(null)'
+</sql-statement>
+<sql-statement>
+-- AND expression need to return null if there's any nulls and not all
+-- of the value are true
+SELECT istrue AND isnul AND istrue FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT istrue AND istrue AND isnul FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isnul AND istrue AND istrue FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isfalse AND isnul AND istrue FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT istrue AND isfalse AND isnul FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isnul AND istrue AND isfalse FROM booltbl4;
+</sql-statement>
+<sql-statement>
+-- OR expression need to return null if there's any nulls and none
+-- of the value is true
+SELECT isfalse OR isnul OR isfalse FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isfalse OR isfalse OR isnul FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isnul OR isfalse OR isfalse FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isfalse OR isnul OR istrue FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT istrue OR isfalse OR isnul FROM booltbl4;
+</sql-statement>
+<sql-statement>
+SELECT isnul OR istrue OR isfalse FROM booltbl4;
+</sql-statement>
+<sql-statement>
+--
+-- 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;
+</sql-statement>
+<sql-statement>
+DROP TABLE  BOOLTBL2;
+</sql-statement>
+<sql-statement>
+DROP TABLE  BOOLTBL3;
+</sql-statement>
+<sql-statement>
+DROP TABLE  BOOLTBL4;
+</sql-statement>