How do I force V17 ISQL to report right truncation in a SELECT?

[Breck_Carter]

The V17 dbisql GUI can silently partially truncate the SELECT result set when a string right truncation is encountered in the SELECT list.

Update: A simple reproducible...

Using ISQL Version 17.0.10 build 6089, with Data - Show Results in Scrollable Table this works as expected:

SET OPTION on_error = 'Prompt';
SELECT CAST ( STRING ( row_num, ' ', '1234567890' ) AS VARCHAR ( 12 ) ) 
  FROM rowgenerator
 WHERE row_num <= 10
 ORDER BY row_num;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Right truncation of string data
SQLCODE=-638, ODBC 3 State="22001"
Line 2, column 1

With Data - Show Results as Text it displays a partial result set with no error message.

SET OPTION on_error = 'Prompt';
SELECT CAST ( STRING ( row_num, ' ', '1234567890' ) AS VARCHAR ( 12 ) ) 
  FROM rowgenerator
 WHERE row_num <= 10
 ORDER BY row_num;

STRING(rowgenerator.row_num,' ','1234567890') 
--------------------------------------------- 
1 1234567890                                  
2 1234567890                                  
3 1234567890                                  
4 1234567890                                  
5 1234567890                                  
6 1234567890                                  
7 1234567890                                  
8 1234567890                                  
(8 rows)

...end Update

---

Is this a bug, or an enhancement? (I can try to build a simpler reproducible if anyone is interested).

A bad SELECT is shown below; it contains CAST ( ... VARCHAR ( 24 ) ) which should have length 25.

The string_rtruncation option is ON.

The V16 copy of the GUI dbisql.com connected to a 17.0.9.4882 database on the 17.0.10.6089 engine does not return any rows, and correctly reports this exception:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.

Cursor not in a valid state
SQLCODE=-853, ODBC 3 State="24000"

Right truncation of string data
SQLCODE=-638, ODBC 3 State="22001"
Line 1, column 1
(Continuing after error)

The V17 dbisql.com returns 7 rows (there should be 173), and it does not report ANY error.

Active At                       ID Alert                    Status                                     
----------------------- ---------- ------------------------ ------------------------------------------ 
2020-12-24 01:34:50.181          1 28 Long transaction      Clear/Cancelled at 2020-12-24 01:44:25.623 
2020-12-24 00:09:43.433          1 21 Temp space usage      Clear/Cancelled at 2020-12-24 00:15:20.593 
2020-12-24 00:09:05.052          1 22 Conn temp space usage Clear/Cancelled at 2020-12-24 00:15:20.593 
2020-12-23 19:45:41.472          1 28 Long transaction      Clear/Cancelled at 2020-12-23 19:58:44.708 
2020-12-23 07:08:42.696          1 28 Long transaction      Clear/Cancelled at 2020-12-23 17:41:34.735 
2020-12-23 01:34:42.682          1 28 Long transaction      Clear/Cancelled at 2020-12-23 01:44:07.579 
2020-12-23 00:08:23.545          1 21 Temp space usage      Clear/Cancelled at 2020-12-23 00:17:32.238 
(7 rows)

Here is the bad query...

SELECT DATEFORMAT ( alert.alert_in_effect_at, 'yyyy-mm-dd hh:nn:ss.sss' ) AS "Active At",
       sampling_options.sampling_id                                       AS "ID",
       CAST ( STRING ( alert.alert_number,  
              ' ',  
              alert_title.alert_title ) AS VARCHAR ( 24 ) )               AS "Alert",
       IF alert.alert_is_clear_or_cancelled = 'N'
          THEN 'Still Active'
          ELSE STRING ( 
             'Clear/Cancelled at ',
             DATEFORMAT ( COALESCE ( all_clear.alert_all_clear_at,  
                          alert_cancelled.alert_all_clear_at ), 
                         'yyyy-mm-dd hh:nn:ss.sss' ) )
       END IF                                                             AS "Status" 
  FROM sampling_options
       INNER JOIN alert
          ON alert.sampling_id = sampling_options.sampling_id
       LEFT OUTER JOIN all_clear
          ON all_clear.sampling_id        = sampling_options.sampling_id
         AND all_clear.alert_number       = alert.alert_number
         AND all_clear.alert_in_effect_at = alert.alert_in_effect_at
       LEFT OUTER JOIN alert_cancelled
          ON alert_cancelled.sampling_id        = sampling_options.sampling_id
         AND alert_cancelled.alert_number       = alert.alert_number
         AND alert_cancelled.alert_in_effect_at = alert.alert_in_effect_at
      INNER JOIN alert_title
          ON alert.alert_number     = alert_title.alert_number
 WHERE alert.alert_number <> 1
 ORDER BY "ID",
       "Active At" DESC,
       "Alert";

[jack_schueler]

Is there anything interesting in the History pane?

[Breck_Carter]

No, that's the problem. The History pane only shows the SELECT statement which implies the statement worked (it did not).

As shown above Results pane ends with "(7 rows)" which implies the statement worked (it did not).

Generally I don't like using V17 ISQL because of other problems with the History pane, but I am forced to test with it because a lot of customers don't have access to earlier versions of ISQL.

However, this problem is a huge showstopper... ISQL is presenting fake data... hence my question "How do I force V17 ISQL to report string right truncation?"

Oh, oh, why don't I just turn the option off temporarily?

...thank you for the idea! 🙂

[VolkerBarth]

Generally I don't like using V17 ISQL because of other problems with the History pane.

I fully agree...

[Breck_Carter]

Yabut Compare Plans 🙂

[VolkerBarth]

As to your updated sample: Does the behaviour also differ when using the different result set display options with v16 dbisql?

[Breck_Carter]

That is a very good question!

The answer is no and yes.

No, V16 ISQL displays the same "Right truncation of string data" error message in a dialog box for both "Data - Show Results in Scrollable Table" and "Data - Show Results as Text".

Yes, the V16 ISQL dialog box looks and behaves quite differently in the two cases... but you still get an error, unlike V17.

(I'm not going to show the two different V16 dialog boxes, because I don't care... V16 works, it's V17 that's got the problem 🙂

[Breck_Carter]

Here's a ghastly workaround for 17.0.10.6089 dbisql.com...

Learn to live with the mistakes you've made, and turn off string truncation error messages... you will still get fake data, but you'll get all of it 🙂

In the following example, all 173 rows are returned instead of just 7. One of the column values is truncated (see comment below)...

SET TEMPORARY OPTION string_rtruncation = 'Off';

SELECT DATEFORMAT ( alert.alert_in_effect_at, 'yyyy-mm-dd hh:nn:ss.sss' ) AS "Active At",
       sampling_options.sampling_id                                       AS "ID",
       CAST ( STRING ( alert.alert_number,  
              ' ',  
              alert_title.alert_title ) AS VARCHAR ( 24 ) )               AS "Alert",
       IF alert.alert_is_clear_or_cancelled = 'N'
          THEN 'Still Active'
          ELSE STRING ( 
             'Clear/Cancelled at ',
             DATEFORMAT ( COALESCE ( all_clear.alert_all_clear_at,  
                          alert_cancelled.alert_all_clear_at ), 
                         'yyyy-mm-dd hh:nn:ss.sss' ) )
       END IF                                                             AS "Status" 
  FROM sampling_options
       INNER JOIN alert
          ON alert.sampling_id = sampling_options.sampling_id
       LEFT OUTER JOIN all_clear
          ON all_clear.sampling_id        = sampling_options.sampling_id
         AND all_clear.alert_number       = alert.alert_number
         AND all_clear.alert_in_effect_at = alert.alert_in_effect_at
       LEFT OUTER JOIN alert_cancelled
          ON alert_cancelled.sampling_id        = sampling_options.sampling_id
         AND alert_cancelled.alert_number       = alert.alert_number
         AND alert_cancelled.alert_in_effect_at = alert.alert_in_effect_at
      INNER JOIN alert_title
          ON alert.alert_number     = alert_title.alert_number
 WHERE alert.alert_number <> 1
 ORDER BY "ID",
       "Active At" DESC,
       "Alert";

-- Row 9 should display "Uncommitted operations" but it has been truncated...

Active At                       ID Alert                    Status                                     
----------------------- ---------- ------------------------ ------------------------------------------ 
2020-12-24 01:34:50.181          1 28 Long transaction      Clear/Cancelled at 2020-12-24 01:44:25.623 
2020-12-24 00:09:43.433          1 21 Temp space usage      Clear/Cancelled at 2020-12-24 00:15:20.593 
2020-12-24 00:09:05.052          1 22 Conn temp space usage Clear/Cancelled at 2020-12-24 00:15:20.593 
2020-12-23 19:45:41.472          1 28 Long transaction      Clear/Cancelled at 2020-12-23 19:58:44.708 
2020-12-23 07:08:42.696          1 28 Long transaction      Clear/Cancelled at 2020-12-23 17:41:34.735 
2020-12-23 01:34:42.682          1 28 Long transaction      Clear/Cancelled at 2020-12-23 01:44:07.579 
2020-12-23 00:08:23.545          1 21 Temp space usage      Clear/Cancelled at 2020-12-23 00:17:32.238 
2020-12-23 00:07:49.765          1 22 Conn temp space usage Clear/Cancelled at 2020-12-23 00:17:32.238 
2020-12-22 22:40:16.543          1 33 Uncommitted operation Clear/Cancelled at 2020-12-22 22:41:45.804 
...
2020-12-18 01:00:43.198          8 11 ServerName change     Clear/Cancelled at 2020-12-18 01:01:05.085 
(173 rows)

[Breck_Carter]

For Foxhound 5 users who want their adhoc queries to show all the rows regardless of whether displayed string values are being truncated, the following statement can be run once:

SET OPTION ADHOC.string_rtruncation = 'Off';

[VolkerBarth]

Unfortunately the OPTION clause on SELECT does not support that option...