You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
What is the contents of columns 17 and 18 (targetarrivaltime and targetdeparturetime)? They are both of type TIME according to the header, but I would then expect something like a time value in the output. The first row has "2" and "2" for those values (including quotes). It should be something like 09:42:00 (without quotes).
What do you mean with "limiting the columns"? Do you mean using something like \w80 in mclient or do you mean projecting in the SELECT query?
Can you run the command
mclient -d database -X -s 'select * from kv17' > file
and attach the file?
What is the contents of columns 17 and 18 (targetarrivaltime and
targetdeparturetime)? They are both of type TIME according to the header, but
I would then expect something like a time value in the output. The first row
has "2" and "2" for those values (including quotes). It should be something
like 09:42:00 (without quotes).
What do you mean with "limiting the columns"? Do you mean using something like
\w80 in mclient or do you mean projecting in the SELECT query?
Projecting the query.
But I just selected on the two time fields, and I'm not happy with what I see.
Can you give the result of the queries
select cast(targetarrivaltime as interval hour to second) from kv17;
select cast(cast(targetarrivaltime as interval hour to second) as int) from kv17;
Can you give the result of the queries
select cast(targetarrivaltime as interval hour to second) from kv17;
select cast(cast(targetarrivaltime as interval hour to second) as int) from
kv17;
The problem has to do with negative times.
First of all, a negative time doesn't make sense (the time type is supposed to represent a time-of-day which goes from 00:00:00 to 23:59:59). Negative intervals do make sense, but negative times don't.
Because of that, the server code deals badly with negative times, so that is what's causing the bad behavior.
Of course, the server should do something better: either refuse to allow negative values in a time column, or somehow convert the values to something reasonable on output.
Created attachment 94
Indication what goes in in 'pseudo' SQL
Attached contains the SQL statements that are used to insert the data. Sadly I have no clue how I can get 'marked up queries' so the field values are not 'escaped', but as you can see those are normal times.
Attached file: naarsjoerd-insert.txt (text/plain, 8582 bytes)
Description: Indication what goes in in 'pseudo' SQL
I see that not all rows get values for the two times. Do you have a default value for those columns? I'm guessing you don't, and that therefore the values that are actually inserted are cast(null as time).
If that is the case, there is something wrong with null handling.
I see that not all rows get values for the two times. Do you have a default
value for those columns? I'm guessing you don't, and that therefore the values
that are actually inserted are cast(null as time).
If that is the case, there is something wrong with null handling.
No default values are in there. NULLs are allowed.
I just committed a fix which makes that the query runs.
However, there is still a problem:
When a null value is inserted into a time column, the internal representation of the null value is incorrect. This is masked by the (now fixed) function that converts a time value to a string for output.
Date: 2012-01-04 09:27:23 +0100
From: @skinkie
To: clients devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @grobian
Last updated: 2012-01-26 15:32:02 +0100
Comment 16719
Date: 2012-01-04 09:27:23 +0100
From: @skinkie
User-Agent: Mozilla/5.0 (X11; Linux ppc; rv:5.0) Gecko/20100101 Firefox/5.0
Build Identifier:
note: to disable dropping columns and/or truncating fields use \w-1
sql>select * from kv17;
+-------------+-------+------+------------+---------+------+-------------+------+------+-------------+------+------+-------------+----------+-------+-------+----------+----------+-------------+---------+-------------+-------------+
| messagetype | datao | line | operatingd | journey | rein | timestamp | reas | subr | reasonconte | advi | suba | adviceconte | userstop | passa | lagti | targetar | targetde | journeystop | destina | destination | destination |>
: : wnerc : plan : ay : number : forc : : onty : easo : nt : cety : dvic : nt : code : geseq : me : rivaltim : parturet : type : tioncod : name50 : name16 :>
: : ode : ning : : : emen : : pe : ntyp : : pe : etyp : : : uence : : e : ime : : e : : :>
: : : numb : : : tnum : : : e : : : e : : : numbe : : : : : : : :>
: : : er : : : ber : : : : : : : : : r : : : : : : : :>
+=============+=======+======+============+=========+======+=============+======+======+=============+======+======+=============+==========+=======+=======+==========+==========+=============+=========+=============+=============+
| CANCEL | ARR | N198 | 2007-10-31 | 1021 | 0 | 2007-10-31 | 1 | 19_1 | null | 1 | 2 | null | null | null | null | 2 | 2 | null | null | null | null |
: : : : : : : 10:44:09.00 : : : : : : : : : : : : : : : :
: : : : : : : 0000 : : : : : : : : : : : : : : : :
invalid tuple received from server, got 17 columns, expected 24, ignoring
invalid tuple received from server, got 17 columns, expected 24, ignoring
+-------------+-------+------+------------+---------+------+-------------+------+------+-------------+------+------+-------------+----------+-------+-------+----------+----------+-------------+---------+-------------+-------------+
95 tuples (16.519ms) !2 columns dropped!
sql>\f raw
sql>select * from kv17;
% sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17, sys.kv17 table_name
% messagetype, dataownercode, lineplanningnumber, operatingday, journeynumber, reinforcementnumber, timestamp, reasontype, subreasontype, reasoncontent, advicetype, subadvicetype, advicecontent, userstopcode, passagesequencenumber, lagtime, targetarrivaltime, targetdeparturetime, journeystoptype, destinationcode, destinationname50, destinationname16, destinationdetail16, destinationdisplay16 name
% varchar, varchar, varchar, date, decimal, decimal, timestamp, decimal, varchar, varchar, decimal, varchar, varchar, varchar, decimal, decimal, time, time, varchar, varchar, varchar, varchar, varchar, varchar type
% 17, 5, 4, 10, 7, 3, 26, 4, 4, 68, 4, 1, 41, 8, 5, 5, 8, 8, 12, 7, 20, 12, 15, 15 length
[ "CANCEL", "ARR", "N198", 2007-10-31, 1021, 0, 2007-10-31 10:44:09.000000, 1, "19_1", NULL, 1, "2", NULL, NULL, NULL, NULL, "2", "2", NULL, NULL, NULL, NULL, NULL, NULL ]
[ "SHORTEN", "ARR", "N198", 2007-10-31, 1021, 0, 2007-10-31 10:44:09.000000, NULL, NULL, NULL, NULL, NULL, NULL, "57330090", 1, NULL, 1
[ "SHORTEN", "ARR", "N198", 2007-10-31, 1021, 0, 2007-10-31 10:44:09.000000, NULL, NULL, NULL, NULL, NULL, NULL, "57330090", 1, NULL, 1
Nothing else.
Reproducible: Always
Steps to Reproduce:
MonetDB 5 server v11.8.0 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 3.9GiB available memory, 2 available cpu cores
Libraries:
libpcre: 8.20 2011-10-21 (compiled with 8.20)
openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e 6 Sep 2011)
libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: skinkie@openkvk (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64
Comment 16720
Date: 2012-01-04 09:47:26 +0100
From: @sjoerdmullender
What is the contents of columns 17 and 18 (targetarrivaltime and targetdeparturetime)? They are both of type TIME according to the header, but I would then expect something like a time value in the output. The first row has "2" and "2" for those values (including quotes). It should be something like 09:42:00 (without quotes).
What do you mean with "limiting the columns"? Do you mean using something like \w80 in mclient or do you mean projecting in the SELECT query?
Can you run the command
mclient -d database -X -s 'select * from kv17' > file
and attach the file?
Comment 16721
Date: 2012-01-04 10:06:46 +0100
From: @skinkie
(In reply to comment 1)
Projecting the query.
But I just selected on the two time fields, and I'm not happy with what I see.
sql>select targetarrivaltime, targetdeparturetime from kv17;
+-------------------+---------------------+
| targetarrivaltime | targetdeparturetime |
+===================+=====================+
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| ½½½ | ½½½ |
| 10:00:00 | 10:00:00 |
| 10: | 10: |
| 10: | 10: |
| 10: | 10: |
| 19:28:00 | 19:30:00 |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 19: | 19: |
| 08:17:00 | 08:20:00 |
| 08: | 08: |
| 08: | 08: |
| 08: | 08: |
| 10:00:00 | 10:00:00 |
.....
Comment 16722
Date: 2012-01-04 10:07:21 +0100
From: @skinkie
Created attachment 93
Output from mclient
Comment 16723
Date: 2012-01-04 10:51:40 +0100
From: @grobian
The tuples actually even lack a closing ], so it seems like the reconstruction terminates for the rest of the line.
Comment 16724
Date: 2012-01-04 10:53:58 +0100
From: @grobian
I would be interested to see what jdbcclient (sorry Java) thinks of this query.
Comment 16725
Date: 2012-01-04 12:29:58 +0100
From: @grobian
Ok, there's control chars in the output:
[ "SHORTEN", "ARR", "N198", 2007-10-31, 1021, 0, 2007-10-31 10:44:09. 000000, NULL, NULL, NULL, NULL, NULL, NULL, "57330090", 1, NULL,
1^@7, 1^@7, NULL, NULL, NULL, NULL, NULL, NULL ]
(Vim makes it ^@), so the tuples are complete, but the clients get confused since they treat the null-byte as end of the line.
Comment 16728
Date: 2012-01-04 12:54:03 +0100
From: @sjoerdmullender
Can you give the result of the queries
select cast(targetarrivaltime as interval hour to second) from kv17;
select cast(cast(targetarrivaltime as interval hour to second) as int) from kv17;
Comment 16729
Date: 2012-01-04 13:00:02 +0100
From: @skinkie
(In reply to comment 7)
sql>select cast(targetarrivaltime as interval hour to second) from kv17;
+-------------------------------------------+
| targetarrivaltime |
+===========================================+
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| 36000.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
| 68400.000 |
| -2145600.000 |
| -2145600.000 |
| -2145600.000 |
....
Observe the width of the column above, it is full screen width, don't know if that is cast related.
+-------------------+
| targetarrivaltime |
+===================+
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| 36000000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| 68400000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
| -2145600000 |
Comment 16730
Date: 2012-01-04 13:10:52 +0100
From: @sjoerdmullender
The problem has to do with negative times.
First of all, a negative time doesn't make sense (the time type is supposed to represent a time-of-day which goes from 00:00:00 to 23:59:59). Negative intervals do make sense, but negative times don't.
Because of that, the server code deals badly with negative times, so that is what's causing the bad behavior.
Of course, the server should do something better: either refuse to allow negative values in a time column, or somehow convert the values to something reasonable on output.
Comment 16731
Date: 2012-01-04 13:23:42 +0100
From: @skinkie
(In reply to comment 9)
The "funny" thing is... I am not inserting negative times as far as I can see.
tmi8:targetarrivaltime19:28:00</tmi8:targetarrivaltime>
tmi8:targetarrivaltime08:17:00</tmi8:targetarrivaltime>
tmi8:targetarrivaltime10:00:00</tmi8:targetarrivaltime>
tmi8:targetarrivaltime11:00:00</tmi8:targetarrivaltime>
tmi8:targetarrivaltime12:00:00</tmi8:targetarrivaltime>
tmi8:targetdeparturetime19:30:00</tmi8:targetdeparturetime>
tmi8:targetdeparturetime08:20:00</tmi8:targetdeparturetime>
tmi8:targetdeparturetime10:00:00</tmi8:targetdeparturetime>
tmi8:targetdeparturetime11:01:00</tmi8:targetdeparturetime>
tmi8:targetdeparturetime12:00:00</tmi8:targetdeparturetime>
Attached will give an indication what goes in...
Comment 16732
Date: 2012-01-04 13:25:11 +0100
From: @skinkie
Created attachment 94
Indication what goes in in 'pseudo' SQL
Attached contains the SQL statements that are used to insert the data. Sadly I have no clue how I can get 'marked up queries' so the field values are not 'escaped', but as you can see those are normal times.
Comment 16733
Date: 2012-01-04 13:39:33 +0100
From: @sjoerdmullender
I see that not all rows get values for the two times. Do you have a default value for those columns? I'm guessing you don't, and that therefore the values that are actually inserted are cast(null as time).
If that is the case, there is something wrong with null handling.
Comment 16734
Date: 2012-01-04 14:03:22 +0100
From: @skinkie
(In reply to comment 12)
No default values are in there. NULLs are allowed.
Comment 16737
Date: 2012-01-04 14:59:58 +0100
From: @sjoerdmullender
Changeset 29e91cf9f4f1 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=29e91cf9f4f1
Changeset description:
Comment 16738
Date: 2012-01-04 15:01:51 +0100
From: @sjoerdmullender
I just committed a fix which makes that the query runs.
However, there is still a problem:
When a null value is inserted into a time column, the internal representation of the null value is incorrect. This is masked by the (now fixed) function that converts a time value to a string for output.
Comment 16739
Date: 2012-01-04 15:03:19 +0100
From: @sjoerdmullender
The bug also occurs in Dec2011.
Comment 16740
Date: 2012-01-04 16:27:29 +0100
From: @sjoerdmullender
Changeset c0edd6cd8ecf made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c0edd6cd8ecf
Changeset description:
Comment 16741
Date: 2012-01-04 16:29:39 +0100
From: @sjoerdmullender
Changeset 6e6b3637b011 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6e6b3637b011
Changeset description:
Comment 16742
Date: 2012-01-04 16:31:52 +0100
From: @skinkie
Bedankt :)
Comment 16743
Date: 2012-01-04 16:32:31 +0100
From: @sjoerdmullender
Fixed in Dec2011 branch.
Comment 16825
Date: 2012-01-26 15:32:02 +0100
From: @sjoerdmullender
The Dec2011 version has been release, so declaring this bug as FIXED.
The text was updated successfully, but these errors were encountered: