Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-2127

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: Icebox
    • Component/s: ExeMgr
    • 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]#
       
      
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            winstone Zdravelina Sokolovska (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration