search

manticoresoftware / manticoresearch

Easy to use open source fast database for search | Good alternative to Elasticsearch now | Drop-in replacement for E in the ELK soon
https://manticoresearch.com
GNU General Public License v3.0
8.93k stars 495 forks source link

Clone schema before modifying eval stages for json si expressions #2589

Closed glookka closed 4 days ago

glookka commented 4 days ago

Clone schema before modifying eval stages for json si expressions

github-actions[bot] commented 4 days ago

Linux debug test results

   10 files   10 suites   8m 41s :stopwatch: 1 008 tests 958 :white_check_mark: 50 :zzz: 0 :x: 1 022 runs  972 :white_check_mark: 50 :zzz: 0 :x:

Results for commit db37d437.

:recycle: This comment has been updated with latest results.

github-actions[bot] commented 4 days ago

Linux release test results

   10 files   10 suites   7m 27s :stopwatch: 1 008 tests 958 :white_check_mark: 50 :zzz: 0 :x: 1 022 runs  972 :white_check_mark: 50 :zzz: 0 :x:

Results for commit db37d437.

:recycle: This comment has been updated with latest results.

github-actions[bot] commented 4 days ago

Windows test results

  3 files    3 suites   18m 6s :stopwatch: 984 tests 934 :white_check_mark: 50 :zzz: 0 :x: 988 runs  938 :white_check_mark: 50 :zzz: 0 :x:

Results for commit db37d437.

:recycle: This comment has been updated with latest results.

github-actions[bot] commented 4 days ago

clt

👎 CLT tests in test/clt-tests/core/ test/clt-tests/expected-errors/ ✅ OK: 25 ❌ Failed: 1 ⏳ Duration: 604s 👉 Check Action Results for commit 03697d0 Failed tests:

test/clt-tests/core/test-replace-into.rec ```diff ––– input ––– rm -f /var/log/manticore/searchd.log; searchd --stopwait > /dev/null; searchd; if timeout 10 grep -qm1 '\[BUDDY\] started' <(tail -n 1000 -f /var/log/manticore/searchd.log); then echo 'Buddy started!'; else echo 'Timeout or failed!'; cat /var/log/manticore/searchd.log;fi ––– output ––– Manticore %{SEMVER} %{COMMITDATE}#!/(\sdev)?\s/!#(columnar %{SEMVER} %{COMMITDATE}) (secondary %{SEMVER} %{COMMITDATE}) (knn %{SEMVER} %{COMMITDATE}) Copyright (c) 2001-2016, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com) Copyright (c) 2017-%{YEAR}, Manticore Software LTD (https://manticoresearch.com) [#!/[0-9]{2}:[0-9]{2}.[0-9]{3}/!#] [#!/[0-9]+/!#] using config file '/etc/manticoresearch/manticore.conf' (%{NUMBER} chars)... starting daemon version '%{SEMVER} %{COMMITDATE}#!/(\sdev)?\s/!#(columnar %{SEMVER} %{COMMITDATE}) (secondary %{SEMVER} %{COMMITDATE}) (knn %{SEMVER} %{COMMITDATE})' ... listening on %{IPADDR}:9312 for sphinx and http(s) listening on %{IPADDR}:9306 for mysql listening on %{IPADDR}:9308 for sphinx and http(s) Buddy started! ––– input ––– mysql -v -h0 -P9306 -e "SHOW TABLES; DROP TABLE IF EXISTS tbl; SHOW TABLES; CREATE TABLE tbl (id BIGINT, model TEXT, storage_capacity INTEGER, color string, release_year INTEGER, price FLOAT, discounted_price FLOAT, sold BOOL, date_added TIMESTAMP, product_codes MULTI, values MULTI64, additional_info JSON, vector float_vector knn_type='hnsw' knn_dims='4' hnsw_similarity='l2');" ––– output ––– -------------- SHOW TABLES -------------- -------------- DROP TABLE IF EXISTS tbl -------------- -------------- SHOW TABLES -------------- -------------- CREATE TABLE tbl (id BIGINT, model TEXT, storage_capacity INTEGER, color string, release_year INTEGER, price FLOAT, discounted_price FLOAT, sold BOOL, date_added TIMESTAMP, product_codes MULTI, values MULTI64, additional_info JSON, vector float_vector knn_type='hnsw' knn_dims='4' hnsw_similarity='l2') -------------- ––– input ––– chmod +x ./test/clt-tests/scripts/generate-1mln-records.sh ––– output ––– ––– input ––– total_iterations=20000 ./test/clt-tests/scripts/generate-1mln-records.sh ––– output ––– +-------+------+ | Index | Type | +-------+------+ | tbl | rt | +-------+------+ +----------+ | count(*) | +----------+ | 1000000 | +----------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 15 PRO' WHERE id = 101; select * from tbl WHERE id = 101;" ––– output ––– +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 101 | iPhone 15 PRO | 256 | silver | 2021 | 1099.989990 | 989.989990 | 1 | 2224442480 | 1,2,3 | 523456764345678976,9223372036854775807 | {"features":["ProMotion display","A15 Bionic chip","Ceramic Shield front cover"]} | 0.773448,0.312478,0.137971,0.459821 | +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET values = (623456764345678976, 9223372036854775807) WHERE id = 120; select * from tbl WHERE id = 120;" ––– output ––– +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 120 | LG Wing | 256 | aurora gray | 2020 | 999.989990 | 899.989990 | 1 | 1496660080 | 57,58,59 | 623456764345678976,9223372036854775807 | {"features":["Qualcomm Snapdragon 765G","Swivel display","Triple-camera system"]} | 0.863448,0.402478,0.227971,0.549821 | +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET product_codes = (53,45,77), values = (92233777368548,92233720368548) WHERE id = 119; select * from tbl WHERE id = 119;" ––– output ––– +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 119 | BlackBerry Evolve X | 64 | black | 2018 | 599.989990 | 499.989990 | 0 | 3697138672 | 45,53,77 | 92233720368548,92233777368548 | {"features":["Qualcomm Snapdragon 660","5.99-inch display","Dual-camera system"]} | 0.051106,0.948278,0.291892,0.104594 | +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99 WHERE id = 117; select * from tbl WHERE id = 117;" ––– output ––– +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ | 117 | Motorola Edge 20 Pro | 256 | midnight blue | 2021 | 15000.990234 | 599.989990 | 0 | 1501096560 | 48,49,50 | 1123456764345678976,9223372036854775807 | {"features":["Qualcomm Snapdragon 870","OLED display","Triple-camera system"]} | 0.041106,0.938278,0.281892,0.094594 | +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99, discounted_price =445.88 WHERE id = 330; select * from tbl WHERE id = 330;" ––– output ––– +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ | 330 | Motorola Moto G Power (2021) | 64 | flash gray | 2021 | 15000.990234 | 445.880005 | 1 | 1306783344 | 84,85,86 | 2323456764345678976,9223372036854775807 | {"features":["Snapdragon 662","6.6-inch display","Triple-camera system"]} | 0.663448,0.202478,0.027971,0.349821 | +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET vector= (-0.037794, 0.856278, 0.202892, 0.015594) WHERE id = 102; select * from tbl WHERE id = 102;" ––– output ––– +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ | 102 | iPhone 13 | 128 | blue | 2021 | 799.989990 | 719.989990 | 0 | 1820294112 | 4,5,6 | 623456764345678976,9223372036854775807 | {"features":["A15 Bionic chip","Ceramic Shield front cover","Dual-camera system"]} | -0.037794,0.856278,0.202892,0.015594 | +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99, discounted_price =445.88, vector= (-0.037794, 0.856278, 0.202892, 0.015594) WHERE id = 336; select * from tbl WHERE id = 336;" ––– output ––– +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ | 336 | HTC Wildfire E1 lite | 32 | black | 2021 | 15000.990234 | 445.880005 | 1 | 3438054384 | 102,103,104 | 2923456764345678976,9223372036854775807 | {"features":["MediaTek Helio A20","6.1-inch display","Dual-camera system"]} | -0.037794,0.856278,0.202892,0.015594 | +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model='iPhone 13 Pro Max', color='titan', storage_capacity=1000, release_year=2023, sold=1, date_added=4077861360, product_codes=(30,31,32), values= (9223372444854775807,9223372036812375244), additional_info= '{\"features\":[\"Snapdragon 900\",\"8.0-inch display\",\"Dual-camera new-system\"]}', vector= (-0.037795, 0.856278, 0.202892, -0.015595) WHERE id = 247; select * from tbl WHERE id = 247;" ––– output ––– +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ | 247 | iPhone 13 Pro Max | 1000 | titan | 2023 | 799.989990 | 699.989990 | 1 | 4077861360 | 30,31,32 | 9223372036812375244,9223372036854775807 | {"features":["Snapdragon 900","8.0-inch display","Dual-camera new-system"]} | -0.037795,0.856278,0.202892,-0.015595 | +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET additional_info= '{\"features\":[\"Snapdragon 900\",\"8.0-inch display\",\"Dual-camera new-system\"]}' WHERE id = 323; select * from tbl WHERE id =323;" ––– output ––– +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ | 323 | Asus ROG Phone 5 | 256 | phantom black | 2021 | 999.989990 | 899.989990 | 0 | 3632367600 | 66,67,68 | 1723456764345678976,9223372036854775807 | {"features":["Snapdragon 900","8.0-inch display","Dual-camera new-system"]} | 0.071106,0.968278,0.311892,0.124594 | +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "Replace Into tbl Set price =15780.99, discounted_price =335.88 WHERE id = 335; select * from tbl WHERE id = 335;" ––– output ––– +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ | 335 | LG G8 ThinQ | 128 | aurora black | 2019 | 15780.990234 | 335.880005 | 0 | 1194467680 | 99,100,101 | 2823456764345678976,9223372036854775807 | {"features":["Snapdragon 855","6.1-inch display","Dual-camera system"]} | -0.118894,0.778278,0.121892,-0.065406 | +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ ––– input ––– curl -X POST "http://localhost:9308/tbl/_update/101" -H "Content-Type: application/json" -d '{"doc": {"model": "new-model", "color": "new-color"}}'; echo $? ––– output ––– {"_index":"tbl","updated":1}0 ––– input ––– start_time=$(date +%s%3N) && for i in {1..5000}; do echo "REPLACE INTO tbl SET color = 'color_$i' WHERE id = ${i}01;"; done > /tmp/replace_dump && mysql -h0 -P9306 < /tmp/replace_dump > /dev/null && end_time=$(date +%s%3N) && export elapsed_time=$((end_time - start_time)) ––– output ––– ––– input ––– echo $elapsed_time ––– output ––– #!/[0-9]{4,5}/!# ––– input ––– mysql -h0 -P9306 -e "select count(*) from tbl WHERE regex (color, 'color_[0-9]+')" ––– output ––– +----------+ | count(*) | +----------+ | 5000 | +----------+ ––– input ––– start_time2=$(date +%s%3N) && for i in {1..5000}; do echo "REPLACE INTO tbl (id, model, storage_capacity, color, release_year, price, discounted_price, sold, date_added, product_codes, values, additional_info, vector) VALUES (${i}101, 'iPhone 13 Pro', 256, 'silver${i}', 2021, 1099.99, 989.99, 'TRUE', '1591362342000', (1,2,3), (523456764345678976, 98765409877866654098, 1109876543450987650987), '{\"features\": [\"ProMotion display\", \"A15 Bionic chip\", \"Ceramic Shield front cover\"]}', (0.773448, 0.312478, 0.137971, 0.459821));"; done > /tmp/replace_dump && mysql -h0 -P9306 < /tmp/replace_dump > /dev/null && end_time2=$(date +%s%3N) && export elapsed_time2=$((end_time2 - start_time2)) ––– output ––– ––– input ––– echo $elapsed_time2 ––– output ––– #!/[0-9]{3,4}/!# ––– input ––– mysql -h0 -P9306 -e "select count(*) from tbl WHERE regex (color, 'silver[0-9]+')" ––– output ––– +----------+ | count(*) | +----------+ | 5000 | +----------+ ––– input ––– echo $((elapsed_time / elapsed_time2)) ––– output ––– #!/[1-9]|1[0-2]/!# ––– input ––– mysql -v -h0 -P9306 -e "SHOW TABLES; DROP TABLE IF EXISTS abc; create table abc(id, t1 text, t2 text indexed); insert into abc VALUES (1, 'abc', 'cde'); desc abc;" ––– output ––– -------------- SHOW TABLES -------------- +-------+------+ | Index | Type | +-------+------+ | tbl | rt | +-------+------+ -------------- DROP TABLE IF EXISTS abc -------------- -------------- create table abc(id, t1 text, t2 text indexed) -------------- -------------- insert into abc VALUES (1, 'abc', 'cde') -------------- -------------- desc abc -------------- +-------+--------+----------------+ | Field | Type | Properties | +-------+--------+----------------+ | id | bigint | | | t1 | text | indexed stored | | t2 | text | indexed | +-------+--------+----------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO abc SET t1 ='iPhone 15 PRO' WHERE id = 1;" ––– output ––– ERROR 1064 (42000) at line 1: Field t2 doesn't have stored property. Replace query can't be performed ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 16 PRO MAX' WHERE id > 105;" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 16 PRO MAX' WHERE id > 105' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 17 PRO MAX' WHERE id = 'abs';" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 17 PRO MAX' WHERE id = 'abs'' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 18 PRO' WHERE id in (1,2,3);" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 18 PRO' WHERE id in (1,2,3)' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 18 PRO' WHERE sold = 0;" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 18 PRO' WHERE sold = 0' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET id =777 WHERE id = 144; select * from tbl WHERE id = 144;" ––– output ––– +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ | 144 | Sony Xperia XZ3 | 64 | forest green | 2018 | 899.989990 | 799.989990 | 1 | 3308512240 | 126,127,128 | 3723456764345678976,9223372036854775807 | {"features":["Snapdragon 845","6.0-inch display","Single camera"]} | 0.733448,0.272478,0.097971,0.419821 | +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -v -h0 -P9306 -e "SHOW TABLES; DROP TABLE IF EXISTS tbl; DROP TABLE IF EXISTS abc; SHOW TABLES; CREATE TABLE tbl (id BIGINT, model TEXT, storage_capacity INTEGER, color string, release_year INTEGER, price FLOAT, discounted_price FLOAT, sold BOOL, date_added TIMESTAMP, product_codes MULTI, values MULTI64, additional_info JSON, vector float_vector knn_type='hnsw' knn_dims='4' hnsw_similarity='l2') engine='columnar';" ––– output ––– -------------- SHOW TABLES -------------- +-------+------+ | Index | Type | +-------+------+ | abc | rt | | tbl | rt | +-------+------+ -------------- DROP TABLE IF EXISTS tbl -------------- -------------- DROP TABLE IF EXISTS abc -------------- -------------- SHOW TABLES -------------- -------------- CREATE TABLE tbl (id BIGINT, model TEXT, storage_capacity INTEGER, color string, release_year INTEGER, price FLOAT, discounted_price FLOAT, sold BOOL, date_added TIMESTAMP, product_codes MULTI, values MULTI64, additional_info JSON, vector float_vector knn_type='hnsw' knn_dims='4' hnsw_similarity='l2') engine='columnar' -------------- ––– input ––– chmod +x ./test/clt-tests/scripts/generate-1mln-records.sh ––– output ––– ––– input ––– total_iterations=20000 ./test/clt-tests/scripts/generate-1mln-records.sh ––– output ––– +-------+------+ | Index | Type | +-------+------+ | tbl | rt | +-------+------+ +----------+ | count(*) | +----------+ | 1000000 | +----------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 15 PRO' WHERE id = 101; select * from tbl WHERE id = 101;" ––– output ––– +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 101 | iPhone 15 PRO | 256 | silver | 2021 | 1099.989990 | 989.989990 | 1 | 2224442480 | 1,2,3 | 523456764345678976,9223372036854775807 | {"features":["ProMotion display","A15 Bionic chip","Ceramic Shield front cover"]} | 0.773448,0.312478,0.137971,0.459821 | +------+---------------+------------------+--------+--------------+-------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET values = (623456764345678976, 9223372036854775807) WHERE id = 120; select * from tbl WHERE id = 120;" ––– output ––– +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 120 | LG Wing | 256 | aurora gray | 2020 | 999.989990 | 899.989990 | 1 | 1496660080 | 57,58,59 | 623456764345678976,9223372036854775807 | {"features":["Qualcomm Snapdragon 765G","Swivel display","Triple-camera system"]} | 0.863448,0.402478,0.227971,0.549821 | +------+---------+------------------+-------------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET product_codes = (53,45,77), values = (92233777368548,92233720368548) WHERE id = 119; select * from tbl WHERE id = 119;" ––– output ––– +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ | 119 | BlackBerry Evolve X | 64 | black | 2018 | 599.989990 | 499.989990 | 0 | 3697138672 | 45,53,77 | 92233720368548,92233777368548 | {"features":["Qualcomm Snapdragon 660","5.99-inch display","Dual-camera system"]} | 0.051106,0.948278,0.291892,0.104594 | +------+---------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-------------------------------+-----------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99 WHERE id = 117; select * from tbl WHERE id = 117;" ––– output ––– +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ | 117 | Motorola Edge 20 Pro | 256 | midnight blue | 2021 | 15000.990234 | 599.989990 | 0 | 1501096560 | 48,49,50 | 1123456764345678976,9223372036854775807 | {"features":["Qualcomm Snapdragon 870","OLED display","Triple-camera system"]} | 0.041106,0.938278,0.281892,0.094594 | +------+----------------------+------------------+---------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99, discounted_price =445.88 WHERE id = 330; select * from tbl WHERE id = 330;" ––– output ––– +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ | 330 | Motorola Moto G Power (2021) | 64 | flash gray | 2021 | 15000.990234 | 445.880005 | 1 | 1306783344 | 84,85,86 | 2323456764345678976,9223372036854775807 | {"features":["Snapdragon 662","6.6-inch display","Triple-camera system"]} | 0.663448,0.202478,0.027971,0.349821 | +------+------------------------------+------------------+------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+---------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET vector= (-0.037794, 0.856278, 0.202892, 0.015594) WHERE id = 102; select * from tbl WHERE id = 102;" ––– output ––– +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ | 102 | iPhone 13 | 128 | blue | 2021 | 799.989990 | 719.989990 | 0 | 1820294112 | 4,5,6 | 623456764345678976,9223372036854775807 | {"features":["A15 Bionic chip","Ceramic Shield front cover","Dual-camera system"]} | -0.037794,0.856278,0.202892,0.015594 | +------+-----------+------------------+-------+--------------+------------+------------------+------+------------+---------------+----------------------------------------+------------------------------------------------------------------------------------+--------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET price =15000.99, discounted_price =445.88, vector= (-0.037794, 0.856278, 0.202892, 0.015594) WHERE id = 336; select * from tbl WHERE id = 336;" ––– output ––– +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ | 336 | HTC Wildfire E1 lite | 32 | black | 2021 | 15000.990234 | 445.880005 | 1 | 3438054384 | 102,103,104 | 2923456764345678976,9223372036854775807 | {"features":["MediaTek Helio A20","6.1-inch display","Dual-camera system"]} | -0.037794,0.856278,0.202892,0.015594 | +------+----------------------+------------------+-------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+--------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model='iPhone 13 Pro Max', color='titan', storage_capacity=1000, release_year=2023, sold=1, date_added=4077861360, product_codes=(30,31,32), values= (9223372444854775807,9223372036812375244), additional_info= '{\"features\":[\"Snapdragon 900\",\"8.0-inch display\",\"Dual-camera new-system\"]}', vector= (-0.037795, 0.856278, 0.202892, -0.015595) WHERE id = 247; select * from tbl WHERE id = 247;" ––– output ––– +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ | 247 | iPhone 13 Pro Max | 1000 | titan | 2023 | 799.989990 | 699.989990 | 1 | 4077861360 | 30,31,32 | 9223372036812375244,9223372036854775807 | {"features":["Snapdragon 900","8.0-inch display","Dual-camera new-system"]} | -0.037795,0.856278,0.202892,-0.015595 | +------+-------------------+------------------+-------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+---------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET additional_info= '{\"features\":[\"Snapdragon 900\",\"8.0-inch display\",\"Dual-camera new-system\"]}' WHERE id = 323; select * from tbl WHERE id =323;" ––– output ––– +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ | 323 | Asus ROG Phone 5 | 256 | phantom black | 2021 | 999.989990 | 899.989990 | 0 | 3632367600 | 66,67,68 | 1723456764345678976,9223372036854775807 | {"features":["Snapdragon 900","8.0-inch display","Dual-camera new-system"]} | 0.071106,0.968278,0.311892,0.124594 | +------+------------------+------------------+---------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+-----------------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "Replace Into tbl Set price =15780.99, discounted_price =335.88 WHERE id = 335; select * from tbl WHERE id = 335;" ––– output ––– +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ | 335 | LG G8 ThinQ | 128 | aurora black | 2019 | 15780.990234 | 335.880005 | 0 | 1194467680 | 99,100,101 | 2823456764345678976,9223372036854775807 | {"features":["Snapdragon 855","6.1-inch display","Dual-camera system"]} | -0.118894,0.778278,0.121892,-0.065406 | +------+-------------+------------------+--------------+--------------+--------------+------------------+------+------------+---------------+-----------------------------------------+-------------------------------------------------------------------------+---------------------------------------+ ––– input ––– curl -X POST "http://localhost:9308/tbl/_update/101" -H "Content-Type: application/json" -d '{"doc": {"model": "new-model", "color": "new-color"}}'; echo $? ––– output ––– {"_index":"tbl","updated":1}0 ––– input ––– start_time=$(date +%s%3N) && for i in {1..5000}; do echo "REPLACE INTO tbl SET color = 'color_$i' WHERE id = ${i}01;"; done > /tmp/replace_dump && mysql -h0 -P9306 < /tmp/replace_dump > /dev/null && end_time=$(date +%s%3N) && export elapsed_time=$((end_time - start_time)) ––– output ––– ––– input ––– echo $elapsed_time ––– output ––– #!/[0-9]{4,5}/!# ––– input ––– mysql -h0 -P9306 -e "select count(*) from tbl WHERE regex (color, 'color_[0-9]+')" ––– output ––– +----------+ | count(*) | +----------+ | 5000 | +----------+ ––– input ––– start_time2=$(date +%s%3N) && for i in {1..5000}; do echo "REPLACE INTO tbl (id, model, storage_capacity, color, release_year, price, discounted_price, sold, date_added, product_codes, values, additional_info, vector) VALUES (${i}101, 'iPhone 13 Pro', 256, 'silver${i}', 2021, 1099.99, 989.99, 'TRUE', '1591362342000', (1,2,3), (523456764345678976, 98765409877866654098, 1109876543450987650987), '{\"features\": [\"ProMotion display\", \"A15 Bionic chip\", \"Ceramic Shield front cover\"]}', (0.773448, 0.312478, 0.137971, 0.459821));"; done > /tmp/replace_dump && mysql -h0 -P9306 < /tmp/replace_dump > /dev/null && end_time2=$(date +%s%3N) && export elapsed_time2=$((end_time2 - start_time2)) ––– output ––– ––– input ––– echo $elapsed_time2 ––– output ––– #!/[0-9]{3,4}/!# ––– input ––– mysql -h0 -P9306 -e "select count(*) from tbl WHERE regex (color, 'silver[0-9]+')" ––– output ––– +----------+ | count(*) | +----------+ | 5000 | +----------+ ––– input ––– echo $((elapsed_time / elapsed_time2)) ––– output ––– - #!/[1-9]|1[0-2]/!# + 10 ––– input ––– mysql -v -h0 -P9306 -e "SHOW TABLES; create table abc(id, t1 text, t2 text indexed) engine='columnar'; insert into abc VALUES (1, 'abc', 'cde'); desc abc;" ––– output ––– -------------- SHOW TABLES -------------- +-------+------+ | Index | Type | +-------+------+ | tbl | rt | +-------+------+ -------------- create table abc(id, t1 text, t2 text indexed) engine='columnar' -------------- -------------- insert into abc VALUES (1, 'abc', 'cde') -------------- -------------- desc abc -------------- +-------+--------+----------------+ | Field | Type | Properties | +-------+--------+----------------+ | id | bigint | columnar | | t1 | text | indexed stored | | t2 | text | indexed | +-------+--------+----------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO abc SET t1 ='iPhone 15 PRO' WHERE id = 1;" ––– output ––– ERROR 1064 (42000) at line 1: Field t2 doesn't have stored property. Replace query can't be performed ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 16 PRO MAX' WHERE id > 105;" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 16 PRO MAX' WHERE id > 105' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 17 PRO MAX' WHERE id = 'abs';" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 17 PRO MAX' WHERE id = 'abs'' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 18 PRO' WHERE id in (1,2,3);" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 18 PRO' WHERE id in (1,2,3)' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model ='iPhone 18 PRO' WHERE sold = 0;" ––– output ––– ERROR 1064 (42000) at line 1: P01: syntax error, unexpected SET, expecting VALUES near 'SET model ='iPhone 18 PRO' WHERE sold = 0' ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET id =777 WHERE id = 144; select * from tbl WHERE id = 144;" ––– output ––– +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ | id | model | storage_capacity | color | release_year | price | discounted_price | sold | date_added | product_codes | values | additional_info | vector | +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ | 144 | Sony Xperia XZ3 | 64 | forest green | 2018 | 899.989990 | 799.989990 | 1 | 3308512240 | 126,127,128 | 3723456764345678976,9223372036854775807 | {"features":["Snapdragon 845","6.0-inch display","Single camera"]} | 0.733448,0.272478,0.097971,0.419821 | +------+-----------------+------------------+--------------+--------------+------------+------------------+------+------------+---------------+-----------------------------------------+--------------------------------------------------------------------+-------------------------------------+ ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET model = 'Sony Xperia X\'Z3' WHERE id = 144; select * from tbl WHERE id = 144\G" ––– output ––– *************************** 1. row *************************** id: 144 model: Sony Xperia X'Z3 storage_capacity: 64 color: forest green release_year: 2018 price: 899.989990 discounted_price: 799.989990 sold: 1 date_added: 3308512240 product_codes: 126,127,128 values: 3723456764345678976,9223372036854775807 additional_info: {"features":["Snapdragon 845","6.0-inch display","Single camera"]} vector: 0.733448,0.272478,0.097971,0.419821 ––– input ––– mysql -h0 -P9306 -e "REPLACE INTO tbl SET release_year = 2019 WHERE id = 144; select * from tbl WHERE id = 144\G" ––– output ––– *************************** 1. row *************************** id: 144 model: Sony Xperia X'Z3 storage_capacity: 64 color: forest green release_year: 2019 price: 899.989990 discounted_price: 799.989990 sold: 1 date_added: 3308512240 product_codes: 126,127,128 values: 3723456764345678976,9223372036854775807 additional_info: {"features":["Snapdragon 845","6.0-inch display","Single camera"]} vector: 0.733448,0.272478,0.097971,0.419821 ```