Opened 8 years ago

Last modified 4 years ago

#1631 new defect

v.db.join and CHARACTER type columns

Reported by: micha Owned by: grass-dev@…
Priority: normal Milestone: 6.4.6
Component: Shell Scripts Version: svn-develbranch6
Keywords: v.db.join Cc:
CPU: Unspecified Platform: Unspecified

Description

The v.db.join script has a shortcoming in that it does not check the length of datatypes when listing the columns from the 'otable' data table. The script then creates new columns in the target vector with wrong data types. For example, if the 'otable' contains columns of type smallint, integer, bigint these all become integer ("int4") in the final table. Similarly, columns of type real and double are both created as double in the final join. I guess this is a limitation of the postgresql db driver (?)

More serious is the case of character columns. Postgresql, by default, creates a CHARACTER column of size 1, unless the size is explicitly stated in the ADD COLUMN statement.

---Quote from the Postgresql Manual---
The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively. character without length specifier is equivalent to character(1). If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

---end quote---

Since the v.db.join script (using v.db.addcol) creates the new column as type CHARACTER, it becomes length 1 in the joined table. This is almost always wrong.

BTW, this problem does not occur with sqlite based tables since sqlite ignores the length of data types

---Quote from sqlite3 site----
Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

---end quote---

As a possible work around, here's a diff for the v.db.join script which collects both column type and column length, and uses both to send a more correct column definition to v.db.addcol.

[root@MS-SL6 scripts]# diff v.db.join.orig v.db.join 
107a108,109
> # MS: Save column size for CHARACTER VARYING datatype
> COLSIZES=`db.describe -c driver="$driver" database="$database" table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f4`
111c113,126
<   v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
---
> 	coltype=`echo $COLTYPES | cut -d' ' -f$i`
> 	colsize=`echo $COLSIZES | cut -d' ' -f$i`
> 	case "$coltype" in  
> 		CHARACTER)
> 			coldef="VARCHAR($colsize)" 
> 		;;
> 		*)
> 			coldef=`echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`
> 		;;
> 	esac
>  
> # v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
> #	g.message -i "Adding column $col $coldef"
>   v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col $coldef"

Attachments (1)

v.db.join.patch (1.3 KB) - added by micha 8 years ago.
New patch with -u

Download all attachments as: .zip

Change History (6)

comment:1 Changed 8 years ago by neteler

Please regenerate the patch with "diff -u" from the main GRASS source code directory.

Changed 8 years ago by micha

Attachment: v.db.join.patch added

New patch with -u

comment:2 in reply to:  1 Changed 8 years ago by micha

Replying to neteler:

Please regenerate the patch with "diff -u" from the main GRASS source code directory.

Sorry, please try this one

comment:3 Changed 6 years ago by neteler

Keywords: v.db.join added
Milestone: 6.4.36.4.5
Version: 6.4.2svn-develbranch6

As the application of the patch failed, can you please recreate the patch against GRASS 6.5.svn (develbranch_6)? Thanks.

comment:4 Changed 4 years ago by martinl

Milestone: 6.4.5

Ticket retargeted after milestone closed

comment:5 Changed 4 years ago by martinl

Milestone: 6.4.6
Note: See TracTickets for help on using tickets.