Thread: Wrong column size using MIN/MAX without return result
Hi all,
We've experiencing an issue using the driver version 10.03.0000 under unixODBC 2.3.1 environment.
When we use MIN/MAX functions in a query returning CHAR without results, the column size is wrong. Seems if we use any function that return "text types" without returning tuples the column size is wrong.
--
Below there are some self contained testcases:
** SQL testcase 1 (ERROR):
SQL> DROP TABLE IF EXISTS T1;
SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );
SQL> INSERT INTO T1 VALUES('1');
SQL> INSERT INTO T1 VALUES('22');
SQL> INSERT INTO T1 VALUES('333');
SQL> SELECT MAX(FLD) AS OK FROM T1;
+-----------+
| ok |
+-----------+
| 333 |
+-----------+
SQLRowCount returns 1
1 rows fetched
SQL> SELECT MAX(FLD) AS ERROR FROM T1 WHERE 1 = 0;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| error |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
** SQL testcase 2 (ERROR):
SQL> DROP TABLE IF EXISTS T1;
SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );
SQL> INSERT INTO T1 VALUES('1');
SQL> INSERT INTO T1 VALUES('22');
SQL> INSERT INTO T1 VALUES('333');
SQL> SELECT SUBSTR(FLD,1,5) AS OK FROM T1 WHERE FLD = '333';
+----+
| ok |
+----+
| 333|
+----+
SQLRowCount returns 1
1 rows fetched
SQL> SELECT SUBSTR(FLD,1,5) AS ERROR FROM T1 WHERE 1 = 0;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| error |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
** SQL testcase 3 (OK):
SQL> DROP TABLE IF EXISTS T1;
SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );
SQL> INSERT INTO T1 VALUES('1');
SQL> INSERT INTO T1 VALUES('22');
SQL> INSERT INTO T1 VALUES('333');
SQL> SELECT FLD AS OK FROM T1 WHERE FLD = '333';
+-----------+
| ok |
+-----------+
| 333 |
+-----------+
SQLRowCount returns 1
1 rows fetched
SQL> SELECT FLD AS ERROR FROM T1 WHERE 1 = 0;
+-----------+
| error |
+-----------+
+-----------+
SQLRowCount returns 0
There are some config to fix this weird behaviour?
Regards,
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Hi,
On 2018/09/24 23:49, Fabrízio de Royes Mello wrote:
Hi all,We've experiencing an issue using the driver version 10.03.0000 under unixODBC 2.3.1 environment.When we use MIN/MAX functions in a query returning CHAR without results, the column size is wrong. Seems if we use any function that return "text types" without returning tuples the column size is wrong.
SQL returns NULL for MIN/MAX functions without results.
regards,
Hiroshi Inoue
Below there are some self contained testcases:** SQL testcase 1 (ERROR):SQL> DROP TABLE IF EXISTS T1;SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );SQL> INSERT INTO T1 VALUES('1');SQL> INSERT INTO T1 VALUES('22');SQL> INSERT INTO T1 VALUES('333');SQL> SELECT MAX(FLD) AS OK FROM T1;+-----------+| ok |+-----------+| 333 |+-----------+SQLRowCount returns 11 rows fetchedSQL> SELECT MAX(FLD) AS ERROR FROM T1 WHERE 1 = 0;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| error |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+SQLRowCount returns 11 rows fetched** SQL testcase 2 (ERROR):SQL> DROP TABLE IF EXISTS T1;SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );SQL> INSERT INTO T1 VALUES('1');SQL> INSERT INTO T1 VALUES('22');SQL> INSERT INTO T1 VALUES('333');SQL> SELECT SUBSTR(FLD,1,5) AS OK FROM T1 WHERE FLD = '333';+----+| ok |+----+| 333|+----+SQLRowCount returns 11 rows fetchedSQL> SELECT SUBSTR(FLD,1,5) AS ERROR FROM T1 WHERE 1 = 0;+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| error |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------++-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+SQLRowCount returns 0** SQL testcase 3 (OK):SQL> DROP TABLE IF EXISTS T1;SQL> CREATE TABLE T1 ( FLD char(10) NOT NULL DEFAULT ' ' );SQL> INSERT INTO T1 VALUES('1');SQL> INSERT INTO T1 VALUES('22');SQL> INSERT INTO T1 VALUES('333');SQL> SELECT FLD AS OK FROM T1 WHERE FLD = '333';+-----------+| ok |+-----------+| 333 |+-----------+SQLRowCount returns 11 rows fetchedSQL> SELECT FLD AS ERROR FROM T1 WHERE 1 = 0;+-----------+| error |+-----------++-----------+SQLRowCount returns 0There are some config to fix this weird behaviour?Regards,--Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Fabrízio de Royes Mello wrote: > When we use MIN/MAX functions in a query returning CHAR without results, the column size is wrong. The column size is the property of a column, so the return value of a function does not really have this property. > There are some config to fix this weird behaviour? Have a look at "Unknown Sizes", "Text as LongVarChar", "Max Varchar", and "Max LongVarChar": <https://odbc.postgresql.org/docs/config.html> Regards, Clemens