[MCOL-2127] query returns empty set if it's executed after failing query in oracle sql-mode Created: 2019-01-30  Updated: 2023-03-06  Resolved: 2023-03-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.2.2
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: None
Environment:

mcs single server. CentOS7.5



 Description   

query returns empty set if it's executed after failing query in oracle sql-mode

set sql-mode=oracle
run query30 several times, it's rerunning 100 records as the it's set limit 100 in the query
run query query28 --it's fails because it's used "" quoting in the query but sql-mode=oracle
sets ANSI_QUOTES flag.
run again query30 – it returns nothing

set sql-mode=oracle

MariaDB [(none)]>  set global sql_mode = 'oracle' ;
Query OK, 0 rows affected (0.000 sec)
 
MariaDB [(none)]> exit
Bye
[root@columnstore mariadb-columnstore-tpcds]# mcsmysql -u root -p1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.11-MariaDB-log Columnstore 1.2.2-1
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> select @@sql_mode ;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 

run query30 several times, it's rerunning 100 records as the it's set limit 100 in the query

[root@columnstore mariadb-columnstore-tpcds]# mcsmysql -u root -p1 tpcds_1 < validate_mdb_query_templates/query30.sql
c_customer_id   c_salutation    c_first_name    c_last_name     c_preferred_cust_flag   c_birth_day     c_birth_month   c_birth_year    c_birth_country c_login c_email_address c_last_review_date      ctr_total_return
AAAAAAAAAAAIBAAA        Mr.     Eduardo Goodwin Y       24      11      1950    GHANA   NULL    Eduardo.Goodwin@rolemn2xusv5Ot0.edu     2452312 3838
AAAAAAAAABHAAAAA        Sir     Robert  Thornton        Y       24      5       1924    HONG KONG       NULL    Robert.Thornton@jas.edu 2452431 2434.58
AAAAAAAAABPNAAAA        Mr.     Alonzo  Huddleston      Y       19      4       1957    FAROE ISLANDS   NULL    Alonzo.Huddleston@g.edu 2452289 2813
AAAAAAAAAFCGBAAA        Ms.     Pamela  Slater  Y       2       6       1931    GAMBIA  NULL    Pamela.Slater@ajU1sMrf5vC7.com  2452613 1746.58
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 1850.7
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 10898.4
AAAAAAAAAFOEBAAA        Dr.     Trevor  Battle  N       12      8       1953    THAILAND        NULL    Trevor.Battle@0F.com    2452643 3556
AAAAAAAAAIKEAAAA        Dr.     Joseph  Miller  N       17      12      1930    MONACO  NULL    Joseph.Miller@Ampmde.edu        2452332 7037.55
AAAAAAAAAJMABAAA        Miss    Brenda  Sloan   Y       23      3       1982    MALAYSIA        NULL    Brenda.Sloan@csCoX.edu  2452336 1783.19
AAAAAAAAAKDNAAAA        NULL    NULL    NULL    NULL    NULL    12      NULL    NULL    NULL    NULL    2452544 2448.03
AAAAAAAAANCLAAAA        Mr.     Raymond Dixon   N       31      12      1977    MONTENEGRO      NULL    Raymond.Dixon@07IH.edu  2452514 1785.96
AAAAAAAAANNNAAAA        Ms.     Cindy   Julian  Y       4       8       1982    UGANDA  NULL    Cindy.Julian@ChEB5eyUCBe.org    2452368 2449.04
AAAAAAAAAPKJAAAA        Ms.     Elizabeth       Pepper  Y       28      12      1930    NICARAGUA       NULL    Elizabeth.Pepper@xlygC22.org    2452645 1764.4
AAAAAAAABAKCBAAA        Miss    Denise  Brock   Y       10      4       1958    JAMAICA NULL    Denise.Brock@7ffTBTNaX7is.com   2452435 1618.24
AAAAAAAABBMDAAAA        Dr.     Walter  Bernard Y       2       10      1948    BELARUS NULL    Walter.Bernard@Th.org   2452628 1755.66
AAAAAAAABIBBBAAA        Miss    Lorene  Mayfield        N       17      12      1947    ALBANIA NULL    Lorene.Mayfield@ECni6UFkF2.edu  2452450 3157.7
AAAAAAAABIIDAAAA        Mr.     Paul    Stevenson       N       26      2       1953    BARBADOS        NULL    Paul.Stevenson@tHMZG9.com       2452488 1947.77
AAAAAAAABMJAAAAA        Dr.     Marion  Mclain  N       16      2       1931    ANGUILLA        NULL    Marion.Mclain@PKik2BMH.com      2452382 3134.46
AAAAAAAABNFHBAAA        Mr.     Kristofer       Payne   N       12      5       1945    SYRIAN ARAB REPUBLIC    NULL    Kristofer.Payne@nc70sAM.com     2452436 3813.33
AAAAAAAABOLLAAAA        Miss    Lisa    Way     N       11      1       1941    LEBANON NULL    Lisa.Way@nnVbE2MvTT.com 2452383 1829.88
AAAAAAAABPHKAAAA        Dr.     Gabriela        Jerome  Y       9       7       1969    IRAQ    NULL    Gabriela.Jerome@Hay.com 2452567 5360.55
AAAAAAAACBAFAAAA        Dr.     Timothy Carroll Y       14      8       1957    UGANDA  NULL    Timothy.Carroll@pUcgCvxC4nkcM.edu       2452535 1642.8
AAAAAAAACBPMAAAA        Ms.     Shana   Fulton  N       10      2       1955    RWANDA  NULL    Shana.Fulton@2CAVqR7d.org       2452378 2089.36
AAAAAAAACCFCBAAA        Mr.     Mark    Pacheco N       9       3       1956    YEMEN   NULL    Mark.Pacheco@NMMki.edu  2452377 1838.48
AAAAAAAACCMEAAAA        Dr.     Adriane Harmon  N       1       10      1964    ARGENTINA       NULL    Adriane.Harmon@X0nMJZPDL.com    2452562 8986.95
AAAAAAAACCNJAAAA        Dr.     Elizabeth       Gilbreath       Y       17      1       1985    KIRIBATI        NULL    Elizabeth.Gilbreath@4LxlO8fArnEeEavQe.com       2452485 2450.86
AAAAAAAACDDNAAAA        Ms.     Holly   Kim     N       15      6       1941    ICELAND NULL    Holly.Kim@OkTfC7tRYgy4.edu      2452580 2719.44
AAAAAAAACGILAAAA        Sir     Nolan   Mosley  N       20      3       1980    KENYA   NULL    Nolan.Mosley@5E.org     2452317 1865.64
AAAAAAAACHFHBAAA        Mr.     Dallas  Courtney        Y       21      5       1928    CÔTE D'IVOIRE   NULL    Dallas.Courtney@c7.org  2452496 4394.79
AAAAAAAACKOFAAAA        Dr.     Eric    Bucher  Y       19      9       1979    ALBANIA NULL    Eric.Bucher@sxSne.org   2452541 4662.35
AAAAAAAACMHNAAAA        Miss    Lisa    Dukes   Y       4       3       1964    SOLOMON ISLANDS NULL    Lisa.Dukes@tuRJRXkQm.com        2452303 10387.84
AAAAAAAACNGBAAAA        Mr.     Michael Haley   Y       10      5       1933    SWAZILAND       NULL    Michael.Haley@OfAOvhrm.com      2452359 4187.82
AAAAAAAACOFAAAAA        Ms.     Tammy   Searcy  N       6       5       1989    SURINAME        NULL    Tammy.Searcy@6ZFFGtIuCqT.com    2452295 5091.84
AAAAAAAADBBHAAAA        Dr.     Charles Fernandez       N       8       12      1992    CAMBODIA        NULL    Charles.Fernandez@MVhjrzVNVY.com        2452574 4640.37
AAAAAAAADFCBBAAA        Dr.     Micheal Collier N       1       9       1934    HONDURAS        NULL    Micheal.Collier@NztJvzb.org     2452446 15944.4
AAAAAAAADIHHAAAA        Miss    Vikki   Garza   Y       29      8       1950    NAMIBIA NULL    Vikki.Garza@v.edu       2452608 2373.84
AAAAAAAADJAPAAAA        Ms.     Angel   Dodd    Y       22      12      1950    TIMOR-LESTE     NULL    Angel.Dodd@gQOLRKqU285BfTIT.edu 2452634 2227.83
AAAAAAAADKEFAAAA        Ms.     Martha  Dillon  N       9       12      1971    VANUATU NULL    Martha.Dillon@iKkeDtKERbu.org   2452404 1863.4
AAAAAAAADKIFAAAA        Sir     Walter  Stout   Y       18      1       1970    BURUNDI NULL    Walter.Stout@Q961tU4iePsOy.org  2452449 2465.55
AAAAAAAADLHNAAAA        Mr.     Joseph  Mckinney        Y       23      3       1940    ARMENIA NULL    Joseph.Mckinney@4YYp7B8ccPz.edu 2452595 1820.02
AAAAAAAADMMDBAAA        Mr.     Darrell Padgett Y       17      3       1992    HONDURAS        NULL    Darrell.Padgett@IrqARXX.org     2452554 2052.54
AAAAAAAADNHPAAAA        Mr.     John    Mathews N       23      2       1925    NICARAGUA       NULL    John.Mathews@UOtpEd.edu 2452543 7883.4
AAAAAAAADOCMAAAA        Dr.     Yvonne  Poirier Y       30      1       1950    ESTONIA NULL    Yvonne.Poirier@l.org    2452648 4075.55
AAAAAAAADODBAAAA        Mrs.    Elizabeth       Spalding        N       27      11      1947    KAZAKHSTAN      NULL    Elizabeth.Spalding@ihnjlmxMbs.edu       2452318 10349.68
AAAAAAAAEBAOAAAA        Miss    Amy     Causey  N       21      9       1951    COMOROS NULL    Amy.Causey@7EvHzf02opq6QlO.com  2452443 8005.4
AAAAAAAAEBLEAAAA        Mr.     Leroy   Couture N       7       5       1938    SWITZERLAND     NULL    Leroy.Couture@vqrL.com  2452595 1683.88
AAAAAAAAEEADAAAA        Mr.     Robert  Donohue Y       21      8       1938    SWAZILAND       NULL    Robert.Donohue@U2xz7.org        2452436 11083.8
AAAAAAAAEHAABAAA        Dr.     Victoria        Morgan  Y       31      5       1960    JERSEY  NULL    Victoria.Morgan@Sk3jzrDZTq.org  2452646 1712.16
AAAAAAAAEHGOAAAA        Miss    Jillian Nelson  Y       27      7       1978    TURKMENISTAN    NULL    Jillian.Nelson@ph0qObqh3fbv.com 2452315 4432.69
AAAAAAAAEHPNAAAA        Dr.     Rachael King    Y       28      11      1927    PANAMA  NULL    Rachael.King@t8dA2klrEIBiCd.edu 2452438 2251.5
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1783.12
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1857.63
AAAAAAAAEIOBBAAA        Mr.     Gary    Davis   N       9       6       1992    KYRGYZSTAN      NULL    Gary.Davis@GTXc2AbKvkM.edu      2452322 1652.7
AAAAAAAAEJFNAAAA        Dr.     Chad    Baldwin Y       11      11      1937    BULGARIA        NULL    Chad.Baldwin@cN988.org  2452560 11133.36
AAAAAAAAEJOPAAAA        Dr.     Alexandra       Henderson       N       4       2       1986    MONTENEGRO      NULL    Alexandra.Henderson@4FCXABbkVkrLK.com   2452376 3426.36
AAAAAAAAELEBAAAA        Mrs.    Dianne  Ross    Y       19      7       1949    ICELAND NULL    Dianne.Ross@D4VH75e9n.org       2452429 4018.3
AAAAAAAAEMDIBAAA        Sir     Scott   Obrien  Y       3       8       1926    JERSEY  NULL    Scott.Obrien@a84VTAJCTB.com     2452504 1456.67
AAAAAAAAEMIAAAAA        Dr.     Danny   Ness    Y       25      10      1955    IRAQ    NULL    Danny.Ness@CC8vAp.edu   2452487 5569.5
AAAAAAAAEMNGBAAA        Mr.     Milton  Oakley  Y       10      10      1976    FINLAND NULL    Milton.Oakley@O4DHXT.com        2452355 1505.16
AAAAAAAAEMPKAAAA        Sir     Gilbert Palmer  N       23      10      1933    BRUNEI DARUSSALAM       NULL    Gilbert.Palmer@7UYjIOOIg0.org   2452463 2391.95
AAAAAAAAENFLAAAA        Sir     Joseph  Gaffney N       22      1       1967    BULGARIA        NULL    Joseph.Gaffney@nBDj.org 2452608 6135.48
AAAAAAAAEPELAAAA        Dr.     Emily   Thomas  Y       14      3       1981    MOZAMBIQUE      NULL    Emily.Thomas@qtJnzLvYcK598T.edu 2452617 3239.04
AAAAAAAAFBLHAAAA        Mr.     Carter  Lombardo        N       22      11      1950    MONACO  NULL    Carter.Lombardo@X.edu   2452582 3965.43
AAAAAAAAFHODBAAA        Ms.     Diana   Hall    N       14      7       1934    LATVIA  NULL    Diana.Hall@Be.com       2452546 9391.76
AAAAAAAAFLAJAAAA        Mr.     John    Hardy   Y       4       9       1977    ANGUILLA        NULL    John.Hardy@LnaYnyZ9AYee.edu     2452289 1640.8
AAAAAAAAFLDFBAAA        Mr.     Demetrius       Hunt    N       30      8       1956    ZAMBIA  NULL    Demetrius.Hunt@J3.com   2452647 3673.6
AAAAAAAAFMCFBAAA        Mrs.    Karen   Middleton       Y       21      10      1934    GUADELOUPE      NULL    Karen.Middleton@q.com   2452343 1415.7
AAAAAAAAFMENAAAA        Ms.     Anne    Jernigan        Y       14      8       1952    ITALY   NULL    Anne.Jernigan@BBtNT7SneOEKG.edu 2452612 1735.14
AAAAAAAAFMLEAAAA        Mr.     Gavin   NULL    NULL    21      NULL    1933    BRAZIL  NULL    Gavin.Maples@AT6ClrGUc79.com    NULL    4882.15
AAAAAAAAFMPMAAAA        Sir     Jack    Thomas  Y       12      1       1971    ARMENIA NULL    Jack.Thomas@dn0mBXIEu7XPH.com   2452409 2501.94
AAAAAAAAFNHBBAAA        Miss    Jennifer        Long    Y       12      6       1984    VENEZUELA       NULL    Jennifer.Long@RyRC5oxxCoVPG.com 2452285 4430.88
AAAAAAAAGAGEAAAA        Sir     Jeffrey Cleveland       N       2       8       1974    GUADELOUPE      NULL    Jeffrey.Cleveland@ijNnAoDZQajhuz.edu    2452380 4215.15
AAAAAAAAGCNPAAAA        Sir     Jack    Stewart N       19      12      1926    SAN MARINO      NULL    Jack.Stewart@G6N710E1oO.edu     2452618 2859.5
AAAAAAAAGFKEAAAA        Dr.     NULL    Brown   NULL    NULL    4       1933    ROMANIA NULL    NULL    NULL    11364.32
AAAAAAAAGGBIBAAA        Dr.     Michael Christiansen    Y       4       3       1947    ISRAEL  NULL    Michael.Christiansen@kQmauEb.org        2452583 1726.2
AAAAAAAAGHHEAAAA        Sir     Jerry   Glover  Y       3       6       1942    ANTIGUA AND BARBUDA     NULL    Jerry.Glover@yCUI3N8C0o7LPvL.edu        2452645 6382.26
AAAAAAAAGICFAAAA        Mr.     Charles Sloan   Y       8       4       1991    KOREA, REPUBLIC OF      NULL    Charles.Sloan@gnfGZRA0F.com     2452431 2793.57
AAAAAAAAGIFHBAAA        Sir     Joe     Jackson N       31      10      1969    FRENCH POLYNESIA        NULL    Joe.Jackson@eHmBJ4QqjNKJypF.com 2452534 1692.69
AAAAAAAAGJEGBAAA        Dr.     Carlos  Hampton N       18      11      1947    DOMINICA        NULL    Carlos.Hampton@nrYQ8MXgIunX6gf.com      2452470 5556.75
AAAAAAAAGMDJAAAA        Dr.     Christopher     Payne   Y       30      12      1958    DJIBOUTI        NULL    Christopher.Payne@R6POV.org     2452350 1638.72
AAAAAAAAGOPMAAAA        Mrs.    Donna   West    N       2       12      1977    SOLOMON ISLANDS NULL    Donna.West@6Q.com       2452645 1623.2
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 4377.3
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 5013.12
AAAAAAAAHCJKAAAA        Ms.     Emma    Bailey  N       2       10      1935    LATVIA  NULL    Emma.Bailey@beDlbUDUUcPhilRyy9.edu      2452491 5638.14
AAAAAAAAHGCHBAAA        Dr.     Thomas  Hull    Y       24      2       1931    DENMARK NULL    Thomas.Hull@XobY92y.com 2452648 2487.6
AAAAAAAAHGIFAAAA        Sir     James   Turner  N       25      10      1937    JERSEY  NULL    James.Turner@4RGTbOoV.org       2452293 2757.44
AAAAAAAAHIIDBAAA        Mrs.    Andrew  Barrett Y       24      5       1992    GUERNSEY        NULL    Andrew.Barrett@i85phxAtKhZfES.com       2452319 7854.67
AAAAAAAAHJOBAAAA        Mrs.    April   Wallace N       19      7       1930    FIJI    NULL    April.Wallace@9iuUi3U0K.edu     2452382 4596.6
AAAAAAAAHMEFBAAA        NULL    Joshua  Bruno   NULL    1       11      NULL    NULL    NULL    Joshua.Bruno@7Ds6hF5nMgOTel9.org        2452303 1806.33
AAAAAAAAHMNNAAAA        Sir     Jeffery Thomas  Y       8       3       1929    MONTSERRAT      NULL    Jeffery.Thomas@Ld7t.org 2452598 2441.4
AAAAAAAAICMGAAAA        Miss    Edith   Robinson        Y       11      7       1967    PUERTO RICO     NULL    Edith.Robinson@zuf2DUz3bhKnEB.org       2452319 7193.61
AAAAAAAAIGLCBAAA        Miss    Erika   Edmondson       Y       1       8       1944    DJIBOUTI        NULL    Erika.Edmondson@BuAbOSrTiD06f.edu       2452460 2381.83
AAAAAAAAIJGGBAAA        Ms.     Carolyn Cardona N       15      9       1936    BULGARIA        NULL    Carolyn.Cardona@EhKjaAPoU8gf.edu        2452453 7659.19
AAAAAAAAIMAHBAAA        Mrs.    Elizabeth       Guzman  Y       19      9       1950    GREECE  NULL    Elizabeth.Guzman@4Rq0Cyh9c.edu  2452315 1856.2
AAAAAAAAIMGFAAAA        Dr.     Paul    Powell  Y       28      11      1935    GREECE  NULL    Paul.Powell@3EgM4I28gtaqb.org   2452320 8416.8
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 1552.46
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 2639.34
AAAAAAAAIMPNAAAA        Sir     Charles Chadwick        N       21      1       1936    ALAND ISLANDS   NULL    Charles.Chadwick@ofbPzGan.org   2452414 4241.44
AAAAAAAAIPHCAAAA        Mr.     Robert  Parham  Y       9       2       1967    ETHIOPIA        NULL    Robert.Parham@0GnPcZbUE.org     2452519 4317.18
AAAAAAAAJABDBAAA        NULL    NULL    NULL    NULL    9       6       1972    TIMOR-LESTE     NULL    NULL    NULL    2387.28
[root@columnstore mariadb-columnstore-tpcds]# mcsmysql -u root -p1 tpcds_1 < validate_mdb_query_templates/query30.sql
c_customer_id   c_salutation    c_first_name    c_last_name     c_preferred_cust_flag   c_birth_day     c_birth_month   c_birth_year    c_birth_country c_login c_email_address c_last_review_date      ctr_total_return
AAAAAAAAAAAIBAAA        Mr.     Eduardo Goodwin Y       24      11      1950    GHANA   NULL    Eduardo.Goodwin@rolemn2xusv5Ot0.edu     2452312 3838
AAAAAAAAABHAAAAA        Sir     Robert  Thornton        Y       24      5       1924    HONG KONG       NULL    Robert.Thornton@jas.edu 2452431 2434.58
AAAAAAAAABPNAAAA        Mr.     Alonzo  Huddleston      Y       19      4       1957    FAROE ISLANDS   NULL    Alonzo.Huddleston@g.edu 2452289 2813
AAAAAAAAAFCGBAAA        Ms.     Pamela  Slater  Y       2       6       1931    GAMBIA  NULL    Pamela.Slater@ajU1sMrf5vC7.com  2452613 1746.58
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 1850.7
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 10898.4
AAAAAAAAAFOEBAAA        Dr.     Trevor  Battle  N       12      8       1953    THAILAND        NULL    Trevor.Battle@0F.com    2452643 3556
AAAAAAAAAIKEAAAA        Dr.     Joseph  Miller  N       17      12      1930    MONACO  NULL    Joseph.Miller@Ampmde.edu        2452332 7037.55
AAAAAAAAAJMABAAA        Miss    Brenda  Sloan   Y       23      3       1982    MALAYSIA        NULL    Brenda.Sloan@csCoX.edu  2452336 1783.19
AAAAAAAAAKDNAAAA        NULL    NULL    NULL    NULL    NULL    12      NULL    NULL    NULL    NULL    2452544 2448.03
AAAAAAAAANCLAAAA        Mr.     Raymond Dixon   N       31      12      1977    MONTENEGRO      NULL    Raymond.Dixon@07IH.edu  2452514 1785.96
AAAAAAAAANNNAAAA        Ms.     Cindy   Julian  Y       4       8       1982    UGANDA  NULL    Cindy.Julian@ChEB5eyUCBe.org    2452368 2449.04
AAAAAAAAAPKJAAAA        Ms.     Elizabeth       Pepper  Y       28      12      1930    NICARAGUA       NULL    Elizabeth.Pepper@xlygC22.org    2452645 1764.4
AAAAAAAABAKCBAAA        Miss    Denise  Brock   Y       10      4       1958    JAMAICA NULL    Denise.Brock@7ffTBTNaX7is.com   2452435 1618.24
AAAAAAAABBMDAAAA        Dr.     Walter  Bernard Y       2       10      1948    BELARUS NULL    Walter.Bernard@Th.org   2452628 1755.66
AAAAAAAABIBBBAAA        Miss    Lorene  Mayfield        N       17      12      1947    ALBANIA NULL    Lorene.Mayfield@ECni6UFkF2.edu  2452450 3157.7
AAAAAAAABIIDAAAA        Mr.     Paul    Stevenson       N       26      2       1953    BARBADOS        NULL    Paul.Stevenson@tHMZG9.com       2452488 1947.77
AAAAAAAABMJAAAAA        Dr.     Marion  Mclain  N       16      2       1931    ANGUILLA        NULL    Marion.Mclain@PKik2BMH.com      2452382 3134.46
AAAAAAAABNFHBAAA        Mr.     Kristofer       Payne   N       12      5       1945    SYRIAN ARAB REPUBLIC    NULL    Kristofer.Payne@nc70sAM.com     2452436 3813.33
AAAAAAAABOLLAAAA        Miss    Lisa    Way     N       11      1       1941    LEBANON NULL    Lisa.Way@nnVbE2MvTT.com 2452383 1829.88
AAAAAAAABPHKAAAA        Dr.     Gabriela        Jerome  Y       9       7       1969    IRAQ    NULL    Gabriela.Jerome@Hay.com 2452567 5360.55
AAAAAAAACBAFAAAA        Dr.     Timothy Carroll Y       14      8       1957    UGANDA  NULL    Timothy.Carroll@pUcgCvxC4nkcM.edu       2452535 1642.8
AAAAAAAACBPMAAAA        Ms.     Shana   Fulton  N       10      2       1955    RWANDA  NULL    Shana.Fulton@2CAVqR7d.org       2452378 2089.36
AAAAAAAACCFCBAAA        Mr.     Mark    Pacheco N       9       3       1956    YEMEN   NULL    Mark.Pacheco@NMMki.edu  2452377 1838.48
AAAAAAAACCMEAAAA        Dr.     Adriane Harmon  N       1       10      1964    ARGENTINA       NULL    Adriane.Harmon@X0nMJZPDL.com    2452562 8986.95
AAAAAAAACCNJAAAA        Dr.     Elizabeth       Gilbreath       Y       17      1       1985    KIRIBATI        NULL    Elizabeth.Gilbreath@4LxlO8fArnEeEavQe.com       2452485 2450.86
AAAAAAAACDDNAAAA        Ms.     Holly   Kim     N       15      6       1941    ICELAND NULL    Holly.Kim@OkTfC7tRYgy4.edu      2452580 2719.44
AAAAAAAACGILAAAA        Sir     Nolan   Mosley  N       20      3       1980    KENYA   NULL    Nolan.Mosley@5E.org     2452317 1865.64
AAAAAAAACHFHBAAA        Mr.     Dallas  Courtney        Y       21      5       1928    CÔTE D'IVOIRE   NULL    Dallas.Courtney@c7.org  2452496 4394.79
AAAAAAAACKOFAAAA        Dr.     Eric    Bucher  Y       19      9       1979    ALBANIA NULL    Eric.Bucher@sxSne.org   2452541 4662.35
AAAAAAAACMHNAAAA        Miss    Lisa    Dukes   Y       4       3       1964    SOLOMON ISLANDS NULL    Lisa.Dukes@tuRJRXkQm.com        2452303 10387.84
AAAAAAAACNGBAAAA        Mr.     Michael Haley   Y       10      5       1933    SWAZILAND       NULL    Michael.Haley@OfAOvhrm.com      2452359 4187.82
AAAAAAAACOFAAAAA        Ms.     Tammy   Searcy  N       6       5       1989    SURINAME        NULL    Tammy.Searcy@6ZFFGtIuCqT.com    2452295 5091.84
AAAAAAAADBBHAAAA        Dr.     Charles Fernandez       N       8       12      1992    CAMBODIA        NULL    Charles.Fernandez@MVhjrzVNVY.com        2452574 4640.37
AAAAAAAADFCBBAAA        Dr.     Micheal Collier N       1       9       1934    HONDURAS        NULL    Micheal.Collier@NztJvzb.org     2452446 15944.4
AAAAAAAADIHHAAAA        Miss    Vikki   Garza   Y       29      8       1950    NAMIBIA NULL    Vikki.Garza@v.edu       2452608 2373.84
AAAAAAAADJAPAAAA        Ms.     Angel   Dodd    Y       22      12      1950    TIMOR-LESTE     NULL    Angel.Dodd@gQOLRKqU285BfTIT.edu 2452634 2227.83
AAAAAAAADKEFAAAA        Ms.     Martha  Dillon  N       9       12      1971    VANUATU NULL    Martha.Dillon@iKkeDtKERbu.org   2452404 1863.4
AAAAAAAADKIFAAAA        Sir     Walter  Stout   Y       18      1       1970    BURUNDI NULL    Walter.Stout@Q961tU4iePsOy.org  2452449 2465.55
AAAAAAAADLHNAAAA        Mr.     Joseph  Mckinney        Y       23      3       1940    ARMENIA NULL    Joseph.Mckinney@4YYp7B8ccPz.edu 2452595 1820.02
AAAAAAAADMMDBAAA        Mr.     Darrell Padgett Y       17      3       1992    HONDURAS        NULL    Darrell.Padgett@IrqARXX.org     2452554 2052.54
AAAAAAAADNHPAAAA        Mr.     John    Mathews N       23      2       1925    NICARAGUA       NULL    John.Mathews@UOtpEd.edu 2452543 7883.4
AAAAAAAADOCMAAAA        Dr.     Yvonne  Poirier Y       30      1       1950    ESTONIA NULL    Yvonne.Poirier@l.org    2452648 4075.55
AAAAAAAADODBAAAA        Mrs.    Elizabeth       Spalding        N       27      11      1947    KAZAKHSTAN      NULL    Elizabeth.Spalding@ihnjlmxMbs.edu       2452318 10349.68
AAAAAAAAEBAOAAAA        Miss    Amy     Causey  N       21      9       1951    COMOROS NULL    Amy.Causey@7EvHzf02opq6QlO.com  2452443 8005.4
AAAAAAAAEBLEAAAA        Mr.     Leroy   Couture N       7       5       1938    SWITZERLAND     NULL    Leroy.Couture@vqrL.com  2452595 1683.88
AAAAAAAAEEADAAAA        Mr.     Robert  Donohue Y       21      8       1938    SWAZILAND       NULL    Robert.Donohue@U2xz7.org        2452436 11083.8
AAAAAAAAEHAABAAA        Dr.     Victoria        Morgan  Y       31      5       1960    JERSEY  NULL    Victoria.Morgan@Sk3jzrDZTq.org  2452646 1712.16
AAAAAAAAEHGOAAAA        Miss    Jillian Nelson  Y       27      7       1978    TURKMENISTAN    NULL    Jillian.Nelson@ph0qObqh3fbv.com 2452315 4432.69
AAAAAAAAEHPNAAAA        Dr.     Rachael King    Y       28      11      1927    PANAMA  NULL    Rachael.King@t8dA2klrEIBiCd.edu 2452438 2251.5
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1783.12
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1857.63
AAAAAAAAEIOBBAAA        Mr.     Gary    Davis   N       9       6       1992    KYRGYZSTAN      NULL    Gary.Davis@GTXc2AbKvkM.edu      2452322 1652.7
AAAAAAAAEJFNAAAA        Dr.     Chad    Baldwin Y       11      11      1937    BULGARIA        NULL    Chad.Baldwin@cN988.org  2452560 11133.36
AAAAAAAAEJOPAAAA        Dr.     Alexandra       Henderson       N       4       2       1986    MONTENEGRO      NULL    Alexandra.Henderson@4FCXABbkVkrLK.com   2452376 3426.36
AAAAAAAAELEBAAAA        Mrs.    Dianne  Ross    Y       19      7       1949    ICELAND NULL    Dianne.Ross@D4VH75e9n.org       2452429 4018.3
AAAAAAAAEMDIBAAA        Sir     Scott   Obrien  Y       3       8       1926    JERSEY  NULL    Scott.Obrien@a84VTAJCTB.com     2452504 1456.67
AAAAAAAAEMIAAAAA        Dr.     Danny   Ness    Y       25      10      1955    IRAQ    NULL    Danny.Ness@CC8vAp.edu   2452487 5569.5
AAAAAAAAEMNGBAAA        Mr.     Milton  Oakley  Y       10      10      1976    FINLAND NULL    Milton.Oakley@O4DHXT.com        2452355 1505.16
AAAAAAAAEMPKAAAA        Sir     Gilbert Palmer  N       23      10      1933    BRUNEI DARUSSALAM       NULL    Gilbert.Palmer@7UYjIOOIg0.org   2452463 2391.95
AAAAAAAAENFLAAAA        Sir     Joseph  Gaffney N       22      1       1967    BULGARIA        NULL    Joseph.Gaffney@nBDj.org 2452608 6135.48
AAAAAAAAEPELAAAA        Dr.     Emily   Thomas  Y       14      3       1981    MOZAMBIQUE      NULL    Emily.Thomas@qtJnzLvYcK598T.edu 2452617 3239.04
AAAAAAAAFBLHAAAA        Mr.     Carter  Lombardo        N       22      11      1950    MONACO  NULL    Carter.Lombardo@X.edu   2452582 3965.43
AAAAAAAAFHODBAAA        Ms.     Diana   Hall    N       14      7       1934    LATVIA  NULL    Diana.Hall@Be.com       2452546 9391.76
AAAAAAAAFLAJAAAA        Mr.     John    Hardy   Y       4       9       1977    ANGUILLA        NULL    John.Hardy@LnaYnyZ9AYee.edu     2452289 1640.8
AAAAAAAAFLDFBAAA        Mr.     Demetrius       Hunt    N       30      8       1956    ZAMBIA  NULL    Demetrius.Hunt@J3.com   2452647 3673.6
AAAAAAAAFMCFBAAA        Mrs.    Karen   Middleton       Y       21      10      1934    GUADELOUPE      NULL    Karen.Middleton@q.com   2452343 1415.7
AAAAAAAAFMENAAAA        Ms.     Anne    Jernigan        Y       14      8       1952    ITALY   NULL    Anne.Jernigan@BBtNT7SneOEKG.edu 2452612 1735.14
AAAAAAAAFMLEAAAA        Mr.     Gavin   NULL    NULL    21      NULL    1933    BRAZIL  NULL    Gavin.Maples@AT6ClrGUc79.com    NULL    4882.15
AAAAAAAAFMPMAAAA        Sir     Jack    Thomas  Y       12      1       1971    ARMENIA NULL    Jack.Thomas@dn0mBXIEu7XPH.com   2452409 2501.94
AAAAAAAAFNHBBAAA        Miss    Jennifer        Long    Y       12      6       1984    VENEZUELA       NULL    Jennifer.Long@RyRC5oxxCoVPG.com 2452285 4430.88
AAAAAAAAGAGEAAAA        Sir     Jeffrey Cleveland       N       2       8       1974    GUADELOUPE      NULL    Jeffrey.Cleveland@ijNnAoDZQajhuz.edu    2452380 4215.15
AAAAAAAAGCNPAAAA        Sir     Jack    Stewart N       19      12      1926    SAN MARINO      NULL    Jack.Stewart@G6N710E1oO.edu     2452618 2859.5
AAAAAAAAGFKEAAAA        Dr.     NULL    Brown   NULL    NULL    4       1933    ROMANIA NULL    NULL    NULL    11364.32
AAAAAAAAGGBIBAAA        Dr.     Michael Christiansen    Y       4       3       1947    ISRAEL  NULL    Michael.Christiansen@kQmauEb.org        2452583 1726.2
AAAAAAAAGHHEAAAA        Sir     Jerry   Glover  Y       3       6       1942    ANTIGUA AND BARBUDA     NULL    Jerry.Glover@yCUI3N8C0o7LPvL.edu        2452645 6382.26
AAAAAAAAGICFAAAA        Mr.     Charles Sloan   Y       8       4       1991    KOREA, REPUBLIC OF      NULL    Charles.Sloan@gnfGZRA0F.com     2452431 2793.57
AAAAAAAAGIFHBAAA        Sir     Joe     Jackson N       31      10      1969    FRENCH POLYNESIA        NULL    Joe.Jackson@eHmBJ4QqjNKJypF.com 2452534 1692.69
AAAAAAAAGJEGBAAA        Dr.     Carlos  Hampton N       18      11      1947    DOMINICA        NULL    Carlos.Hampton@nrYQ8MXgIunX6gf.com      2452470 5556.75
AAAAAAAAGMDJAAAA        Dr.     Christopher     Payne   Y       30      12      1958    DJIBOUTI        NULL    Christopher.Payne@R6POV.org     2452350 1638.72
AAAAAAAAGOPMAAAA        Mrs.    Donna   West    N       2       12      1977    SOLOMON ISLANDS NULL    Donna.West@6Q.com       2452645 1623.2
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 4377.3
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 5013.12
AAAAAAAAHCJKAAAA        Ms.     Emma    Bailey  N       2       10      1935    LATVIA  NULL    Emma.Bailey@beDlbUDUUcPhilRyy9.edu      2452491 5638.14
AAAAAAAAHGCHBAAA        Dr.     Thomas  Hull    Y       24      2       1931    DENMARK NULL    Thomas.Hull@XobY92y.com 2452648 2487.6
AAAAAAAAHGIFAAAA        Sir     James   Turner  N       25      10      1937    JERSEY  NULL    James.Turner@4RGTbOoV.org       2452293 2757.44
AAAAAAAAHIIDBAAA        Mrs.    Andrew  Barrett Y       24      5       1992    GUERNSEY        NULL    Andrew.Barrett@i85phxAtKhZfES.com       2452319 7854.67
AAAAAAAAHJOBAAAA        Mrs.    April   Wallace N       19      7       1930    FIJI    NULL    April.Wallace@9iuUi3U0K.edu     2452382 4596.6
AAAAAAAAHMEFBAAA        NULL    Joshua  Bruno   NULL    1       11      NULL    NULL    NULL    Joshua.Bruno@7Ds6hF5nMgOTel9.org        2452303 1806.33
AAAAAAAAHMNNAAAA        Sir     Jeffery Thomas  Y       8       3       1929    MONTSERRAT      NULL    Jeffery.Thomas@Ld7t.org 2452598 2441.4
AAAAAAAAICMGAAAA        Miss    Edith   Robinson        Y       11      7       1967    PUERTO RICO     NULL    Edith.Robinson@zuf2DUz3bhKnEB.org       2452319 7193.61
AAAAAAAAIGLCBAAA        Miss    Erika   Edmondson       Y       1       8       1944    DJIBOUTI        NULL    Erika.Edmondson@BuAbOSrTiD06f.edu       2452460 2381.83
AAAAAAAAIJGGBAAA        Ms.     Carolyn Cardona N       15      9       1936    BULGARIA        NULL    Carolyn.Cardona@EhKjaAPoU8gf.edu        2452453 7659.19
AAAAAAAAIMAHBAAA        Mrs.    Elizabeth       Guzman  Y       19      9       1950    GREECE  NULL    Elizabeth.Guzman@4Rq0Cyh9c.edu  2452315 1856.2
AAAAAAAAIMGFAAAA        Dr.     Paul    Powell  Y       28      11      1935    GREECE  NULL    Paul.Powell@3EgM4I28gtaqb.org   2452320 8416.8
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 1552.46
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 2639.34
AAAAAAAAIMPNAAAA        Sir     Charles Chadwick        N       21      1       1936    ALAND ISLANDS   NULL    Charles.Chadwick@ofbPzGan.org   2452414 4241.44
AAAAAAAAIPHCAAAA        Mr.     Robert  Parham  Y       9       2       1967    ETHIOPIA        NULL    Robert.Parham@0GnPcZbUE.org     2452519 4317.18
AAAAAAAAJABDBAAA        NULL    NULL    NULL    NULL    9       6       1972    TIMOR-LESTE     NULL    NULL    NULL    2387.28
[root@columnstore mariadb-columnstore-tpcds]#
[root@columnstore mariadb-columnstore-tpcds]#
[root@columnstore mariadb-columnstore-tpcds]#
[root@columnstore mariadb-columnstore-tpcds]# mcsmysql -u root -p1 tpcds_1 < validate_mdb_query_templates/query30.sql
c_customer_id   c_salutation    c_first_name    c_last_name     c_preferred_cust_flag   c_birth_day     c_birth_month   c_birth_year    c_birth_country c_login c_email_address c_last_review_date      ctr_total_return
AAAAAAAAAAAIBAAA        Mr.     Eduardo Goodwin Y       24      11      1950    GHANA   NULL    Eduardo.Goodwin@rolemn2xusv5Ot0.edu     2452312 3838
AAAAAAAAABHAAAAA        Sir     Robert  Thornton        Y       24      5       1924    HONG KONG       NULL    Robert.Thornton@jas.edu 2452431 2434.58
AAAAAAAAABPNAAAA        Mr.     Alonzo  Huddleston      Y       19      4       1957    FAROE ISLANDS   NULL    Alonzo.Huddleston@g.edu 2452289 2813
AAAAAAAAAFCGBAAA        Ms.     Pamela  Slater  Y       2       6       1931    GAMBIA  NULL    Pamela.Slater@ajU1sMrf5vC7.com  2452613 1746.58
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 1850.7
AAAAAAAAAFLBAAAA        Sir     Keith   Stern   N       18      2       1925    ICELAND NULL    Keith.Stern@34Hb991eohmd.com    2452549 10898.4
AAAAAAAAAFOEBAAA        Dr.     Trevor  Battle  N       12      8       1953    THAILAND        NULL    Trevor.Battle@0F.com    2452643 3556
AAAAAAAAAIKEAAAA        Dr.     Joseph  Miller  N       17      12      1930    MONACO  NULL    Joseph.Miller@Ampmde.edu        2452332 7037.55
AAAAAAAAAJMABAAA        Miss    Brenda  Sloan   Y       23      3       1982    MALAYSIA        NULL    Brenda.Sloan@csCoX.edu  2452336 1783.19
AAAAAAAAAKDNAAAA        NULL    NULL    NULL    NULL    NULL    12      NULL    NULL    NULL    NULL    2452544 2448.03
AAAAAAAAANCLAAAA        Mr.     Raymond Dixon   N       31      12      1977    MONTENEGRO      NULL    Raymond.Dixon@07IH.edu  2452514 1785.96
AAAAAAAAANNNAAAA        Ms.     Cindy   Julian  Y       4       8       1982    UGANDA  NULL    Cindy.Julian@ChEB5eyUCBe.org    2452368 2449.04
AAAAAAAAAPKJAAAA        Ms.     Elizabeth       Pepper  Y       28      12      1930    NICARAGUA       NULL    Elizabeth.Pepper@xlygC22.org    2452645 1764.4
AAAAAAAABAKCBAAA        Miss    Denise  Brock   Y       10      4       1958    JAMAICA NULL    Denise.Brock@7ffTBTNaX7is.com   2452435 1618.24
AAAAAAAABBMDAAAA        Dr.     Walter  Bernard Y       2       10      1948    BELARUS NULL    Walter.Bernard@Th.org   2452628 1755.66
AAAAAAAABIBBBAAA        Miss    Lorene  Mayfield        N       17      12      1947    ALBANIA NULL    Lorene.Mayfield@ECni6UFkF2.edu  2452450 3157.7
AAAAAAAABIIDAAAA        Mr.     Paul    Stevenson       N       26      2       1953    BARBADOS        NULL    Paul.Stevenson@tHMZG9.com       2452488 1947.77
AAAAAAAABMJAAAAA        Dr.     Marion  Mclain  N       16      2       1931    ANGUILLA        NULL    Marion.Mclain@PKik2BMH.com      2452382 3134.46
AAAAAAAABNFHBAAA        Mr.     Kristofer       Payne   N       12      5       1945    SYRIAN ARAB REPUBLIC    NULL    Kristofer.Payne@nc70sAM.com     2452436 3813.33
AAAAAAAABOLLAAAA        Miss    Lisa    Way     N       11      1       1941    LEBANON NULL    Lisa.Way@nnVbE2MvTT.com 2452383 1829.88
AAAAAAAABPHKAAAA        Dr.     Gabriela        Jerome  Y       9       7       1969    IRAQ    NULL    Gabriela.Jerome@Hay.com 2452567 5360.55
AAAAAAAACBAFAAAA        Dr.     Timothy Carroll Y       14      8       1957    UGANDA  NULL    Timothy.Carroll@pUcgCvxC4nkcM.edu       2452535 1642.8
AAAAAAAACBPMAAAA        Ms.     Shana   Fulton  N       10      2       1955    RWANDA  NULL    Shana.Fulton@2CAVqR7d.org       2452378 2089.36
AAAAAAAACCFCBAAA        Mr.     Mark    Pacheco N       9       3       1956    YEMEN   NULL    Mark.Pacheco@NMMki.edu  2452377 1838.48
AAAAAAAACCMEAAAA        Dr.     Adriane Harmon  N       1       10      1964    ARGENTINA       NULL    Adriane.Harmon@X0nMJZPDL.com    2452562 8986.95
AAAAAAAACCNJAAAA        Dr.     Elizabeth       Gilbreath       Y       17      1       1985    KIRIBATI        NULL    Elizabeth.Gilbreath@4LxlO8fArnEeEavQe.com       2452485 2450.86
AAAAAAAACDDNAAAA        Ms.     Holly   Kim     N       15      6       1941    ICELAND NULL    Holly.Kim@OkTfC7tRYgy4.edu      2452580 2719.44
AAAAAAAACGILAAAA        Sir     Nolan   Mosley  N       20      3       1980    KENYA   NULL    Nolan.Mosley@5E.org     2452317 1865.64
AAAAAAAACHFHBAAA        Mr.     Dallas  Courtney        Y       21      5       1928    CÔTE D'IVOIRE   NULL    Dallas.Courtney@c7.org  2452496 4394.79
AAAAAAAACKOFAAAA        Dr.     Eric    Bucher  Y       19      9       1979    ALBANIA NULL    Eric.Bucher@sxSne.org   2452541 4662.35
AAAAAAAACMHNAAAA        Miss    Lisa    Dukes   Y       4       3       1964    SOLOMON ISLANDS NULL    Lisa.Dukes@tuRJRXkQm.com        2452303 10387.84
AAAAAAAACNGBAAAA        Mr.     Michael Haley   Y       10      5       1933    SWAZILAND       NULL    Michael.Haley@OfAOvhrm.com      2452359 4187.82
AAAAAAAACOFAAAAA        Ms.     Tammy   Searcy  N       6       5       1989    SURINAME        NULL    Tammy.Searcy@6ZFFGtIuCqT.com    2452295 5091.84
AAAAAAAADBBHAAAA        Dr.     Charles Fernandez       N       8       12      1992    CAMBODIA        NULL    Charles.Fernandez@MVhjrzVNVY.com        2452574 4640.37
AAAAAAAADFCBBAAA        Dr.     Micheal Collier N       1       9       1934    HONDURAS        NULL    Micheal.Collier@NztJvzb.org     2452446 15944.4
AAAAAAAADIHHAAAA        Miss    Vikki   Garza   Y       29      8       1950    NAMIBIA NULL    Vikki.Garza@v.edu       2452608 2373.84
AAAAAAAADJAPAAAA        Ms.     Angel   Dodd    Y       22      12      1950    TIMOR-LESTE     NULL    Angel.Dodd@gQOLRKqU285BfTIT.edu 2452634 2227.83
AAAAAAAADKEFAAAA        Ms.     Martha  Dillon  N       9       12      1971    VANUATU NULL    Martha.Dillon@iKkeDtKERbu.org   2452404 1863.4
AAAAAAAADKIFAAAA        Sir     Walter  Stout   Y       18      1       1970    BURUNDI NULL    Walter.Stout@Q961tU4iePsOy.org  2452449 2465.55
AAAAAAAADLHNAAAA        Mr.     Joseph  Mckinney        Y       23      3       1940    ARMENIA NULL    Joseph.Mckinney@4YYp7B8ccPz.edu 2452595 1820.02
AAAAAAAADMMDBAAA        Mr.     Darrell Padgett Y       17      3       1992    HONDURAS        NULL    Darrell.Padgett@IrqARXX.org     2452554 2052.54
AAAAAAAADNHPAAAA        Mr.     John    Mathews N       23      2       1925    NICARAGUA       NULL    John.Mathews@UOtpEd.edu 2452543 7883.4
AAAAAAAADOCMAAAA        Dr.     Yvonne  Poirier Y       30      1       1950    ESTONIA NULL    Yvonne.Poirier@l.org    2452648 4075.55
AAAAAAAADODBAAAA        Mrs.    Elizabeth       Spalding        N       27      11      1947    KAZAKHSTAN      NULL    Elizabeth.Spalding@ihnjlmxMbs.edu       2452318 10349.68
AAAAAAAAEBAOAAAA        Miss    Amy     Causey  N       21      9       1951    COMOROS NULL    Amy.Causey@7EvHzf02opq6QlO.com  2452443 8005.4
AAAAAAAAEBLEAAAA        Mr.     Leroy   Couture N       7       5       1938    SWITZERLAND     NULL    Leroy.Couture@vqrL.com  2452595 1683.88
AAAAAAAAEEADAAAA        Mr.     Robert  Donohue Y       21      8       1938    SWAZILAND       NULL    Robert.Donohue@U2xz7.org        2452436 11083.8
AAAAAAAAEHAABAAA        Dr.     Victoria        Morgan  Y       31      5       1960    JERSEY  NULL    Victoria.Morgan@Sk3jzrDZTq.org  2452646 1712.16
AAAAAAAAEHGOAAAA        Miss    Jillian Nelson  Y       27      7       1978    TURKMENISTAN    NULL    Jillian.Nelson@ph0qObqh3fbv.com 2452315 4432.69
AAAAAAAAEHPNAAAA        Dr.     Rachael King    Y       28      11      1927    PANAMA  NULL    Rachael.King@t8dA2klrEIBiCd.edu 2452438 2251.5
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1783.12
AAAAAAAAEIDHAAAA        Sir     George  Garcia  Y       21      4       1960    CROATIA NULL    George.Garcia@uzGHyYUnvs.edu    2452306 1857.63
AAAAAAAAEIOBBAAA        Mr.     Gary    Davis   N       9       6       1992    KYRGYZSTAN      NULL    Gary.Davis@GTXc2AbKvkM.edu      2452322 1652.7
AAAAAAAAEJFNAAAA        Dr.     Chad    Baldwin Y       11      11      1937    BULGARIA        NULL    Chad.Baldwin@cN988.org  2452560 11133.36
AAAAAAAAEJOPAAAA        Dr.     Alexandra       Henderson       N       4       2       1986    MONTENEGRO      NULL    Alexandra.Henderson@4FCXABbkVkrLK.com   2452376 3426.36
AAAAAAAAELEBAAAA        Mrs.    Dianne  Ross    Y       19      7       1949    ICELAND NULL    Dianne.Ross@D4VH75e9n.org       2452429 4018.3
AAAAAAAAEMDIBAAA        Sir     Scott   Obrien  Y       3       8       1926    JERSEY  NULL    Scott.Obrien@a84VTAJCTB.com     2452504 1456.67
AAAAAAAAEMIAAAAA        Dr.     Danny   Ness    Y       25      10      1955    IRAQ    NULL    Danny.Ness@CC8vAp.edu   2452487 5569.5
AAAAAAAAEMNGBAAA        Mr.     Milton  Oakley  Y       10      10      1976    FINLAND NULL    Milton.Oakley@O4DHXT.com        2452355 1505.16
AAAAAAAAEMPKAAAA        Sir     Gilbert Palmer  N       23      10      1933    BRUNEI DARUSSALAM       NULL    Gilbert.Palmer@7UYjIOOIg0.org   2452463 2391.95
AAAAAAAAENFLAAAA        Sir     Joseph  Gaffney N       22      1       1967    BULGARIA        NULL    Joseph.Gaffney@nBDj.org 2452608 6135.48
AAAAAAAAEPELAAAA        Dr.     Emily   Thomas  Y       14      3       1981    MOZAMBIQUE      NULL    Emily.Thomas@qtJnzLvYcK598T.edu 2452617 3239.04
AAAAAAAAFBLHAAAA        Mr.     Carter  Lombardo        N       22      11      1950    MONACO  NULL    Carter.Lombardo@X.edu   2452582 3965.43
AAAAAAAAFHODBAAA        Ms.     Diana   Hall    N       14      7       1934    LATVIA  NULL    Diana.Hall@Be.com       2452546 9391.76
AAAAAAAAFLAJAAAA        Mr.     John    Hardy   Y       4       9       1977    ANGUILLA        NULL    John.Hardy@LnaYnyZ9AYee.edu     2452289 1640.8
AAAAAAAAFLDFBAAA        Mr.     Demetrius       Hunt    N       30      8       1956    ZAMBIA  NULL    Demetrius.Hunt@J3.com   2452647 3673.6
AAAAAAAAFMCFBAAA        Mrs.    Karen   Middleton       Y       21      10      1934    GUADELOUPE      NULL    Karen.Middleton@q.com   2452343 1415.7
AAAAAAAAFMENAAAA        Ms.     Anne    Jernigan        Y       14      8       1952    ITALY   NULL    Anne.Jernigan@BBtNT7SneOEKG.edu 2452612 1735.14
AAAAAAAAFMLEAAAA        Mr.     Gavin   NULL    NULL    21      NULL    1933    BRAZIL  NULL    Gavin.Maples@AT6ClrGUc79.com    NULL    4882.15
AAAAAAAAFMPMAAAA        Sir     Jack    Thomas  Y       12      1       1971    ARMENIA NULL    Jack.Thomas@dn0mBXIEu7XPH.com   2452409 2501.94
AAAAAAAAFNHBBAAA        Miss    Jennifer        Long    Y       12      6       1984    VENEZUELA       NULL    Jennifer.Long@RyRC5oxxCoVPG.com 2452285 4430.88
AAAAAAAAGAGEAAAA        Sir     Jeffrey Cleveland       N       2       8       1974    GUADELOUPE      NULL    Jeffrey.Cleveland@ijNnAoDZQajhuz.edu    2452380 4215.15
AAAAAAAAGCNPAAAA        Sir     Jack    Stewart N       19      12      1926    SAN MARINO      NULL    Jack.Stewart@G6N710E1oO.edu     2452618 2859.5
AAAAAAAAGFKEAAAA        Dr.     NULL    Brown   NULL    NULL    4       1933    ROMANIA NULL    NULL    NULL    11364.32
AAAAAAAAGGBIBAAA        Dr.     Michael Christiansen    Y       4       3       1947    ISRAEL  NULL    Michael.Christiansen@kQmauEb.org        2452583 1726.2
AAAAAAAAGHHEAAAA        Sir     Jerry   Glover  Y       3       6       1942    ANTIGUA AND BARBUDA     NULL    Jerry.Glover@yCUI3N8C0o7LPvL.edu        2452645 6382.26
AAAAAAAAGICFAAAA        Mr.     Charles Sloan   Y       8       4       1991    KOREA, REPUBLIC OF      NULL    Charles.Sloan@gnfGZRA0F.com     2452431 2793.57
AAAAAAAAGIFHBAAA        Sir     Joe     Jackson N       31      10      1969    FRENCH POLYNESIA        NULL    Joe.Jackson@eHmBJ4QqjNKJypF.com 2452534 1692.69
AAAAAAAAGJEGBAAA        Dr.     Carlos  Hampton N       18      11      1947    DOMINICA        NULL    Carlos.Hampton@nrYQ8MXgIunX6gf.com      2452470 5556.75
AAAAAAAAGMDJAAAA        Dr.     Christopher     Payne   Y       30      12      1958    DJIBOUTI        NULL    Christopher.Payne@R6POV.org     2452350 1638.72
AAAAAAAAGOPMAAAA        Mrs.    Donna   West    N       2       12      1977    SOLOMON ISLANDS NULL    Donna.West@6Q.com       2452645 1623.2
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 4377.3
AAAAAAAAHBBDBAAA        Mrs.    NULL    Henry   N       NULL    NULL    1948    ARUBA   NULL    NULL    2452337 5013.12
AAAAAAAAHCJKAAAA        Ms.     Emma    Bailey  N       2       10      1935    LATVIA  NULL    Emma.Bailey@beDlbUDUUcPhilRyy9.edu      2452491 5638.14
AAAAAAAAHGCHBAAA        Dr.     Thomas  Hull    Y       24      2       1931    DENMARK NULL    Thomas.Hull@XobY92y.com 2452648 2487.6
AAAAAAAAHGIFAAAA        Sir     James   Turner  N       25      10      1937    JERSEY  NULL    James.Turner@4RGTbOoV.org       2452293 2757.44
AAAAAAAAHIIDBAAA        Mrs.    Andrew  Barrett Y       24      5       1992    GUERNSEY        NULL    Andrew.Barrett@i85phxAtKhZfES.com       2452319 7854.67
AAAAAAAAHJOBAAAA        Mrs.    April   Wallace N       19      7       1930    FIJI    NULL    April.Wallace@9iuUi3U0K.edu     2452382 4596.6
AAAAAAAAHMEFBAAA        NULL    Joshua  Bruno   NULL    1       11      NULL    NULL    NULL    Joshua.Bruno@7Ds6hF5nMgOTel9.org        2452303 1806.33
AAAAAAAAHMNNAAAA        Sir     Jeffery Thomas  Y       8       3       1929    MONTSERRAT      NULL    Jeffery.Thomas@Ld7t.org 2452598 2441.4
AAAAAAAAICMGAAAA        Miss    Edith   Robinson        Y       11      7       1967    PUERTO RICO     NULL    Edith.Robinson@zuf2DUz3bhKnEB.org       2452319 7193.61
AAAAAAAAIGLCBAAA        Miss    Erika   Edmondson       Y       1       8       1944    DJIBOUTI        NULL    Erika.Edmondson@BuAbOSrTiD06f.edu       2452460 2381.83
AAAAAAAAIJGGBAAA        Ms.     Carolyn Cardona N       15      9       1936    BULGARIA        NULL    Carolyn.Cardona@EhKjaAPoU8gf.edu        2452453 7659.19
AAAAAAAAIMAHBAAA        Mrs.    Elizabeth       Guzman  Y       19      9       1950    GREECE  NULL    Elizabeth.Guzman@4Rq0Cyh9c.edu  2452315 1856.2
AAAAAAAAIMGFAAAA        Dr.     Paul    Powell  Y       28      11      1935    GREECE  NULL    Paul.Powell@3EgM4I28gtaqb.org   2452320 8416.8
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 1552.46
AAAAAAAAIMJCBAAA        Dr.     Dennis  Brown   Y       24      10      1978    BULGARIA        NULL    Dennis.Brown@fAvOOY4Q13dVdPfb.com       2452404 2639.34
AAAAAAAAIMPNAAAA        Sir     Charles Chadwick        N       21      1       1936    ALAND ISLANDS   NULL    Charles.Chadwick@ofbPzGan.org   2452414 4241.44
AAAAAAAAIPHCAAAA        Mr.     Robert  Parham  Y       9       2       1967    ETHIOPIA        NULL    Robert.Parham@0GnPcZbUE.org     2452519 4317.18
AAAAAAAAJABDBAAA        NULL    NULL    NULL    NULL    9       6       1972    TIMOR-LESTE     NULL    NULL    NULL    2387.28
[root@columnstore mariadb-columnstore-tpcds]#

run query query28 --it's fails because it's used "" quoting in the query but sql-mode=oracle
sets ANSI_QUOTES flag.
run again query30 – it returns nothing

]# mcsmysql -u root -p1 tpcds_1 <<rr
> select  TRIM(TRAILING '.' FROM TRIM(B1_LP))+0 as B1_LP,B1_CNT, B1_CNTD, B2_LP,B2_CNT, B2_CNTD, format(B3_LP,6) as B3_LP ,B3_CNT, B3_CNTD, B4_LP,B4_CNT, B4_CNTD, B5_LP,B5_CNT, B5_CNTD, B6_LP,B6_CNT, B6_CNTD
> from (select avg(ss_list_price) B1_LP,"a" as j
>             ,count(ss_list_price) B1_CNT
>             ,count(distinct ss_list_price) B1_CNTD
>       from store_sales
>       where ss_quantity between 0 and 5
>         and (ss_list_price between 8 and 8+10
>              or ss_coupon_amt between 459 and 459+1000
>              or ss_wholesale_cost between 57 and 57+20)) B1,
>      (select avg(ss_list_price) B2_LP,"a" as j
>             ,count(ss_list_price) B2_CNT
>             ,count(distinct ss_list_price) B2_CNTD
>       from store_sales
>       where ss_quantity between 6 and 10
>         and (ss_list_price between 90 and 90+10
>           or ss_coupon_amt between 2323 and 2323+1000
>           or ss_wholesale_cost between 31 and 31+20)) B2,
>      (select avg(ss_list_price) B3_LP,"a" as j
>             ,count(ss_list_price) B3_CNT
>             ,count(distinct ss_list_price) B3_CNTD
>       from store_sales
>       where ss_quantity between 11 and 15
>         and (ss_list_price between 142 and 142+10
>           or ss_coupon_amt between 12214 and 12214+1000
>           or ss_wholesale_cost between 79 and 79+20)) B3,
>      (select avg(ss_list_price) B4_LP,"a" as j
>             ,count(ss_list_price) B4_CNT
>             ,count(distinct ss_list_price) B4_CNTD
>       from store_sales
>       where ss_quantity between 16 and 20
>         and (ss_list_price between 135 and 135+10
>           or ss_coupon_amt between 6071 and 6071+1000
>           or ss_wholesale_cost between 38 and 38+20)) B4,
>      (select avg(ss_list_price) B5_LP,"a" as j
>             ,count(ss_list_price) B5_CNT
>             ,count(distinct ss_list_price) B5_CNTD
>       from store_sales
>       where ss_quantity between 21 and 25
>         and (ss_list_price between 122 and 122+10
>           or ss_coupon_amt between 836 and 836+1000
>           or ss_wholesale_cost between 17 and 17+20)) B5,
>      (select avg(ss_list_price) B6_LP,"a" as j
>             ,count(ss_list_price) B6_CNT
>             ,count(distinct ss_list_price) B6_CNTD
>       from store_sales
>       where ss_quantity between 26 and 30
>         and (ss_list_price between 154 and 154+10
>           or ss_coupon_amt between 7326 and 7326+1000
>           or ss_wholesale_cost between 7 and 7+20)) B6
> where B1.j = B2.j and B2.j = B3.j and  B3.j = B4.j and  B4.j = B5.j and  B5.j = B6.j
> limit 100;
> rr
ERROR 1054 (42S22) at line 1: Unknown column 'a' in 'field list'

# mcsmysql -u root -p1 tpcds_1 < query_templates/validate_mdb_query_templates/query30.sql
 
# mcsmysql -u root -p1 tpcds_1 <<rr
> with customer_total_return as
>  (select wr_returning_customer_sk as ctr_customer_sk
>         ,ca_state as ctr_state,
>         sum(wr_return_amt) as ctr_total_return
>  from web_returns
>      ,date_dim
>      ,customer_address
>  where wr_returned_date_sk = d_date_sk
>    and d_year =2002
>    and wr_returning_addr_sk = ca_address_sk
>  group by wr_returning_customer_sk
>          ,ca_state)
>   select  c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
>        ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
>        ,c_last_review_date, TRIM(TRAILING '.' FROM TRIM(ctr_total_return))+0 as ctr_total_return
>  from customer_total_return ctr1
>      ,customer_address
>      ,customer
>  where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
>                           from customer_total_return ctr2
>                           where ctr1.ctr_state = ctr2.ctr_state)
>        and ca_address_sk = c_current_addr_sk
>        and ca_state = 'GA'
>        and ctr1.ctr_customer_sk = c_customer_sk
>  order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
>                   ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
>                   ,c_last_review_date,ctr_total_return
> limit 100;
> rr
[root@columnstore mariadb-columnstore-tpcds]#
 



 Comments   
Comment by Todd Stoffel (Inactive) [ 2023-03-06 ]

This ticket was created prior to convergence with the server and may be obsolete. If you find this issue still exists in a modern version, please open a new ticket.

Generated at Thu Feb 08 02:33:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.