Changeset: 7eb1f93159c8 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7eb1f93159c8 Added Files: sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.sql sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.err sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.out Modified Files: sql/test/BugTracker-2018/Tests/All Branch: Mar2018 Log Message:
added test for bug 6638 "(sequences of) mkey.bulk_rotate_xor_hash() can generate NIL from non-NIL making multi-col joins return wrong results" diffs (truncated from 869 to 300 lines): diff --git a/sql/test/BugTracker-2018/Tests/All b/sql/test/BugTracker-2018/Tests/All --- a/sql/test/BugTracker-2018/Tests/All +++ b/sql/test/BugTracker-2018/Tests/All @@ -74,3 +74,4 @@ singleton-stddev.Bug-6627 create_table_if_not_exists_returns_42000.Bug-6629 sqlitelogictest-cast-null-add.Bug-6630 ilike-foregin-characters.Bug-6633 +multi-column-hash-wrongly-NIL.Bug-6638 diff --git a/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.sql b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.sql @@ -0,0 +1,73 @@ + +-- sample value constellationa where multi-column joins drop results +-- (false-negatives) because (sequences of) mkey.bulk_rotate_xor_hash() +-- produce NIL (which then does not match/join) + +start transaction; + +create table r2 (a bigint, b bigint, x bigint generated always as identity primary key); +insert into r2 (a,b) values (11,21),(12,22),(13,23),(2199023255552,0),(0,2199023255552),(6597069766656,1),(1,6597069766656),(4398046511104,-9223372036854775807),(-9223372036854775807,4398046511104),(1,-9223372036850581504),(-9223372036850581504,1),(-1,9223372036854775807),(9223372036854775807,-1); +create table s2 as (select a, b, -x as y from r2 order by x desc) with data; +select * from r2; +select * from s2; +select * from r2 natural join s2 order by x,y; +select * from s2 natural join r2 order by x,y; + +create table r3 (a bigint, b bigint, c bigint, x bigint generated always as identity primary key); +insert into r3 (a,b,c) values (11,21,31),(12,22,32),(13,23,33),(2147483648,0,0),(0,2147483648,0),(0,0,2147483648),(140737488355328,0,0),(0,140737488355328,0),(0,0,140737488355328); +create table s3 as (select a, b, c, -x as y from r3 order by x desc) with data; +select * from r3; +select * from s3; +select * from r3 natural join s3 order by x,y; +select * from s3 natural join r3 order by x,y; + +create table r4 (a bigint, b bigint, c bigint, d bigint, x bigint generated always as identity primary key); +insert into r4 (a,b,c,d) values (11,21,31,41),(12,22,32,42),(13,23,33,43),(16777216,0,0,0),(0,16777216,0,0),(0,0,16777216,0),(0,0,0,16777216),(1125899906842624,0,0,0),(0,1125899906842624,0,0),(0,0,1125899906842624,0),(0,0,0,1125899906842624); +create table s4 as (select a, b, c, d, -x as y from r4 order by x desc) with data; +select * from r4; +select * from s4; +select * from r4 natural join s4 order by x,y; +select * from s4 natural join r4 order by x,y; + +create table r5 (a bigint, b bigint, c bigint, d bigint, e bigint, x bigint generated always as identity primary key); +insert into r5 (a,b,c,d,e) values (11,21,31,41,51),(12,22,32,42,52),(13,23,33,43,53),(524288,0,0,0,0),(0,524288,0,0,0),(0,0,524288,0,0),(0,0,0,524288,0),(0,0,0,0,524288),(4503599627370496,0,0,0,0),(0,4503599627370496,0,0,0),(0,0,4503599627370496,0,0),(0,0,0,4503599627370496,0),(0,0,0,0,4503599627370496); +create table s5 as (select a, b, c, d, e, -x as y from r5 order by x desc) with data; +select * from r5; +select * from s5; +select * from r5 natural join s5 order by x,y; +select * from s5 natural join r5 order by x,y; + +create table r6 (a bigint, b bigint, c bigint, d bigint, e bigint, f bigint, x bigint generated always as identity primary key); +insert into r6 (a,b,c,d,e,f) values (11,21,31,41,51,61),(12,22,32,42,52,62),(13,23,33,43,53,63),(8192,0,0,0,0,0),(0,8192,0,0,0,0),(0,0,8192,0,0,0),(0,0,0,8192,0,0),(0,0,0,0,8192,0),(0,0,0,0,0,8192),(9007199254740992,0,0,0,0,0),(0,9007199254740992,0,0,0,0),(0,0,9007199254740992,0,0,0),(0,0,0,9007199254740992,0,0),(0,0,0,0,9007199254740992,0),(0,0,0,0,0,9007199254740992); +create table s6 as (select a, b, c, d, e, f, -x as y from r6 order by x desc) with data; +select * from r6; +select * from s6; +select * from r6 natural join s6 order by x,y; +select * from s6 natural join r6 order by x,y; + +create table r7 (a bigint, b bigint, c bigint, d bigint, e bigint, f bigint, g bigint, x bigint generated always as identity primary key); +insert into r7 (a,b,c,d,e,f,g) values (11,21,31,41,51,61,71),(12,22,32,42,52,62,72),(13,23,33,43,53,63,73),(32768,0,0,0,0,0,0),(0,32768,0,0,0,0,0),(0,0,32768,0,0,0,0),(0,0,0,32768,0,0,0),(0,0,0,0,32768,0,0),(0,0,0,0,0,32768,0),(0,0,0,0,0,0,32768),(36028797018963968,0,0,0,0,0,0),(0,36028797018963968,0,0,0,0,0),(0,0,36028797018963968,0,0,0,0),(0,0,0,36028797018963968,0,0,0),(0,0,0,0,36028797018963968,0,0),(0,0,0,0,0,36028797018963968,0),(0,0,0,0,0,0,36028797018963968); +create table s7 as (select a, b, c, d, e, f, g, -x as y from r7 order by x desc) with data; +select * from r7; +select * from s7; +select * from r7 natural join s7 order by x,y; +select * from s7 natural join r7 order by x,y; + +create table r8 (a bigint, b bigint, c bigint, d bigint, e bigint, f bigint, g bigint, h bigint, x bigint generated always as identity primary key); +insert into r8 (a,b,c,d,e,f,g,h) values (11,21,31,41,51,61,71,81),(12,22,32,42,52,62,72,82),(13,23,33,43,53,63,73,83),(128,0,0,0,0,0,0,0),(0,128,0,0,0,0,0,0),(0,0,128,0,0,0,0,0),(0,0,0,128,0,0,0,0),(0,0,0,0,128,0,0,0),(0,0,0,0,0,128,0,0),(0,0,0,0,0,0,128,0),(0,0,0,0,0,0,0,128),(36028797018963968,0,0,0,0,0,0,0),(0,36028797018963968,0,0,0,0,0,0),(0,0,36028797018963968,0,0,0,0,0),(0,0,0,36028797018963968,0,0,0,0),(0,0,0,0,36028797018963968,0,0,0),(0,0,0,0,0,36028797018963968,0,0),(0,0,0,0,0,0,36028797018963968,0),(0,0,0,0,0,0,0,36028797018963968); +create table s8 as (select a, b, c, d, e, f, g, h, -x as y from r8 order by x desc) with data; +select * from r8; +select * from s8; +select * from r8 natural join s8 order by x,y; +select * from s8 natural join r8 order by x,y; + +create table r9 (a bigint, b bigint, c bigint, d bigint, e bigint, f bigint, g bigint, h bigint, i bigint, x bigint generated always as identity primary key); +insert into r9 (a,b,c,d,e,f,g,h,i) values (11,21,31,41,51,61,71,81,91),(12,22,32,42,52,62,72,82,92),(13,23,33,43,53,63,73,83,93),(128,0,0,0,0,0,0,0,0),(0,128,0,0,0,0,0,0,0),(0,0,128,0,0,0,0,0,0),(0,0,0,128,0,0,0,0,0),(0,0,0,0,128,0,0,0,0),(0,0,0,0,0,128,0,0,0),(0,0,0,0,0,0,128,0,0),(0,0,0,0,0,0,0,128,0),(0,0,0,0,0,0,0,0,128),(72057594037927936,0,0,0,0,0,0,0,0),(0,72057594037927936,0,0,0,0,0,0,0),(0,0,72057594037927936,0,0,0,0,0,0),(0,0,0,72057594037927936,0,0,0,0,0),(0,0,0,0,72057594037927936,0,0,0,0),(0,0,0,0,0,72057594037927936,0,0,0),(0,0,0,0,0,0,72057594037927936,0,0),(0,0,0,0,0,0,0,72057594037927936,0),(0,0,0,0,0,0,0,0,72057594037927936); +create table s9 as (select a, b, c, d, e, f, g, h, i, -x as y from r9 order by x desc) with data; +select * from r9; +select * from s9; +select * from r9 natural join s9 order by x,y; +select * from s9 natural join r9 order by x,y; + +rollback; + diff --git a/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.err b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.err @@ -0,0 +1,35 @@ +stderr of test 'multi-column-hash-wrongly-NIL.Bug-6638` in directory 'sql/test/BugTracker-2018` itself: + + +# 16:21:04 > +# 16:21:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=34059" "--set" "mapi_usock=/var/tmp/mtest-9105/.s.monetdb.34059" "--set" "monet_prompt=" "--forcemito" "--dbpath=/ufs/manegold/_/Monet/release/prefix/_/var/MonetDB/mTests_sql_test_BugTracker-2018" "--set" "embedded_c=true" +# 16:21:04 > + +# builtin opt gdk_dbpath = /ufs/manegold/_/Monet/release/prefix/_/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 34059 +# cmdline opt mapi_usock = /var/tmp/mtest-9105/.s.monetdb.34059 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /ufs/manegold/_/Monet/release/prefix/_/var/MonetDB/mTests_sql_test_BugTracker-2018 +# cmdline opt embedded_c = true +# cmdline opt gdk_debug = 553648138 + +# 16:21:04 > +# 16:21:04 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-9105" "--port=34059" +# 16:21:04 > + + +# 16:21:04 > +# 16:21:04 > "Done." +# 16:21:04 > + diff --git a/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.out b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/multi-column-hash-wrongly-NIL.Bug-6638.stable.out @@ -0,0 +1,738 @@ +stdout of test 'multi-column-hash-wrongly-NIL.Bug-6638` in directory 'sql/test/BugTracker-2018` itself: + + +# 16:21:04 > +# 16:21:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=34059" "--set" "mapi_usock=/var/tmp/mtest-9105/.s.monetdb.34059" "--set" "monet_prompt=" "--forcemito" "--dbpath=/ufs/manegold/_/Monet/release/prefix/_/var/MonetDB/mTests_sql_test_BugTracker-2018" "--set" "embedded_c=true" +# 16:21:04 > + +# MonetDB 5 server v11.29.8 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2018', using 4 threads +# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers +# Found 31.309 GiB available main-memory. +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://earth.da.cwi.nl:34059/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-9105/.s.monetdb.34059 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. +# SQL catalog created, loading sql scripts once +# loading sql script: 09_like.sql +# loading sql script: 10_math.sql +# loading sql script: 11_times.sql +# loading sql script: 12_url.sql +# loading sql script: 13_date.sql +# loading sql script: 14_inet.sql +# loading sql script: 15_querylog.sql +# loading sql script: 16_tracelog.sql +# loading sql script: 17_temporal.sql +# loading sql script: 18_index.sql +# loading sql script: 20_vacuum.sql +# loading sql script: 21_dependency_functions.sql +# loading sql script: 21_dependency_views.sql +# loading sql script: 22_clients.sql +# loading sql script: 23_skyserver.sql +# loading sql script: 25_debug.sql +# loading sql script: 26_sysmon.sql +# loading sql script: 27_rejects.sql +# loading sql script: 39_analytics.sql +# loading sql script: 39_analytics_hge.sql +# loading sql script: 40_geom.sql +# loading sql script: 40_json.sql +# loading sql script: 40_json_hge.sql +# loading sql script: 41_md5sum.sql +# loading sql script: 45_uuid.sql +# loading sql script: 46_profiler.sql +# loading sql script: 51_sys_schema_extension.sql +# loading sql script: 60_wlcr.sql +# loading sql script: 72_fits.sql +# loading sql script: 74_netcdf.sql +# loading sql script: 75_lidar.sql +# loading sql script: 75_shp.sql +# loading sql script: 75_storagemodel.sql +# loading sql script: 80_statistics.sql +# loading sql script: 80_udf.sql +# loading sql script: 80_udf_hge.sql +# loading sql script: 85_bam.sql +# loading sql script: 90_generator.sql +# loading sql script: 90_generator_hge.sql +# loading sql script: 99_system.sql + +# 16:21:04 > +# 16:21:04 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-9105" "--port=34059" +# 16:21:04 > + +#start transaction; +#create table r2 (a bigint, b bigint, x bigint generated always as identity primary key); +#insert into r2 (a,b) values (11,21),(12,22),(13,23),(2199023255552,0),(0,2199023255552),(6597069766656,1),(1,6597069766656),(4398046511104,-9223372036854775807),(-9223372036854775807,4398046511104),(1,-9223372036850581504),(-9223372036850581504,1),(-1,9223372036854775807),(9223372036854775807,-1); +[ 13 ] +#create table s2 as (select a, b, -x as y from r2 order by x desc) with data; +#select * from r2; +% sys.r2, sys.r2, sys.r2 # table_name +% a, b, x # name +% bigint, bigint, bigint # type +% 20, 20, 2 # length +[ 11, 21, 1 ] +[ 12, 22, 2 ] +[ 13, 23, 3 ] +[ 2199023255552, 0, 4 ] +[ 0, 2199023255552, 5 ] +[ 6597069766656, 1, 6 ] +[ 1, 6597069766656, 7 ] +[ 4398046511104, -9223372036854775807, 8 ] +[ -9223372036854775807, 4398046511104, 9 ] +[ 1, -9223372036850581504, 10 ] +[ -9223372036850581504, 1, 11 ] +[ -1, 9223372036854775807, 12 ] +[ 9223372036854775807, -1, 13 ] +#select * from s2; +% sys.s2, sys.s2, sys.s2 # table_name +% a, b, y # name +% bigint, bigint, bigint # type +% 20, 20, 3 # length +[ 9223372036854775807, -1, -13 ] +[ -1, 9223372036854775807, -12 ] +[ -9223372036850581504, 1, -11 ] +[ 1, -9223372036850581504, -10 ] +[ -9223372036854775807, 4398046511104, -9 ] +[ 4398046511104, -9223372036854775807, -8 ] +[ 1, 6597069766656, -7 ] +[ 6597069766656, 1, -6 ] +[ 0, 2199023255552, -5 ] +[ 2199023255552, 0, -4 ] +[ 13, 23, -3 ] +[ 12, 22, -2 ] +[ 11, 21, -1 ] +#select * from r2 natural join s2 order by x,y; +% sys.L1, sys.L1, sys.r2, sys.s2 # table_name +% a, b, x, y # name +% bigint, bigint, bigint, bigint # type +% 20, 13, 2, 3 # length +[ 11, 21, 1, -1 ] +[ 12, 22, 2, -2 ] +[ 13, 23, 3, -3 ] +[ 2199023255552, 0, 4, -4 ] +[ 0, 2199023255552, 5, -5 ] +[ 6597069766656, 1, 6, -6 ] +[ 1, 6597069766656, 7, -7 ] +[ 4398046511104, -9223372036854775807, 8, -8 ] +[ -9223372036854775807, 4398046511104, 9, -9 ] +[ 1, -9223372036850581504, 10, -10 ] +[ -9223372036850581504, 1, 11, -11 ] +[ -1, 9223372036854775807, 12, -12 ] +[ 9223372036854775807, -1, 13, -13 ] +#select * from s2 natural join r2 order by x,y; +% sys.L1, sys.L1, sys.s2, sys.r2 # table_name +% a, b, y, x # name +% bigint, bigint, bigint, bigint # type +% 20, 13, 3, 2 # length +[ 11, 21, -1, 1 ] +[ 12, 22, -2, 2 ] +[ 13, 23, -3, 3 ] +[ 2199023255552, 0, -4, 4 ] +[ 0, 2199023255552, -5, 5 ] +[ 6597069766656, 1, -6, 6 ] +[ 1, 6597069766656, -7, 7 ] +[ 4398046511104, -9223372036854775807, -8, 8 ] +[ -9223372036854775807, 4398046511104, -9, 9 ] +[ 1, -9223372036850581504, -10, 10 ] +[ -9223372036850581504, 1, -11, 11 ] +[ -1, 9223372036854775807, -12, 12 ] +[ 9223372036854775807, -1, -13, 13 ] +#create table r3 (a bigint, b bigint, c bigint, x bigint generated always as identity primary key); +#insert into r3 (a,b,c) values (11,21,31),(12,22,32),(13,23,33),(2147483648,0,0),(0,2147483648,0),(0,0,2147483648),(140737488355328,0,0),(0,140737488355328,0),(0,0,140737488355328); +[ 9 ] +#create table s3 as (select a, b, c, -x as y from r3 order by x desc) with data; +#select * from r3; +% sys.r3, sys.r3, sys.r3, sys.r3 # table_name +% a, b, c, x # name +% bigint, bigint, bigint, bigint # type +% 15, 15, 15, 1 # length +[ 11, 21, 31, 1 ] +[ 12, 22, 32, 2 ] +[ 13, 23, 33, 3 ] +[ 2147483648, 0, 0, 4 ] +[ 0, 2147483648, 0, 5 ] +[ 0, 0, 2147483648, 6 ] +[ 140737488355328, 0, 0, 7 ] +[ 0, 140737488355328, 0, 8 ] +[ 0, 0, 140737488355328, 9 ] +#select * from s3; +% sys.s3, sys.s3, sys.s3, sys.s3 # table_name +% a, b, c, y # name +% bigint, bigint, bigint, bigint # type +% 15, 15, 15, 2 # length +[ 0, 0, 140737488355328, -9 ] +[ 0, 140737488355328, 0, -8 ] _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
