Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21894

MariaDB 10.3 onward unable to parse syntax that works in earlier versions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 5.5, 10.0, 10.2, 10.3, 10.2.26, 10.3.20, 10.4.10, 10.4, 10.1
    • Fix Version/s: N/A
    • Component/s: Parser
    • Labels:
      None
    • Environment:
      CentOS 7

      Description

      Syntax that works in 10.2 generates a syntax error from 10.3 onward. Full test case follows:

      drop database if exists t36235;
       
      create database t36235;
       
      use t36235;
       
      CREATE TABLE indefinite_curiosity (
      	thatsme varchar(100) NOT NULL DEFAULT '',
      	imimportant varchar(100) NOT NULL DEFAULT '',
      	PRIMARY KEY (thatsme,imimportant),
      	KEY thatsme (thatsme)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE indefinite_persimmons (
      	persimmon varchar(50) NOT NULL DEFAULT '',
      	imimportant varchar(50) NOT NULL DEFAULT '',
      	persimmonName varchar(45) DEFAULT NULL,
      	Site int(10) DEFAULT NULL,
      	PRIMARY KEY (persimmon,imimportant),
      	KEY curiosity_FK00 (persimmon),
      	KEY curiosity_FK01 (imimportant)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE quadricle_header (
      	quadricleID int(10) NOT NULL AUTO_INCREMENT,
      	entolology varchar(50) DEFAULT NULL,
      	inquirynotebook varchar(50) NOT NULL,
      	inquiryName varchar(50) DEFAULT NULL,
      	inquirySQL mediumtext DEFAULT NULL,
      	SlowJoinA mediumtext DEFAULT NULL,
      	SlowJoinB mediumtext DEFAULT NULL,
      	inquiryConditions longtext DEFAULT NULL,
      	inquiryDSN varchar(50) NOT NULL DEFAULT 'Interweb',
      	inquiryDSNType varchar(45) NOT NULL DEFAULT 'MySQL',
      	critiqueable tinyint(1) unsigned NOT NULL DEFAULT 0,
      	critiquepersimmons longtext DEFAULT NULL,
      	AddToQueuepersimmons longtext DEFAULT NULL,
      	steaknotebook varchar(100) DEFAULT NULL,
      	steakTable varchar(100) DEFAULT NULL,
      	quadricleKeyColumn varchar(100) DEFAULT NULL,
      	Defaulthamachi varchar(100) DEFAULT NULL,
      	DefaulthamachiDate varchar(45) DEFAULT NULL,
      	steakrashineptitude varchar(100) DEFAULT NULL,
      	persimmons longtext DEFAULT NULL,
      	presumptivequadricle tinyint(1) unsigned NOT NULL DEFAULT 0,
      	quadriclerashineptitude varchar(150) DEFAULT NULL,
      	AllowQuickWindows tinyint(1) unsigned NOT NULL DEFAULT 0,
      	doublespeak varchar(45) DEFAULT NULL,
      	whatroom varchar(45) DEFAULT NULL,
      	AddToQueue tinyint(1) unsigned NOT NULL DEFAULT 0,
      	SnapshotPreset varchar(255) DEFAULT NULL,
      	whereami int(2) unsigned DEFAULT NULL,
      	RetailerLimit tinyint(3) unsigned NOT NULL DEFAULT 0,
      	demurscraped tinyint(3) unsigned NOT NULL DEFAULT 0,
      	demurinventoried tinyint(3) unsigned NOT NULL DEFAULT 0,
      	quadricleHelp longtext DEFAULT NULL,
      	demurFromDealers varchar(50) DEFAULT NULL,
      	demur_from_non_sps tinyint(1) NOT NULL DEFAULT 0,
      	demur_from_non_sc tinyint(1) NOT NULL DEFAULT 0,
      	demur_from_non_POS tinyint(1) NOT NULL DEFAULT 0,
      	PRIMARY KEY (quadricleID),
      	KEY doublespeak (doublespeak)
      ) ENGINE=InnoDB AUTO_INCREMENT=751 DEFAULT CHARSET=utf8;
       
      CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW usercuriosity AS select a.thatsme AS thatsme,p.persimmon AS imimportant,p.persimmonName AS persimmon,p.Site AS site from (indefinite_curiosity a left join indefinite_persimmons p on(a.imimportant = p.imimportant));
       
      CREATE TABLE global_dsn (
      	DSN varchar(45) NOT NULL,
      	id int(2) NOT NULL AUTO_INCREMENT,
      	Site int(2) DEFAULT NULL,
      	Parent int(10) DEFAULT NULL,
      	PRIMARY KEY (id) USING BTREE,
      	KEY Site (Site)
      ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
       
      CREATE TABLE sites (
      	id int(11) unsigned NOT NULL AUTO_INCREMENT,
      	url varchar(255) DEFAULT NULL,
      	nickname mediumtext DEFAULT NULL,
      	displayOrder int(11) unsigned DEFAULT NULL,
      	exposition varchar(255) DEFAULT NULL,
      	myHandle varchar(45) DEFAULT NULL,
      	whereItIs varchar(100) DEFAULT NULL,
      	splitting varchar(45) DEFAULT NULL,
      	showonsite tinyint(3) unsigned NOT NULL DEFAULT 0,
      	circuit varchar(45) DEFAULT NULL,
      	persimmon varchar(45) DEFAULT NULL,
      	path varchar(1024) DEFAULT NULL,
      	baseUrl varchar(1024) DEFAULT NULL,
      	localwhereami int(10) DEFAULT NULL,
      	excludeFromMaster tinyint(4) DEFAULT NULL,
      	stone_tablet varchar(128) DEFAULT NULL,
      	fiction_joker varchar(128) DEFAULT NULL,
      	phonenumber varchar(45) DEFAULT NULL,
      	Security tinyint(4) DEFAULT NULL,
      	tempname varchar(50) DEFAULT NULL,
      	salesenabled tinyint(4) DEFAULT 0,
      	train_brand varchar(50) DEFAULT NULL,
      	train_brand_id int(11) DEFAULT NULL,
      	train_org_id bigint(20) DEFAULT NULL,
      	train_ticket_form bigint(20) DEFAULT NULL,
      	train_override_url varchar(255) DEFAULT NULL,
      	color varchar(10) DEFAULT NULL,
      	copyfictions tinyint(4) DEFAULT 0,
      	mapics_env_code varchar(8) DEFAULT NULL,
      	IncludeInMaster bit(1) NOT NULL DEFAULT b'0',
      	PRIMARY KEY (id),
      	KEY nickname (nickname(100)),
      	KEY URL (url),
      	KEY mapics_env_code (mapics_env_code),
      	KEY IncludeInMaster (IncludeInMaster)
      ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
       
      select 
      	entolology,
      	replace(quadriclerashineptitude,'mutualsplitting',"ameriwood"),
      	doublespeak,
      	whatroom, 
      	replace(g.persimmons,'X-',concat(d.Site,'-'))
       
      from quadricle_header g 
      left join usercuriosity u 
      inner join global_dsn d 
      inner join sites s 
      	on s.id=d.site and s.circuit='ameriwood' and d.Parent=24
      	on replace(g.persimmons,'X-',concat(d.Site,'-')) like concat('%', u.imimportant, '%')
       
      where 
      	g.quadriclerashineptitude like 'mutualsplitting.%'
      	and u.thatsme='geoffrey'
       
      group by g.quadricleid
      ;
      

      Error produced from 10.3 onward:

      ERROR 1064 (42000) at line 114: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where
          g.quadriclerashineptitude like 'mutualsplitting.%'
          and u.thatsme=' at line 15
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: