Repository: ariga/atlas
Pull request #2399 (2023-12-22 20:21:32)

sql/migrate: fix compound blocks scanning
Fixed https://github.com/ariga/atlas/issues/2397


Patch:
diff --git a/sql/migrate/lex.go b/sql/migrate/lex.go
index a1fb5a83..8bf6ec30 100644
--- a/sql/migrate/lex.go
+++ b/sql/migrate/lex.go
@@ -218,17 +218,17 @@ Scan:
 		case r == '/' && s.next() == '*':
 			s.comment("/*", "*/")
 		case s.delim == delimiter && s.MatchBeginAtomic && reBeginAtomic.MatchString(s.input[s.pos-1:]):
-			if err := s.skipBeginAtomic(); err != nil {
-				return nil, err
+			if err := s.skipBeginAtomic(); err == nil {
+				text = s.input[:s.pos]
+				break Scan
 			}
-			text = s.input[:s.pos]
-			break Scan
+			// Not a "BEGIN ATOMIC" block.
 		case s.delim == delimiter && s.MatchBegin && reBegin.MatchString(s.input[s.pos-1:]):
-			if err := s.skipBegin(); err != nil {
-				return nil, err
+			if err := s.skipBegin(); err == nil {
+				text = s.input[:s.pos]
+				break Scan
 			}
-			text = s.input[:s.pos]
-			break Scan
+			// Not a "BEGIN" block.
 		}
 	}
 	return s.emit(text), nil


Test patch:
diff --git a/sql/migrate/testdata/lexgroup/2_function.sql b/sql/migrate/testdata/lexgroup/2_function.sql
index dc8037d5..cfbc6baa 100644
--- a/sql/migrate/testdata/lexgroup/2_function.sql
+++ b/sql/migrate/testdata/lexgroup/2_function.sql
@@ -1,4 +1,3 @@
-
 CREATE PROCEDURE CompProc(IN p INT)
 BEGIN
     DECLARE v1 INT DEFAULT 0;
@@ -123,4 +122,42 @@ BEGIN
         END;
     END LOOP;
     CLOSE cur;
-END;
\ No newline at end of file
+END;
+
+select * from t1 where `begin` < 10;
+
+create table t2(begin int);
+select * from t2 where begin < 10;
+
+create table t3(begin int, end int);
+select * from t3 where begin <> end;
+
+create table t4(begin int, end int);
+select * from t4 where begin > end or begin < end or begin = end or begin in (1,2,3) and end not in (1,2,3);
+
+create table t5(begin int, end int);
+create trigger t5_insert before insert on t5 for each row set new.begin = new.end;
+create trigger t5_insert before insert on t5 for each row
+begin
+    set new.begin = new.end;
+end;
+
+-- issue 2397.
+CREATE OR REPLACE VIEW claims.current_member_view AS
+    SELECT l.member_load_id, n.name_last_or_organization, g.name_given, n.name_middle, n.name_prefix, n.name_suffix, ps.external_plan_sponsor_id, i.member_identification_code, i.date_of_birth, cgop.group_or_policy_number, a.plan_sponsor_name, stat.is_subscriber, stat.relationship_to_subscriber, ed.employment_begin, cd.benefit_begin, cd.benefit_end
+    FROM claims.member_coverage_load mcl
+    LEFT OUTER JOIN claims.member_load l ON mcl.member_load_id = l.member_load_id
+    LEFT OUTER JOIN claims.plan_sponsor_match m ON mcl.coverage_group_or_plan_id = m.coverage_group_or_plan_id
+    LEFT OUTER JOIN claims.plan_sponsor ps ON ps.plan_sponsor_id = m.plan_sponsor_id
+    LEFT OUTER JOIN claims.member_seen ms ON ms.member_seen_id = l.member_seen_id
+    LEFT OUTER JOIN claims.member_identification i ON ms.member_identification_id = i.member_identification_id
+    LEFT OUTER JOIN claims.name n ON n.name_id = ms.name_id
+    LEFT OUTER JOIN claims.name_given g ON n.name_first_id = g.name_given_id
+    LEFT OUTER JOIN claims.eligibility_date ed ON ed.eligibility_date_id = l.eligibility_date_id
+    LEFT OUTER JOIN claims.eligibility_reference r ON r.eligibility_reference_id = l.eligibility_reference_id
+    LEFT OUTER JOIN claims.eligibility_status stat ON stat.eligibility_status_id = l.eligibility_status_id
+    LEFT OUTER JOIN claims.eligibility_admin a ON a.eligibility_admin_id = l.eligibility_admin_id
+    LEFT OUTER JOIN claims.eligibility_submission s ON s.eligibility_submission_id = l.eligibility_submission_id
+    LEFT OUTER JOIN claims.coverage_group_or_plan cgop ON cgop.coverage_group_or_plan_id = mcl.coverage_group_or_plan_id
+    LEFT OUTER JOIN claims.coverage_date cd ON cd.coverage_date_id = mcl.coverage_date_id
+    WHERE benefit_begin < NOW() AND benefit_end > NOW();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lexgroup/2_function.sql.golden b/sql/migrate/testdata/lexgroup/2_function.sql.golden
index 61c319d6..6b13384e 100644
--- a/sql/migrate/testdata/lexgroup/2_function.sql.golden
+++ b/sql/migrate/testdata/lexgroup/2_function.sql.golden
@@ -119,4 +119,46 @@ BEGIN
         END;
     END LOOP;
     CLOSE cur;
-END;
\ No newline at end of file
+END;
+-- end --
+select * from t1 where `begin` < 10;
+-- end --
+create table t2(begin int);
+-- end --
+select * from t2 where begin < 10;
+-- end --
+create table t3(begin int, end int);
+-- end --
+select * from t3 where begin <> end;
+-- end --
+create table t4(begin int, end int);
+-- end --
+select * from t4 where begin > end or begin < end or begin = end or begin in (1,2,3) and end not in (1,2,3);
+-- end --
+create table t5(begin int, end int);
+-- end --
+create trigger t5_insert before insert on t5 for each row set new.begin = new.end;
+-- end --
+create trigger t5_insert before insert on t5 for each row
+begin
+    set new.begin = new.end;
+end;
+-- end --
+CREATE OR REPLACE VIEW claims.current_member_view AS
+    SELECT l.member_load_id, n.name_last_or_organization, g.name_given, n.name_middle, n.name_prefix, n.name_suffix, ps.external_plan_sponsor_id, i.member_identification_code, i.date_of_birth, cgop.group_or_policy_number, a.plan_sponsor_name, stat.is_subscriber, stat.relationship_to_subscriber, ed.employment_begin, cd.benefit_begin, cd.benefit_end
+    FROM claims.member_coverage_load mcl
+    LEFT OUTER JOIN claims.member_load l ON mcl.member_load_id = l.member_load_id
+    LEFT OUTER JOIN claims.plan_sponsor_match m ON mcl.coverage_group_or_plan_id = m.coverage_group_or_plan_id
+    LEFT OUTER JOIN claims.plan_sponsor ps ON ps.plan_sponsor_id = m.plan_sponsor_id
+    LEFT OUTER JOIN claims.member_seen ms ON ms.member_seen_id = l.member_seen_id
+    LEFT OUTER JOIN claims.member_identification i ON ms.member_identification_id = i.member_identification_id
+    LEFT OUTER JOIN claims.name n ON n.name_id = ms.name_id
+    LEFT OUTER JOIN claims.name_given g ON n.name_first_id = g.name_given_id
+    LEFT OUTER JOIN claims.eligibility_date ed ON ed.eligibility_date_id = l.eligibility_date_id
+    LEFT OUTER JOIN claims.eligibility_reference r ON r.eligibility_reference_id = l.eligibility_reference_id
+    LEFT OUTER JOIN claims.eligibility_status stat ON stat.eligibility_status_id = l.eligibility_status_id
+    LEFT OUTER JOIN claims.eligibility_admin a ON a.eligibility_admin_id = l.eligibility_admin_id
+    LEFT OUTER JOIN claims.eligibility_submission s ON s.eligibility_submission_id = l.eligibility_submission_id
+    LEFT OUTER JOIN claims.coverage_group_or_plan cgop ON cgop.coverage_group_or_plan_id = mcl.coverage_group_or_plan_id
+    LEFT OUTER JOIN claims.coverage_date cd ON cd.coverage_date_id = mcl.coverage_date_id
+    WHERE benefit_begin < NOW() AND benefit_end > NOW();
\ No newline at end of file