DROP DATABASE IF EXISTS regr_fe_conv; CREATE DATABASE regr_fe_conv; USE regr_fe_conv; Warnings: Note 1265 Data truncated for column 'dt' at row 1 Warning 1265 Data truncated for column 'c1' at row 1 Warning 1265 Data truncated for column 'c2' at row 1 Warning 1265 Data truncated for column 'c3' at row 1 Warning 1265 Data truncated for column 'c4' at row 1 Warning 1265 Data truncated for column 'c5' at row 1 Warning 1265 Data truncated for column 'c6' at row 1 Warning 1265 Data truncated for column 'c7' at row 1 Warning 1265 Data truncated for column 'vc1' at row 1 Warning 1265 Data truncated for column 'vc2' at row 1 Warning 1265 Data truncated for column 'vc3' at row 1 Warning 1265 Data truncated for column 'vc4' at row 1 Warning 1265 Data truncated for column 'vc5' at row 1 Warning 1265 Data truncated for column 'vc6' at row 1 Warning 1265 Data truncated for column 'vc7' at row 1 Note 1265 Data truncated for column 'd1' at row 1 Warning 1264 Out of range value for column 'd2' at row 1 Note 1265 Data truncated for column 'd3' at row 1 Note 1265 Data truncated for column 'd4' at row 1 Warning 1264 Out of range value for column 'd5' at row 1 Note 1265 Data truncated for column 'd6' at row 1 Note 1265 Data truncated for column 'd7' at row 1 Note 1265 Data truncated for column 'd8' at row 1 Warning 1264 Out of range value for column 'd9' at row 1 Note 1265 Data truncated for column 'd10' at row 1 Note 1265 Data truncated for column 'd11' at row 1 Note 1265 Data truncated for column 'd12' at row 1 Note 1265 Data truncated for column 'd13' at row 1 Warning 1264 Out of range value for column 'd14' at row 1 Note 1265 Data truncated for column 'd15' at row 1 Note 1265 Data truncated for column 'd16' at row 1 Note 1265 Data truncated for column 'd17' at row 1 Note 1265 Data truncated for column 'd18' at row 1 Note 1265 Data truncated for column 'd19' at row 1 Warning 1264 Out of range value for column 'd20' at row 1 Note 1265 Data truncated for column 'd21' at row 1 Note 1265 Data truncated for column 'd22' at row 1 Note 1265 Data truncated for column 'd23' at row 1 Note 1265 Data truncated for column 'd24' at row 1 Note 1265 Data truncated for column 'd25' at row 1 Warning 1264 Out of range value for column 'd27' at row 1 Note 1265 Data truncated for column 'd28' at row 1 Note 1265 Data truncated for column 'd29' at row 1 Note 1265 Data truncated for column 'd30' at row 1 Note 1265 Data truncated for column 'd31' at row 1 Note 1265 Data truncated for column 'd32' at row 1 Warning 1264 Out of range value for column 'd35' at row 1 Note 1265 Data truncated for column 'd36' at row 1 Note 1265 Data truncated for column 'd37' at row 1 Note 1265 Data truncated for column 'd38' at row 1 Note 1265 Data truncated for column 'd39' at row 1 Note 1265 Data truncated for column 'd40' at row 1 Warning 1264 Out of range value for column 'd44' at row 1 Note 1265 Data truncated for column 'd45' at row 1 Note 1265 Data truncated for column 'd46' at row 1 Note 1265 Data truncated for column 'd47' at row 1 Note 1265 Data truncated for column 'd48' at row 1 Note 1265 Data truncated for column 'd49' at row 1 Warning 1264 Out of range value for column 'd54' at row 1 Note 1265 Data truncated for column 'd55' at row 1 Note 1265 Data truncated for column 'd56' at row 1 Note 1265 Data truncated for column 'd57' at row 1 Note 1265 Data truncated for column 'd58' at row 1 Note 1265 Data truncated for column 'd59' at row 1 select conv(dt, -10, -8) from dtypes where conv(dt, -10, -8) > 3731; conv(dt, -10, -8) 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 3732 select dtm from dtypes where conv(dtm, -10, -8) > 3731 order by dtm, conv(dtm, 10, 8); dtm 2010-01-04 07:35:22 2010-01-11 07:36:22 2010-01-18 07:37:22 2010-01-25 07:38:22 2010-02-01 07:39:22 2010-02-08 07:40:22 2010-02-15 07:41:22 2010-02-22 07:42:22 2010-03-01 07:43:22 2010-03-08 07:44:22 2010-03-15 07:45:22 2010-03-22 07:46:22 2010-03-29 07:47:22 2010-04-05 07:48:22 2010-04-12 07:49:22 2010-04-19 07:50:22 2010-04-26 07:51:22 2010-05-03 07:52:22 2010-05-10 07:53:22 2010-05-17 07:54:22 2010-05-24 07:55:22 2010-05-31 07:56:22 2010-06-07 07:57:22 2010-06-14 07:58:22 2010-06-21 07:59:22 2010-06-28 08:00:22 2010-07-05 08:01:22 2010-07-12 08:02:22 2010-07-19 08:03:22 2010-07-26 08:04:22 2010-08-02 08:05:22 2010-08-09 08:06:22 2010-08-16 08:07:22 2010-08-23 08:08:22 2010-08-30 08:09:22 2010-09-06 08:10:22 2010-09-13 08:11:22 2010-09-20 08:12:22 2010-09-27 08:13:22 2010-10-04 08:14:22 2010-10-11 08:15:22 2010-10-18 08:16:22 2010-10-25 08:17:22 2010-11-01 08:18:22 2010-11-08 08:19:22 select conv(db, 10, 16), conv(ti, 8,16), conv(si, 16, 8), conv(i, 4, 8), conv(bi, 10, 8) from dtypes; conv(db, 10, 16) conv(ti, 8,16) conv(si, 16, 8) conv(i, 4, 8) conv(bi, 10, 8) FFFFFFFFFFFFFFF8 0 1777777777777777777767 0 1777777777777777777767 1 1 1 1 1 0 1 1 1 1 0 1 1 1 1 0 0 0 0 0 0 FFFFFFFFFFFFFFFF 1777777777777777777777 1777777777777777777777 1777777777777777777777 0 0 0 0 0 FFFFFFFFFFFFFFF9 0 1777777777777777777770 0 1777777777777777777770 4 5 5 0 5 0 0 0 0 0 4A00A6 57 623547 0 22400246 FFFFFFFFFFFFFFFA FFFFFFFFFFFFFFF9 1777777777777777777771 0 1777777777777777777771 9 0 11 0 11 0 1 1 1 1 28799A 57 623547 2 12074633 FFFFFFFFFFFFFFFB FFFFFFFFFFFFFFFA 1777777777777777777772 0 1777777777777777777772 10 F 27 1 21 1 1 1 1 1 392FA 57 623547 13 711372 2 2 2 2 2 0 0 0 0 0 EA 57 1064 13 352 3 3 3 3 3 5B7F 57 432043 13 55577 8 0 10 0 10 9 8 20 4 12 4 4 4 0 4 FFFFFFFFFFFFF747 FFFFFFFFFFFFFFAA 1777777777777777756715 1777777777777777777521 1777777777777777773507 FFFFFFFFFFFFFFD4 FFFFFFFFFFFFFFDC 1777777777777777777674 0 1777777777777777777724 1 1 1 1 1 2C 25 105 0 55 FFFFFFFFFFFFFFE9 FFFFFFFFFFFFFFEC 1777777777777777777734 1777777777777777777776 1777777777777777777750 378 57 4210 0 1570 1 1 1 1 1 0 1 1 1 1 32AEEC 57 623547 371 14527354 FFFFFFFFFFFFFFFC FFFFFFFFFFFFFFFC 1777777777777777777774 0 1777777777777777777774 19 16 46 2 32 0 1 1 1 1 3D400A 57 623547 0 17240012 FFFFFFFFFFFFFFFD FFFFFFFFFFFFFFFD 1777777777777777777775 1777777777777777777775 1777777777777777777775 24 1E 66 3 44 0 1 1 1 1 FFFFFFFFFFFFFFFE FFFFFFFFFFFFFFFD 1777777777777777777775 1777777777777777777775 1777777777777777777775 31 28 120 0 62 0 1 1 1 1 FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 1777777777777777777777 1777777777777777777777 1777777777777777777777 40 35 145 0 101 0 0 0 0 0 0 FFFFFFFFFFFFFFFF 1777777777777777777777 1777777777777777777777 1777777777777777777777 51 0 201 0 121 0 1 1 1 1 189CA0 57 623547 1 6116241 2C 24 104 0 54 FFFFFFFFFFFFFFD4 FFFFFFFFFFFFFFDC 1777777777777777777674 0 1777777777777777777724 0 1 1 1 1 64 41 401 21 145 0 1 1 1 1 1 2 2 2 2 79 52 442 32 172 0 0 0 0 0 7CA42 57 623547 0 1745102 2 2 2 2 2 90 57 505 1 221 0 0 0 0 0 3 3 3 3 3 A9 57 551 1 251 0 0 0 0 0 3CB450 57 623547 3 17132121 4 5 5 0 5 C4 57 627 1 305 0 0 0 0 0 471882 57 623547 0 21614202 5 5 5 0 5 E1 57 1046 12 342 0 0 0 0 0 45420A 57 623547 0 21241013 6 7 7 0 7 100 57 1126 2 400 0 0 0 0 0 23C8BE 57 623547 13 10744277 7 7 7 0 7 121 57 1220 2 442 0 1 1 1 1 3983FC 57 623547 3 16301775 8 0 11 0 11 144 57 1445 16 505 0 1 1 1 1 596CA8 57 623547 0 26266251 9 0 11 0 11 169 57 1541 3 551 0 1 1 1 1 182DEC 57 623547 1 6026755 A 9 21 5 13 190 57 2000 0 620 0 0 0 0 0 1245D0 57 623547 5 4442720 184 57 1610 3 604 FFFFFFFFFFFFFF18 FFFFFFFFFFFFFFAB 1777777777777777776716 1777777777777777777722 1777777777777777777430 0 0 0 0 0 select conv (c1, 5, 10), conv(substr(c8,2,4), 8, 10), conv(concat(vc1, vc2), 10,8) from dtypes; conv (c1, 5, 10) conv(substr(c8,2,4), 8, 10) conv(concat(vc1, vc2), 10,8) 0 0 0 1 0 13 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 7 0 4 0 54 0 0 0 4 0 700 0 6 0 0 0 143 0 0 0 2 3413 342 0 5 0 1 6 164 1 NULL 13 2 1811 337 2 NULL 26 0 0 0 2 28 337 3 NULL 41 2 1811 337 0 0 130 0 0 143 4 0 54 0 1179 0 0 36 0 1 0 13 4 4 674 0 19 0 0 0 1570 1 0 13 0 0 0 3 1677 515 0 4 0 2 5 341 0 0 0 4 96 670 0 3 0 3 6 520 0 0 0 0 2 0 4 0 701 0 0 0 0 1 0 0 4 1230 0 0 0 0 0 0 0 1 1561 0 0 0 1 394 164 4 4 674 0 36 0 0 0 0 1 0 156 0 0 0 1 0 13 1 17 160 0 0 0 0 555 1047 2 0 26 1 36 162 0 0 0 3 0 41 1 6 164 0 0 0 3 0 523 4 0 54 1 0 167 0 0 0 4 53 676 0 0 67 2 21 336 0 0 0 4 43 675 0 0 102 2 46 341 0 0 0 2 1833 337 0 0 115 2 0 344 0 0 0 3 62 521 0 0 130 3 20 514 0 0 0 0 0 1056 0 0 143 3 49 520 0 0 0 1 5 163 1 0 156 4 0 670 0 0 0 1 1 157 3 0 522 0 154 0 0 0 0 select substr(vc255,2,3), conv(substr(vc255,2,3),16,10) from dtypes where id < 50 ; substr(vc255,2,3) conv(substr(vc255,2,3),16,10) 8.7 8 .29 0 .84 0 .9 0 .2 0 0.9 0 0.2 0 7.8 7 .81 0 .17 0 849 2121 6.6 6 .49 0 .91 0 652 1618 5.7 5 6.6 6 NULL 342 834 NULL 0.0 0 34 52 NULL 342 834 .23 0 .88 0 .23 0 223 547 44 68 .23 0 4.8 4 23. 35 88 136 .23 0 .57 0 321 801 4.4 4 5.5 5 .85 0 014 20 3.3 3 6.0 6 .72 0 2.7 2 9.6 9 .89 0 1.0 1 4.9 4 .37 0 select conv (c1, 5, 2), conv(substr(c8,2,4), 8, 2), conv(concat(vc1, vc2), 10, 2) from dtypes; conv (c1, 5, 2) conv(substr(c8,2,4), 8, 2) conv(concat(vc1, vc2), 10, 2) 0 0 0 1 0 1011 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 111 0 100 0 101100 0 0 0 100 0 111000000 0 110 0 0 0 1100011 0 0 0 10 110101010101 11100010 0 101 0 1 110 1110100 1 NULL 1011 10 11100010011 11011111 10 NULL 10110 0 0 0 10 11100 11011111 11 NULL 100001 10 11100010011 11011111 0 0 1011000 0 0 1100011 100 0 101100 0 10010011011 0 0 100100 0 1 0 1011 100 100 110111100 0 10011 0 0 0 1101111000 1 0 1011 0 0 0 11 11010001101 101001101 0 100 0 10 101 11100001 0 0 0 100 1100000 110111000 0 11 0 11 110 101010000 0 0 0 0 10 0 100 0 111000001 0 0 0 0 1 0 0 100 1010011000 0 0 0 0 0 0 0 1 1101110001 0 0 0 1 110001010 1110100 100 100 110111100 0 100100 0 0 0 0 1 0 1101110 0 0 0 1 0 1011 1 10001 1110000 0 0 0 0 1000101011 1000100111 10 0 10110 1 100100 1110010 0 0 0 11 0 100001 1 110 1110100 0 0 0 11 0 101010011 100 0 101100 1 0 1110111 0 0 0 100 110101 110111110 0 0 110111 10 10101 11011110 0 0 0 100 101011 110111101 0 0 1000010 10 101110 11100001 0 0 0 10 11100101001 11011111 0 0 1001101 10 0 11100100 0 0 0 11 111110 101010001 0 0 1011000 11 10100 101001100 0 0 0 0 0 1000101110 0 0 1100011 11 110001 101010000 0 0 0 1 101 1110011 1 0 1101110 100 0 110111000 0 0 0 1 1 1101111 11 0 101010010 0 10011010 0 0 0 0 drop table if exists bug3509; create table bug3509 (cookie varchar(32), d_datekey date) engine=columnstore; insert into bug3509 values ('f48d2dce907ce3c54a9c12855754c0b5', 19980404); select conv(substr(cookie,1,12),16,10), conv(substr(cookie,1,16),16,10), conv(substr(cookie,1,16),18,10) from bug3509; conv(substr(cookie,1,12),16,10) conv(substr(cookie,1,16),16,10) conv(substr(cookie,1,16),18,10) 268887196078204 17621791282181235653 18446744073709551615 drop table bug3509; DROP DATABASE regr_fe_conv;