doris
doris copied to clipboard
Published
20 hours ago •
apache
apache
[fix](create-table)The CREATE TABLE IF NOT EXISTS AS SELECT statement should refrain from performing any INSERT operations if the table already exists
what happend
env
- 2.1.2
CREATE TABLE `address` (
`timestamp` DATE NOT NULL COMMENT '['0000-01-01', '9999-12-31']',
`type` TINYINT NOT NULL COMMENT '[-128, 127]',
`user_id` BIGINT NULL COMMENT '[-9223372036854775808, 9223372036854775807]'
) ENGINE=OLAP
DUPLICATE KEY(`timestamp`, `type`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`type`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
insert into address values('2023-07-19', 1, 1001)
create table if not exists image PROPERTIES("replication_num"="1") as select * from address
select count(1) from image result is 1
create table if not exists image PROPERTIES("replication_num"="1") as select * from address
select count(1) from image result is 2
An INSERT operation was performed despite the table already existing. The issue persists across both the new and legacy optimizers.
Reference documentation:
- https://clickhouse.com/docs/en/sql-reference/statements/create/table#from-select-query
- https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html#:~:text=For%20CREATE%20TABLE%20...%20SELECT%2C%20if%20IF%20NOT%20EXISTS%20is%20given%20and%20the%20target%20table%20exists%2C%20nothing%20is%20inserted%20into%20the%20destination%20table%2C%20and%20the%20statement%20is%20not%20logged.
changes
The CREATE TABLE IF NOT EXISTS AS SELECT statement should refrain from performing any INSERT operations if the table already exists
test
add test
affected version
- 1.2.x
- 2.0.x
- 2.1.x
Thank you for your contribution to Apache Doris. Don't know what should be done next? See How to process your PR
Since 2024-03-18, the Document has been moved to doris-website. See Doris Document.
PR approved by at least one committer and no changes requested.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
TPC-H: Total hot run time: 40040 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 56b7819d3d2fa6f49aefafe021b0d18e21486e60, data reload: false
------ Round 1 ----------------------------------
q1 18033 4322 4245 4245
q2 2021 210 199 199
q3 10610 1215 1223 1215
q4 10731 814 713 713
q5 7856 2715 2631 2631
q6 220 132 135 132
q7 950 621 596 596
q8 9270 2059 2079 2059
q9 9392 6583 6553 6553
q10 10456 3695 3699 3695
q11 453 244 247 244
q12 470 219 223 219
q13 17769 2985 2989 2985
q14 276 230 224 224
q15 523 499 476 476
q16 524 389 376 376
q17 949 694 711 694
q18 8175 7436 7474 7436
q19 3031 1538 1512 1512
q20 653 311 307 307
q21 4886 3765 3251 3251
q22 337 287 278 278
Total cold run time: 117585 ms
Total hot run time: 40040 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4331 4242 4185 4185
q2 360 267 271 267
q3 2951 2705 2731 2705
q4 1871 1668 1578 1578
q5 5219 5270 5243 5243
q6 218 122 123 122
q7 2090 1722 1743 1722
q8 3159 3314 3296 3296
q9 8289 8337 8326 8326
q10 3895 3733 3694 3694
q11 577 480 473 473
q12 772 592 601 592
q13 16420 3007 3035 3007
q14 286 248 271 248
q15 521 474 475 474
q16 469 408 410 408
q17 1757 1483 1456 1456
q18 7579 7570 7462 7462
q19 1661 1559 1551 1551
q20 2005 1779 1761 1761
q21 4714 4801 4677 4677
q22 568 479 487 479
Total cold run time: 69712 ms
Total hot run time: 53726 ms
TPC-DS: Total hot run time: 169749 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 56b7819d3d2fa6f49aefafe021b0d18e21486e60, data reload: false
query1 926 386 362 362
query2 6438 2383 2344 2344
query3 6649 209 201 201
query4 19315 17513 17494 17494
query5 4166 416 416 416
query6 251 156 161 156
query7 4584 292 290 290
query8 239 190 189 189
query9 8767 2394 2360 2360
query10 463 289 284 284
query11 10722 9960 10063 9960
query12 136 95 87 87
query13 1653 376 372 372
query14 10171 7038 7644 7038
query15 268 171 173 171
query16 8114 255 260 255
query17 1851 514 513 513
query18 1873 264 269 264
query19 202 165 150 150
query20 94 88 85 85
query21 194 135 128 128
query22 4283 3962 3960 3960
query23 33702 32994 32973 32973
query24 12112 2924 2830 2830
query25 628 351 367 351
query26 1675 158 154 154
query27 2963 313 327 313
query28 7383 2049 2054 2049
query29 984 627 584 584
query30 309 166 173 166
query31 945 762 734 734
query32 95 56 54 54
query33 765 288 258 258
query34 1030 476 497 476
query35 786 624 590 590
query36 1086 911 916 911
query37 282 72 73 72
query38 2925 2795 2777 2777
query39 861 799 772 772
query40 297 126 122 122
query41 47 46 42 42
query42 103 96 96 96
query43 571 521 523 521
query44 1267 723 735 723
query45 180 166 160 160
query46 1084 765 691 691
query47 1886 1797 1805 1797
query48 373 298 296 296
query49 1189 376 378 376
query50 759 394 384 384
query51 6992 6708 6678 6678
query52 112 90 90 90
query53 356 283 287 283
query54 949 428 424 424
query55 73 72 70 70
query56 258 243 241 241
query57 1119 1054 1056 1054
query58 239 228 208 208
query59 3280 3125 3006 3006
query60 279 265 250 250
query61 91 86 90 86
query62 661 477 452 452
query63 312 292 285 285
query64 9794 2204 1742 1742
query65 3379 3120 3112 3112
query66 1322 326 331 326
query67 15173 15001 15010 15001
query68 4585 548 531 531
query69 444 262 275 262
query70 1152 1148 1134 1134
query71 407 270 273 270
query72 7528 5195 2725 2725
query73 715 317 320 317
query74 5983 5673 5691 5673
query75 3355 2625 2629 2625
query76 2885 1009 1052 1009
query77 583 275 269 269
query78 10162 9913 9854 9854
query79 1578 509 520 509
query80 787 438 439 438
query81 525 245 239 239
query82 613 89 91 89
query83 335 171 168 168
query84 234 82 82 82
query85 1398 266 251 251
query86 424 290 316 290
query87 3291 3109 3159 3109
query88 3952 2320 2302 2302
query89 463 400 369 369
query90 1976 182 202 182
query91 123 93 103 93
query92 59 48 48 48
query93 1909 505 498 498
query94 1252 182 182 182
query95 396 349 296 296
query96 587 262 257 257
query97 3129 3007 2991 2991
query98 238 219 210 210
query99 1072 818 832 818
Total cold run time: 274175 ms
Total hot run time: 169749 ms
ClickBench: Total hot run time: 30.56 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 56b7819d3d2fa6f49aefafe021b0d18e21486e60, data reload: false
query1 0.04 0.03 0.03
query2 0.08 0.03 0.04
query3 0.23 0.06 0.05
query4 1.66 0.07 0.07
query5 0.49 0.50 0.49
query6 1.12 0.72 0.72
query7 0.02 0.02 0.02
query8 0.05 0.04 0.05
query9 0.55 0.50 0.50
query10 0.55 0.55 0.53
query11 0.16 0.11 0.11
query12 0.14 0.12 0.12
query13 0.60 0.59 0.59
query14 0.77 0.78 0.77
query15 0.83 0.81 0.80
query16 0.37 0.37 0.37
query17 1.00 0.93 1.01
query18 0.21 0.21 0.23
query19 1.78 1.73 1.71
query20 0.01 0.01 0.02
query21 15.46 0.68 0.66
query22 4.79 6.82 2.06
query23 18.38 1.40 1.19
query24 1.73 0.30 0.20
query25 0.15 0.09 0.09
query26 0.25 0.16 0.16
query27 0.08 0.08 0.08
query28 13.35 1.00 0.99
query29 13.18 3.28 3.33
query30 0.25 0.06 0.07
query31 2.86 0.39 0.38
query32 3.27 0.48 0.46
query33 2.87 2.88 2.82
query34 17.15 4.47 4.41
query35 4.55 4.56 4.54
query36 0.65 0.50 0.45
query37 0.18 0.16 0.16
query38 0.15 0.14 0.15
query39 0.04 0.04 0.03
query40 0.16 0.15 0.15
query41 0.09 0.05 0.05
query42 0.06 0.06 0.06
query43 0.04 0.04 0.04
Total cold run time: 110.35 s
Total hot run time: 30.56 s