Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > coalesceTests


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.coalesceTests
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.io.*;
25 import java.sql.*;
26
27 import org.apache.derby.tools.ij;
28
29 /**
30  * Coalesce/Value tests for various datatypes
31  * coalesce/value function takes arguments and returns the first argument that is not null.
32  * The arguments are evaluated in the order in which they are specified, and the result of the
33  * function is the first argument that is not null. The result can be null only if all the arguments
34  * can be null. The selected argument is converted, if necessary, to the attributes of the result.
35  */
36 public class coalesceTests
37 {
38
39     private static String VALID_DATE_STRING = "'2000-01-01'";
40     private static String VALID_TIME_STRING = "'15:30:20'";
41     private static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
42     private static String NULL_VALUE="NULL";
43
44     private static String [] SQLTypes =
45     {
46         "SMALLINT",
47         "INTEGER",
48         "BIGINT",
49         "DECIMAL(10,5)",
50         "REAL",
51         "DOUBLE",
52         "CHAR(60)",
53         "VARCHAR(60)",
54         "LONG VARCHAR",
55         "CHAR(60) FOR BIT DATA",
56         "VARCHAR(60) FOR BIT DATA",
57         "LONG VARCHAR FOR BIT DATA",
58         "CLOB(1k)",
59         "DATE",
60         "TIME",
61         "TIMESTAMP",
62         "BLOB(1k)",
63
64     };
65
66     private static String [] ColumnNames =
67     {
68         "SMALLINTCOL",
69         "INTEGERCOL",
70         "BIGINTCOL",
71         "DECIMALCOL",
72         "REALCOL",
73         "DOUBLECOL",
74         "CHARCOL",
75         "VARCHARCOL",
76         "LONGVARCHARCOL",
77         "CHARFORBITCOL",
78         "VARCHARFORBITCOL",
79         "LVARCHARFORBITCOL",
80         "CLOBCOL",
81         "DATECOL",
82         "TIMECOL",
83         "TIMESTAMPCOL",
84         "BLOBCOL",
85
86     };
87
88  private static String [][]SQLData =
89     {
90         {NULL_VALUE, "0","1","2"}, // SMALLINT
91 {NULL_VALUE,"11","111",NULL_VALUE}, // INTEGER
92 {NULL_VALUE,"22","222","3333"}, // BIGINT
93 {NULL_VALUE,"3.3","3.33",NULL_VALUE}, // DECIMAL(10,5)
94 {NULL_VALUE,"4.4","4.44","4.444"}, // REAL,
95 {NULL_VALUE,"5.5","5.55",NULL_VALUE}, // DOUBLE
96 {NULL_VALUE,"'1992-01-06'","'1992-01-16'",NULL_VALUE}, // CHAR(60)
97 {NULL_VALUE,"'1992-01-07'","'1992-01-17'",VALID_TIME_STRING}, //VARCHAR(60)",
98 {NULL_VALUE,"'1992-01-08'","'1992-01-18'",VALID_TIMESTAMP_STRING}, // LONG VARCHAR
99 {NULL_VALUE,"X'10aa'",NULL_VALUE,"X'10aaaa'"}, // CHAR(60) FOR BIT DATA
100 {NULL_VALUE,"X'10bb'",NULL_VALUE,"X'10bbbb'"}, // VARCHAR(60) FOR BIT DATA
101 {NULL_VALUE,"X'10cc'",NULL_VALUE,"X'10cccc'"}, //LONG VARCHAR FOR BIT DATA
102 {NULL_VALUE,"'13'","'14'",NULL_VALUE}, //CLOB(1k)
103 {NULL_VALUE,VALID_DATE_STRING,VALID_DATE_STRING,NULL_VALUE}, // DATE
104 {NULL_VALUE,VALID_TIME_STRING,VALID_TIME_STRING,NULL_VALUE}, // TIME
105 {NULL_VALUE,VALID_TIMESTAMP_STRING,VALID_TIMESTAMP_STRING,NULL_VALUE}, // TIMESTAMP
106 {NULL_VALUE,NULL_VALUE,NULL_VALUE,NULL_VALUE} // BLOB
107 };
108
109     /**
110        SQL Reference Guide for DB2 has section titled "Rules for result data types" at the following url
111        http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0008480.htm
112
113        I have constructed following table based on various tables and information under "Rules for result data types"
114        This table has FOR BIT DATA TYPES broken out into separate columns for clarity and testing
115     **/
116
117
118     public static final String [][] resultDataTypeRulesTable = {
119
120   // Types. S I B D R D C V L C V L C D T T B
121 // M N I E E O H A O H A O L A I I L
122 // A T G C A U A R N A R N O T M M O
123 // L E I I L B R C G R C G B E E E B
124 // L G N M L H V . H V S
125 // I E T A E A A B A A T
126 // N R L R R I R R A
127 // T C T . . M
128 // H B B P
129 // A I I
130 // R T T
131 /* 0 SMALLINT */ { "SMALLINT", "INTEGER", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
132 /* 1 INTEGER */ { "INTEGER", "INTEGER", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
133 /* 2 BIGINT */ { "BIGINT", "BIGINT", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
134 /* 3 DECIMAL */ { "DECIMAL", "DECIMAL", "DECIMAL", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
135 /* 4 REAL */ { "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "REAL", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
136 /* 5 DOUBLE */ { "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
137 /* 6 CHAR */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CHAR", "VARCHAR", "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "DATE", "TIME", "TIMESTAMP", "ERROR" },
138 /* 7 VARCHAR */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "VARCHAR", "VARCHAR","LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "DATE", "TIME", "TIMESTAMP", "ERROR" },
139 /* 8 LONGVARCHAR */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "LONG VARCHAR", "LONG VARCHAR", "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "ERROR", "ERROR", "ERROR", "ERROR" },
140 /* 9 CHAR FOR BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CHAR () FOR BIT DATA", "VARCHAR () FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
141 /* 10 VARCH. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "VARCHAR () FOR BIT DATA", "VARCHAR () FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
142 /* 11 LONGVAR. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
143 /* 12 CLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CLOB", "CLOB", "CLOB", "ERROR", "ERROR", "ERROR", "CLOB", "ERROR", "ERROR", "ERROR", "ERROR" },
144 /* 13 DATE */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "DATE", "DATE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "DATE", "ERROR", "ERROR", "ERROR" },
145 /* 14 TIME */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIME", "TIME", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIME", "ERROR", "ERROR" },
146 /* 15 TIMESTAMP */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIMESTAMP", "TIMESTAMP", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIMESTAMP", "ERROR" },
147 /* 16 BLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "BLOB" },
148
149     };
150
151     public static void main (String [] argv) throws Throwable
152     {
153         ij.getPropertyArg(argv);
154         Connection conn = ij.startJBMS();
155
156         testCoalesceSyntax(conn);
157
158         tablesForTestingAllDatatypesCombinations(conn);
159         testCompatibleDatatypesCombinations(conn);
160         testAllDatatypesCombinations(conn);
161
162         testDateCoalesce(conn);
163         testTimeCoalesce(conn);
164         testTimeStampCoalesce(conn);
165         testNumericCoalesce(conn);
166         testMiscellaneousCoalesce(conn);
167         testCharCoalesce(conn);
168         testCharForBitDataCoalesce(conn);
169     }
170
171     public static void testCoalesceSyntax( Connection conn) throws Throwable
172     {
173     try {
174             System.out.println("TestA - some syntax testing for Coalesce/Value function");
175
176             PreparedStatement ps;
177             Statement s = conn.createStatement();
178             try {
179             s.executeUpdate("drop table tA");
180             } catch(Exception ex) {}
181             s.executeUpdate("create table tA (c1 int, c2 char(254))");
182             s.executeUpdate("insert into tA (c1) values(1)");
183
184             System.out.println("TestAla - select coalesce from tA will give error because no arguments were supplied to the function");
185             try {
186                 s.executeQuery("select coalesce from tA");
187                 System.out.println("FAIL - should have gotten error for incorrect syntax");
188             }
189             catch (SQLException e) {
190                 if (e.getSQLState().equals("42X04"))
191                     System.out.println("expected exception " + e.getMessage());
192                 else
193                     dumpSQLExceptions(e);
194             }
195
196             System.out.println("TestAlb - select value from tA will give error because no arguments were supplied to the function");
197             try {
198                 s.executeQuery("select value from tA");
199                 System.out.println("FAIL - should have gotten error for incorrect syntax");
200             }
201             catch (SQLException e) {
202                 if (e.getSQLState().equals("42X04"))
203                     System.out.println("expected exception " + e.getMessage());
204                 else
205                     dumpSQLExceptions(e);
206             }
207
208             System.out.println("TestA2a - select coalesce from tA will give error because no arguments were supplied inside the parentheses");
209             try {
210                 s.executeQuery("select coalesce() from tA");
211                 System.out.println("FAIL - should have gotten error for incorrect syntax");
212             }
213             catch (SQLException e) {
214                 if (e.getSQLState().equals("42X01"))
215                     System.out.println("expected exception " + e.getMessage());
216                 else
217                     dumpSQLExceptions(e);
218             }
219
220             System.out.println("TestA2b - select value from tA will give error because no arguments were supplied inside the parentheses");
221             try {
222                 s.executeQuery("select value() from tA");
223                 System.out.println("FAIL - should have gotten error for incorrect syntax");
224             }
225             catch (SQLException e) {
226                 if (e.getSQLState().equals("42X01"))
227                     System.out.println("expected exception " + e.getMessage());
228                 else
229                     dumpSQLExceptions(e);
230             }
231
232             System.out.println("TestA3a - select coalesce from tA with only one argument will give error");
233             try {
234                 s.executeQuery("select coalesce(c1) from tA");
235                 System.out.println("FAIL - should have gotten error for incorrect syntax");
236             }
237             catch (SQLException e) {
238                 if (e.getSQLState().equals("42605"))
239                     System.out.println("expected exception " + e.getMessage());
240                 else
241                     dumpSQLExceptions(e);
242             }
243
244             System.out.println("TestA3b - select value from tA with only one argument will give error");
245             try {
246                 s.executeQuery("select value(c1) from tA");
247                 System.out.println("FAIL - should have gotten error for incorrect syntax");
248             }
249             catch (SQLException e) {
250                 if (e.getSQLState().equals("42605"))
251                     System.out.println("expected exception " + e.getMessage());
252                 else
253                     dumpSQLExceptions(e);
254             }
255
256             System.out.println("TestA4a - select coalesce from tA with incorrect column name will give error");
257             try {
258                 s.executeQuery("select coalesce(c111) from tA");
259                 System.out.println("FAIL - should have gotten error for incorrect syntax");
260             }
261             catch (SQLException e) {
262                 if (e.getSQLState().equals("42X04"))
263                     System.out.println("expected exception " + e.getMessage());
264                 else
265                     dumpSQLExceptions(e);
266             }
267
268             System.out.println("TestA4b - select value from tA with incorrect column name will give error");
269             try {
270                 s.executeQuery("select value(c111) from tA");
271                 System.out.println("FAIL - should have gotten error for incorrect syntax");
272             }
273             catch (SQLException e) {
274                 if (e.getSQLState().equals("42X04"))
275                     System.out.println("expected exception " + e.getMessage());
276                 else
277                     dumpSQLExceptions(e);
278             }
279
280             System.out.println("TestA5a - create table with table name as coalesce and column name as coalesce will pass because coalesce is not a reserved-word");
281             s.executeUpdate("create table coalesce (coalesce int, c12 int)");
282             s.executeUpdate("insert into coalesce(coalesce) values(null)");
283             s.executeUpdate("insert into coalesce values(null,1)");
284             dumpRS(s.executeQuery("select coalesce(coalesce,c12) from coalesce"));
285             s.executeUpdate("drop table coalesce");
286
287             System.out.println("TestA5b - create table with table name as value and column name as value will pass because value is not a reserved-word");
288             s.executeUpdate("create table value (value int, c12 int)");
289             s.executeUpdate("insert into value(value) values(null)");
290             s.executeUpdate("insert into value values(null,1)");
291             dumpRS(s.executeQuery("select coalesce(value,c12) from value"));
292             s.executeUpdate("drop table value");
293
294             System.out.println("TestA6a - All arguments to coalesce function passed as parameters is an error");
295             try {
296                 ps = conn.prepareStatement("select coalesce(?,?) from tA");
297                 System.out.println("FAIL - should have gotten error for using parameters for all the arguments");
298             }
299             catch (SQLException e) {
300                 if (e.getSQLState().equals("42610"))
301                     System.out.println("expected exception " + e.getMessage());
302                 else
303                     dumpSQLExceptions(e);
304             }
305
306             System.out.println("TestA6b - All arguments to value function passed as parameters is an error");
307             try {
308                 ps = conn.prepareStatement("select value(?,?) from tA");
309                 System.out.println("FAIL - should have gotten error for using parameters for all the arguments");
310             }
311             catch (SQLException e) {
312                 if (e.getSQLState().equals("42610"))
313                     System.out.println("expected exception " + e.getMessage());
314                 else
315                     dumpSQLExceptions(e);
316             }
317
318             s.executeUpdate("drop table tA");
319         } catch (SQLException sqle) {
320             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
321             sqle.printStackTrace(System.out);
322         }
323     }
324
325     public static void tablesForTestingAllDatatypesCombinations( Connection conn) throws Throwable
326     {
327         System.out.println("Set up by creating table for testing all datatypes combinations");
328
329         Statement s = conn.createStatement();
330
331         try {
332             s.executeUpdate("DROP TABLE AllDataTypesTable");
333         }
334         catch(SQLException se) {}
335
336         StringBuffer createSQL = new StringBuffer ("create table AllDataTypesTable (");
337         for (int type = 0; type < SQLTypes.length - 1; type++)
338         {
339             createSQL.append(ColumnNames[type] + " " + SQLTypes[type] + ",");
340         }
341         createSQL.append(ColumnNames[SQLTypes.length - 1] + " " + SQLTypes[SQLTypes.length - 1] + ")");
342         System.out.println(createSQL);
343         s.executeUpdate(createSQL.toString());
344
345         for (int row = 0; row < SQLData[0].length; row++)
346         {
347             createSQL = new StringBuffer ("insert into AllDataTypesTable values(");
348             for (int type = 0; type < SQLTypes.length - 1; type++)
349             {
350                 createSQL.append(SQLData[type][row] + ",");
351             }
352             createSQL.append(SQLData[SQLTypes.length - 1][row]+")");
353             System.out.println(createSQL);
354             s.executeUpdate(createSQL.toString());
355         }
356
357         s.close();
358         conn.commit();
359     }
360
361     public static void testAllDatatypesCombinations( Connection conn) throws Throwable
362     {
363         System.out.println("Start testing all datatypes combinations in COALESCE/VALUE function");
364
365         Statement s = conn.createStatement();
366
367         // Try COALESCE with 2 datatype combinations at a time
368 for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
369             for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
370                 try {
371                     String coalesceString = "SELECT COALESCE(" + ColumnNames[firstColumnType] + "," + ColumnNames[secondColumnType] + ") from AllDataTypesTable";
372                     System.out.println(coalesceString);
373                     printExpectedResultDataType(firstColumnType,secondColumnType);
374                     dumpRS(s.executeQuery(coalesceString));
375                     isSupportedCoalesce(firstColumnType,secondColumnType, true);
376                 } catch (SQLException e)
377                 {
378                     if (e.getSQLState().equals("22007"))
379                         System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
380                     else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false) && e.getSQLState().equals("42815"))
381                         System.out.println("expected exception " + e.getMessage());
382                     else
383                         dumpSQLExceptions(e);
384                 }
385                 try {
386                     String valueString = "SELECT VALUE(" + ColumnNames[firstColumnType] + "," + ColumnNames[secondColumnType] + ") from AllDataTypesTable";
387                     System.out.println(valueString);
388                     printExpectedResultDataType(firstColumnType,secondColumnType);
389                     dumpRS(s.executeQuery(valueString));
390                     isSupportedCoalesce(firstColumnType,secondColumnType, true);
391                 } catch (SQLException e)
392                 {
393                     if (e.getSQLState().equals("22007"))
394                         System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
395                     else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false) && e.getSQLState().equals("42815"))
396                         System.out.println("expected exception " + e.getMessage());
397                     else
398                         dumpSQLExceptions(e);
399                 }
400             }
401         }
402     }
403
404     public static void testCompatibleDatatypesCombinations( Connection conn) throws Throwable
405     {
406         System.out.println("Start testing all compatible datatypes combinations in COALESCE/VALUE function");
407
408         Statement s = conn.createStatement();
409
410         for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
411             StringBuffer coalesceString = new StringBuffer ("SELECT COALESCE(" + ColumnNames[firstColumnType]);
412             for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
413                 try {
414                     if (resultDataTypeRulesTable[firstColumnType][secondColumnType].equals("ERROR"))
415                         continue; //the datatypes are incompatible, don't try them in COALESCE/VALUE
416 coalesceString.append("," + ColumnNames[secondColumnType]);
417                     System.out.println(coalesceString + ") from AllDataTypesTable");
418                     dumpRS(s.executeQuery(coalesceString + ") from AllDataTypesTable"));
419                 } catch (SQLException e)
420                 {
421                     if (e.getSQLState().equals("22007"))
422                         System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
423                     else if (isClobWithCharAndDateTypeArguments(coalesceString.toString()) && e.getSQLState().equals("42815"))
424                         System.out.println("expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments " + e.getMessage());
425                     else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false) && e.getSQLState().equals("42815"))
426                         System.out.println("expected exception " + e.getMessage());
427                     else
428                         dumpSQLExceptions(e);
429                 }
430             }
431         }
432     }
433
434     private static void printExpectedResultDataType(int oneType, int anotherType)
435     {
436         String coalesceDescription;
437         if (resultDataTypeRulesTable[oneType][anotherType].equals("ERROR"))
438         {
439             coalesceDescription = " Operands " +
440             SQLTypes[oneType] +
441             " , " + SQLTypes[anotherType] + " are incompatible for Coalesce/Value function";
442         }
443         else
444         {
445             coalesceDescription = " Coalesc/Value with operands " +
446             SQLTypes[oneType] +
447             " , " + SQLTypes[anotherType] + " will have result data type of " + resultDataTypeRulesTable[oneType][anotherType];
448         }
449         System.out.println(coalesceDescription);
450     }
451
452     public static boolean isClobWithCharAndDateTypeArguments(String coalesceString) throws Throwable
453     {
454         if(coalesceString.indexOf("CLOB") != -1)
455         {
456             if(coalesceString.indexOf("CHAR") != -1 && (coalesceString.indexOf("DATE") != -1 || coalesceString.indexOf("TIME") != -1))
457                     return true;
458         }
459         return false;
460     }
461
462     private static boolean isSupportedCoalesce(int oneType, int anotherType, boolean throwError)
463     {
464         String coalesceDescription = " Coalesc/Value with operands " +
465             SQLTypes[oneType] +
466             " , " + SQLTypes[anotherType];
467
468         if (throwError && resultDataTypeRulesTable[oneType][anotherType].equals("ERROR"))
469                 System.out.println("FAIL:" + coalesceDescription +
470                                " should not be supported");
471
472         return (!(resultDataTypeRulesTable[oneType][anotherType].equals("ERROR")));
473     }
474
475     public static void testMiscellaneousCoalesce( Connection conn) throws Throwable
476     {
477     try {
478             Statement s = conn.createStatement();
479             PreparedStatement ps;
480     try {
481             s.executeUpdate("drop table tD");
482     } catch(Exception ex) {}
483             s.executeUpdate("create table tD (c1 int, c2 char(254))");
484             s.executeUpdate("insert into tD (c1,c2) values(1,'abcdefgh')");
485             s.executeUpdate("insert into tD (c1) values(2)");
486
487             System.out.println("TestD - some miscellaneous testing for Coalesce/Value function");
488
489             System.out.println("TestD1a - test coalesce function in values clause");
490             dumpRS(s.executeQuery("values coalesce(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));
491
492             System.out.println("TestD1b - test value function in values clause");
493             dumpRS(s.executeQuery("values value(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));
494
495             System.out.println("TestD2a - First argument to coalesce function passed as parameter with non-null value");
496             ps = conn.prepareStatement("select coalesce(?,c2) from tD");
497             ps.setString(1,"first argument to coalesce");
498             dumpRS(ps.executeQuery());
499
500             System.out.println("TestD2b - First argument to value function passed as parameter with non-null value");
501             ps = conn.prepareStatement("select value(?,c2) from tD");
502             ps.setString(1,"first argument to value");
503             dumpRS(ps.executeQuery());
504
505             System.out.println("TestD3a - First argument to coalesce function passed as parameter with null value");
506             ps = conn.prepareStatement("select coalesce(?,c2) from tD");
507             ps.setNull(1,Types.CHAR);
508             dumpRS(ps.executeQuery());
509
510             System.out.println("TestD3b - First argument to value function passed as parameter with null value");
511             ps = conn.prepareStatement("select value(?,c2) from tD");
512             ps.setNull(1,Types.BIGINT);
513             dumpRS(ps.executeQuery());
514
515             System.out.println("TestD4a - Pass incompatible value for parameter to coalesce function");
516             ps = conn.prepareStatement("select coalesce(c1,?) from tD");
517             try {
518                 ps.setString(1,"abc");
519                 dumpRS(ps.executeQuery());
520                 System.out.println("FAIL - should have gotten error because result type is int and we are trying to pass a parameter of type char");
521             }
522             catch (SQLException e) {
523                 if (e.getSQLState().equals("22018"))
524                     System.out.println("expected exception " + e.getMessage());
525                 else
526                     dumpSQLExceptions(e);
527             }
528
529             s.executeUpdate("drop table tD");
530         } catch (SQLException sqle) {
531             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
532             sqle.printStackTrace(System.out);
533         }
534     }
535
536     public static void testDateCoalesce( Connection conn) throws Throwable
537     {
538     try {
539             Statement s = conn.createStatement();
540             PreparedStatement ps;
541     try {
542             s.executeUpdate("drop table tF");
543     } catch(Exception ex) {}
544             s.executeUpdate("create table tF (dateCol date, charCol char(10), varcharCol varchar(50))");
545             s.executeUpdate("insert into tF values(null, null, null)");
546             s.executeUpdate("insert into tF values(date('1992-01-02'), '1992-01-03', '1992-01-04')");
547
548             System.out.println("TestF - focus on date datatypes");
549             System.out.println("TestF1a - coalesce(dateCol,dateCol)");
550             dumpRSwithScale(s.executeQuery("select coalesce(dateCol,dateCol) from tF"));
551
552             System.out.println("TestF1b - value(dateCol,dateCol)");
553             dumpRSwithScale(s.executeQuery("select value(dateCol,dateCol) from tF"));
554
555             System.out.println("TestF2a - coalesce(dateCol,charCol)");
556             dumpRSwithScale(s.executeQuery("select coalesce(dateCol,charCol) from tF"));
557
558             System.out.println("TestF2b - value(dateCol,charCol)");
559             dumpRSwithScale(s.executeQuery("select value(dateCol,charCol) from tF"));
560
561             System.out.println("TestF3a - coalesce(charCol,dateCol)");
562             dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
563
564             System.out.println("TestF3b - value(charCol,dateCol)");
565             dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
566
567             System.out.println("TestF4a - coalesce(dateCol,varcharCol)");
568             dumpRSwithScale(s.executeQuery("select coalesce(dateCol,charCol) from tF"));
569
570             System.out.println("TestF4b - value(dateCol,varcharCol)");
571             dumpRSwithScale(s.executeQuery("select value(dateCol,charCol) from tF"));
572
573             System.out.println("TestF5a - coalesce(varcharCol,dateCol)");
574             dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
575
576             System.out.println("TestF5b - value(varcharCol,dateCol)");
577             dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
578
579             System.out.println("TestF - Try invalid string representation of date into chars and varchars and then use them in coalesce function with date datatype");
580             s.executeUpdate("insert into tF values(date('1992-01-01'), 'I am char', 'I am varchar')");
581
582             try {
583                 System.out.println("TestF6a - coalesce(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
584                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
585                 System.out.println("TestF6a - should have failed");
586             } catch (SQLException e) {
587                 if (e.getSQLState().equals("22007"))
588                     System.out.println("expected exception " + e.getMessage());
589                 else
590                     dumpSQLExceptions(e);
591             }
592
593             try {
594                 System.out.println("TestF6b - value(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
595                 dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
596                 System.out.println("TestF6b - should have failed");
597             } catch (SQLException e) {
598                 if (e.getSQLState().equals("22007"))
599                     System.out.println("expected exception " + e.getMessage());
600                 else
601                     dumpSQLExceptions(e);
602             }
603
604             try {
605                 System.out.println("TestF7a - coalesce(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
606                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
607                 System.out.println("TestF7a - should have failed");
608             } catch (SQLException e) {
609                 if (e.getSQLState().equals("22007"))
610                     System.out.println("expected exception " + e.getMessage());
611                 else
612                     dumpSQLExceptions(e);
613             }
614
615             try {
616                 System.out.println("TestF7b - value(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
617                 dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
618                 System.out.println("TestF7b - should have failed");
619             } catch (SQLException e) {
620                 if (e.getSQLState().equals("22007"))
621                     System.out.println("expected exception " + e.getMessage());
622                 else
623                     dumpSQLExceptions(e);
624             }
625
626             s.executeUpdate("drop table tF");
627         } catch (SQLException sqle) {
628             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
629             sqle.printStackTrace(System.out);
630         }
631     }
632
633     public static void testTimeStampCoalesce( Connection conn) throws Throwable
634     {
635     try {
636             Statement s = conn.createStatement();
637             PreparedStatement ps;
638     try {
639             s.executeUpdate("drop table tH");
640     } catch(Exception ex) {}
641             s.executeUpdate("create table tH (timestampCol timestamp, charCol char(19), varcharCol varchar(50))");
642             s.executeUpdate("insert into tH values(null, null, null)");
643             s.executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:30'), '1992-01-01 12:30:31', '1992-01-01 12:30:32')");
644
645             System.out.println("TestH - focus on timestamp datatypes");
646             System.out.println("TestH1a - coalesce(timestampCol,timestampCol)");
647             dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,timestampCol) from tH"));
648
649             System.out.println("TestH1b - value(timestampCol,timestampCol)");
650             dumpRSwithScale(s.executeQuery("select value(timestampCol,timestampCol) from tH"));
651
652             System.out.println("TestH2a - coalesce(timestampCol,charCol)");
653             dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,charCol) from tH"));
654
655             System.out.println("TestH2b - value(timestampCol,charCol)");
656             dumpRSwithScale(s.executeQuery("select value(timestampCol,charCol) from tH"));
657
658             System.out.println("TestH3a - coalesce(charCol,timestampCol)");
659             dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
660
661             System.out.println("TestH3b - value(charCol,timestampCol)");
662             dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
663
664             System.out.println("TestH4a - coalesce(timestampCol,varcharCol)");
665             dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,charCol) from tH"));
666
667             System.out.println("TestH4b - value(timestampCol,varcharCol)");
668             dumpRSwithScale(s.executeQuery("select value(timestampCol,charCol) from tH"));
669
670             System.out.println("TestH5a - coalesce(varcharCol,timestampCol)");
671             dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
672
673             System.out.println("TestH5b - value(varcharCol,timestampCol)");
674             dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
675
676             System.out.println("TestH - Try invalid string representation of timestamp into chars and varchars and then use them in coalesce function with timestamp datatype");
677             s.executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:33'), 'I am char', 'I am varchar')");
678
679             try {
680                 System.out.println("TestH6a - coalesce(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
681                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
682                 System.out.println("TestH6a - should have failed");
683             } catch (SQLException e) {
684                 if (e.getSQLState().equals("22007"))
685                     System.out.println("expected exception " + e.getMessage());
686                 else
687                     dumpSQLExceptions(e);
688             }
689
690             try {
691                 System.out.println("TestH6b - value(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
692                 dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
693                 System.out.println("TestH6b - should have failed");
694             } catch (SQLException e) {
695                 if (e.getSQLState().equals("22007"))
696                     System.out.println("expected exception " + e.getMessage());
697                 else
698                     dumpSQLExceptions(e);
699             }
700
701             try {
702                 System.out.println("TestH7a - coalesce(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
703                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
704                 System.out.println("TestH7a - should have failed");
705             } catch (SQLException e) {
706                 if (e.getSQLState().equals("22007"))
707                     System.out.println("expected exception " + e.getMessage());
708                 else
709                     dumpSQLExceptions(e);
710             }
711
712             try {
713                 System.out.println("TestH7b - value(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
714                 dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
715                 System.out.println("TestH7b - should have failed");
716             } catch (SQLException e) {
717                 if (e.getSQLState().equals("22007"))
718                     System.out.println("expected exception " + e.getMessage());
719                 else
720                     dumpSQLExceptions(e);
721             }
722
723             s.executeUpdate("drop table tH");
724         } catch (SQLException sqle) {
725             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
726             sqle.printStackTrace(System.out);
727         }
728     }
729
730     public static void testTimeCoalesce( Connection conn) throws Throwable
731     {
732     try {
733             Statement s = conn.createStatement();
734             PreparedStatement ps;
735     try {
736             s.executeUpdate("drop table tG");
737     } catch(Exception ex) {}
738             s.executeUpdate("create table tG (timeCol time, charCol char(10), varcharCol varchar(50))");
739             s.executeUpdate("insert into tG values(null, null, null)");
740             s.executeUpdate("insert into tG values(time('12:30:30'), '12:30:31', '12:30:32')");
741
742             System.out.println("TestG - focus on time datatypes");
743             System.out.println("TestG1a - coalesce(timeCol,timeCol)");
744             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,timeCol) from tG"));
745
746             System.out.println("TestG1b - value(timeCol,timeCol)");
747             dumpRSwithScale(s.executeQuery("select value(timeCol,timeCol) from tG"));
748
749             System.out.println("TestG2a - coalesce(timeCol,charCol)");
750             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));
751
752             System.out.println("TestG2b - value(timeCol,charCol)");
753             dumpRSwithScale(s.executeQuery("select value(timeCol,charCol) from tG"));
754
755             System.out.println("TestG3a - coalesce(charCol,timeCol)");
756             dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
757
758             System.out.println("TestG3b - value(charCol,timeCol)");
759             dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
760
761             System.out.println("TestG4a - coalesce(timeCol,varcharCol)");
762             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));
763
764             System.out.println("TestG4b - value(timeCol,varcharCol)");
765             dumpRSwithScale(s.executeQuery("select value(timeCol,charCol) from tG"));
766
767             System.out.println("TestG5a - coalesce(varcharCol,timeCol)");
768             dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
769
770             System.out.println("TestG5b - value(varcharCol,timeCol)");
771             dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
772
773             System.out.println("TestG - Try invalid string representation of time into chars and varchars and then use them in coalesce function with time datatype");
774             s.executeUpdate("insert into tG values(time('12:30:33'), 'I am char', 'I am varchar')");
775
776             try {
777                 System.out.println("TestG6a - coalesce(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
778                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
779                 System.out.println("TestG6a - should have failed");
780             } catch (SQLException e) {
781                 if (e.getSQLState().equals("22007"))
782                     System.out.println("expected exception " + e.getMessage());
783                 else
784                     dumpSQLExceptions(e);
785             }
786
787             try {
788                 System.out.println("TestG6b - value(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
789                 dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
790                 System.out.println("TestG6b - should have failed");
791             } catch (SQLException e) {
792                 if (e.getSQLState().equals("22007"))
793                     System.out.println("expected exception " + e.getMessage());
794                 else
795                     dumpSQLExceptions(e);
796             }
797
798             try {
799                 System.out.println("TestG7a - coalesce(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
800                 dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
801                 System.out.println("TestG7a - should have failed");
802             } catch (SQLException e) {
803                 if (e.getSQLState().equals("22007"))
804                     System.out.println("expected exception " + e.getMessage());
805                 else
806                     dumpSQLExceptions(e);
807             }
808
809             try {
810                 System.out.println("TestG7b - value(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
811                 dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
812                 System.out.println("TestG7b - should have failed");
813             } catch (SQLException e) {
814                 if (e.getSQLState().equals("22007"))
815                     System.out.println("expected exception " + e.getMessage());
816                 else
817                     dumpSQLExceptions(e);
818             }
819
820             System.out.println("TestG - Following will work fine with invalid string representation of time because timeCol is not null and hence we don't look at invalid time string in char/varchar columns");
821             System.out.println("TestG8a - coalesce(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
822             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));
823
824             System.out.println("TestG8b - value(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
825             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));
826
827             System.out.println("TestG9a - coalesce(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
828             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,varcharCol) from tG"));
829
830             System.out.println("TestG9b - value(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
831             dumpRSwithScale(s.executeQuery("select coalesce(timeCol,varcharCol) from tG"));
832
833             s.executeUpdate("drop table tG");
834         } catch (SQLException sqle) {
835             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
836             sqle.printStackTrace(System.out);
837         }
838     }
839
840     public static void testNumericCoalesce( Connection conn) throws Throwable
841     {
842     try {
843             Statement s = conn.createStatement();
844             PreparedStatement ps;
845     try {
846             s.executeUpdate("drop table tE");
847     } catch(Exception ex) {}
848             s.executeUpdate("create table tE (smallintCol smallint, intCol integer, bigintCol bigint, decimalCol1 decimal(22,2), decimalCol2 decimal(8,6), decimalCol3 decimal(31,28), realCol real, doubleCol double)");
849             s.executeUpdate("insert into tE values(1, 2, 3, 4, 5.5, 6.6, 7.7, 3.4028235E38)");
850             s.executeUpdate("insert into tE values(null,null,null,null,null,null,null,null)");
851
852             System.out.println("TestE - focus on smallint datatypes");
853             System.out.println("TestE1 - coalesce(smallintCol,smallintCol)");
854             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,smallintCol) from tE"));
855
856             System.out.println("TestE1a - coalesce(smallintCol,intCol)");
857             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,intCol) from tE"));
858
859             System.out.println("TestE1b - coalesce(smallintCol,bigintCol)");
860             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,bigintCol) from tE"));
861
862             System.out.println("TestE1c - coalesce(SMALLINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,5) and if that gives p>31, then p is set to 31");
863             System.out.println("TestE1c1 - coalesce(smallintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
864             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol1) from tE"));
865
866             System.out.println("TestE1c2 - coalesce(smallintCol,decimalCol2) with decimal(8,6) will give result decimal(11,6)");
867             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol2) from tE"));
868
869             System.out.println("TestE1c3 - coalesce(smallintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
870             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol3) from tE"));
871
872             System.out.println("TestE1d - coalesce(smallintCol,realCol)");
873             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,realCol) from tE"));
874
875             System.out.println("TestE1e - coalesce(smallintCol,doubleCol)");
876             dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,doubleCol) from tE"));
877
878             System.out.println("TestE - focus on int datatypes");
879             System.out.println("TestE1 - coalesce(intCol,intCol)");
880             dumpRSwithScale(s.executeQuery("select coalesce(intCol,intCol) from tE"));
881
882             System.out.println("TestE1f - coalesce(intCol,smallintCol)");
883             dumpRSwithScale(s.executeQuery("select coalesce(intCol,smallintCol) from tE"));
884
885             System.out.println("TestE1g - coalesce(intCol,bigintCol)");
886             dumpRSwithScale(s.executeQuery("select coalesce(intCol,bigintCol) from tE"));
887
888             System.out.println("TestE1h - coalesce(INT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,11) and if that gives p>31, then p is set to 31");
889             System.out.println("TestE1h1 - coalesce(intCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
890             dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol1) from tE"));
891
892             System.out.println("TestE1h2 - coalesce(intCol,decimalCol2) with decimal(8,6) will give result decimal(17,6)");
893             dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol2) from tE"));
894
895             System.out.println("TestE1h3 - coalesce(intCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
896             dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol3) from tE"));
897
898             System.out.println("TestE1i - coalesce(intCol,realCol)");
899             dumpRSwithScale(s.executeQuery("select coalesce(intCol,realCol) from tE"));
900
901             System.out.println("TestE1j - coalesce(intCol,doubleCol)");
902             dumpRSwithScale(s.executeQuery("select coalesce(intCol,doubleCol) from tE"));
903
904             System.out.println("TestE - focus on bigint datatypes");
905             System.out.println("TestE1 - coalesce(bigintCol,bigintCol)");
906             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,bigintCol) from tE"));
907
908             System.out.println("TestE1k - coalesce(bigintCol,smallintCol)");
909             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,smallintCol) from tE"));
910
911             System.out.println("TestE1l - coalesce(bigintCol,intCol)");
912             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,intCol) from tE"));
913
914             System.out.println("TestE1m - coalesce(BIGINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,19) and if that gives p>31, then p is set to 31");
915             System.out.println("TestE1m1 - coalesce(bigintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
916             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol1) from tE"));
917
918             System.out.println("TestE1m2 - coalesce(bigintCol,decimalCol2) with decimal(8,6) will give result decimal(21,6)");
919             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol2) from tE"));
920
921             System.out.println("TestE1m3 - coalesce(bigintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
922             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol3) from tE"));
923
924             System.out.println("TestE1n - coalesce(bigintCol,realCol)");
925             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,realCol) from tE"));
926
927             System.out.println("TestE1o - coalesce(bigintCol,doubleCol)");
928             dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,doubleCol) from tE"));
929
930             System.out.println("TestE - focus on decimal datatypes");
931
932             System.out.println("TestE1 - coalesce(DECIMAL,DECIMAL) with decimal(w,x), decimal(y,z) will give result decimal(p,s)");
933             System.out.println(" where p=max(x,z)+max(w-x,y-z), s=max(x,z) and if that gives p>31, then p is set to 31");
934             System.out.println("TestE11 - coalesce(decimalCol1,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
935             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol1) from tE"));
936
937             System.out.println("TestE12 - coalesce(decimalCol1,decimalCol2) with decimal(22,2) and decimal(8,6) will give result decimal(26,6)");
938             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol2) from tE"));
939
940             System.out.println("TestE13 - coalesce(decimalCol1,decimalCol3) with decimal(22,2) and decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
941             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol3) from tE"));
942
943             System.out.println("TestE1p - coalesce(decimalCol1,smallintCol)");
944             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,smallintCol) from tE"));
945
946             System.out.println("TestE1q - coalesce(decimalCol1,intCol)");
947             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,intCol) from tE"));
948
949             System.out.println("TestE1r - coalesce(decimalCol1,bigintCol)");
950             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,bigintCol) from tE"));
951
952             System.out.println("TestE1s - coalesce(decimalCol1,realCol)");
953             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,realCol) from tE"));
954
955             System.out.println("TestE1t - coalesce(decimalCol1,doubleCol)");
956             dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,doubleCol) from tE"));
957
958             System.out.println("TestE - focus on real datatypes");
959             System.out.println("TestE1 - coalesce(realCol,realCol)");
960             dumpRSwithScale(s.executeQuery("select coalesce(realCol,realCol) from tE"));
961
962             System.out.println("TestE1u - coalesce(realCol,smallintCol)");
963             dumpRSwithScale(s.executeQuery("select coalesce(realCol,smallintCol) from tE"));
964
965             System.out.println("TestE1v - coalesce(realCol,intCol)");
966             dumpRSwithScale(s.executeQuery("select coalesce(realCol,intCol) from tE"));
967
968             System.out.println("TestE1w - coalesce(realCol,bigintCol)");
969             dumpRSwithScale(s.executeQuery("select coalesce(realCol,bigintCol) from tE"));
970
971             System.out.println("TestE1x - coalesce(realCol,decimalCol1)");
972             dumpRSwithScale(s.executeQuery("select coalesce(realCol,decimalCol1) from tE"));
973
974             System.out.println("TestE1y - coalesce(realCol,doubleCol)");
975             dumpRSwithScale(s.executeQuery("select coalesce(realCol,doubleCol) from tE"));
976
977             System.out.println("TestE - focus on double datatypes");
978             System.out.println("TestE1 - coalesce(doubleCol,doubleCol)");
979             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,doubleCol) from tE"));
980
981             System.out.println("TestE1z - coalesce(doubleCol,smallintCol)");
982             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,smallintCol) from tE"));
983
984             System.out.println("TestE2a - coalesce(doubleCol,intCol)");
985             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,intCol) from tE"));
986
987             System.out.println("TestE2b - coalesce(doubleCol,bigintCol)");
988             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,bigintCol) from tE"));
989
990             System.out.println("TestE2c - coalesce(doubleCol,decimalCol1)");
991             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,decimalCol1) from tE"));
992
993             System.out.println("TestE2d - coalesce(doubleCol,realCol)");
994             dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,realCol) from tE"));
995
996             s.executeUpdate("drop table tE");
997         } catch (SQLException sqle) {
998             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
999             sqle.printStackTrace(System.out);
1000        }
1001    }
1002
1003    public static void testCharCoalesce( Connection conn) throws Throwable
1004    {
1005    try {
1006            Statement s = conn.createStatement();
1007            PreparedStatement ps;
1008    try {
1009            s.executeUpdate("drop table tB");
1010    } catch(Exception ex) {}
1011            s.executeUpdate("create table tB (c1 char(254), c2 char(40), vc1 varchar(253), vc2 varchar(2000), lvc1 long varchar, lvc2 long varchar, clob1 CLOB(200), clob2 CLOB(33K))");
1012            s.executeUpdate("insert into tB values('c1 not null', 'c2 not null', 'vc1 not null', 'vc2 not null', 'lvc1 not null', 'lvc2 not null', 'clob1 not null', 'clob2 not null')");
1013            s.executeUpdate("insert into tB values('c1 not null but c2 is', null, 'vc1 is not null but vc2 is', null, null, null,null,null)");
1014            s.executeUpdate("insert into tB values(null,'c2 not null but c1 is', null, 'vc2 is not null but vc1 is', 'lvc1 not null again', 'lvc2 not null again', 'clob1 not null again', 'clob2 not null again')");
1015            s.executeUpdate("insert into tB values(null,null, null, null, null, null, null, null)");
1016
1017            System.out.println("TestB - Focus on CHAR as atleast one of the operands");
1018            System.out.println("TestB1a - 2 CHAR operands coalesce(c1,c2) with c1(254) and c2(40)");
1019            dumpRS(s.executeQuery("select coalesce(c1,c2) from tB"));
1020
1021            System.out.println("TestB1b - 2 CHAR operands value(c1,c2) with c1(254) and c2(40)");
1022            dumpRS(s.executeQuery("select value(c1,c2) from tB"));
1023
1024            System.out.println("TestB2a - 2 CHAR operands coalesce(c2,c1) with c2(40) and c1(254)");
1025            dumpRS(s.executeQuery("select coalesce(c2,c1) from tB"));
1026
1027            System.out.println("TestB2b - 2 CHAR operands value(c2,c1) with c2(40) and c1(254)");
1028            dumpRS(s.executeQuery("select value(c2,c1) from tB"));
1029
1030            System.out.println("TestB3a - CHAR and VARCHAR operands coalesce(c1,vc1) with c1(254) and vc1(253)");
1031            dumpRS(s.executeQuery("select coalesce(c1,vc1) from tB"));
1032
1033            System.out.println("TestB3b - CHAR and VARCHAR operands value(c1,vc1) with c1(254) and vc1(253)");
1034            dumpRS(s.executeQuery("select value(c1,vc1) from tB"));
1035
1036            System.out.println("TestB4a - VARCHAR and CHAR operands coalesce(vc1,c1) with vc1(253) and c1(254)");
1037            dumpRS(s.executeQuery("select coalesce(vc1,c1) from tB"));
1038
1039            System.out.println("TestB4b - VARCHAR AND CHAR operands value(vc1,c1) with vc1(253) and c1(254)");
1040            dumpRS(s.executeQuery("select value(vc1,c1) from tB"));
1041
1042            System.out.println("TestB - Focus on VARCHAR as atleast one of the operands");
1043            System.out.println("TestB5a - 2 VARCHAR operands coalesce(vc1,vc2) with vc1(253) and vc2(2000)");
1044            dumpRS(s.executeQuery("select coalesce(vc1,vc2) from tB"));
1045
1046            System.out.println("TestB5b - 2 VARCHAR operands value(vc1,vc2) with vc1(253) and vc2(2000)");
1047            dumpRS(s.executeQuery("select value(vc1,vc2) from tB"));
1048
1049            System.out.println("TestB6a - 2 VARCHAR operands coalesce(vc2,vc1) with vc2(2000) and vc1(253)");
1050            dumpRS(s.executeQuery("select coalesce(vc2,vc1) from tB"));
1051
1052            System.out.println("TestB6b - 2 VARCHAR operands value(vc2,vc1) with vc2(2000) and vc1(253)");
1053            dumpRS(s.executeQuery("select value(vc2,vc1) from tB"));
1054
1055            System.out.println("TestB - Focus on LONG VARCHAR as atleast one of the operands");
1056            System.out.println("TestB7a - CHAR and LONG VARCHAR operands coalesce(c1,lvc1) with c1(254)");
1057            dumpRS(s.executeQuery("select coalesce(c1,lvc1) from tB"));
1058
1059            System.out.println("TestB7b - CHAR and LONG VARCHAR operands value(c1,lvc1) with c1(254)");
1060            dumpRS(s.executeQuery("select value(c1,lvc1) from tB"));
1061
1062            System.out.println("TestB8a - LONG VARCHAR and CHAR operands coalesce(lvc1,c1) with c1(254)");
1063            dumpRS(s.executeQuery("select coalesce(lvc1,c1) from tB"));
1064
1065            System.out.println("TestB8b - LONG VARCHAR and CHAR operands value(lvc1,c1) with c1(254)");
1066            dumpRS(s.executeQuery("select value(lvc1,c1) from tB"));
1067
1068            System.out.println("TestB9a - VARCHAR and LONG VARCHAR operands coalesce(vc1,lvc1) with vc1(253)");
1069            dumpRS(s.executeQuery("select coalesce(vc1,lvc1) from tB"));
1070
1071            System.out.println("TestB9b - VARCHAR and LONG VARCHAR operands value(vc1,lvc1) with vc1(253)");
1072            dumpRS(s.executeQuery("select value(vc1,lvc1) from tB"));
1073
1074            System.out.println("TestB10a - LONG VARCHAR and VARCHAR operands coalesce(lvc1,vc1) with vc1(253)");
1075            dumpRS(s.executeQuery("select coalesce(lvc1,vc1) from tB"));
1076
1077            System.out.println("TestB10b - LONG VARCHAR and VARCHAR operands value(lvc1,vc1) with vc1(253)");
1078            dumpRS(s.executeQuery("select value(lvc1,vc1) from tB"));
1079
1080            System.out.println("TestB11a - LONG VARCHAR and LONG VARCHAR operands coalesce(lvc1,lvc2)");
1081            dumpRS(s.executeQuery("select coalesce(lvc1,lvc2) from tB"));
1082
1083            System.out.println("TestB11b - LONG VARCHAR and LONG VARCHAR operands value(lvc1,lvc2)");
1084            dumpRS(s.executeQuery("select value(lvc1,lvc2) from tB"));
1085
1086            System.out.println("TestB - Focus on CLOB as atleast one of the operands");
1087            System.out.println("TestB12a - CLOB and CHAR operands coalesce(clob1,c1) with clob1(200) and c1(254)");
1088            dumpRS(s.executeQuery("select coalesce(clob1,c1) from tB"));
1089
1090            System.out.println("TestB12b - CLOB and CHAR operands value(clob1,c1) with clob1(200) and c1(254)");
1091            dumpRS(s.executeQuery("select value(clob1,c1) from tB"));
1092
1093            System.out.println("TestB13a - CHAR and CLOB operands coalesce(c1,clob2) with c1(254) and clob2(33K)");
1094            dumpRS(s.executeQuery("select coalesce(c1,clob2) from tB"));
1095
1096            System.out.println("TestB13b - CHAR and CLOB operands value(c1,clob2) with c1(254) and clob2(33K)");
1097            dumpRS(s.executeQuery("select value(c1,clob2) from tB"));
1098
1099            System.out.println("TestB14a - CLOB and VARCHAR operands coalesce(clob1,vc1) with clob1(200) and vc1(253)");
1100            dumpRS(s.executeQuery("select coalesce(clob1,vc1) from tB"));
1101
1102            System.out.println("TestB14b - CLOB and VARCHAR operands value(clob1,vc1) with clob1(200) and vc1(253)");
1103            dumpRS(s.executeQuery("select value(clob1,vc1) from tB"));
1104
1105            System.out.println("TestB15a - VARCHAR and CLOB operands coalesce(vc2,clob2) with vc2(2000) and clob2(33K)");
1106            dumpRS(s.executeQuery("select coalesce(vc2,clob2) from tB"));
1107
1108            System.out.println("TestB15b - VARCHAR and CLOB operands value(vc2,clob2) with vc2(2000) and clob2(33K)");
1109            dumpRS(s.executeQuery("select value(vc2,clob2) from tB"));
1110
1111            System.out.println("TestB16a - CLOB and LONG VARCHAR operands coalesce(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
1112            dumpRS(s.executeQuery("select coalesce(clob1,lvc1) from tB"));
1113
1114            System.out.println("TestB16b - CLOB and LONG VARCHAR operands value(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
1115            dumpRS(s.executeQuery("select value(clob1,lvc1) from tB"));
1116
1117            System.out.println("TestB17a - LONG VARCHAR and CLOB operands coalesce(lvc2,clob2) with clob2(33K). The result length will be 33K since clob length here is > 32700 (long varchar max length)");
1118            dumpRS(s.executeQuery("select coalesce(lvc2,clob2) from tB"));
1119
1120            System.out.println("TestB17b - LONG VARCHAR and CLOB operands value(lvc2,clob2) with clob2(33K). The result length will be 33K since clob length here is > 32700 (long varchar max length)");
1121            dumpRS(s.executeQuery("select value(lvc2,clob2) from tB"));
1122
1123            System.out.println("TestB18a - CLOB and CLOB operands coalesce(clob1,clob2) with clob1(200) and clob2(33K).");
1124            dumpRS(s.executeQuery("select coalesce(clob1,clob2) from tB"));
1125
1126            System.out.println("TestB18b - CLOB and CLOB operands value(clob1,clob2) with clob1(200) and clob2(33K).");
1127            dumpRS(s.executeQuery("select value(clob1,clob2) from tB"));
1128
1129            s.executeUpdate("drop table tB");
1130        } catch (SQLException sqle) {
1131            org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
1132            sqle.printStackTrace(System.out);
1133        }
1134    }
1135
1136    public static void testCharForBitDataCoalesce( Connection conn) throws Throwable
1137    {
1138    try {
1139            Statement s = conn.createStatement();
1140            PreparedStatement ps;
1141    try {
1142            s.executeUpdate("drop table tC");
1143    } catch(Exception ex) {}
1144            s.executeUpdate("create table tC (cbd1 char(254) for bit data, cbd2 char(40) for bit data, vcbd1 varchar(253) for bit data, vcbd2 varchar(2000) for bit data, lvcbd1 long varchar for bit data, lvcbd2 long varchar for bit data, blob1 BLOB(200), blob2 BLOB(33K))");
1145            ps = conn.prepareStatement("insert into tC values (?,?,?,?,?,?,?,?)");
1146            ps.setBytes(1, "cbd1 not null".getBytes("US-ASCII"));
1147            ps.setBytes(2, "cbd2 not null".getBytes("US-ASCII"));
1148            ps.setBytes(3, "vcbd1 not null".getBytes("US-ASCII"));
1149            ps.setBytes(4, "vcbd2 not null".getBytes("US-ASCII"));
1150            ps.setBytes(5, "lvcbd1 not null".getBytes("US-ASCII"));
1151            ps.setBytes(6, "lvcbd2 not null".getBytes("US-ASCII"));
1152            ps.setBytes(7, "blob1 not null".getBytes("US-ASCII"));
1153            ps.setBytes(8, "blob2 not null".getBytes("US-ASCII"));
1154            ps.executeUpdate();
1155            ps.setBytes(1, "cbd1 not null but cbd2 is".getBytes("US-ASCII"));
1156            ps.setBytes(2, null);
1157            ps.setBytes(3, "vcbd1 not null but vcbd2 is".getBytes("US-ASCII"));
1158            ps.setBytes(4, null);
1159            ps.setBytes(5, null);
1160            ps.setBytes(6, null);
1161            ps.setBytes(7, null);
1162            ps.setBytes(8, null);
1163            ps.executeUpdate();
1164            ps.setBytes(1, null);
1165            ps.setBytes(2, "cbd2 not null but cbd1 is".getBytes("US-ASCII"));
1166            ps.setBytes(3, null);
1167            ps.setBytes(4, "vcbd2 not null but vcbd1 is".getBytes("US-ASCII"));
1168            ps.setBytes(5, "lvcbd1 not null again".getBytes("US-ASCII"));
1169            ps.setBytes(6, "lvcbd2 not null again".getBytes("US-ASCII"));
1170            ps.setBytes(7, "blob1 not null again".getBytes("US-ASCII"));
1171            ps.setBytes(8, "blob2 not null again".getBytes("US-ASCII"));
1172            ps.executeUpdate();
1173            ps.setBytes(1, null);
1174            ps.setBytes(2, null);
1175            ps.setBytes(3, null);
1176            ps.setBytes(4, null);
1177            ps.setBytes(5, null);
1178            ps.setBytes(6, null);
1179            ps.setBytes(7, null);
1180            ps.setBytes(8, null);
1181            ps.executeUpdate();
1182
1183            System.out.println("TestC - Focus on CHAR FOR BIT DATA as atleast one of the operands");
1184            System.out.println("TestC1a - 2 CHAR FOR BIT DATA operands coalesce(cbd1,cbd2) with cbd1(254) and cbd2(40)");
1185            dumpRS(s.executeQuery("select coalesce(cbd1,cbd2) from tC"));
1186
1187            System.out.println("TestC1b - 2 CHAR FOR BIT DATA operands value(cbd1,cbd2) with cbd1(254) and cbd2(40)");
1188            dumpRS(s.executeQuery("select value(cbd1,cbd2) from tC"));
1189
1190            System.out.println("TestC2a - 2 CHAR FOR BIT DATA operands coalesce(cbd2,cbd1) with cbd2(40) and cbd1(254)");
1191            dumpRS(s.executeQuery("select coalesce(cbd2,cbd1) from tC"));
1192
1193            System.out.println("TestC2b - 2 CHAR FOR BIT DATA operands value(cbd2,cbd1) with cbd2(40) and cbd1(254)");
1194            dumpRS(s.executeQuery("select value(cbd2,cbd1) from tC"));
1195
1196            System.out.println("TestC3a - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
1197            dumpRS(s.executeQuery("select coalesce(cbd1,vcbd1) from tC"));
1198
1199            System.out.println("TestC3b - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
1200            dumpRS(s.executeQuery("select value(cbd1,vcbd1) from tC"));
1201
1202            System.out.println("TestC4a - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
1203            dumpRS(s.executeQuery("select coalesce(vcbd1,cbd1) from tC"));
1204
1205            System.out.println("TestC4b - VARCHAR FOR BIT DATA AND CHAR FOR BIT DATA operands value(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
1206            dumpRS(s.executeQuery("select value(vcbd1,cbd1) from tC"));
1207
1208            System.out.println("TestC - Focus on VARCHAR FOR BIT DATA as atleast one of the operands");
1209            System.out.println("TestC5a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
1210            dumpRS(s.executeQuery("select coalesce(vcbd1,vcbd2) from tC"));
1211
1212            System.out.println("TestC5b - 2 VARCHAR FOR BIT DATA operands value(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
1213            dumpRS(s.executeQuery("select value(vcbd1,vcbd2) from tC"));
1214
1215            System.out.println("TestC6a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
1216            dumpRS(s.executeQuery("select coalesce(vcbd2,vcbd1) from tC"));
1217
1218            System.out.println("TestC6b - 2 VARCHAR FOR BIT DATA operands value(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
1219            dumpRS(s.executeQuery("select value(vcbd2,vcbd1) from tC"));
1220
1221            System.out.println("TestC - Focus on LONG VARCHAR FOR BIT DATA as atleast one of the operands");
1222            System.out.println("TestC7a - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(cbd1,lvcbd1) with cbd1(254)");
1223            dumpRS(s.executeQuery("select coalesce(cbd1,lvcbd1) from tC"));
1224
1225            System.out.println("TestC7b - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(cbd1,lvcbd1) with cbd1(254)");
1226            dumpRS(s.executeQuery("select value(cbd1,lvcbd1) from tC"));
1227
1228            System.out.println("TestC8a - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(lvcbd1,cbd1) with cbd1(254)");
1229            dumpRS(s.executeQuery("select coalesce(lvcbd1,cbd1) from tC"));
1230
1231            System.out.println("TestC8b - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands value(lvcbd1,cbd1) with cbd1(254)");
1232            dumpRS(s.executeQuery("select value(lvcbd1,cbd1) from tC"));
1233
1234            System.out.println("TestC9a - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(vcbd1,lvcbd1) with vcbd1(253)");
1235            dumpRS(s.executeQuery("select coalesce(vcbd1,lvcbd1) from tC"));
1236
1237            System.out.println("TestC9b - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(vcbd1,lvcbd1) with vcbd1(253)");
1238            dumpRS(s.executeQuery("select value(vcbd1,lvcbd1) from tC"));
1239
1240            System.out.println("TestC10a - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(lvcbd1,vcbd1) with vcbd1(253)");
1241            dumpRS(s.executeQuery("select coalesce(lvcbd1,vcbd1) from tC"));
1242
1243            System.out.println("TestC10b - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(lvcbd1,vcbd1) with vcbd1(253)");
1244            dumpRS(s.executeQuery("select value(lvcbd1,vcbd1) from tC"));
1245
1246            System.out.println("TestC11a - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(lvcbd1,lvcbd2)");
1247            dumpRS(s.executeQuery("select coalesce(lvcbd1,lvcbd2) from tC"));
1248
1249            System.out.println("TestC11b - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(lvcbd1,lvcbd2)");
1250            dumpRS(s.executeQuery("select value(lvcbd1,lvcbd2) from tC"));
1251
1252            System.out.println("TestC - Focus on BLOB as atleast one of the operands");
1253            try {
1254                System.out.println("TestC12a - BLOB and CHAR FOR BIT DATA in coalesce(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1255                dumpRS(s.executeQuery("select coalesce(blob1,cbd1) from tC"));
1256                System.out.println("TestC12a - should have failed");
1257            } catch (SQLException e) {
1258                if (e.getSQLState().equals("42815"))
1259                    System.out.println("expected exception " + e.getMessage());
1260                else
1261                    dumpSQLExceptions(e);
1262            }
1263
1264            try {
1265                System.out.println("TestC12b - BLOB and CHAR FOR BIT DATA in value(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1266                dumpRS(s.executeQuery("select value(blob1,cbd1) from tC"));
1267                System.out.println("TestC12b - should have failed");
1268            } catch (SQLException e) {
1269                if (e.getSQLState().equals("42815"))
1270                    System.out.println("expected exception " + e.getMessage());
1271                else
1272                    dumpSQLExceptions(e);
1273            }
1274
1275            try {
1276                System.out.println("TestC13a - CHAR FOR BIT DATA and BLOB operands coalesce(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1277                dumpRS(s.executeQuery("select coalesce(cbd1,blob2) from tC"));
1278                System.out.println("TestC13a - should have failed");
1279            } catch (SQLException e) {
1280                if (e.getSQLState().equals("42815"))
1281                    System.out.println("expected exception " + e.getMessage());
1282                else
1283                    dumpSQLExceptions(e);
1284            }
1285
1286            try {
1287                System.out.println("TestC13b - CHAR FOR BIT DATA and BLOB operands value(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1288                dumpRS(s.executeQuery("select value(cbd1,blob2) from tC"));
1289                System.out.println("TestC13b - should have failed");
1290            } catch (SQLException e) {
1291                if (e.getSQLState().equals("42815"))
1292                    System.out.println("expected exception " + e.getMessage());
1293                else
1294                    dumpSQLExceptions(e);
1295            }
1296
1297            try {
1298                System.out.println("TestC14a - BLOB and VARCHAR FOR BIT DATA operands coalesce(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1299                dumpRS(s.executeQuery("select coalesce(blob1,vcbd1) from tC"));
1300                System.out.println("TestC14a - should have failed");
1301            } catch (SQLException e) {
1302                if (e.getSQLState().equals("42815"))
1303                    System.out.println("expected exception " + e.getMessage());
1304                else
1305                    dumpSQLExceptions(e);
1306            }
1307
1308            try {
1309                System.out.println("TestC14b - BLOB and VARCHAR FOR BIT DATA operands value(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1310                dumpRS(s.executeQuery("select value(blob1,vcbd1) from tC"));
1311                System.out.println("TestC14b - should have failed");
1312            } catch (SQLException e) {
1313                if (e.getSQLState().equals("42815"))
1314                    System.out.println("expected exception " + e.getMessage());
1315                else
1316                    dumpSQLExceptions(e);
1317            }
1318
1319            try {
1320                System.out.println("TestC15a - VARCHAR FOR BIT DATA and BLOB operands coalesce(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1321                dumpRS(s.executeQuery("select coalesce(vcbd2,blob2) from tC"));
1322                System.out.println("TestC15a - should have failed");
1323            } catch (SQLException e) {
1324                if (e.getSQLState().equals("42815"))
1325                    System.out.println("expected exception " + e.getMessage());
1326                else
1327                    dumpSQLExceptions(e);
1328            }
1329
1330            try {
1331                System.out.println("TestC15b - VARCHAR FOR BIT DATA and BLOB operands value(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1332                dumpRS(s.executeQuery("select value(vcbd2,blob2) from tC"));
1333                System.out.println("TestC15b - should have failed");
1334            } catch (SQLException e) {
1335                if (e.getSQLState().equals("42815"))
1336                    System.out.println("expected exception " + e.getMessage());
1337                else
1338                    dumpSQLExceptions(e);
1339            }
1340
1341            try {
1342                System.out.println("TestC16a - BLOB and LONG VARCHAR FOR BIT DATA operands coalesce(blob1,lvcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1343                dumpRS(s.executeQuery("select coalesce(blob1,lvcbd1) from tC"));
1344                System.out.println("TestC16a - should have failed");
1345            } catch (SQLException e) {
1346                if (e.getSQLState().equals("42815"))
1347                    System.out.println("expected exception " + e.getMessage());
1348                else
1349                    dumpSQLExceptions(e);
1350            }
1351
1352            try {
1353                System.out.println("TestC16b - BLOB and LONG VARCHAR FOR BIT DATA operands coalesce(blob1,lvcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1354                dumpRS(s.executeQuery("select value(blob1,lvcbd1) from tC"));
1355                System.out.println("TestC16b - should have failed");
1356            } catch (SQLException e) {
1357                if (e.getSQLState().equals("42815"))
1358                    System.out.println("expected exception " + e.getMessage());
1359                else
1360                    dumpSQLExceptions(e);
1361            }
1362
1363            try {
1364                System.out.println("TestC17a - LONG VARCHAR FOR BIT DATA and BLOB operands coalesce(lvcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1365                dumpRS(s.executeQuery("select coalesce(lvcbd2,blob2) from tC"));
1366                System.out.println("TestC17a - should have failed");
1367            } catch (SQLException e) {
1368                if (e.getSQLState().equals("42815"))
1369                    System.out.println("expected exception " + e.getMessage());
1370                else
1371                    dumpSQLExceptions(e);
1372            }
1373
1374            try {
1375                System.out.println("TestC17b - LONG VARCHAR FOR BIT DATA and BLOB operands value(lvcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1376                dumpRS(s.executeQuery("select value(lvcbd2,blob2) from tC"));
1377                System.out.println("TestC17b - should have failed");
1378            } catch (SQLException e) {
1379                if (e.getSQLState().equals("42815"))
1380                    System.out.println("expected exception " + e.getMessage());
1381                else
1382                    dumpSQLExceptions(e);
1383            }
1384
1385            System.out.println("TestC18a - BLOB and BLOB operands coalesce(blob1,blob2) with blob1(200) and blob2(33K).");
1386            dumpRS(s.executeQuery("select coalesce(blob1,blob2) from tC"));
1387
1388            System.out.println("TestC18b - BLOB and BLOB operands value(blob1,blob2) with blob1(200) and blob2(33K).");
1389            dumpRS(s.executeQuery("select value(blob1,blob2) from tC"));
1390
1391            s.executeUpdate("drop table tC");
1392        } catch (SQLException sqle) {
1393            org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
1394            sqle.printStackTrace(System.out);
1395        }
1396    }
1397
1398    static private void dumpSQLExceptions (SQLException se) {
1399        System.out.println("FAIL -- unexpected exception: " + se.toString());
1400        while (se != null) {
1401            System.out.print("SQLSTATE("+se.getSQLState()+"):");
1402            se = se.getNextException();
1403        }
1404    }
1405
1406    // lifted from the metadata test
1407 public static void dumpRS(ResultSet s) throws SQLException
1408    {
1409        if (s == null)
1410        {
1411            System.out.println("<NULL>");
1412            return;
1413        }
1414
1415        ResultSetMetaData rsmd = s.getMetaData();
1416
1417        // Get the number of columns in the result set
1418 int numCols = rsmd.getColumnCount();
1419
1420        if (numCols <= 0)
1421        {
1422            System.out.println("(no columns!)");
1423            return;
1424        }
1425
1426        StringBuffer heading = new StringBuffer ("\t ");
1427        StringBuffer underline = new StringBuffer ("\t ");
1428
1429        int len;
1430        // Display column headings
1431 for (int i=1; i<=numCols; i++)
1432        {
1433            if (i > 1)
1434            {
1435                heading.append(",");
1436                underline.append(" ");
1437            }
1438            len = heading.length();
1439            heading.append("COL"+i);
1440            heading.append("(datatype : " + rsmd.getColumnTypeName(i));
1441            heading.append(", precision : " + rsmd.getPrecision(i));
1442            heading.append(", scale : " + rsmd.getScale(i) + ")");
1443            len = heading.length() - len;
1444            for (int j = len; j > 0; j--)
1445            {
1446                underline.append("-");
1447            }
1448        }
1449        System.out.println(heading.toString());
1450        System.out.println(underline.toString());
1451
1452
1453        StringBuffer row = new StringBuffer ();
1454        // Display data, fetching until end of the result set
1455 while (s.next())
1456        {
1457            row.append("\t{");
1458            // Loop through each column, getting the
1459 // column data and displaying
1460 for (int i=1; i<=numCols; i++)
1461            {
1462                if (i > 1) row.append(",");
1463                try{
1464                row.append(s.getString(i));
1465                } catch(SQLException ex){
1466                    if (ex.getSQLState().equals("22005"))
1467                        row.append("Invalid Conversion Error\n");
1468                    else throw ex;
1469                }
1470            }
1471            row.append("}\n");
1472        }
1473        System.out.println(row.toString());
1474        s.close();
1475    }
1476
1477    // lifted from the metadata test
1478 public static void dumpRSwithScale(ResultSet s) throws SQLException
1479    {
1480        if (s == null)
1481        {
1482            System.out.println("<NULL>");
1483            return;
1484        }
1485
1486        ResultSetMetaData rsmd = s.getMetaData();
1487
1488        // Get the number of columns in the result set
1489 int numCols = rsmd.getColumnCount();
1490
1491        if (numCols <= 0)
1492        {
1493            System.out.println("(no columns!)");
1494            return;
1495        }
1496
1497        StringBuffer heading = new StringBuffer ("\t ");
1498        StringBuffer underline = new StringBuffer ("\t ");
1499
1500        int len;
1501        // Display column headings
1502 for (int i=1; i<=numCols; i++)
1503        {
1504            if (i > 1)
1505            {
1506                heading.append(",");
1507                underline.append(" ");
1508            }
1509            len = heading.length();
1510            heading.append("COL"+i);
1511            heading.append("(datatype : " + rsmd.getColumnTypeName(i));
1512            heading.append(", precision : " + rsmd.getPrecision(i));
1513            heading.append(", scale : " + rsmd.getScale(i) + ")");
1514            len = heading.length() - len;
1515            for (int j = len; j > 0; j--)
1516            {
1517                underline.append("-");
1518            }
1519        }
1520        System.out.println(heading.toString());
1521        System.out.println(underline.toString());
1522
1523
1524        StringBuffer row = new StringBuffer ();
1525        // Display data, fetching until end of the result set
1526 while (s.next())
1527        {
1528            row.append("\t{");
1529            // Loop through each column, getting the
1530 // column data and displaying
1531 for (int i=1; i<=numCols; i++)
1532            {
1533                if (i > 1) row.append(",");
1534                row.append(s.getString(i));
1535            }
1536            row.append("}\n");
1537        }
1538        System.out.println(row.toString());
1539        s.close();
1540    }
1541
1542}
1543

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags