SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: uninitialized data
>Description:
uninitialized data in "insert" and "load data infile"
statements:
numeric columns are initialized to 0 (zero)
string-type columns ignore their default
>How-To-Repeat:
See files later
>Fix:
unknown
>Submitter-Id: Josef Kretz
>Originator:
>Organization:
<organization of PR author (multiple lines)>
>MySQL support: none
>Synopsis: <synopsis of the problem (one line)>
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release: mysql-3.23.41 (Source distribution)
>Server: /usr/local/MySQL/bin/mysqladmin Ver 8.21 Distrib 3.23.41, for sun-solaris2.8
>on sparc
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.41
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/tmp/mysql/mysql.sock
Uptime: 2 hours 18 min 20 sec
Threads: 14 Questions: 197899 Slow queries: 0 Opens: 16 Flush
tables: 1 Open tables: 16 Queries per second avg: 23.843
>Environment:
<machine, os, target, libraries (multiple lines)>
System: SunOS sepp 5.8 Generic_108528-01 sun4u sparc
SUNW,UltraSPARC-IIi-Engine
Architecture: sun4
Some paths: /usr/local/bin/perl /usr/ccs/bin/make /usr/local/bin/gmake
/usr/local/bin/gcc /opt/SUNWspro/bin/cc
GCC: Reading specs from
/usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.0/specs
Configured with: /u/kretz/gnu/gcc-3.0/configure --enable-shared
--disable-static --without-fast-fixincludes --with-as=/usr/local/bin/as
--with-ld=/usr/local/bin/ld
Thread model: posix
gcc version 3.0
Compilation info: CC='gcc' CFLAGS='-O6' CXX='gcc' CXXFLAGS='-O6
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='-s
-L/usr/local/MySQL/lib -L/usr/local/lib
-R/usr/local/MySQL/lib:/usr/local/lib'
LIBC:
-rw-r--r-- 1 root bin 1748448 Mar 10 2000 /lib/libc.a
lrwxrwxrwx 1 root root 11 Nov 2 2000 /lib/libc.so ->
./libc.so.1
-rwxr-xr-x 1 root bin 1126652 Mar 10 2000 /lib/libc.so.1
-rw-r--r-- 1 root bin 1748448 Mar 10 2000 /usr/lib/libc.a
lrwxrwxrwx 1 root root 11 Nov 2 2000 /usr/lib/libc.so
-> ./libc.so.1
-rwxr-xr-x 1 root bin 1126652 Mar 10 2000 /usr/lib/libc.so.1
Configure command: ./configure --prefix=/usr/local/MySQL
--enable-shared --disable-static --with-tcp-port=57643
--with-unix-socket-path=/var/tmp/mysql/mysql.sock --without-debug
--without-low-memory --without-raid --with-pthread --without-mit-threads
--without-comment --localstatedir=/u/MySQL_data --without-bench --with-x
--disable-large-files --with-mysqld-user=kretz
Dear Sirs,
I am running into some difficulties with uninitialized data:
appearently when load data infile contains a sequence like
<tab><tab> - which should leave a column uninitialized -
I get
- 0 (zero) into an integer column
- 0.00 (zero) into a double column
- NULL into a char/varchar column with default null,
which is all right
- NULL into a char/varchar column with default not null,
which is not OK.
when typing
insert into T35 values ( , , ) results in syntax error
insert into T35 values ( , NULL , )
if the inserted to column is integer or double, it is initialized to
zero
if it is a char/varchar column, ut is leaved uninitialized,
regardless of its default value.
I am currently running mysql-3.23.41 under SUN Sparc Solaris 8,
compiled from source using GNU gcc-3.0, and with the following
configure script:
<start of configure script>
#!/bin/csh
setenv CC gcc
setenv CXX gcc
setenv CPP "gcc -E"
setenv CFLAGS "-O6"
setenv CXXFLAGS "-O6 -felide-constructors -fno-exceptions -fno-rtti"
setenv CXXLDFLAGS -lstdc++
setenv LDFLAGS "-s -L/usr/local/MySQL/lib -L/usr/local/lib
-R/usr/local/MySQL/lib:/usr/local/lib"
./configure --prefix=/usr/local/MySQL --enable-shared --disable-static \
--with-tcp-port=57643 --with-unix-socket-path=/var/tmp/mysql/mysql.sock
\
--without-debug --without-low-memory --without-raid \
--with-pthread --without-mit-threads --without-comment \
--localstatedir=/u/MySQL_data --without-bench \
--with-x --disable-large-files \
--with-mysqld-user=kretz
<end of configure script>
The attached files are
t35 ... sql statements to create table T35
a ... sql statements that perform mainly correct, but ARTNR remains
NULL (uninitialized)
a1 ... sql statements that - I think - should work
q ... for load data infile '...' into table T35
load data in file 'q' into table T35
The 1. record has ARTNR initialized to '', although its default is '',
it is set to NULL. The other uninitialied columns are NULL for strings
and zero (initialized !) for numbers.
t35:
drop TABLE if exists T35 ;
CREATE TABLE T35 (
MAND2 varchar(4) default '' ,
ARTNR varchar(30) default '' ,
ARTVAR varchar(4) default '' ,
MAND varchar(4) default '' ,
STLNR varchar(30) default '' ,
STLVAR varchar(4) default '' ,
POSNR varchar(4) default '' ,
SUBPOSNR varchar(4) default '' ,
GUELTIGAB varchar(8) default '' ,
AENDNR varchar(4) default '' ,
GUELTIGBIS int(11),
AUSLAUFKZ smallint(6),
DPA char(1),
ARTBEZ1 varchar(40),
ARTBEZ2 varchar(40),
ZNR varchar(20),
KLASSE varchar(10),
ROHMENGE double(11,2),
ROHMEH char(3),
ROHFUM double(7,3),
ROHFUMA double(7,3),
MENGE double(11,2),
MEH char(3),
FUM double(7,3),
FUMA double(7,3),
SET_MUTATION smallint(1),
SET_DRUCK smallint(1),
VORLAUFZ double(7,2),
VORLAUFZTW smallint(1),
LMM int(11),
BMM int(11),
HMM int(11),
OBERFL double(11,2),
OBERFLMEH char(3),
STUECK double(9,2),
GEWEIGENFERT double(11,2),
GEWZUKTEILE double(11,2),
GEWMEH char(3),
ZUSCHNITTKZ char(1),
TEILLIEFKZ smallint(1),
STLKZ1 char(1),
STLKZ2 char(1),
STLKZ3 char(1),
STLKZ4 char(1),
STLKZ5 char(1),
STLKZ6 char(1),
ARTAENDNR varchar(4),
WST varchar(20),
FUNKGRP varchar(12),
HBKS char(3),
FRSTLA5 varchar(10),
FRSTLA8 varchar(10),
STLTYP smallint(1),
AHBEISTELLKZ smallint(1),
SBZ varchar(4),
SBE varchar(4),
SBAE varchar(4),
EDT int(11),
AEDT int(11)
);
load data infile 'q' into table T35;
a:
insert into T35 values
('1000','','0000','1000','AZ041349','0000','0000','0000','00000000','',
NULL,NULL,NULL,NULL,NULL,'AZ041349','AZ04',NULL,NULL,NULL,
NULL,1.000000,'ST',1.000000,1.000000,1,1,NULL,NULL,NULL,
NULL,NULL,12364.850000,'M2',21468.170000,440732.420000,110856.740000,'KG',NULL,1,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'L',NULL,
NULL,NULL,1,NULL,'lf','lf',NULL,20010120,NULL)
a1:
insert into T35 values
('1000','','0000','1000','AZ041349','0000','0000','0000','00000000','',
,,,,,'AZ041349','AZ04',,,,
,1.000000,'ST',1.000000,1.000000,1,1,,,,
,,12364.850000,'M2',21468.170000,440732.420000,110856.740000,'KG',,1,
,,,,,,,,'L',,
,,1,,'lf','lf',,20010120,)
q:
1000 0000 1000 AZ041349 0000 0000 0000
00000000 AZ041349
AZ04 1.000000 ST
1.000000 1.000000 1 1 12364.850000
M2 21468.170000 440732.420000 110856.740000 KG
1 L1 lf
lf 20010120
1000 HUGO9876 0000 1000 AZ041349 0000 0101
0000 00000000 AZ041349
AZ04 1.000000 ST
1.000000 1.000000 1 1 12364.850000
M2 21468.170000 440732.420000 110856.740000 KG
1 L1 lf
lf 20010120
Best regards from Austria
Josef Kretz
sepp 20%
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
