[dbsrgits/SQL-Abstract.git] / t / 11parser.t

use strict;
use warnings;

use Test::More;
use Test::Warn;
use SQL::Abstract::Tree;

my $sqlat = SQL::Abstract::Tree->new;
is_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "a"
            ]
          ],
          [
            "-LITERAL",
            [
              "b.*"
            ]
          ],
          [
            "-LITERAL",
            [
              "*"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "-LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "-LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'simple statement parsed correctly');

is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    "SELECT",
    [
      [
        "-LITERAL",
        [
          "*"
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-PAREN",
            [
              [
                "SELECT",
                [
                  [
                    "-LITERAL",
                    [
                      "*"
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "-LITERAL",
                    [
                      "foobar"
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "foo"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "-LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "-LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'subquery statement parsed correctly');

is_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[users_roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me].[role_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles_permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role_permissions].[role_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions].[permission_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissionscreens]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission_screens]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission_screens].[permission_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[screens]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission_screens].[screen_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "-PAREN",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "[me].[user_id]"
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ]
], 'real life statement 1 parsed correctly');

is_deeply($sqlat->parse("CASE WHEN FOO() > BAR()"), [
  [
    "-MISC",
    [
      [
        "-LITERAL",
        [
          "CASE"
        ]
      ],
      [
        "-LITERAL",
        [
          "WHEN"
        ]
      ]
    ]
  ],
  [
    ">",
    [
      [
        "FOO",
        [
          [
            "-PAREN",
            []
          ]
        ]
      ],
      [
        "BAR",
        [
          [
            "-PAREN",
            []
          ]
        ]
      ]
    ]
  ]
]);


is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "x"
            ]
          ],
          [
            "-LITERAL",
            [
              "y"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "IN",
        [
          [
            "-LITERAL",
            [
              "x"
            ]
          ],
          [
            "-PAREN",
            [
              [
                "-LIST",
                [
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Lists parsed correctly');

is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo LIKE ? DESC, ickk ASC'), [
  [
    "SELECT",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "bar"
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "+"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ]
                ]
              ],
            ]
          ],
          [
            "-LITERAL",
            [
              "oomph"
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "-"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ]
              ],
            ]
          ],
          [
            "-LITERAL",
            [
              "unf"
            ]
          ],
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "baz.g"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "/"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ]
              ],
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "buzz"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "*"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      0
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-DESC",
            [
              [
                "LIKE",
                [
                  [
                    "-LITERAL",
                    [
                      "foo"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
            ]
          ],
          [
            "-ASC",
            [
              [
                "-LITERAL",
                [
                  "ickk"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Crazy ORDER BY parsed correctly');

is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
  [
    "-LITERAL",
    [
      "META"
    ]
  ],
  [
    "SELECT",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "*"
            ]
          ],
          [
            "-LITERAL",
            [
              "*"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-PAREN",
            [
              [
                "SELECT",
                [
                  [
                    "-LIST",
                    [
                      [
                        "-LITERAL",
                        [
                          "*"
                        ]
                      ],
                      []
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-LITERAL",
                        [
                          "foobar"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "baz"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "buzz"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "foo"
            ]
          ],
          [
            "-LITERAL",
            [
              "bar"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "NOT",
            []
          ],
          [
            "NOT",
            []
          ],
          [
            "NOT EXISTS",
            [
              [
                "-PAREN",
                [
                  [
                    "SELECT",
                    [
                      [
                        "-LIST",
                        [
                          [
                            "-LITERAL",
                            [
                              "'cr"
                            ]
                          ],
                          [
                            "-LITERAL",
                            [
                              "ap'"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-MISC",
            [
              [
                "=",
                [
                  [
                    "-LITERAL",
                    [
                      "foo.a"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
              [
                "-LITERAL",
                [
                  "STUFF"
                ]
              ],
            ],
          ],
          [
            'moar',
            [
              [
                '-PAREN',
                [
                  [
                    '-LITERAL',
                    [
                      'stuff'
                    ]
                 ]
                ]
              ]
            ]
          ],
          [
            "NOT",
            [
              [
                "-PAREN",
                [
                  [
                    "LIKE",
                    [
                      [
                        "-LITERAL",
                        [
                          "foo.b"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "'station'"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ]
            ]
          ],
          [
            'IN',
            [
              [
                '-LITERAL',
                [
                  'z',
                ],
              ],
              [
                '-PAREN',
                [
                  [
                    '-PAREN',
                    [
                      [
                        '-LIST',
                        [
                          [
                            '-LITERAL',
                            [
                              '1'
                            ]
                          ],
                          [
                            '-LITERAL',
                            [
                              '2'
                            ]
                          ],
                        ],
                      ],
                    ],
                  ],
                ],
              ],
            ],
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "a"
                ]
              ],
              [
                "-LITERAL",
                [
                  "b"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [],
          []
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x1"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x2"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ]
                ]
              ],
            ],
          ],
          [
                "-DESC",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-DESC",
                        [
                          [
                            "max",
                            [
                              [
                                "-PAREN",
                                [
                                  [
                                    "-LITERAL",
                                    [
                                      "y"
                                    ]
                                  ]
                                ]
                              ]
                            ],
                          ]
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "x"
                        ]
                      ],
                  [
                    "-LITERAL",
                    [
                      "z"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Deliberately malformed SQL parsed "correctly"');


# test for recursion warnings on huge selectors
my @lst = ('AA' .. 'zz');
#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
warnings_are {
  my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
  my $tree = $sqlat->parse($sql);

  is_deeply( $tree, [
    [
      "SELECT",
      [
        [
          "-LIST",
          [
            (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "-LITERAL",
          [
            "foo"
          ]
        ]
      ]
    ]
  ], 'long list parsed correctly');

  is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
} [], 'no recursion warnings on insane SQL';

done_testing;
Commit	Line	Data
d49e5323	1	use strict;
	2	use warnings;
	3
3be357b0	4	use Test::More;
0f9a26cb	5	use Test::Warn;
d49e5323	6	use SQL::Abstract::Tree;
d49e5323	7
408cbda3	8	my $sqlat = SQL::Abstract::Tree->new;
6f2a5b66	9	is_deeply($sqlat->parse("SELECT a, b., FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	10	[
6f2a5b66	11	"SELECT",
958f1ce4	12	[
958f1ce4	13	[
6f2a5b66	14	"-LIST",
958f1ce4	15	[
958f1ce4	16	[
6f2a5b66	17	"-LITERAL",
958f1ce4	18	[
6f2a5b66	19	"a"
	20	]
	21	],
	22	[
	23	"-LITERAL",
958f1ce4	24	[
6f2a5b66	25	"b.*"
	26	]
	27	],
	28	[
	29	"-LITERAL",
958f1ce4	30	[
6f2a5b66	31	"*"
958f1ce4	32	]
	33	]
	34	]
	35	]
6f2a5b66	36	]
	37	],
	38	[
	39	"FROM",
958f1ce4	40	[
958f1ce4	41	[
6f2a5b66	42	"-LITERAL",
958f1ce4	43	[
6f2a5b66	44	"foo"
958f1ce4	45	]
	46	]
	47	]
	48	],
	49	[
	50	"WHERE",
	51	[
	52	[
	53	"AND",
	54	[
	55	[
	56	"=",
	57	[
	58	[
6f2a5b66	59	"-LITERAL",
958f1ce4	60	[
	61	"foo.a"
	62	]
	63	],
	64	[
6f2a5b66	65	"-LITERAL",
958f1ce4	66	[
	67	1
	68	]
	69	]
	70	]
	71	],
	72	[
	73	"LIKE",
	74	[
	75	[
6f2a5b66	76	"-LITERAL",
958f1ce4	77	[
	78	"foo.b"
	79	]
	80	],
	81	[
6f2a5b66	82	"-LITERAL",
958f1ce4	83	[
	84	"'station'"
	85	]
	86	]
	87	]
	88	]
	89	]
	90	]
	91	]
	92	]
	93	], 'simple statement parsed correctly');
54750c7a	94
6f2a5b66	95	is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	96	[
6f2a5b66	97	"SELECT",
958f1ce4	98	[
958f1ce4	99	[
6f2a5b66	100	"-LITERAL",
958f1ce4	101	[
6f2a5b66	102	"*"
958f1ce4	103	]
958f1ce4	104	]
6f2a5b66	105	]
	106	],
	107	[
	108	"FROM",
958f1ce4	109	[
958f1ce4	110	[
6f2a5b66	111	"-MISC",
958f1ce4	112	[
958f1ce4	113	[
6f2a5b66	114	"-PAREN",
958f1ce4	115	[
	116	[
	117	"SELECT",
	118	[
	119	[
6f2a5b66	120	"-LITERAL",
958f1ce4	121	[
	122	"*"
	123	]
	124	]
	125	]
	126	],
	127	[
	128	"FROM",
	129	[
	130	[
6f2a5b66	131	"-LITERAL",
958f1ce4	132	[
	133	"foobar"
	134	]
	135	]
	136	]
	137	]
	138	]
6f2a5b66	139	],
	140	[
	141	"-LITERAL",
	142	[
	143	"foo"
	144	]
958f1ce4	145	]
	146	]
	147	]
	148	]
	149	],
	150	[
	151	"WHERE",
	152	[
	153	[
	154	"AND",
	155	[
	156	[
	157	"=",
	158	[
	159	[
6f2a5b66	160	"-LITERAL",
958f1ce4	161	[
	162	"foo.a"
	163	]
	164	],
	165	[
6f2a5b66	166	"-LITERAL",
958f1ce4	167	[
	168	1
	169	]
	170	]
	171	]
	172	],
	173	[
	174	"LIKE",
	175	[
	176	[
6f2a5b66	177	"-LITERAL",
958f1ce4	178	[
	179	"foo.b"
	180	]
	181	],
	182	[
6f2a5b66	183	"-LITERAL",
958f1ce4	184	[
	185	"'station'"
	186	]
	187	]
	188	]
	189	]
	190	]
	191	]
	192	]
	193	]
	194	], 'subquery statement parsed correctly');
54750c7a	195
6f2a5b66	196	is_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [
958f1ce4	197	[
6f2a5b66	198	"SELECT",
958f1ce4	199	[
958f1ce4	200	[
6f2a5b66	201	"-LIST",
958f1ce4	202	[
958f1ce4	203	[
6f2a5b66	204	"-LITERAL",
	205	[
	206	"[screen].[id]"
	207	]
	208	],
	209	[
	210	"-LITERAL",
	211	[
	212	"[screen].[name]"
	213	]
	214	],
	215	[
	216	"-LITERAL",
	217	[
	218	"[screen].[section_id]"
	219	]
	220	],
	221	[
	222	"-LITERAL",
	223	[
	224	"[screen].[xtype]"
	225	]
958f1ce4	226	]
	227	]
	228	]
6f2a5b66	229	]
	230	],
	231	[
	232	"FROM",
958f1ce4	233	[
958f1ce4	234	[
6f2a5b66	235	"-MISC",
958f1ce4	236	[
958f1ce4	237	[
6f2a5b66	238	"-LITERAL",
	239	[
	240	"[users_roles]"
	241	]
	242	],
	243	[
	244	"-LITERAL",
	245	[
	246	"[me]"
	247	]
958f1ce4	248	]
	249	]
	250	]
	251	]
	252	],
	253	[
6f2a5b66	254	"JOIN",
958f1ce4	255	[
958f1ce4	256	[
6f2a5b66	257	"-MISC",
958f1ce4	258	[
958f1ce4	259	[
6f2a5b66	260	"-LITERAL",
958f1ce4	261	[
6f2a5b66	262	"[roles]"
958f1ce4	263	]
	264	],
	265	[
6f2a5b66	266	"-LITERAL",
958f1ce4	267	[
6f2a5b66	268	"[role]"
958f1ce4	269	]
	270	]
	271	]
	272	]
	273	]
6f2a5b66	274	],
958f1ce4	275	[
6f2a5b66	276	"ON",
958f1ce4	277	[
958f1ce4	278	[
6f2a5b66	279	"=",
958f1ce4	280	[
958f1ce4	281	[
6f2a5b66	282	"-LITERAL",
958f1ce4	283	[
6f2a5b66	284	"[role].[id]"
958f1ce4	285	]
	286	],
	287	[
6f2a5b66	288	"-LITERAL",
958f1ce4	289	[
6f2a5b66	290	"[me].[role_id]"
958f1ce4	291	]
958f1ce4	292	]
6f2a5b66	293	]
	294	]
	295	]
	296	],
	297	[
	298	"JOIN",
	299	[
	300	[
	301	"-MISC",
958f1ce4	302	[
958f1ce4	303	[
6f2a5b66	304	"-LITERAL",
958f1ce4	305	[
6f2a5b66	306	"[roles_permissions]"
958f1ce4	307	]
6f2a5b66	308	],
958f1ce4	309	[
6f2a5b66	310	"-LITERAL",
958f1ce4	311	[
6f2a5b66	312	"[role_permissions]"
958f1ce4	313	]
	314	]
	315	]
	316	]
6f2a5b66	317	]
	318	],
	319	[
	320	"ON",
958f1ce4	321	[
958f1ce4	322	[
6f2a5b66	323	"=",
958f1ce4	324	[
958f1ce4	325	[
6f2a5b66	326	"-LITERAL",
958f1ce4	327	[
6f2a5b66	328	"[role_permissions].[role_id]"
	329	]
	330	],
	331	[
	332	"-LITERAL",
	333	[
	334	"[role].[id]"
958f1ce4	335	]
	336	]
	337	]
	338	]
	339	]
	340	],
	341	[
6f2a5b66	342	"JOIN",
958f1ce4	343	[
958f1ce4	344	[
6f2a5b66	345	"-MISC",
958f1ce4	346	[
958f1ce4	347	[
6f2a5b66	348	"-LITERAL",
958f1ce4	349	[
6f2a5b66	350	"[permissions]"
958f1ce4	351	]
	352	],
	353	[
6f2a5b66	354	"-LITERAL",
	355	[
	356	"[permission]"
	357	]
	358	]
	359	]
	360	]
	361	]
	362	],
	363	[
	364	"ON",
	365	[
	366	[
	367	"=",
	368	[
	369	[
	370	"-LITERAL",
	371	[
	372	"[permission].[id]"
	373	]
	374	],
	375	[
	376	"-LITERAL",
	377	[
	378	"[role_permissions].[permission_id]"
	379	]
	380	]
	381	]
	382	]
	383	]
	384	],
	385	[
	386	"JOIN",
	387	[
	388	[
	389	"-MISC",
	390	[
	391	[
	392	"-LITERAL",
	393	[
	394	"[permissionscreens]"
	395	]
	396	],
	397	[
	398	"-LITERAL",
	399	[
	400	"[permission_screens]"
	401	]
	402	]
	403	]
	404	]
	405	]
	406	],
	407	[
	408	"ON",
	409	[
	410	[
	411	"=",
	412	[
	413	[
	414	"-LITERAL",
	415	[
	416	"[permission_screens].[permission_id]"
	417	]
418	],
419	[
420	"-LITERAL",
421	[
422	"[permission].[id]"
423	]
424	]
425	]
426	]
427	]
428	],
429	[
430	"JOIN",
431	[
432	[
433	"-MISC",
434	[
435	[
436	"-LITERAL",
437	[
438	"[screens]"
439	]
440	],
441	[
442	"-LITERAL",
443	[
444	"[screen]"
445	]
446	]
447	]
448	]
449	]
450	],
451	[
452	"ON",
453	[
454	[
455	"=",
456	[
457	[
458	"-LITERAL",
459	[
460	"[screen].[id]"
461	]
462	],
463	[
464	"-LITERAL",
465	[
466	"[permission_screens].[screen_id]"
467	]
468	]
469	]
470	]
471	]
472	],
473	[
474	"WHERE",
475	[
476	[
477	"-PAREN",
478	[
479	[
480	"=",
481	[
482	[
483	"-LITERAL",
484	[
485	"[me].[user_id]"
486	]
487	],
488	[
489	"-PLACEHOLDER",
490	[
491	"?"
492	]
493	]
494	]
495	]
496	]
497	]
498	]
499	],
500	[
501	"GROUP BY",
502	[
503	[
504	"-LIST",
505	[
506	[
507	"-LITERAL",
508	[
509	"[screen].[id]"
510	]
511	],
512	[
513	"-LITERAL",
958f1ce4	514	[
	515	"[screen].[name]"
	516	]
	517	],
	518	[
6f2a5b66	519	"-LITERAL",
958f1ce4	520	[
	521	"[screen].[section_id]"
	522	]
	523	],
	524	[
6f2a5b66	525	"-LITERAL",
958f1ce4	526	[
	527	"[screen].[xtype]"
	528	]
	529	]
	530	]
	531	]
	532	]
	533	]
	534	], 'real life statement 1 parsed correctly');
1bb3956e	535
08e16360	536	is_deeply($sqlat->parse("CASE WHEN FOO() > BAR()"), [
	537	[
	538	"-MISC",
	539	[
	540	[
	541	"-LITERAL",
	542	[
	543	"CASE"
	544	]
	545	],
	546	[
	547	"-LITERAL",
	548	[
	549	"WHEN"
	550	]
	551	]
	552	]
	553	],
	554	[
	555	">",
	556	[
	557	[
	558	"FOO",
	559	[
	560	[
	561	"-PAREN",
	562	[]
	563	]
	564	]
	565	],
	566	[
	567	"BAR",
	568	[
	569	[
	570	"-PAREN",
	571	[]
	572	]
	573	]
	574	]
	575	]
	576	]
	577	]);
	578
	579
6f2a5b66	580	is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
958f1ce4	581	[
6f2a5b66	582	"SELECT",
958f1ce4	583	[
958f1ce4	584	[
6f2a5b66	585	"-LIST",
958f1ce4	586	[
958f1ce4	587	[
6f2a5b66	588	"-LITERAL",
958f1ce4	589	[
6f2a5b66	590	"x"
	591	]
	592	],
	593	[
	594	"-LITERAL",
958f1ce4	595	[
6f2a5b66	596	"y"
958f1ce4	597	]
	598	]
	599	]
	600	]
6f2a5b66	601	]
	602	],
	603	[
	604	"FROM",
958f1ce4	605	[
958f1ce4	606	[
6f2a5b66	607	"-LITERAL",
958f1ce4	608	[
6f2a5b66	609	"foo"
958f1ce4	610	]
	611	]
	612	]
	613	],
	614	[
	615	"WHERE",
	616	[
	617	[
	618	"IN",
	619	[
	620	[
6f2a5b66	621	"-LITERAL",
958f1ce4	622	[
	623	"x"
	624	]
	625	],
	626	[
6f2a5b66	627	"-PAREN",
958f1ce4	628	[
958f1ce4	629	[
6f2a5b66	630	"-LIST",
958f1ce4	631	[
958f1ce4	632	[
6f2a5b66	633	"-PLACEHOLDER",
958f1ce4	634	[
	635	"?"
	636	]
	637	],
	638	[
6f2a5b66	639	"-PLACEHOLDER",
958f1ce4	640	[
	641	"?"
	642	]
	643	],
	644	[
6f2a5b66	645	"-PLACEHOLDER",
958f1ce4	646	[
	647	"?"
	648	]
	649	],
	650	[
6f2a5b66	651	"-PLACEHOLDER",
958f1ce4	652	[
	653	"?"
	654	]
	655	]
	656	]
	657	]
	658	]
	659	]
	660	]
	661	]
	662	]
	663	]
	664	], 'Lists parsed correctly');
7f2dd81e	665
c84a4321	666	is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo LIKE ? DESC, ickk ASC'), [
1ec9b9e3	667	[
	668	"SELECT",
	669	[
	670	[
	671	"-LITERAL",
	672	[
	673	"foo"
	674	]
	675	]
	676	]
	677	],
	678	[
	679	"FROM",
	680	[
	681	[
	682	"-LITERAL",
	683	[
	684	"bar"
	685	]
	686	]
	687	]
	688	],
	689	[
	690	"ORDER BY",
	691	[
	692	[
	693	"-LIST",
	694	[
	695	[
	696	"-DESC",
	697	[
	698	[
	699	"-MISC",
	700	[
	701	[
	702	"-LITERAL",
	703	[
	704	"x"
	705	]
	706	],
	707	[
	708	"-LITERAL",
	709	[
	710	"+"
	711	]
b4085a1a	712	],
	713	[
	714	"-PLACEHOLDER",
	715	[
	716	"?"
	717	]
1ec9b9e3	718	]
	719	]
	720	],
1ec9b9e3	721	]
	722	],
	723	[
	724	"-LITERAL",
	725	[
	726	"oomph"
	727	]
	728	],
	729	[
	730	"-DESC",
	731	[
	732	[
	733	"-MISC",
	734	[
	735	[
	736	"-LITERAL",
	737	[
	738	"y"
	739	]
	740	],
	741	[
	742	"-LITERAL",
	743	[
	744	"-"
	745	]
b4085a1a	746	],
	747	[
	748	"-PLACEHOLDER",
	749	[
	750	"?"
	751	]
	752	],
1ec9b9e3	753	]
1ec9b9e3	754	],
1ec9b9e3	755	]
	756	],
	757	[
	758	"-LITERAL",
	759	[
	760	"unf"
	761	]
	762	],
	763	[
	764	"-ASC",
	765	[
	766	[
	767	"-MISC",
	768	[
	769	[
	770	"-LITERAL",
	771	[
	772	"baz.g"
	773	]
	774	],
	775	[
	776	"-LITERAL",
	777	[
	778	"/"
	779	]
b4085a1a	780	],
	781	[
	782	"-PLACEHOLDER",
	783	[
	784	"?"
	785	]
	786	],
1ec9b9e3	787	]
1ec9b9e3	788	],
1ec9b9e3	789	]
	790	],
	791	[
	792	"-DESC",
	793	[
	794	[
	795	"-MISC",
	796	[
	797	[
	798	"-LITERAL",
	799	[
	800	"buzz"
	801	]
	802	],
	803	[
	804	"-LITERAL",
	805	[
	806	"*"
	807	]
	808	],
	809	[
	810	"-LITERAL",
	811	[
	812	0
	813	]
	814	]
	815	]
	816	]
	817	]
	818	],
	819	[
	820	"-DESC",
	821	[
	822	[
c84a4321	823	"LIKE",
1ec9b9e3	824	[
c84a4321	825	[
	826	"-LITERAL",
	827	[
	828	"foo"
	829	]
	830	],
	831	[
	832	"-PLACEHOLDER",
	833	[
	834	"?"
	835	]
	836	],
	837	],
	838	],
1ec9b9e3	839	]
	840	],
	841	[
	842	"-ASC",
	843	[
	844	[
	845	"-LITERAL",
	846	[
	847	"ickk"
	848	]
	849	]
	850	]
	851	]
	852	]
	853	]
	854	]
	855	]
	856	], 'Crazy ORDER BY parsed correctly');
	857
7d273452	858	is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
6f30911f	859	[
	860	"-LITERAL",
	861	[
	862	"META"
	863	]
	864	],
6f2a5b66	865	[
	866	"SELECT",
	867	[
	868	[
1ec9b9e3	869	"-MISC",
6f2a5b66	870	[
	871	[
	872	"-LITERAL",
	873	[
	874	"*"
	875	]
1ec9b9e3	876	],
	877	[
	878	"-LITERAL",
	879	[
	880	"*"
	881	]
6f2a5b66	882	]
	883	]
	884	]
	885	]
	886	],
	887	[
	888	"FROM",
	889	[
	890	[
	891	"-MISC",
	892	[
	893	[
	894	"-PAREN",
	895	[
	896	[
	897	"SELECT",
	898	[
	899	[
	900	"-LIST",
	901	[
	902	[
	903	"-LITERAL",
	904	[
	905	"*"
	906	]
	907	],
	908	[]
	909	]
	910	]
	911	]
	912	],
	913	[
	914	"FROM",
	915	[
	916	[
	917	"-MISC",
	918	[
	919	[
	920	"-LITERAL",
	921	[
	922	"foobar"
	923	]
	924	],
	925	[
	926	"-LITERAL",
	927	[
	928	"baz"
	929	]
	930	],
	931	[
	932	"-LITERAL",
	933	[
	934	"buzz"
	935	]
	936	]
	937	]
	938	]
	939	]
	940	]
	941	]
	942	],
	943	[
	944	"-LITERAL",
	945	[
946	"foo"
947	]
948	],
949	[
950	"-LITERAL",
951	[
952	"bar"
953	]
954	]
955	]
956	]
957	]
958	],
959	[
960	"WHERE",
961	[
962	[
963	"AND",
964	[
965	[
966	"NOT",
967	[]
968	],
969	[
970	"NOT",
971	[]
972	],
973	[
974	"NOT EXISTS",
975	[
976	[
977	"-PAREN",
978	[
979	[
980	"SELECT",
981	[
982	[
983	"-LIST",
984	[
985	[
986	"-LITERAL",
987	[
988	"'cr"
989	]
990	],
991	[
992	"-LITERAL",
993	[
994	"ap'"
995	]
996	]
997	]
998	]
999	]
1000	]
1001	]
1002	]
1003	]
1004	],
1005	[
b4085a1a	1006	"-MISC",
6f2a5b66	1007	[
6f2a5b66	1008	[
b4085a1a	1009	"=",
6f2a5b66	1010	[
6f30911f	1011	[
b4085a1a	1012	"-LITERAL",
6f30911f	1013	[
b4085a1a	1014	"foo.a"
6f30911f	1015	]
	1016	],
	1017	[
b4085a1a	1018	"-PLACEHOLDER",
6f30911f	1019	[
b4085a1a	1020	"?"
6f30911f	1021	]
b4085a1a	1022	],
6f30911f	1023	],
e2a120ce	1024	],
e2a120ce	1025	[
b4085a1a	1026	"-LITERAL",
	1027	[
	1028	"STUFF"
	1029	]
	1030	],
	1031	],
	1032	],
	1033	[
	1034	'moar',
	1035	[
	1036	[
	1037	'-PAREN',
e2a120ce	1038	[
e2a120ce	1039	[
b4085a1a	1040	'-LITERAL',
e2a120ce	1041	[
b4085a1a	1042	'stuff'
e2a120ce	1043	]
b4085a1a	1044	]
e2a120ce	1045	]
6f2a5b66	1046	]
	1047	]
	1048	],
	1049	[
	1050	"NOT",
	1051	[
	1052	[
	1053	"-PAREN",
	1054	[
	1055	[
	1056	"LIKE",
	1057	[
	1058	[
	1059	"-LITERAL",
	1060	[
	1061	"foo.b"
	1062	]
	1063	],
	1064	[
	1065	"-LITERAL",
	1066	[
	1067	"'station'"
	1068	]
	1069	]
	1070	]
	1071	]
	1072	]
	1073	]
	1074	]
	1075	],
	1076	[
	1077	"=",
	1078	[
	1079	[
	1080	"-LITERAL",
	1081	[
	1082	"x"
	1083	]
	1084	],
	1085	[
	1086	"-LITERAL",
	1087	[
	1088	"y"
	1089	]
	1090	]
	1091	]
	1092	],
	1093	[
7d273452	1094	'IN',
	1095	[
	1096	[
	1097	'-LITERAL',
	1098	[
	1099	'z',
	1100	],
	1101	],
	1102	[
	1103	'-PAREN',
	1104	[
	1105	[
	1106	'-PAREN',
	1107	[
	1108	[
	1109	'-LIST',
	1110	[
	1111	[
	1112	'-LITERAL',
	1113	[
	1114	'1'
	1115	]
	1116	],
	1117	[
	1118	'-LITERAL',
	1119	[
	1120	'2'
	1121	]
	1122	],
	1123	],
	1124	],
	1125	],
	1126	],
	1127	],
	1128	],
	1129	],
	1130	],
	1131	[
6f2a5b66	1132	"=",
	1133	[
	1134	[
	1135	"-LITERAL",
	1136	[
	1137	"a"
	1138	]
	1139	],
	1140	[
	1141	"-LITERAL",
	1142	[
	1143	"b"
	1144	]
	1145	]
	1146	]
	1147	]
	1148	]
	1149	]
	1150	]
	1151	],
	1152	[
	1153	"GROUP BY",
	1154	[
	1155	[
	1156	"-LIST",
	1157	[
	1158	[],
	1159	[]
	1160	]
	1161	]
	1162	]
	1163	],
	1164	[
	1165	"ORDER BY",
	1166	[
	1167	[
	1168	"-LIST",
	1169	[
	1170	[
1ec9b9e3	1171	"-ASC",
6f2a5b66	1172	[
	1173	[
	1174	"-MISC",
	1175	[
	1176	[
1ec9b9e3	1177	"-LITERAL",
6f2a5b66	1178	[
1ec9b9e3	1179	"x"
6f2a5b66	1180	]
	1181	],
	1182	[
	1183	"-LITERAL",
	1184	[
1ec9b9e3	1185	"x1"
6f2a5b66	1186	]
	1187	],
	1188	[
	1189	"-LITERAL",
	1190	[
1ec9b9e3	1191	"x2"
6f2a5b66	1192	]
	1193	],
	1194	[
	1195	"-LITERAL",
	1196	[
1ec9b9e3	1197	"y"
	1198	]
	1199	]
	1200	]
	1201	],
	1202	],
	1203	],
	1204	[
1ec9b9e3	1205	"-DESC",
	1206	[
	1207	[
	1208	"-MISC",
	1209	[
	1210	[
c84a4321	1211	"-DESC",
1ec9b9e3	1212	[
1ec9b9e3	1213	[
c84a4321	1214	"max",
1ec9b9e3	1215	[
1ec9b9e3	1216	[
6f30911f	1217	"-PAREN",
1ec9b9e3	1218	[
6f30911f	1219	[
	1220	"-LITERAL",
	1221	[
	1222	"y"
	1223	]
	1224	]
1ec9b9e3	1225	]
1ec9b9e3	1226	]
c84a4321	1227	],
c84a4321	1228	]
1ec9b9e3	1229	]
	1230	],
	1231	[
	1232	"-LITERAL",
	1233	[
c84a4321	1234	"x"
1ec9b9e3	1235	]
c84a4321	1236	],
	1237	[
	1238	"-LITERAL",
	1239	[
	1240	"z"
6f2a5b66	1241	]
	1242	]
	1243	]
	1244	]
	1245	]
	1246	]
	1247	]
	1248	]
	1249	]
	1250	]
	1251	], 'Deliberately malformed SQL parsed "correctly"');
	1252
	1253
0f9a26cb	1254	# test for recursion warnings on huge selectors
ad591616	1255	my @lst = ('AA' .. 'zz');
ad591616	1256	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	1257	warnings_are {
ad591616	1258	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	1259	my $tree = $sqlat->parse($sql);
	1260
	1261	is_deeply( $tree, [
	1262	[
	1263	"SELECT",
	1264	[
	1265	[
6f2a5b66	1266	"-LIST",
0f9a26cb	1267	[
ad591616	1268	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	1269	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	1270	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	1271	]
	1272	]
	1273	]
	1274	],
	1275	[
	1276	"FROM",
	1277	[
	1278	[
6f2a5b66	1279	"-LITERAL",
0f9a26cb	1280	[
	1281	"foo"
	1282	]
	1283	]
	1284	]
	1285	]
	1286	], 'long list parsed correctly');
	1287
	1288	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	1289	} [], 'no recursion warnings on insane SQL';
	1290
3be357b0	1291	done_testing;